call 'tables' in SQLite...?

[Burgs]

Greetings,

  I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task.  I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...?  The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database.

  I am attempting the following:

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

Global $hDb, $sIn, $sOut

...

$sIn = ".tables" & @CRLF  
_SQLite_SQLiteExe($hDb, $sIn, $sOut)  

 if @error == 0 Then
    ;Show Table (using SQLite3.dll)
 Else
  if @error == 2 Then
  ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF)
  Else
  ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF)
  EndIf  ;@error is "2"...OR NOT...
 EndIf  ;@error is "0"...OR NOT...
 
 ...

  The error being thrown is "ERROR: Sqlite3.exe file not found" ...

  Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...???  I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated.  Thanks in advance. 

Regards

 

 

 

 

 

 

 

[jchd]

You definitely don't need to use the command-line utility sqlite3.exe to get a list of tables.

Use this SQL query to retrieve an ordered list of tables in the Main database:

select tbl_name "Table" from sqlite_master where type = 'table' order by "Table";

_SQLite_GetTable() is a good way to return that array of strings.

[Burgs]

Hello jchd,

  Thanks for the response...I'll give that a try later when I have a chance.  I knew '_SQLite_GetTable()' can get the row data...but I would have to know what table name to pass into it. 

  Can the '_SQLite_Query' be passed any valid SQLite command...?  I was under the impression it could only be used with certain commands as given in the manual documentation...?

  Just to verify I have the syntax correct...my code should read:

 

_SQLite_Query($hDb, "select tbl_name "Table" from sqlite_master where type = 'table' order by "Table";", $hQuery)

 

  correct...?   

  Thanks again for your post...much appreciated.

[benners]

In this instance you'll have to double quote the Table

_SQLite_Query($hDb, "select tbl_name ""Table"" from sqlite_master where type = 'table' order by ""Table"";", $hQuery)

 

[kylomas]

Burgs,

An example of some functions that I use occasionally...

expandcollapse popup

#include <SQLite.au3>

; sqlite3.dll can be in any of the following @ScriptDir, @SystemDir, @WindowsDir, or @WorkingDir.
_SQLite_Startup('sqlite3.dll')

; open a memory DB
Local $hdb = _SQLite_Open()

; create memory DB with some tables
Local $sql = 'create table t1 (a1, a2, a3);'
$sql &= 'create table t2 (b1, b2, b3);'
$sql &= 'create table t3 (c1, c2, c3);'
_SQLite_Exec($hdb, $sql)

_ArrayDisplay(table_list())
_ArrayDisplay(column_list('t2'))

If table_exists('t3') Then ConsoleWrite('t3 exists' & @CRLF)

_SQLite_Shutdown()

; some functions that I use occasionally to interrogate configuration
Func table_exists($tbl)
    Local $rows, $nbrows, $nbcols, $ret
    $ret = _SQLite_GetTable2d(-1, "select * from sqlite_master where name = " & "'" & $tbl & "'" & ";", $rows, $nbrows, $nbcols)
    If @error Then ConsoleWrite(@error & ' ' & @extended & ' ' & $ret & @LF)
    If $nbrows > 0 Then
        Return True
    Else
        Return False
    EndIf
EndFunc   ;==>table_exists

Func table_list()
    Local $rows, $nbrows, $nbcols
    $ret = _SQLite_GetTable2d(-1, "select * from sqlite_master;", $rows, $nbrows, $nbcols, 1000)
    If @error Then ConsoleWrite(@error & ' ' & @extended & ' ' & $ret & @LF)
    Return $rows
EndFunc   ;==>table_list

Func column_list($tbl)
    Local $rows, $nbrows, $nbcols
    $ret = _SQLite_GetTable2d(-1, "pragma table_info(" & $tbl & ");", $rows, $nbrows, $nbcols)
    If @error Then ConsoleWrite(@error & ' ' & @extended & ' ' & $ret & @LF)
    Return $rows
EndFunc   ;==>column_list

kylomas

[Burgs]

Thanks to all who posted replies...that clarification and other information are extremely useful...I'm new to using SQLite and I will find this very helpful...much appreciation to all of you...regards

[Earthshine]

Learn SQL well. It’s your friend with lots of databases

[jchd]

In my example I decided to put the (optional) alias name "Table" inside double quotes as it's a reserved SQL keyword. But in this case SQLite is clever enough to understand the query even if you don't put double quotes there. They would be obligatory if you wanted the alias to be "The name of my tables", because of whitespaces in the name.

Forget about _SQLite_Query and the code complexities it requires. Prefer the higher-level *_GetTable[2d] or *_Exec functions.

[Earthshine]

My advice Comes from years programming with work in Oracle and SQL server

[water]

NB:
"==" is the string compare operator. As @error is set to an integer you should use the ordinary compare operator:

if @error = 0 Then

The result is the same but it's cleaner coding

[Burgs]

Yes I agree SQLite is quite powerful...and I become more impressed the more acclimated I become with it.  Thanks as well for the syntax clarifications...yes I tend to use "==" out of habit...

thanks again to all who replied  

 

[jchd]

I'd guess you'll soon find it's 10 times as powerfull, 100 times more flexible and 1M times as ubiquitous as you currently feel it is.
Needless to say, I'm a big, really big fan, so my opinion is 100% biaised (yet based on solid facts).

Have fun with an example:

 

[Burgs]

Hi,

  As you stated ..."Serious warning: the SQL involved is NOT for heartfainted people."...   you are not kidding are you...     hehehe

  well done...yes a great example of what might be accomplished using SQLite, thanks again mate...

 

[jchd]

I agree this example is a bit extreme but it has the merit to show that SQL in general can solve non-trivial problems elegantly and very efficiently, well beyond the traditional "handle my business data" domain. SQLite also has the huge benefit of it's incredible portability: the very same SQLite code would return the exact same result on any device running a recent enough SQLite3 version (your TV, modem, router, GPS, set top box, mainframe, Raspberry Pi, PC, smartphone, tablet, ...)