[Solved]_SQLite semi Embedded database functionality in AutoIt

[goldenix]

How can I make the databasefile, so I can include it lather & use SQL Queries to select data? As much as I tried its not working at all. 2 Basic questons How to make the file & where can I find SQL SELECT statements? Cuz the ones I found hire are not working: http://www.w3schools.com/sql/sql_where.asp

Errors:

!   SQLite.au3 Error
--> Function: _SQLite_Query
--> Query:  SELECT * FROM aTest WHERE Col1='1';
--> Error:  file is encrypted or is not a database

!   SQLite.au3 Error
--> Function: _SQLite_Exec
--> Query:  DROP TABLE TestTable;
--> Error:  disk I/O error

MY code so far:

#include <SQLite.au3>
#include <Mydatabase.au3> ; Empty txt file

Local $hQuery, $aRow, $aNames
_SQLite_Startup ()
_SQLite_Open ('Mydatabase.au3') ; open :memory: Database
_SQLite_Exec ('Mydatabase.au3', "CREATE TABLE aTest (ID,Col.1,Col.2);")
_SQLite_Exec ('Mydatabase.au3', "INSERT INTO aTest(ID,Col.1,Col.2) VALUES ('1','Jane','Fee');")

$MyQuery = _SQlite_Query (-1, "SELECT * FROM aTest WHERE ID='1';", $hQuery)

ConsoleWrite($MyQuery & @CRLF)

_SQLite_Exec (-1, "DROP TABLE aTest;")
_SQLite_Close ()
_SQLite_Shutdown ()

Edited July 2, 2008 by goldenix

[weaponx]

This works for me, it seems to create the file in a proprietary format. After I ran it the first time I commented out the call to _SQLite_Exec and it read in the values just fine:

#include <sqlite.au3>
#include <sqlite.dll.au3>

Local $hQuery,$aRow
_SQLite_Startup()
_SQLite_Open("test.sql")
; Whithout $sCallback its an Resultless query
_SQLite_Exec(-1,"Create table tblTest (a,b int,c single not null);" & _
                "Insert into tblTest values ('1',2,3);" & _
                "Insert into tblTest values (Null,5,6);")

$d = _SQLite_Exec(-1,"Select oid,* From tblTest","_cb") ; _cb Will be called for each row
Func _cb($aRow)
    For $s In $aRow
        ConsoleWrite($s & @TAB)
    Next
    ConsoleWrite(@LF)
    ; Return $SQLITE_ABORT ; Would Abort the process and trigger an @error in _SQLite_Exec()
EndFunc
_SQLite_Close()
_SQLite_Shutdown()

Edited July 2, 2008 by weaponx

[goldenix]

This works for me, it seems to create the file in a proprietary format. After I ran it the first time I commented out the call to _SQLite_Exec and it read in the values just fine:

Yes sup Everything works the way it supposed to now. But since all the info is invisible how do you get the tabeles list? cuz how do I know how many tables & what are their names that you got in the database file are?

Edited July 2, 2008 by goldenix

[ptrex]

@goldenix

Maybe this can get you started.

look in the AU3 helpfile for SQLite and you will find this.

_SQLite_GetTable

Regards

ptrex

[goldenix]

@goldenix

Maybe this can get you started.

look in the AU3 helpfile for SQLite and you will find this.

_SQLite_GetTable

Regards

ptrex

You think i havent looked at that already? Sadly It wont since you need to know exact table name in order to use this. & what if I want to save multiple tables in the file so user can load the tables & the data at program startup, i mean this is what the database is for, isn`t it?

$iRval = _SQLite_GetTable (-1, "SELECT * FROM TablePersons;", $aResult, $iRows, $iColumns)

[Airwolf]

You think i havent looked at that already? Sadly It wont since you need to know exact table name in order to use this. & what if I want to save multiple tables in the file so user can load the tables & the data at program startup, i mean this is what the database is for, isn`t it?

$iRval = _SQLite_GetTable (-1, "SELECT * FROM TablePersons;", $aResult, $iRows, $iColumns)

In SQLite, there is a system table that lists all tables in the database. You can execute the following query and it will list all of the tables.

SELECT * FROM sqlite_master WHERE type='table'

[goldenix]

In SQLite, there is a system table that lists all tables in the database. You can execute the following query and it will list all of the tables.

SELECT * FROM sqlite_master WHERE type='table'

Well thechnically I can do it like this then but isnt there any other way? I mean the data is not inside an array is it? And do i really need to #include <sqlite.dll.au3>? i tried without it & it worked with no problems.

see example:

#include <sqlite.au3>

_SQLite_Startup()
_SQLite_Open("test.sql")


_SQLite_Exec (-1, "CREATE TABLE tblTest1 (Col1,Col2,Col3);")
_SQLite_Exec (-1, "INSERT INTO tblTest1(Col1,Col2,Col3) VALUES ('1','Jane','Fee');")
_SQLite_Exec (-1, "INSERT INTO tblTest1(Col1,Col2,Col3) VALUES ('2','Jane2','Fee');")
_SQLite_Exec (-1, "INSERT INTO tblTest1(Col1,Col2,Col3) VALUES ('3','Jane','Fee');")

_SQLite_Exec (-1, "CREATE TABLE tblTest2 (Col1,Col2,Col3);")
_SQLite_Exec (-1, "INSERT INTO tblTest2(Col1,Col2,Col3) VALUES ('1','Jane','Fee');")
_SQLite_Exec (-1, "INSERT INTO tblTest2(Col1,Col2,Col3) VALUES ('2','Jane2','Fee');")
_SQLite_Exec (-1, "INSERT INTO tblTest2(Col1,Col2,Col3) VALUES ('3','Jane','Fee');")


$d = _SQLite_Exec(-1,"SELECT * FROM sqlite_master WHERE type='table'","_cb") ; _cb Will be called for each row

Func _cb($aRow)
    $x = 1
    For $s In $aRow
        If $x = 2 And $s <> 'name' Then 
            ConsoleWrite($s & @TAB)     ; get the table names
            $x = 1
            ConsoleWrite(@LF)
            ExitLoop            
        EndIf
        $x = $x + 1
    Next
EndFunc

_SQLite_Exec (-1, "DROP TABLE tblTest1;")
_SQLite_Exec (-1, "DROP TABLE tblTest2;")
_SQLite_Close()
_SQLite_Shutdown()

Edited July 2, 2008 by goldenix

[weaponx]

So you just want a function that will return an array of table names?

[goldenix]

So you just want a function that will return an array of table names?

Obviously I want to be able to manipulate the data, to fill a listview columns, for example: SELECT Column1,Column2 FROM tablename . So this means I will have to count the tabs every time I want to add new value into the list the way i did in the example code. I also tried sringsplit($aRow,'@TAB') but it didnt wotk, So I was wondering if there is any other way doing this.

[weaponx]

If you need to know how many columns are returned by a result you use _SQLite_FetchNames.

This returns an array of all column names in your query, you can use Ubound to get the count.

[goldenix]

k thanx for replys this resolves all my questions.