No such table error sqlite

Databases
1

I have been having problems reading and writing to an sqlite database in Xojo. This has been an intermittent problem on and off for a few months. For no apparent reason, I get an error message, “No such table: table name” when I try to create a recordset. I can connect to the database without issue but cannot create a recordset. I have looked through countless pages on the forum for this issue but could not find any posts specific to this problem. Don’t understand how this code can work then not work without changing the code. db is defined as a global. Below is the code I am using: Thanks in advance for any help.

db = new SQLiteDatabase
dim rs as RecordSet
dim wInitial as String

db.DatabaseFile=GetFolderItem(gDBSourceString)

if db.Connect then
rs = db.SQLSelect(“Select * from ClientMaster order by cm_MainLastName, cm_MainFirstName”)
rs.MoveFirst

popClient.AddRow "N/A"

do until rs.EOF
  wInitial = mid(rs.Field("cm_MainFirstName").StringValue,1,1)
  
  popClient.AddRow wInitial + ". " + rs.Field("cm_MainLastName").StringValue
  
  rs.MoveNext

loop

popClient.ListIndex = 1
popService.ListIndex = 1


rs.Close

end if

2

Did you CREATE the table at some time in the past?

3

yes I did

4

Is that the exact error? If so, it implies that your SQL code is using a table called “table name”. Perhaps you have a mistake where you are building your SQL statement?

5

Hi paul - The exact message for this example is “No such table: ClientMaster” . This was working when I went to bed last night. woke up and can’t access any recordset in my application. I have recreated the entire database thinking it got corrupted and still the same problem. This has been an intermittent problem since using Xojo. The sql statements have not been changed and have all worked as recently as last night. I am very frustrated and hope you might have some ideas.

6

David

Firstly, why this line in the function:

 db = new SQLiteDatabase

If db is a global should it not be constructed and initialised at the start of the program?

I would set a breakpoint on this line:

 db.DatabaseFile=GetFolderItem(gDBSourceString)

and examine gDBSourceString. It wouldn’t surprise me if this is null or “”. If it is “” then that brings me back to the first question.

Apart from not initialising the database at the start of the program your code looks good so I don’t think it is that code. It is quite possible that gDBSourceString holds different values at different times and the program is ‘opening’ a file that either does not exist or it creates one. That would explain why you are getting a ‘table does not exist’ error.

I would strongly suggest that you look at your whole program and get the flow to be as follows:

In the App.Open event do:

gDBSourceString = InitialiseMeHereAtTheStartOfTheProgram
db = new SQLiteDatabase
db.DatabaseFile=GetFolderItem(gDBSourceString)

if not db.Connect then
  MsgBox "Could not connect to the database - " + gDBSourceString
  Quit
end if

Then, in the App.Close event:

db.Close

The above actions will ensure that the db variable is open and connected from the start of your application to its ending. Then your quoted original function can be written as:

[code]dim wInitial as String
dim rs as RecordSet

popClient.DeleteAllRows

rs = db.SQLSelect(“Select * from ClientMaster order by cm_MainLastName, cm_MainFirstName”)

if rs <> nil and not rs.eof then
popClient.AddRow “N/A”

do until rs.EOF
wInitial = mid(rs.Field(“cm_MainFirstName”).StringValue,1,1)
popClient.AddRow wInitial + ". " + rs.Field(“cm_MainLastName”).StringValue
rs.MoveNext
loop

end if

popClient.ListIndex = 1
popService.ListIndex = 1
[/code]

7

Inspect the value of db.DatabaseFile very closely. If it is nil, connect will succeed, but you’ll be pointing to an in-memory database, which obviously won’t have your table. If it is non-nil, then look closely at the path and make sure that it is the correct file. Force yourself to not make any assumptions here, or you could be missing the obvious.

8

The problem seems to be solved. I looked at db.DatabaseFile and it was nil. I implemented the code changes throughout the app and all seems to be fine. Still not sure why it worked on and off, but I think if I follow this methodology of initializing my databases, I will be all right going forward. Much thanks to everyone who took the time to help.

9

My pleasure!