System.Data.SQLite
View Ticket
Not logged in
 Ticket UUID: 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): % 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  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): Externals\Eagle\bin\EagleShell.exe -noexit -preinit "set build_directory {C:\path\to\latest\System.Data.SQLite\binaries}" -file Tests\empty.eagle  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.