SQLite Error: Could not finalize statement

NETMF & Gadgeteer
1

I am trying to execute the following sql query:

SELECT * FROM tblHistory WHERE Timestamp > ‘10/07/2010 09:20:52’ ORDER BY Timestamp ASC

I was able to successfully execute this query:

SELECT * FROM tblHistory LIMIT 1

So I know the database is ok. I am getting the following message:

[b]A first chance exception of type ‘GHIElectronics.NETMF.SQLite.SQLiteException’ occurred in GHIElectronics.NETMF.SQLite.dll

Additional information: Could not finalize SQL statement.[/b]

Any ideas what my problem is? Also, how do I search the forum posts? A few days ago there was a drop down button labeled ‘Search’ under ‘Forum’ in the top menu. But now I only see ‘Unread Messages’ and ‘Users’. I don’t see any other place to search posts.

2

One more thing Timestamp is of the type DATETIME.

3

This is an internal error message that comes from SQLite engine. I wish I can give you more information. probably you could find an answer at SQLite website.
[url]http://www.sqlite.org/[/url]

SQLite on ChipworkX version is 3.6.13

4

Search forum is under help menu

5

Can you please provide couple of full rows of the database and the sample of your code?

6

I am using this to create the database:

CREATE TABLE tblHistory (Timestamp DATETIME, FlowRate FLOAT, TankTempUpper FLOAT, TankTempLower FLOAT, TotalGalUsed FLOAT, ElementStateUpper INT, ElementStateLower INT)

I have successfully used the following commands to read data from the DB:

SELECT * FROM tblHistory
SELECT * FROM tblHistory LIMIT 1

But get errors on the following:

SELECT * FROM tblHistory WHERE Timestamp > ‘10/07/2010 12:26:25’ ORDER BY Timestamp ASC

I have a feeling the error is with the command string itself probably relating to the DATETIME. But I couldn’t find any issues with it looking at the SQLite website.

The .NET functions I am using are:

Database.ExecuteQuery(…)
Database.ExecuteNonQuery(…)

Also, I open the database upon entering a method and close it (in a finally block) before leaving and don’t have problems until I use the SELECT statement above.

Has anyone tried a statement like this before? … A SELECT … WHERE on a DATETIME object?

7

This maybe the key?

Can you put together a real small example to repro so we can try on our end?

8

I have tried it on MS SQL and it works fine. Will try it on ChipworkX with SQLite later.

Also, you might be interested in my SQLite wrapper on Fezzer:
http://www.fezzer.com/project/192/sqlite-wrapper/

9

One other thing that you can try, that might shed some light.

See what Database.GetLastError() returns right after you caught the exception.

10

Thanks for the help so far Architect. I have put together a solution that creates a database with a single table and performs a few operations on it. What is repro and how do I get the solution out there so someone else can try this?

11

you can upload it on wiki and post the link here

12

We are looking for simple example, about 10 lines of code that you can post right here. To repro (reproduce) the problem

13

Matt,

Have you figure it out? What was the issue?

14

I didn’t exactly figure out what it was but did fix the issue…so here we go.

SQLite does not have a Date/Time datatype, it will convert it to the type INTEGER. Which gave me that error, so I decided to store the Date/Time ticks as a 64-bit value. Well…SQLite, at least the .NET Micro port, doesn’t seem to support 64-bit values. They go into the database without errors, but come out wrong.

My work around was to create my own reference date (the first second of 2010) and converted the DateTime.Ticks into seconds (divide by 10,000). Then I stored them in the database as unsigned 32-bit integers. This works for me because I only need a minimum resolution of seconds. If my calculations are correct, this should work for about 136 years before it overflows.

Maybe this will help someone, maybe not, but it worked for me.

15

Thanks Matt

16

It definitely helps. I remember reading that date is not supported, but your statement that creation of the table went through OK threw me off. Good tip!