Need SQLite Help - (Moved)

[Trax]

I am just starting in on SQLite and to say the least am a little frustrated. I cannot figure out why I keep getting errors on a simple insert. Can someone help me out?

expandcollapse popup

#include <SQLite.au3>

Local $hDB, $sSQL, $Result
Local Const $SQLITE_DLL = "C:\Windows\System\sqlite3.dll"
Local Const $SQLITE_Database_Name = "Test.sq3"

_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then
    MsgBox($MB_ICONERROR, "SQLite Startup Error", "Unable to start SQLite. Check existence of DLL")
    Exit
EndIf

If FileExists ($SQLITE_Database_Name) Then
    FileDelete($SQLITE_Database_Name)
EndIf

$hDB = _SQLite_Open($SQLITE_Database_Name)
If $hDB = 0 Then
    MsgBox(0, "SQLite Open Error", "Unable to start SQLite. Check existence of DLL")
    Exit
EndIf

$sSQL = "CREATE TABLE MeterReadings (Id INT PRIMARY KEY, Meter INT not null, Date CHAR not null, Time CHAR not null, Reading REAL not null, Error not null);"
$Result = _SQLite_Exec($hDB, $sSQL)
If $Result <> $SQLITE_OK Then
    MsgBox(0, "SQLite Create Table Error", "Result: " & $Result & "    @error: " & @error)
EndIf

Local Const $Date = "2020/03/06", $Time = "09:59:00", $StdStr = "INSERT INTO MeterReadings(Meter,Date,Time,Reading,Error) VALUES("
$sSQL = $StdStr & 1 & "," & $Date & "," & $Time & "," & 194.0 & "," & "Y" & ");"
$Result = _SQLite_Exec(-1, $sSQL)
If $Result <> $SQLITE_OK Then
    MsgBox(0, "SQLite Log Data Error", "Result: " & $Result & "    @error: " & @error)
    MsgBox(0,"",$sSql)
EndIf

_SQLite_Close()
_SQLite_Shutdown()

 

[Danp2]

What about your primary key? Don't you need to set it's value when performing the insert?

[Nine]

Also embed string with single quotes like this :

Local Const $Date = "2020/03/06", $Time = "09:59:00", $StdStr = "INSERT INTO MeterReadings(Id,Meter,Date,Time,Reading,Error) VALUES("
$sSQL = $StdStr & "1,1,'" & $Date & "','" & $Time & "'," & 194.0 & ",'" & "Y" & "');"

 

[Trax]

Embedding strings with a single quote seemed to work. I was able to insert 10 records. The Primary Key is a mystery to me. It is supposed to be unique in the table but when I queried the 10 records the Id showed up as being empty in all 10 records so I really don't understand what it is.  Still get an error in my "real" program but at least now I know it can be done.

[jchd]

Back home.

Please post the SQL statements you use. Is the PK declared as INTEGER PRIMARY KEY ?

[Nine]

you havn't declare the primary key as not null...

[Trax]

Here is the Cteate "CREATE TABLE MeterReadings (Id INT PRIMARY KEY, Meter INT not null, Date CHAR not null, Time CHAR not null, Reading REAL not null, Error not null);"

So I can see that I need to make it not null but the insert command is one of the things that is throwing me. Enclosing strings in single quotes did the trick in the quick and dirty test program  but I never updated the PK"

Local Const $Date = "2020/03/06", $StdStr = "INSERT INTO MeterReadings(Meter,Date,Time,Reading,Error) VALUES("
    $sSQL = $StdStr & 1 & ",'" & $Date & "','" & $Time & "'," & 194.0 & ",'" & "Y" & "');"

 

The one I can't get to work is in the "full version" in a loop"

For $Index = 1 To $NumMeters
        $sSQL = $InsertString & 1 & ",'" & _NowDate() & "','" & _NowTime(5) & "'," & 194.0 & ",'" & "Y" & "');"
        ;$sSQL = $InsertString & $Index & "," & $AMeter[$Index] & ",'" & $ADate[$Index] & "','" & $ATime[$Index] & "'," & $AValue[$Index] & ",'" & $AError[$Index] & "');"
        $Result = _SQLite_Exec(-1, $sSQL)
        If $Result <> $SQLITE_OK Then
            MsgBox(0, "SQLite Log Data Error", "Result: " & $Result & "    @error: " & @error)
            MsgBox(0,"Insert Error on Loop " & $Index,$sSql)
            Quit()
        EndIf

 

So when you do a Insert do you need to include the PK and if so what value do you use?

 

 

[Nine]

Try this table creation, you will not have to provide ID at insert, it will be creating it automatically :

$sSQL = "CREATE TABLE MeterReadings (Id INTEGER PRIMARY KEY AUTOINCREMENT not null Unique, Meter INT not null, Date CHAR not null," _
  & "Time CHAR not null, Reading REAL not null, Error not null);"

 

[Trax]

This PK is totally blowing my mind. Back when I did RDBMS it was called a Record Number :). I guess messing with it can happen farther down the road. So attached is the schema. I was able to quick and dirty insert 10 records but still get and error in my larger, main program when I try and insert:

Even short cutting the data doesn't work:

Local Const $InsertString ="INSERT INTO MeterReadings(Meter,Date,Time,Reading,Error) VALUES ("

$sSQL = $InsertString & 1 & ",'" & _NowDate() & "','" & _NowTime(5) & "'," & 194.0 & ",'" & "Y" & "');"

Still throws an error

[Nine]

Works well for me with this :

expandcollapse popup

#include <SQLite.au3>
#include <Date.au3>

Local $hDB, $sSQL, $Result
Local Const $SQLITE_DLL = "sqlite3.dll"
Local Const $SQLITE_Database_Name = "Temp.sq3"

_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then
    MsgBox($MB_ICONERROR, "SQLite Startup Error", "Unable to start SQLite. Check existence of DLL")
    Exit
EndIf

If FileExists ($SQLITE_Database_Name) Then
    FileDelete($SQLITE_Database_Name)
EndIf

$hDB = _SQLite_Open($SQLITE_Database_Name)
If $hDB = 0 Then
    MsgBox(0, "SQLite Open Error", "Unable to start SQLite. Check existence of DLL")
    Exit
EndIf

$sSQL = "CREATE TABLE MeterReadings (Id INTEGER PRIMARY KEY AUTOINCREMENT not null Unique, Meter INT not null, Date CHAR not null," _
  & "Time CHAR not null, Reading REAL not null, Error not null);"
$Result = _SQLite_Exec($hDB, $sSQL)
If $Result <> $SQLITE_OK Then
    MsgBox(0, "SQLite Create Table Error", "Result: " & $Result & "    @error: " & @error)
EndIf

Local Const $Date = _NowDate(), $Time = _NowTime(5), $StdStr = "INSERT INTO MeterReadings(Meter,Date,Time,Reading,Error) VALUES("
$sSQL = $StdStr & "1,'" & $Date & "','" & $Time & "',194.0,'Y');"
$Result = _SQLite_Exec(-1, $sSQL)
$Result = _SQLite_Exec(-1, $sSQL)
$Result = _SQLite_Exec(-1, $sSQL)
$Result = _SQLite_Exec(-1, $sSQL)
$Result = _SQLite_Exec(-1, $sSQL)

If $Result <> $SQLITE_OK Then
    MsgBox(0, "SQLite Log Data Error", "Result: " & $Result & "    @error: " & @error)
    MsgBox(0,"",$sSql)
EndIf

_SQLite_Close()
_SQLite_Shutdown()

 

 

Edited March 31, 2023 by Nine

[Trax]

   I must have had some kind of typo in the "real" program. I copied and pasted the insert command from the quick and dirty program that worked to the main program and started, one by one, putting the real data items back in the line. It worked. Maybe I need to get away from it for a while

Thanks for putting up with a newbie guys.

  

[JLogan3o13]

Moved to the appropriate forum.

Moderation Team

[jchd]

For the rowid (= meaningless "record number") you don't need autoincrement (unless you expect rowids numbers in the vincinity of 2^63 - 1), you don't need not null (because the PK here is type INTEGER) and you certainly don't need unique (which creates a needless duplicate "unique" index (PK can't be non-unique with a historical exception in SQLite for PK of types not INTEGER [distinct type from INT] where NULLs are always distinct).

Any SQLite (SQL) table must have either an implicit rowid or a rowid alias in the form of a column like ID of type INTEGER declared as primary key. The difference between int and integer is specific to SQLite. Also SQLite doesn't honor declared sizes of column: char(14) is the same as char and you can store Mb of text in it.

The type system of SQLite is flexible: you can store any type in any column except the rowid or rowid alias (INTEGER type). Declaring a type for a column is only an indication for what SQLite calls affinity. In an int[teger] column, you can input '123' and since the conversion to int is lossless, then SQLite will store 123 as int. ' 123' can't be stored losslessly (leading space) and will store as text ( = char). Else you can declare a column of type you call humour and all is fine. This "type" is unknow and no attempt at conversion will occur, no error either. This type system fits AutoIt variants well enough for most purpose, but you can always force storage of only controlled types by using check contraint(s) or trigger(s).

You don't need to provide a value for an Id of type integer either in your inserts, except if you declare ID in the list of columns to insert (even then you can provide Null and it will create a new unique id for you).

You can use create table if not exists blah ... which won't error out if the table blah already exists.

You probably should merge date and time columns and specify default CURRENT_DATETIME: SQLite date/time are UTC and that makes them universal. You can manipulate timestamps to convert to local time, or whatever. Prefer the format YYYY-MM-DD since it's the format used by internal datetime functions, contrary to _NowDate() which uses YYYY/MM/DD. I anticipate you'll be extracting data based on timestamps and meter# so better use the format SQLite can manipulate inherently. It's always easy to convert to another format for display, outside the storage engine.

The error column looks like binary or even ternary logic (Y/N/don't know). Hence you can store it as int (regular 0=N, 1= Y, Null if don't know) as SQLite stores and handles these values in a special format.

[jchd]

In general you use data binding in SQL statements to avoid SQL injection by adversaries. https://xkcd.com/327/

I've posted here a set of functions for binding for SQLite but that slows down things significantly, due to numerous use of DllCalls and glue code. Binding should be reserved for fast (compiled) languages like C, not slow (interpreted) like AutoIt.

Yet AutoIt + SQLite is more than enough for your use case.

That leaves you with building your SQLite statements with SQL parts and inlined values, mostly variables, of different types. You often have to escape strings (double single quotes in strings) using _SQLite_FastEscape() if you suspect they might someday contain '. Use _SQLite_FastEncode() for binaries. You also have to take care of enclosing strings in single quotes (double quotes are reserved for schema names like table or column names containing spaces or ") and insert commas between second and next arguments (i.e. in insert statements).

That makes typing statements a little painful. I personally use a set of little functions to make that much less error-prone.
To use that you need to remember that SQLite understands 4 basic datatypes which end up in only 3 here: Text, Int or Real (Numeric) and Hex.
The first literal parameter doesn't need a leading comma, but next ones do.
So the rules for using this set of functions is: for first parm, use single letter function with 1 appended, use single letter functions for subsequent parameters.

Consider the insert statement previously posted:

Local $StdStr = "INSERT INTO MeterReadings(Meter,Date,Time,Reading,Error) VALUES("
$sSQL = $StdStr & "1,'" & $Date & "','" & $Time & "',194.0,'Y');"
$Result = _SQLite_Exec(-1, $sSQL)

A single error in the mixup of " ' , results in an error. Hard to type when you have dozens of literal values of various types. I understand that your use case is simple, but anyway.

Here's the list of functions:

; for the first parameter only

Func N1($v)
    Return (Number($v))
EndFunc   ;==>N1

Func T1($v)
    Return ("'" & StringReplace($v, "'", "''") & "'")
EndFunc   ;==>T1

Func B1($v)
    Return ("X'" & Hex($v) & "'")
EndFunc   ;==>B1


; for subsequent parameters

Func N($v)
    Return ("," & Number($v))
EndFunc   ;==>N

Func T($v)
    Return (",'" & StringReplace($v, "'", "''") & "'")
EndFunc   ;==>T

Func B($v)
    Return (",X'" & Hex($v) & "'")
EndFunc   ;==>B

N1() and N() are for Numeric (int or real)
T1() and T() are for Text
B1() and B() are for Binary

N, T & B are very intuitive as type mnemonics and you just have to remember to use N1, T1 or B1 for the 1st parm.

Here's the same statement rewriten using them:

Local $StdStr = "INSERT INTO MeterReadings(Meter,Date,Time,Reading,Error) VALUES("
$sSQL = $StdStr & N1(1) & T($Date) & T($Time) & N(194.0) & T('Y') & ")"
$Result = _SQLite_Exec(-1, $sSQL)

 

[argumentum]

4 hours ago, jchd said:

You often have to escape strings (double single quotes in strings) using _SQLite_FastEscape()

brain freeze. Too fast too cool

I use and used Escape() for the longest time. These "fast_" variants, could you explain the difference and advantages among them ?
I don't see much difference looking at the help file.

[jchd]

Shorter code as I recall. Going to close eyes now (5.40 AM here)

[Trax]

So I am still a little lost on the PK and rowid. Here is the current Create:

$sSQL = "CREATE TABLE MeterReadings (Id INTEGER PRIMARY KEY AUTOINCREMENT not null Unique, Meter INT not null, Date CHAR not null, Time CHAR not null, Reading REAL not null, Error not null);"

If that is inefficient and unnecessary as far as the PK goes what should it be?

 

 

[jchd]

As I previously said, you can use:

$sSQL = "CREATE TABLE MeterReadings (Id INTEGER PRIMARY KEY, Meter INT not null, Date CHAR not null, Time CHAR not null, Reading REAL not null, Error INT not null);"

Why autoincrement is unneeded: https://www.sqlite.org/autoinc.html#summary

An INTEGER PRIMARY KEY column can't contain Nulls and is forcibly unique. Hence Not null unique is unneeded as well.

Declaring a INT datatype for Error (use it as a boolean similar to AutoIt) is preferable.

Storing Dates and Times in separate columns is probably a bad idea (as previously explained also).

[Trax]

OK. Changed the create and things seem to be going well but....  I turned this collection system loose. It ran for a little over 5 days and 18,000 inserts then threw and SQLite Error. Result: 1, @error: -1 on the insert command. This is actually the second time. The first time it ran a little over 4 days.

That is my one problem with SQLite. The errors aren't very definitive. How do you go about trying to figure out what the error was with generic error codes like that?

[Skysnake]

My concern with this thread is that these are very basic SQL questions. The general support here is awesome, but some basic homework concerning SQL will definitely help.  I have text books in PDF format if required. Please DM me.