Puzzling SQLite behavior

Databases
1

I have a SQLite database that has been running successfully for a couple of years. One of the tables has a column named “isActive” which is defined as an integer value.

All of a sudden, any attempt to do an UPDATE of that column fails with an error like no such column: isActive. Yet, I am able to SELECT on that column and get results with no error.

(I also get the same error when I try to EXPLAIN on the UPDATE command.)

SQLite Manager reports that the database integrity is OK, and an older backup of the database does not exhibit the problem.

Any ideas about what might be going on would be MOST welcome…

2

have you used a database tool (Navicat, SQLManger, etc) to see if those tool(s) have the same issue or if the issue is Xojo related? I am curious where the issue is popping up. Have you switched which version of Xojo you are using for the project/app? updated plugins or controls?

3

Thanks for the quick response, Scott.

I’ve tested it using SQLabs SQLiteManager 4.3.5, and the problem is present there, without my Xojo app in the loop. Using the same tool and queries shows no problem on the backup db, however.

4

That is a good sign (I know you dont see it that way). We have rules out Xojo and all its goodness.

Have you tried creating a new SQLite file, and copying all the data into the new file and see if the new file has the same issues? the sqlite file might be corrupt and a new file might fix it. Also, you could try SQLiteDoctor and see if it finds issue with the SQLite file.

5

Actually, I do see it that way. I was happy that it’s not my app/Xojo. (Of course, it could still be Xojo since Marco builds SQLite Manager wit it, but since the backup from a few days ago is OK, I think Xojo is off the hook…)

I’ll try your other suggestions…

Thanks.

6

try sqlite3 at the OS X command line
thats just the sqlite command line tool written in C / C++ compiled for OS X

7

Sadly, easier said than done. I first have to download the >3 GB database from our customer network to do that with my Mac. My remote access only permits me to use a Windows machine over which I have very little control. I’ll have to wait until they are more or less done for the day, to do it…

Thank you both for the suggestions…

8

[quote=239409:@Peter Truskier]I have a SQLite database that has been running successfully for a couple of years. One of the tables has a column named “isActive” which is defined as an integer value.

All of a sudden, any attempt to do an UPDATE of that column fails with an error like no such column: isActive. Yet, I am able to SELECT on that column and get results with no error.
.[/quote]

Try what happens when you put the field name between qoutes.

Also what may help if you show us the exact SQL command that triggers the error.

9

Same error

This works:

SELECT * FROM tblItems WHERE isActive=1

This does not:

UPDATE tblItems SET isActive=0 WHERE id=12345

It returns the error: no such column: isActive

Absent some sort of corruption, it makes no sense to me…

10

[quote=239446:@Peter Truskier]Same error

This works:

SELECT * FROM tblItems WHERE isActive=1

This does not:

UPDATE tblItems SET isActive=0 WHERE id=12345

It returns the error: no such column: isActive

Absent some sort of corruption, it makes no sense to me…[/quote]

Do you use database triggers? It could be that a trigger is responsible for the error and not the UPDATE statement.

11

Now THAT is an interesting possibility. Thanks for the idea. I’ll look into it a bit further…

12

Thank you, Marc! That was it! I had modified a trigger, and inadvertently forgot to include new. as in:

new.isActive.

While kicking myself for the error, I do so appreciate everybody’s quick help!

13

glad we could help.