Weird sqlite problem

Databases
1

I have a rather weird problem reading an sqlite database. I’m adapting a program
which on opening has to read in from the database, concatenate the fields found
into a string and add the string to an array. The original program does this just fine.

To adapt the program, I made a copy in a separate folder with everything - source
code and the database. The adaptation is purely cosmetic and at no time affects
the database part. But it crashes when it tries to read the db. Crashes as in Windows
just throws up a “program has stopped” message immediately on starting the program.

I found that the database contains a date field which is set to the null default value
of “1899-12-30” and that the crash happens when the program tries to read this.
The code fragment, which is identical in both versions, is:

fc = rs.FieldCount while Not rs.eof j = "" for i = 1 to fc // Add the fields to the string j plus the delimiter fd (";") j = j + rs.IdxField(i).StringValue + fd next Pend.Append j rs.MoveNext wend

This works perfectly in the original program using exactly the same db but
not in the copy. Any ideas?

2

Have you tried running in the debugger to see exactly when the problem occurs?

3

Yes, but the debugger won’t open the db. I put MsgBoxes in the code progressively until I found the
place where it crashed.

4

did you open the db by code, or just by putting it in the project ?
since 2017r2 or r3 putting the db in the project is not opening it when you run anymore. must be a bug…
use db.connect to open your database first.

5

The db is opened in code; it’s not in the project.

6

Is the database field name by any chance a reserved word? Like “Current_Date” or something?

That’s happened to me before, and it would crash my app.

https://www.sqlite.org/lang_keywords.html

7

Do you have both builds open at the same time using the same database file?
If so, try it with a copy of the db or have only 1 instance open.

Even if you copy, you should be sure the path is different to the database if you have both instances of your application open.

8

[quote=376019:@Derk Jochems]Do you have both builds open at the same time using the same database file?
If so, try it with a copy of the db or have only 1 instance open.

Even if you copy, you should be sure the path is different to the database if you have both instances of your application open.[/quote]

Only one instance at a time. The dbs are in different directories and the two versions use their
own copies. The copies are identical, just in different directories and each instance has the
correct path to its own db.

9

SQLIte with Xojo (I do not know elsewhere) does not like (in fact crash) when the db file is locked (when you cannot write in the file).

And that even if you simply want to red it. SQLite (or Xojo) set in the OS the .sqlite file as Open and cannot do that when the said file is locked.

Can you check that ?

10

It’s not locked.

11

[quote=375982:@Peter Rodwell]Yes, but the debugger won’t open the db. I put MsgBoxes in the code progressively until I found the
place where it crashed.[/quote]
Are you checking for a db error immediately after opening the database? The debugger should be able to open it so I’m thinking that perhaps it isn’t really being opened and connected to. Another indication of a problem is if the recordset is nil. Are you checking it immediately after it is created?

12

Yes, it checks for db.error and that recordset isn’t nil. In fact I put a msgbox in the loop
I posted above to show the string being built and it is built, up until it hits the 1898 date,
at which point it crashes.

13

With the Xojo version you use for the adapted version ?

If no, maybe something have changed in sqlite between the two Xojo versions. A check in the Release Notes may give you some lights.