SQLite semi Embedded database functionality in AutoIt

[Kaarafin]

This scipt ownd. Really nice job. TY. Make game a lot easier.

[ivan]

Dear ptrex:

compiling with beta 3.2.13.2 I get ERROR: undefined macro because the library uses @ProcessorArch instead of

@OSArch.

Made a note on the help and support forum topic bug in @ProcessorArch

Regards

IVAN

Edited June 19, 2008 by ivan

[ptrex]

@ivan

Thanks for the update.

Can you tell me where in the lib this is used ?

I couldn't find it immediately.

regards,

ptrex

[ivan]

Sure, in the file "SQLite.dll.au3", line 7 reads

If @ProcessorArch <> "X86" Then

and i get the compile error:

>"H:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /beta /in "H:\Documents and Settings\iap\Desktop\banks\banks\DataBank_vz4.au3" /autoit3dir "H:\Program Files\AutoIt3\beta"
+>13:29:28 Starting AutoIt3Wrapper v.1.10.1.7   Environment(Language:0409  Keyboard:0000080A  OS:WIN_XP/Service Pack 2  CPU:X86)
>Running AU3Check (1.54.13.0)  from:H:\Program Files\AutoIt3\beta
H:\Program Files\AutoIt3\beta\Include\SQLite.dll.au3(7,20) : ERROR: undefined macro.
    If @ProcessorArch <>
    ~~~~~~~~~~~~~~~~~~^
H:\Documents and Settings\iap\Desktop\banks\banks\DataBank_vz4.au3 - 1 error(s), 0 warning(s)
!>13:29:30 AU3Check ended.rc:2
>Exit code: 0   Time: 4.223

Regards,

IVAN

[weaponx]

Hi, I am new with AutoIt and I like it very much. About the SQL UDF, I would like know how I can read the last record in the database without reading each record. I am using this code and I think there is a better way. pardon me if I am not using the right forum/format.

CODE

Local $TxList, $lNo

_SQlite_Query($DataBase, "SELECT * FROM Daily ORDER BY TxNo ;", $Query)

While _SQLite_FetchData($Query, $TxList) = $SQLITE_OK

$lNo = $TxList[0]

WEnd

_SQLite_QueryFinalize($Query)

Return($lNo)

A little late but also a different approach. This will work on columns containing numbers or strings.

opt("MustDeclareVars", 1)
#include <SQLite.au3>
Local $hQuery, $aRow, $sMsg,$d
$d = _SQLite_Startup ()
$d = _SQLite_Open () ; open :memory: Database
$d = _SQLite_Exec (Default, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
$d = _SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") ; INSERT Data
$d = _SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") ; INSERT Data
$d = _SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") ; INSERT Data
$d = _SQlite_Query (-1, "SELECT MAX(a) FROM aTest;", $hQuery) ; the query
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    $sMsg &= $aRow[0]
WEnd
_SQLite_Exec (-1, "DROP TABLE aTest;") ; Remove the table
ConsoleWrite($sMsg & @CR)
_SQLite_Close()
_SQLite_Shutdown()

[Mattraks]

Just a quick question but just to clear this up, does this work if you already have a SQL database and stuff? Cause I'm not sure where it has the thing to connect to a specific database with user of X and pass of Y and then select schema of Z. Can someone just tell me if that does work with this?

EDIT: Found what i needed. http://www.autoitscript.com/forum/index.php?showtopic=20814

Edited July 21, 2008 by Mattraks

[Edek]

LiteX has been moved to assembla:

https://www.assembla.com/spaces/litex

Now you can easily browse LiteX sources in SVN repository.

[ptrex]

@Edek

Thanks for the update.

I also noticed that the milage of this thread went over 100.000 !!

regards,

ptrex

[wraithdu]

Maybe I'm missing something very simple, but is there a way to save a "memory" db to a file db? And I don't mean just exporting the data manually, I mean to actually save the whole db to a file so it may be opened again later by SQLite. Basically the same functionality as the .dump command - I need to .dump the memory db, then I can call _SQLite_SQLiteExe("new.db", $out_from_dump, $out).

In reverse, is there a way to load a file db into a "memory" db? I think when you open a file db with SQLite all changes are then made to the file directly. I ask because it's faster to work in memory, but it's essential to be able to save that DB to a file for later use.

Edited September 5, 2008 by wraithdu

[ptrex]

@wraithdu

Not that I know of.

Regards

ptrex

[JorgeW]

@wraithdu:

I had the same issue. The in-memory database is incredibly fast but needed this db for later use. Unfortunately, I couldn't find a copy either but in the SQLite documentation I found the "ATTACH DATABASE" thingy and gave it a try.

It works very well without array manipulation for me. You may want to test it...

I came up with the following function that takes the memory database and insert the tables into the file based database. Both database structures are identical (same table and column names).

Func WriteMemDb2File(ByRef $objMemDbConn, ByRef $objFileDbConn, $strDbFileName)
    $strResult = _SQLite_Exec($objMemDbConn, "ATTACH DATABASE '" & $strDbFileName & "' AS FileDb;")
    $strResult = _SQLite_Exec($objMemDbConn, "INSERT INTO FileDb.File_Elements SELECT * FROM File_Elements;")
    $strResult = _SQLite_Exec($objMemDbConn, "INSERT INTO FileDb.Raw_File SELECT * FROM Raw_File;")
    $strResult = _SQLite_Exec($objMemDbConn, "DETACH DATABASE FileDb;")
    Return 1
EndFunc

[wraithdu]

This is cool. Quick question. Are 'File_Elements' and 'Raw_File' special tables in SQlite, or specific to your DB?

Oh, and do you have to _SQLite_Open() the new file DB first, or does ATTACH automatically create it?

Edited September 26, 2008 by wraithdu

[rroot]

@Edek

If you're the one who develops it, thank you for litex. I absolutely love that library!

[microsoft]

Thanks of this job

Edited December 21, 2008 by microsoft

[ivan]

I think I found an error in the help file example for the _SQLite_SQLiteExe function.

Without calling _SQLite_Startup() before _SQLite_SQLiteExe I get an @error=1 (cannot create the db).

It reads:

expandcollapse popup

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

;Filenames
Local $sTsvFile = FileGetShortName(_TempFile(@ScriptDir,"~",".tsv"))
Local $sDbFile = FileGetShortName(_TempFile(@ScriptDir,"~",".db"))

;Create Tsv File
FileWriteLine($sTsvFile,"a" & @TAB & "b" & @TAB & "c")
FileWriteLine($sTsvFile,"a1" & @TAB & "b1" & @TAB & "c1")
FileWriteLine($sTsvFile,"a2" & @TAB & "b2" & @TAB & "c2")

;import (using SQLite3.exe)
Local $sIn,$sOut,$i, $sCreate = "CREATE TABLE TblImport (";
for $i = 1 To _StringCountOccurance(FileReadLine($sTsvFile,1),@TAB) + 1
    $sCreate &= "Column_" & $i & ","
Next
$sCreate = StringTrimRight($sCreate,1) & ");"
$sIn  = $sCreate & @CRLF; Create Table
$sIn &= ".separator \t" & @CRLF; Select @TAB as Separator
$sIn &= ".import '" & $sTsvFile & "' TblImport" & @CRLF
_SQLite_SQLiteExe($sDbFile,$sIn,$sOut,-1,true)

If @error = 0 Then
;Show Table (using SQLite3.dll)
    Local $iRows,$iColumns,$aRes
    _SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CR)
    _SQLite_Open($sDbFile)
    _SQLite_GetTable2d(-1,"SELECT ROWID,* FROM TblImport;",$aRes,$iRows,$iColumns)
    _SQLite_Display2DResult($aRes); Output to Console
    _SQLite_Close()
    _SQLite_Shutdown()
Else
    If @error = 2 Then 
        ConsoleWrite("ERROR: Sqlite3.exe file not found" & @LF)
    Else
        ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @LF)
    EndIf
EndIf

;Remove Temp Files
FileDelete($sTsvFile)
FileDelete($sDbFile)

;~ Output:
;~  rowid  Column_1  Column_2  Column_3 
;~  1     a      b       c      
;~  2     a1        b1      c1     
;~  3     a2        b2      c2     



Func _StringCountOccurance($sSearchString,$sSubString,$fCaseSense = 0); Returns Number of $sSubString in $sSearchString
    Local $iOccCnt = 1
    Do
        If StringInStr($sSearchString,$sSubString,$fCaseSense,$iOccCnt) > 0 Then
            $iOccCnt += 1
        Else
            ExitLoop
        EndIf
    Until 0
    Return $iOccCnt - 1
EndFunc

and should read:

expandcollapse popup

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

;Filenames
Local $sTsvFile = FileGetShortName(_TempFile(@ScriptDir,"~",".tsv"))
Local $sDbFile = FileGetShortName(_TempFile(@ScriptDir,"~",".db"))

;Create Tsv File
FileWriteLine($sTsvFile,"a" & @TAB & "b" & @TAB & "c")
FileWriteLine($sTsvFile,"a1" & @TAB & "b1" & @TAB & "c1")
FileWriteLine($sTsvFile,"a2" & @TAB & "b2" & @TAB & "c2")

;import (using SQLite3.exe)
Local $sIn,$sOut,$i, $sCreate = "CREATE TABLE TblImport (";
for $i = 1 To _StringCountOccurance(FileReadLine($sTsvFile,1),@TAB) + 1
    $sCreate &= "Column_" & $i & ","
Next
$sCreate = StringTrimRight($sCreate,1) & ");"
$sIn  = $sCreate & @CRLF; Create Table
$sIn &= ".separator \t" & @CRLF; Select @TAB as Separator
$sIn &= ".import '" & $sTsvFile & "' TblImport" & @CRLF
_SQLite_Startup()
_SQLite_SQLiteExe($sDbFile,$sIn,$sOut,-1,true)

If @error = 0 Then
   ;Show Table (using SQLite3.dll)
    Local $iRows,$iColumns,$aRes
    _SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CR)
    _SQLite_Open($sDbFile)
    _SQLite_GetTable2d(-1,"SELECT ROWID,* FROM TblImport;",$aRes,$iRows,$iColumns)
    _SQLite_Display2DResult($aRes); Output to Console
    _SQLite_Close()
    _SQLite_Shutdown()
Else
    If @error = 2 Then 
        ConsoleWrite("ERROR: Sqlite3.exe file not found" & @LF)
    Else
        ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @LF)
    EndIf
EndIf

;Remove Temp Files
FileDelete($sTsvFile)
FileDelete($sDbFile)

;~ Output:
;~  rowid  Column_1  Column_2  Column_3 
;~  1     a      b       c      
;~  2     a1        b1      c1     
;~  3     a2        b2      c2     



Func _StringCountOccurance($sSearchString,$sSubString,$fCaseSense = 0); Returns Number of $sSubString in $sSearchString
    Local $iOccCnt = 1
    Do
        If StringInStr($sSearchString,$sSubString,$fCaseSense,$iOccCnt) > 0 Then
            $iOccCnt += 1
        Else
            ExitLoop
        EndIf
    Until 0
    Return $iOccCnt - 1
EndFunc

[ptrex]

@ivan

Indead this is a problem.

But then the SQLStartup in here can disappear.

If @error = 0 Then

;Show Table (using SQLite3.dll)

Local $iRows,$iColumns,$aRes

_SQLite_Startup() <------------------ remove ?!

ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CR)

_SQLite_Open($sDbFile)

_SQLite_GetTable2d(-1,"SELECT ROWID,* FROM TblImport;",$aRes,$iRows,$iColumns)

_SQLite_Display2DResult($aRes); Output to Console

_SQLite_Close()

_SQLite_Shutdown()

Else

If @error = 2 Then

ConsoleWrite("ERROR: Sqlite3.exe file not found" & @LF)

Else

ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @LF)

EndIf

EndIf

Best is to post a ccrrect working version as a bug section of AU3.

Regards

ptrex

[ivan]

@ivan

Indead this is a problem.

But then the SQLStartup in here can disappear.

Best is to post a ccrrect working version as a bug section of AU3.

Regards

ptrex

Thanks ptrex.

I installed AutoIt v3.3.0.0 and I am having trouble with _SQLite_Query. I wanted to confirm with other users if the same was happening before posting to the bug section of AU3. Specifically, this function returns a value

$SQLITE_MISUSE = 21 ; /* Library used incorrectly */

When I am absolutely sure I use it correctly. In fact the same happens with the example provided in the help file for the function _SQLite_Query... Anyone having the same trouble?

For now, I'm switching to an older version of autoit, as I need to get a script working before 9:00 am tomorrow.

Regards,

IVAN

[ptrex]

@Ivan

Sorry I am not usng the AutoIt v3.3.0.0 version yet. So I can't test for now.

Maybe some other users.

Regards

ptrex

[ivan]

@Ivan

Sorry I am not usng the AutoIt v3.3.0.0 version yet. So I can't test for now.

Maybe some other users.

Regards

ptrex

Ptrex,

Thanks for your concern. My passion for autoit has me permanently downloading the newest versions and the betas, which means I often find myself having to revert to other versions when I stump into something I do not expect. I can't post it as a bug as I no longer have the latest autoit version installed. As soon as I finish a scriptwhich is taking longer than I first expected, I'll test it again, to see if it requires fixing.

Regards,

IVAN

[panlatv]

Hello all, and sorry for my bad english.

I have a problem, i know it's a usual question:

In a dos console all it's ok (sql3.exe), but if i use autoit :

_SQLite_Exec (-1,"INSERT INTO test (letter) values ("&chr(39)&"à"&chr(39)&");" )

The " à " is transformed in "Ó"

It's the same for "é" = "Ú"

...

So what can i do ? thank you

__________________

Edit:

Ok, it's not perfect, but it's a solution:

I use stringremplace:

$data = StringReplace ( $data, "é","é" )
    $data = StringReplace ( $data, "è","è" )
    $data = StringReplace ( $data, "ë","ë" )
    $data = StringReplace ( $data, "ê","ê" )
    $data = StringReplace ( $data, "à","à" )
    $data = StringReplace ( $data, "ù","ù" )
    $data = StringReplace ( $data, "ô","ô" )
    $data = StringReplace ( $data, "œ","œ")
    $data = StringReplace ( $data, "ï","ï")
    $data = StringReplace ( $data, "î","î")
    $data = StringReplace ( $data, "ç","ç") 
    $data = StringReplace ( $data, "û","û")  
    $data = StringReplace ( $data, "ü","ü")
    $data = StringReplace ( $data, "«","«")
    $data = StringReplace ( $data, "»","»")
    $data = StringReplace ( $data, "½","½")
        ...

Edited March 15, 2009 by panlatv