Jump to content
Sign in to follow this  
GoofyGremlin

Problem with SQLite

Recommended Posts

GoofyGremlin

For some reason, with this code:

#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:

#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.

Share this post


Link to post
Share on other sites
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")

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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, "\", "/")

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Eduardo

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

Regards,

Eduardo.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×