Sign in to follow this  
Followers 0
Vakari

SQLite and reading table names

4 posts in this topic

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.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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