System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 6bd05db31ff84223603ab6c42aad926668c076a5
Title: SQLite ADO.NET raises no errors, exceptions, etc.
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Command Resolution: Works_As_Designed
Last Modified: 2013-10-29 17:49:59
Version Found In: System.Data.SQLite.1.0.89.0
User Comments:
anonymous added on 2013-10-29 10:52:40:
I have been working in Visual Studio 2010 and 2013 to produce an Public Domain example of how to use the SQLite ADO.Net Library (along with learning how to use it myself).

I have tried both X86 and X64 versions, the Interop version, and the sqlite-netFx40-setup-bundle-x86-2010-1.0.88.0.exe version. The results are the same with all of them. I can pass a invalid SQL command to SQLite.SQLiteCommand or SQLite.SQLiteDataAdapter and it will NOT generate an error. SQLite.SQLiteErrorCode reports back "OK". I have tried Empty strings and commands that I know are bad on none-existent table in empty DataBase files. SQLite ADO.Net happily processes them and goes on about its bussiness.

EXAMPLE CODE:
--------------------------------------------------------------------------------
' PUBLIC DOMAIN:
' The author or authors of this code dedicate any and all copyright interest in this 
' code to the public domain. The author makes this dedication for the benefit of the 
' public at large and to the detriment of the author's heirs and successors. The author 
' intends this dedication to be an overt act of relinquishment in perpetuity of all 
' present and future rights to this code under copyright law.
Public Class Form1
    Public Sql3ErrorMsg As String
    Public Sql3ErrorCode As String
    Private ProcessTableName As Boolean = False
    Private Sql3FileName As String

    Public Function Sql3GetDataTable(ByVal DB As String, ByVal Sql As String) As DataTable
        ' This reference is for MS SQL but with a bit of effort it translate to SQLite.
        ' http://msdn.microsoft.com/en-us/library/vstudio/fbk67b6z(v=vs.100).aspx
        ' The biggest problem is there are numerous mistakes in the Microsoft's example code.
        ' That should be no surprise. Be sure to read the user comments.
        ' The principle difference in using SQLite and MS SQL is that you must prefix all the keywords 
        ' with "SQLite.SQLite". Yes, you are correct: It is redundant. 
        '
        ' I will not try to explain the use of the "DataAdapter", "CommandBuilder" or "DataTable" because
        ' I do not understand them. All I can tell you is that after HOURS of trial and error coding
        ' I found that this sequence works. I have tried to encapsulate it in this function. For a plain
        ' SQL command that does not produce a record set this will return an Empty DataTable (I think).
        ' For Error handling check the Public Variable: Sql3ErrorCode
        ' If it is not zero then the error message will be in: Sql3ErrorMsg
        '
        Dim T As String
        Dim DT As New DataTable()
        Dim CN_SQL3 As New SQLite.SQLiteConnection
        Dim Cmd_SQL3 As New SQLite.SQLiteCommand
        Dim X As SQLite.SQLiteErrorCode

        Sql3ErrorCode = 0
        Try
            If Dir(DB) <> "" Then
                If Sql <> "" Then
                    Sql = "Bad command  -- Realy bad command"
                    CN_SQL3.ConnectionString = "data source=" & DB
                    CN_SQL3.Open()
                    T = CN_SQL3.ResultCode.ToString
                    If CN_SQL3.State = 1 Then
                        Cmd_SQL3.CommandText = Sql
                        Cmd_SQL3.Connection = CN_SQL3
                        T = Cmd_SQL3.ToString
                        Dim AdapterTemp As New SQLite.SQLiteDataAdapter(Cmd_SQL3)
                        T = X.ToString
                        T = Cmd_SQL3.ToString
                        T = CN_SQL3.ResultCode.ToString
                        Dim BuilderTemp As New SQLite.SQLiteCommandBuilder()
                        T = CN_SQL3.ResultCode.ToString
                        BuilderTemp = New SQLite.SQLiteCommandBuilder(AdapterTemp)
                        T = CN_SQL3.ResultCode.ToString
                        DT.Locale = System.Globalization.CultureInfo.InvariantCulture
                        AdapterTemp.Fill(DT)
                        BuilderTemp.Dispose()
                        AdapterTemp.Dispose()
                    End If
                Else
                    Sql3ErrorMsg = "SQLite Query can not be an Empty String."
                    Sql3ErrorCode = -30
                End If
            Else
                If DB = "" Then
                    Sql3ErrorMsg = "SQLite Data file can not be an Empty String."
                    Sql3ErrorCode = -10
                Else
                    Sql3ErrorMsg = "SQLite Data file " & Chr(34) & DB & Chr(34) & " not found."
                    Sql3ErrorCode = -20
                End If
            End If
        Catch Ex As System.ArgumentException
            Sql3ErrorMsg = Ex.Message & Chr(13) & Chr(10) & _
                "If you get this error it may mean that the SQLite connection string is incorect."
            Sql3ErrorCode = -1
        Catch Ex As SQLite.SQLiteException
            ' One of the MAJOR PROBLEMS in SQLite ADO.Net and Microsoft's NEW fangled aproach to error
            ' handling is that there is NO WAY to check the error code from an SQLite command. This should
            ' catch any SQLite error but ti does not. If you pass a bad SQL line to this function or even
            ' any totally empty SQL string then it will happily process it and keep right on going without 
            ' raising an exception.
            '
            Sql3ErrorMsg = Ex.Message
            Sql3ErrorCode = Ex.ResultCode
        End Try
        Return DT
    End Function
....
End Class

anonymous added on 2013-10-29 14:02:47: (text/x-fossil-plain)
Changed level to minor:

It is the statement "AdapterTemp.Fill(DT)" that finally raises an error. However it does NOT generate an error if the SQL string is empty (i.e. SQL="") or if there is a valid SQL statement with garbage appended at the end (i.e. SQL = "Select * from Employees; garbage appended at the end")

This project will be released to the Public domain on my web site:
http://www.keywild.com in the next few days. 

I have some more error testing and spell checking to be completed.

regards,

Lewis Balentine
lewis@keywild.com

mistachkin added on 2013-10-29 17:45:27: (text/x-fossil-wiki)
First, an empty string is technically valid.  Though, it will not produce any
results.

Secondly, I get the following error from SQLiteCommand (using Eagle):

<verbatim>
% setupDb test-1.1.db
System#Data#SQLite#SQLiteConnection#25897
% sql exec $db "SELECT * FROM sqlite_master; garbage appended at the end"
Error, line 1: System.Data.SQLite.SQLiteException (0x80004005): SQL logic error or missing database
near "garbage": syntax error
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:line 867
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 376
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 388
   at System.Data.SQLite.SQLiteDataReader.NextResult() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:line 1258
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 852
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 834
   at Eagle._Commands.Sql.Execute(Interpreter interpreter, IClientData clientData, ArgumentList arguments, Result& result) in c:\dev\mistachkin\Eagle\Library\Commands\Sql.cs:line 661
</verbatim>

mistachkin added on 2013-10-29 17:48:06: (text/x-fossil-wiki)
FYI, in order to start the Eagle Shell and reproduce the above results, the command line is (from the root of the System.Data.SQLite checkout):

<verbatim>
Externals\Eagle\bin\EagleShell.exe -noexit -preinit "set build_directory {C:\path\to\latest\System.Data.SQLite\binaries}" -file Tests\empty.eagle
</verbatim>

mistachkin added on 2013-10-29 17:49:59: (text/x-fossil-plain)
Also, the SQLiteDataAdapter class does not do anything with the provided
SQLiteCommand object until the Fill method is called.  This is also by
design.