Problem with SQLite

[GoofyGremlin]

For some reason, with this code:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>

Dim $dataBase = @ScriptDir + "\cardList.db"
Dim $sqlPath = FileGetShortName("C:\Program Files\AutoIt3\Extras\SQLite")
 
Dim $sOutput

_SQLite_Startup ()

If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf


_SQLite_Open ("@ScriptDir\cardList.db"); Open a :memory: database

If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf


Dim $createtbl = "CREATE TABLE cardList(one text, two text, three numeric, four numeric, five numeric, six numeric);" & @CRLF
Dim $sInput = "INSERT INTO " & $dataBase & ".cardList" & " VALUES (Tim, TimsSet, 5, 7, 1, 2);" & @CRLF

_SQLite_SQLiteExe (-1, $createtbl, $sOutput)
_SQLite_SQLiteExe (-1, $sInput, $sOutput)



If @error > 0 Then
    MsgBox(16, "Error", @error)
    Exit - 1
EndIf



_SQLite_Close ()
_SQLite_Shutdown ()


#cs
CREATE TABLE [cardList] 
(
    [Name] VARCHAR,
    [Set] VARCHAR,
    [PCS1] NUMERIC,
    [PCS2] NUMERIC,
    [BPrice] NUMERIC,
    [SPrice] NUMERIC
)
#ce

I get no error message but still cannot find the db file that it is supposed to create. However when I add and ElseIf statement to the final error check like this:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>

Dim $dataBase = @ScriptDir + "\cardList.db"
Dim $sqlPath = FileGetShortName("C:\Program Files\AutoIt3\Extras\SQLite")
 
Dim $sOutput

_SQLite_Startup ()

If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf


_SQLite_Open ("@ScriptDir\cardList.db"); Open a :memory: database

If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf


Dim $createtbl = "CREATE TABLE cardList(one text, two text, three numeric, four numeric, five numeric, six numeric);" & @CRLF
Dim $sInput = "INSERT INTO " & $dataBase & ".cardList" & " VALUES (Tim, TimsSet, 5, 7, 1, 2);" & @CRLF

_SQLite_SQLiteExe (-1, $createtbl, $sOutput)
_SQLite_SQLiteExe (-1, $sInput, $sOutput)



If @error > 0 Then
    MsgBox(16, "Error", @error)
    Exit - 1
Else
    MsgBox(16, "Success", "Success")
    Sleep(500)
    Exit - 1
EndIf



_SQLite_Close ()
_SQLite_Shutdown ()


#cs
CREATE TABLE [cardList] 
(
    [Name] VARCHAR,
    [Set] VARCHAR,
    [PCS1] NUMERIC,
    [PCS2] NUMERIC,
    [BPrice] NUMERIC,
    [SPrice] NUMERIC
)
#ce

I get an error=2, meaning the _SQLite_SQLiteExe command cannot find SQLite. I have no idea what is goin on here, I am simply tryin to create a program that can enter data into an SQLite table.

[picaxe]

Hi GoofyGremlin

Dim $dataBase = @ScriptDir + "\cardList.db"

;

;------------ try adding this line here in your script-------------

$dataBase = StringReplace($dataBase, "\", "/")

;---------------------------------------------------------------------

;

Dim $sqlPath = FileGetShortName("C:\Program Files\AutoIt3\Extras\SQLite")

[GoofyGremlin]

Hi GoofyGremlin

Dim $dataBase = @ScriptDir + "\cardList.db"

;

;------------ try adding this line here in your script-------------

$dataBase = StringReplace($dataBase, "\", "/")

;---------------------------------------------------------------------

;

Dim $sqlPath = FileGetShortName("C:\Program Files\AutoIt3\Extras\SQLite")

Tried that, still get the error 2 message. I don't understand why when I just startup and open SQLite I dont get an error message, but when I try the _SQLite_SQLiteExe command it can't find SQLite.exe.

[picaxe]

Error 2 is "An internal logic error in SQLite", I've had the same problem when I run a script (where sqlite3 is not initially present) and I'm using FileInstall to deliver sqlite3.exe.

I just ignore this error (possible bug) and haven't had any problems.

Just try this

If @error > 0 And @error <> 2 Then

MsgBox(16, "Error", @error)

Exit - 1

Else

MsgBox(16, "Success", "Success")

Sleep(500)

Exit - 1

EndIf

[picaxe]

Oh btw, everywhere you have

If @error > 0

change to

If @error > 0 And @error <> 2

Then see if it runs, also I think you will need

$dataBase = StringReplace($dataBase, "\", "/")

[gebrock132]

I haven't had any problems using sqlite. I'm no expert but I don't think you need to reverse your slashes for that statement to work. I would recommend that you check the error status after you create the table. Your create table statement doesn't look right to me. Try:

Dim $createtbl "CREATE TABLE cardList(one char[10], two char[10], three integer, four integer, five integer, six integer);"

obviously 10 can be any integer you choose.

also in your insert statement you may need the column names after the table name:

Dim $sInput = "INSERT INTO " & $dataBase & ".cardList" & " (one, two, three, four, five, six)

VALUES (Tim, TimsSet, 5, 7, 1, 2);" & @CRLF

one more thing if you're using the @ScriptDir you need to run this script from the same directory the database is in.

[Eduardo]

Hi GoofyGremlin, checking your script i realized it is needing seven changes in other to be near to rigth.

#include <SQLite.au3>

#include <SQLite.dll.au3>

; first change

;Dim $dataBase = @ScriptDir + "\cardList.db"

Dim $dataBase = @ScriptDir & "\cardList.db"

; second change

;Dim $sqlPath = FileGetShortName("C:\Program Files\AutoIt3\Extras\SQLite")

Dim $sqlPath = FileGetShortName("C:\Program Files\AutoIt3\Extras\SQLite\SQLite3.exe")

Dim $sOutput

_SQLite_Startup ()

If @error > 0 Then

MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")

Exit - 1

EndIf

; third change

;_SQLite_Open ("@ScriptDir\cardList.db"); Open a :memory: database

$db = _SQLite_Open (@ScriptDir & "\cardList.db"); Open a :memory: database

; fourth change.

; If @error > 0 Then

If Not $db Then

MsgBox(16, "SQLite Error", "Can't Load Database!")

Exit - 1

EndIf

Dim $createtbl = "CREATE TABLE cardList(one text, two text, three numeric, four numeric, five numeric, six numeric);" & @CRLF

; fifth change

;Dim $sInput = "INSERT INTO " & $dataBase & ".cardList" & " VALUES (Tim, TimsSet, 5, 7, 1, 2);" & @CRLF

Dim $sInput = "INSERT INTO cardList" & " VALUES (""Tim"", ""TimsSet"", 5, 7, 1, 2);" & @CRLF

; sixth change

; _SQLite_SQLiteExe (-1, $createtbl, $sOutput)

_SQLite_SQLiteExe ($dataBase, $createtbl , $sOutput, $sqlPath)

; seventh change

;_SQLite_SQLiteExe (-1, $sInput, $sOutput)

_SQLite_SQLiteExe ($dataBase, $sInput, $sOutput, $sqlPath)

If @error > 0 Then

MsgBox(16, "Error", @error)

Exit - 1

EndIf

_SQLite_Close ()

_SQLite_Shutdown ()

#cs

CREATE TABLE [cardList]

(

[Name] VARCHAR,

[set] VARCHAR,

[PCS1] NUMERIC,

[PCS2] NUMERIC,

[bPrice] NUMERIC,

[sPrice] NUMERIC

)

#ce

Regards,

Eduardo.

[Eduardo]

Of course the four one is optional. Actually, the script is demanding 6 changes instead of 7.

Regards,

Eduardo.