Problems using SQLITE .Net Data Provider (Windows)

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Problems using SQLITE .Net Data Provider (Windows)

Ken & Deb Allen
I recently downloaded the (Finisar) .Net Data Provider for SQLITE  
from SourceForge in order to use this in a C# applications under  
Windoes XP. I had a few small problems getting the database created  
at all via .Net, but that is working, and I was able to also define  
the schema for my database using CREATE TABLE, CREATE TRIGGER and  
CREATE INDEX statements.

Issue #1: I had an original problem where defining a table with the  
first column as "Record ID INTGER PRIMARY KEY AUTOINCREMENT NOT NULL"  
works fine when I execute it from the command line, but it chokes on  
the "AUTOINCREMENT" word when executed via .Net. It was suggested  
that I simply remove the word, which I did, and which permitted the  
command to execute without error, but it is my understanding that the  
behaviour of the table is different with and without this keyword.

Issue #2: Another problem is attempting to use UNICODE in the  
database. If, from the command line, the first command after I open  
the database is [PRAGMA encoding="UTF-16"], and then I create the  
schema, when I open the database later from the command line and  
execute the command [PRAGMA encoding;], the result is reported as  
"UTF-16le", as expected. When I do the same thing from my C# code,  
the command line reports the encoding as "UTF-8".

Issue #3: If I define a table as [CREATE TABLE Events (EventID INT  
NOT NULL, EventName VARCHAR(32) NOT NULL, EventLongName VARCHAR(128)  
NOT NULL, CreationDate DATETIME NOT NULL] via the C# code, then when  
I attempt to add records to the table I get the following exception.  
This same command executed from the SQLITE command line executes  
without an error and adds the record to the table.

Anyone have any thoughts on why these errors may be reported by the  
SQLITE .Net Data Provider?

By the way, I am using the SQLITE 3.2.2 DLL file for Windows  
downloaded from the SQLITE web site and (I believe, since I am home  
now) version 1.7 of the .Net Data Provider.

-ken


Database creation results;

MSDE database successfully created! (765.6397 milliseconds)
SQLITE database successfully created! (93.7518 milliseconds)
VistaDB database successfully created! (62.5012 milliseconds)
Firebird database successfully created! (1109.3963 milliseconds)

—> System.ApplicationException; 5QLITE; error inserting [Event]; no  
such column; current_timestamp

INSERT INTO Events(EventID; EventName; EventLongName; CreationDate)  
VALUE5(1; 'Event #1', 'Long Event Name #1'; current.

Finisar.5QLite.5QLiteException; no such column; current_timestamp

al:Finisar.SQLite.sqlite3.Throw()

al:Finisar,SQLite,sqlite3,CheckOK()

al: Finisar.SQLite.sqlite3.compile(String zSql)

al:Finisar.5QLite.One5QL5tatement.Compile()

al:Finisar.5QLite.5QLiteCommand.E?;ecul:eNonQuery()

al: DatabaseE valuation. 5qliteDatabase.Populal:eEventInformation
(Int32 MaxEventID) in
f;\testing\databaseevaluation
\databaseevaluation^sqlitedatabase.cs;linell6

— End of inner exception stack trace —

at DatabaseE valuation. 5qliteDatabase.PopulateEventInformation(Int32  
MaxEventID) in
f;\testing\databaseevaluation\databaseevaluation\sqlitedatabase.cs;  
line 130

at DatabaseE valuation.  
DatabaseEvaluationForm,buttonPrepareDatabases_Clicb(Object sender;  
EventArgs e) in
f;\testing\databaseevaluation\databaseevaluation
\databaseevaluationform,cs; line 171

i ^...........^.............^ i


Reply | Threaded
Open this post in threaded view
|

RE: Problems using SQLITE .Net Data Provider (Windows)

Robert Simpson
> -----Original Message-----
> From: Ken & Deb Allen [mailto:[hidden email]]
> Sent: Monday, August 15, 2005 4:44 PM
> To: [hidden email]
> Subject: [sqlite] Problems using SQLITE .Net Data Provider (Windows)
>
> I recently downloaded the (Finisar) .Net Data Provider for
> SQLITE from SourceForge in order to use this in a C#
> applications under Windoes XP. I had a few small problems
> getting the database created at all via .Net, but that is
> working, and I was able to also define the schema for my
> database using CREATE TABLE, CREATE TRIGGER and CREATE INDEX
> statements.

Hopefully you posted this on the Sourceforge forums for the Finisar SQLite
library ... I'm afraid the issues you describe are all beyond the scope of
this mailing list.

However, to answer your question (or at least attempt to) ...

If I recall correctly, one of the things the Finisar library does is
pre-parse (as best it can) your SQL statements before passing them to the
underlying database engine.  It sounds like all your issues are related to
the parser.

If you (or anyone else for that matter) is planning on switching to .NET 2.0
and Visual Studio 2005, it might be worth your while to check out the .Net
2.0 provider currently complete and being beta-tested:

http://sourceforge.net/projects/sqlite-dotnet2

Robert