goldenix Posted July 2, 2008 Share Posted July 2, 2008 (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.aspErrors:! 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 errorMY 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 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] Link to comment Share on other sites More sharing options...
weaponx Posted July 2, 2008 Share Posted July 2, 2008 (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 July 2, 2008 by weaponx Link to comment Share on other sites More sharing options...
goldenix Posted July 2, 2008 Author Share Posted July 2, 2008 (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 July 2, 2008 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] Link to comment Share on other sites More sharing options...
ptrex Posted July 2, 2008 Share Posted July 2, 2008 @goldenix Maybe this can get you started. look in the AU3 helpfile for SQLite and you will find this. _SQLite_GetTable Regards ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New Link to comment Share on other sites More sharing options...
goldenix Posted July 2, 2008 Author Share Posted July 2, 2008 @goldenix Maybe this can get you started. look in the AU3 helpfile for SQLite and you will find this. _SQLite_GetTable Regards ptrexYou 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] Link to comment Share on other sites More sharing options...
Airwolf Posted July 2, 2008 Share Posted July 2, 2008 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 Link to comment Share on other sites More sharing options...
goldenix Posted July 2, 2008 Author Share Posted July 2, 2008 (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 July 2, 2008 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] Link to comment Share on other sites More sharing options...
weaponx Posted July 2, 2008 Share Posted July 2, 2008 So you just want a function that will return an array of table names? Link to comment Share on other sites More sharing options...
goldenix Posted July 2, 2008 Author Share Posted July 2, 2008 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] Link to comment Share on other sites More sharing options...
weaponx Posted July 2, 2008 Share Posted July 2, 2008 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. Link to comment Share on other sites More sharing options...
goldenix Posted July 2, 2008 Author Share Posted July 2, 2008 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] Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now