Sign in to follow this  
Followers 0
goldenix

[Solved]_SQLite semi Embedded database functionality in AutoIt

11 posts in this topic

#1 ·  Posted (edited)

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 by goldenix

My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by weaponx

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by goldenix

My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]

Share this post


Link to post
Share on other sites

@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)

My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]

Share this post


Link to post
Share on other sites

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'

Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by goldenix

My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

k thanx for replys this resolves all my questions.


My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]

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  
Followers 0