SQLite and reading table names

[Vakari]

Hello folks.

I just recently discovered SQLite and have been figuring out some fun or interesting things to do with it via AutoIT.

I can write to a database no problem, such as importing an entire spreadsheet into a database. It isn't terribly pretty, but it works.

I can read from a database no problem, as long as I know the table names and such.

What I'm having problems with is getting my code to find the names of each table.

This is an example of the code I'm playing with

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

Local $TableList, $dbname = "test.db"

FileDelete(@ScriptDir & "\" & $dbname)

_SQLite_Startup()
_SQLite_Open($dbname)

_SQLite_Exec(-1, "CREATE TABLE '1' (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE test (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE anothertest (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE 'space test' (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE thisisalongernamethantherest (Name,Date,Status);")

_SQLite_SQLiteExe($dbname, ".tables", $TableList)

_SQLite_Close($dbname)
_SQLite_Shutdown()

MsgBox(0, "", $TableList)
;FileDelete(@ScriptDir & "\" & $dbname) ?

(I'm sure it's because my head is about to explode, but I can't get that last file delete to work, so you'll have "test.db" left over in your @ScriptDir folder. Perhaps I'm not closing the database properly like I thought I was)

Anyway, you'll notice that $Tablelist returns a nice long string that contains all of the table names (kinda). What I want to do from here is create an array from $TableList that has a list of the tables:

$TableNames[0] = 5

$TableNames[1] = '1'

$TableNames[2] = 'test'

$TableNames[3] = 'anothertest'

and so on..

How do I go about breaking down that string into usable names? I can't think of a creative way to use StringSplit(), StringReplace(), or StringInStr() to get what I need. It appears that sqlite (or autoit) will automatically add spaces to any table name so it becomes as long as the longest table name.

It turns 'test' into the same length as 'thisisalongernamethantherest' making it 'test '

If anyone have any spiffy ideas to get me past this step (or explanations as to why I'm too retarded to do it) I would appreciate it greatly if you could give me a pointer.

Thank you for your time, everyone.

[PsaltyDS]

Hello folks.

I just recently discovered SQLite and have been figuring out some fun or interesting things to do with it via AutoIT.

I can write to a database no problem, such as importing an entire spreadsheet into a database. It isn't terribly pretty, but it works.

I can read from a database no problem, as long as I know the table names and such.

What I'm having problems with is getting my code to find the names of each table.

Try this:

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

Global $hQuery, $aRow

_SQLite_Startup()
_SQLite_Open()

_SQLite_Exec(-1, "CREATE TABLE '1' (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE test (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE anothertest (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE 'space test' (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE thisisalongernamethantherest (Name,Date,Status);")

_SQLite_Query(-1, "SELECT * FROM SQLite_Master", $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLite_OK
    _ArrayDisplay($aRow, "$aRow")
WEnd

_SQLite_Close(-1)
_SQLite_Shutdown()

Edited March 18, 2008 by PsaltyDS

[Vakari]

Awesome PsaltyDS! Thank you so much!

SQLite_Master never occured to me.... /sigh

Here is what I whipped up to get that array I was looking for:

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

Local $TableList, $dbname = "test.db", $hQuery, $aRow, $TableNum, $TableCount[1] = [0]

FileDelete(@ScriptDir & "\" & $dbname)

_SQLite_Startup()
_SQLite_Open($dbname)

_SQLite_Exec(-1, "CREATE TABLE '1' (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE test (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE anothertest (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE 'space test' (Name,Date,Status);")
_SQLite_Exec(-1, "CREATE TABLE thisisalongernamethantherest (Name,Date,Status);")

_SQLite_Query(-1, "SELECT * FROM SQLite_Master", $hQuery)

While _SQLite_FetchData($hQuery, $aRow) = $SQLite_OK
    ConsoleWrite("'" & $aRow[1] & "'" &@CR)
    $TableNum = $aRow[3] - 1
    If $TableNum > $TableCount[0] Then $TableCount[0] = $TableNum
    _ArrayAdd($TableCount, $aRow[1]); SCORE !
WEnd

_SQLite_Close($dbname)
_SQLite_Shutdown()

MsgBox(0, "", "There are " & $TableCount[0] & " tables in " & $dbname)
_ArrayDisplay($TableCount, "Tables in " & $dbname)

Thanks again PsaltyDS. You have been most helpful.

Edit: On another note, do you happen to know what was causing the last FileDelete to fail? I commented it out since it wasn't working and moved it to the beginning of the script.

Edited March 19, 2008 by Vakari

[PsaltyDS]

Thanks again PsaltyDS. You have been most helpful.

You're welcome.

Edit: On another note, do you happen to know what was causing the last FileDelete to fail? I commented it out since it wasn't working and moved it to the beginning of the script.

No idea, I didn't bother with a file as it was irrelevant to the SQLite issue. One problem might be that you opened it without the full path, so it may have been created somewhere else, like the current @WorkingDir. Much safer to ALWAYS provide a full path.