SQLite semi Embedded database functionality in AutoIt

[JSThePatriot]

Unfortunately with the development of plugins still in beta stages the SQLite4AutoIt plugin will be put on hold until the plugin architecture can handle the returns that I need.

Thanks,

JS

[piccaso]

@ptrex

i'll add the SQL Features wrapper functions when v2 is done...

this is the easy part

@JS

Can plugin functions have optional byref parameters?

[JSThePatriot]

@ptrex

i'll add the SQL Features wrapper functions when v2 is done...

this is the easy part

@JS

Can plugin functions have optional byref parameters?

Nope... , and only one return parameter.

JS

[piccaso]

... like Autoit's functions.

thats why i used '$hDB = _SQLite_Open($dbFilename)' and would prefer it

[JSThePatriot]

... like Autoit's functions.

thats why i used '$hDB = _SQLite_Open($dbFilename)' and would prefer it

That is what it will be, but I have to get AutoIt to support that type of return.

...

JS

[piccaso]

i have read a bit of the php docs and i have no good news...

Supporting php's functionality would require to use gettable only and drop usage of prepare/step otherwise the data would not be available.

that would require a allmost complete rewrite, and binary support will be lost to.

php's 'sqlite' functions are for sqlite2 where the prepare/step thing didnt exist so they implemented something similar into php.

php's PDO supports sqlite3...

this is more work than i have thought and i wont start it because i would never finish it.

[JSThePatriot]

i have read a bit of the php docs and i have no good news...

Supporting php's functionality would require to use gettable only and drop usage of prepare/step otherwise the data would not be available.

that would require a allmost complete rewrite, and binary support will be lost to.

php's 'sqlite' functions are for sqlite2 where the prepare/step thing didnt exist so they implemented something similar into php.

php's PDO supports sqlite3...

this is more work than i have thought and i wont start it because i would never finish it.

I read them a bit as well, but I dont think we want to strictly follow their implementation of the library, just use their naming standards.

If I can get the plugin issues resolved soon there wont really be a need for the DllCall version other than testing the speed of each .

JS

[ptrex]

@JSThePatriot

but I dont think we want to strictly follow their implementation of the library, just use their naming standards

YES indead. I am glad you understood the whole concept

@piccaso

It was never said that we would copy what was there in PHP.

Again I would only like the see the structure implemented like PHP did.

Meaning that they a lot in SEPARATE functions available in there UDF module.

This way it makes it easy for NEWBIE's to script and EASY for the other's to call the functions they need.

So the ultimate goal is to have as many functions available in the UDF which can be called individually.

This is all more about a conceptual matter rather than a functional matter.

Maybe a way to start it is to gether a list of function (names) we would like to see in the UDF.

Than agree on it. Than imoplement it. This will avoid miscommunictions.

Do you agree on this approach.

regards

ptrex

[ptrex]

@piccaso

I will do an attempt to do a mapping of the functions we find in SQLite UDF and PHP.

See what is available and what's not. See what is there and needs renaming.

To RENAME and or CHANGE

; SQLite UDF --------------> PHP function

; _SQLite_Startup

; _SQLite_Shutdown

; _SQLite_Open ----------> sqlite_open

; _SQLite_Close -----------> sqlite_close

; _SQLite_GetTable

; _SQLite_Exec -----------> sqlite_exec

; _SQLite_LibVersion -----> sqlite_libversion

; _SQLite_LastInsertRowID - sqlite_last_insert_rowid

; _SQLite_GetTable2d ----> sqlite_current

; _SQLite_Changes -------> sqlite_changes

; _SQLite_TotalChanges

; _SQLite_ErrCode

; _SQLite_ErrMsg --------> sqlite_error_string

; _SQLite_Display2DResult - sqlite_array_query

; _SQLite_FetchData -------> sqlite_fetch_all

; _SQLite_Query -----------> sqlite_query

; _SQLite_SetTimeout -----> sqlite_busy_timeout

; _SQLite_SaveMode

; _SQLite_QueryFinalize

; _SQLite_QueryReset

; _SQLite_FetchNames ------> sqlite_column

; _SQLite_QuerySingleRow -> sqlite_fetch_array (fetch next row)

; _SQLite_SQLiteExe

; _SQLite_Encode -----------> sqlite_udf_encode_binary

; _SQLite_Escape -----------> sqlite_escape_string

To DECIDE which one to add

; sqlite_create_aggregate

; sqlite_create_function

; sqlite_factory

------> Not needed

; sqlite_fetch_column_types

; sqlite_fetch_object

; sqlite_fetch_single

; sqlite_fetch_string

; sqlite_num_rows

; sqlite_field_name

; sqlite_num_fields

; sqlite_has_more

; sqlite_has_prev

; sqlite_key

; sqlite_last_error

; sqlite_next

; sqlite_prev

; sqlite_rewind

; sqlite_seek

; sqlite_popen ----------------> Not needed

; sqlite_single_query

; sqlite_libencoding

; sqlite_udf_decode_binary

; sqlite_unbuffered_query

; sqlite_valid

NEW to add

_SQLite_Analyze($hDB,$sTable)

_SQLite_Attach($hDB,$sDatabase)

_SQLite_Detach($hDB,$sDatabase)

_SQLite_Explain($hDB,$sSQL)

_SQLite_Vakuum($hDB = -1, $sTable = "")

_SQLite_Begin($hDB = -1)

_SQLite_End($hDB = -1)

_SQLite_Commit($hDB = -1)

_SQLite_Rollback($hDB = -1)

_SQLite_Pragma( ...)

First decide which functions needs to be added to the UDF.

Than prioritize, which missing function needs to be added first.

Than agree in the naming standard of the new added functions

Of the existing functions we only need to decide upon the name and content of the funtion.

Everyone feel free to add, change and or comment.

I hope this will get the ball rolling.

regards,

ptrex

Edited September 27, 2006 by ptrex

[piccaso]

added my 2 cents to the list...

To RENAME and or CHANGE

; SQLite UDF --------------> PHP function

; _SQLite_Startup

; _SQLite_Shutdown

; _SQLite_Open ----------> sqlite_open

; _SQLite_Close -----------> sqlite_close

; _SQLite_GetTable

; _SQLite_Exec -----------> sqlite_exec

; _SQLite_LibVersion -----> sqlite_libversion

; _SQLite_LastInsertRowID - sqlite_last_insert_rowid

; _SQLite_GetTable2d

; _SQLite_Changes -------> sqlite_changes

; _SQLite_TotalChanges

; _SQLite_ErrCode -------> sqlite_last_error

; _SQLite_ErrMsg --------> sqlite_error_string

; _SQLite_Display2DResult

; _SQLite_FetchData -------> sqlite_fetch_array

; _SQLite_Query -----------> sqlite_query

; _SQLite_SetTimeout -----> sqlite_busy_timeout

; _SQLite_SaveMode

; _SQLite_QueryFinalize

; _SQLite_QueryReset ------> sqlite_rewind

; _SQLite_FetchNames ------> sqlite_column

; _SQLite_QuerySingleRow -> sqlite_single_query

; _SQLite_SQLiteExe -> i'd like to remove that one for v2

; _SQLite_Encode

; _SQLite_Escape -----------> sqlite_escape_string

To DECIDE which one to add

; sqlite_fetch_column_types

; sqlite_fetch_single = sqlite_fetch_string

; sqlite_num_rows

; sqlite_field_name

; sqlite_num_fields -> Ubound($aRow)

; sqlite_has_more

; sqlite_has_prev

; sqlite_key

; sqlite_last_error

; sqlite_next

; sqlite_prev

; sqlite_seek

; sqlite_single_query

; sqlite_udf_decode_binary

; sqlite_unbuffered_query

; sqlite_valid

NEW to add

_SQLite_Analyze($hDB,$sTable)

_SQLite_Attach($hDB,$sDatabase)

_SQLite_Detach($hDB,$sDatabase)

_SQLite_Explain($hDB,$sSQL)

_SQLite_Vakuum($hDB = -1, $sTable = "")

_SQLite_Begin($hDB = -1)

_SQLite_End($hDB = -1)

_SQLite_Commit($hDB = -1)

_SQLite_Rollback($hDB = -1)

_SQLite_Pragma( ...)

my prioritys would look like this:

1. Write a list of functions.

2. Rename the functions, correct examples doc's n stuff

3. Write missing functions and document them

4. Write the 'SQL Feature' Wrappers

but right now im not sure if its worth the effort...

[ptrex]

@piccaso

my prioritys would look like this:

1. Write a list of functions.

2. Rename the functions, correct examples doc's n stuff

3. Write missing functions and document them

4. Write the 'SQL Feature' Wrappers

I fully agree with this approac.

but right now im not sure if its worth the effort...

Why is that ?

ptrex

[JSThePatriot]

I wanted to give my 2 cents in here... the naming looks pretty good so far. I will make a list of what I think, and compare it to the two compiled above.

I also want to let you know that Valik has been so kind as to inform me of a work-around in the plugin area. It will slow me down for a bit as I will have to decide how it is to be done. We are moving forward again so that is good!

JS

[ptrex]

@piccaso

This is an example of how I quickly created a count columns from one of your UDF's.

;#include <SQLite.dll.au3>

#include <SQLite.au3>

Local $hQuery, $iCount, $aRow

_SQLite_Startup ()

_SQLite_Open () ; open :memory: Database

_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c);")

_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');")

_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');")

_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');")

_SQlite_Query (-1, "SELECT ROWID,* FROM aTest ORDER BY a;", $hQuery) ; Including RowID

_SQLite_ColumnCount ($hQuery, $iCount) ; Read out the ColumnCount

MsgBox(0,"SQLite","Get # Columns using ColumnCount : " & _

StringFormat(" %-10s %-10s %-10s %-10s ", $iCount) & @CR)

While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK

MsgBox(0,"SQLite","Get Data using FetchData : " & StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)

WEnd

_SQLite_Exec (-1, "DROP TABLE aTest;")

_SQLite_Close ()

_SQLite_Shutdown ()

Func _SQLite_ColumnCount($hQuery, ByRef $iCount)

If Not __SQLite_hChk($hQuery, $SQLITE_QUERYHANDLE) = $SQLITE_OK Then Return SetError(3, 0, $SQLITE_MISUSE)

$avDataCnt = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery)

If @error > 0 Then

Return SetError(1, 0, $SQLITE_MISUSE) ; DllCall Error (sqlite3_column_count)

ElseIf $avDataCnt[0] > 0 Then

$iCount = $avDataCnt[0]

Return $SQLITE_OK

Else

Return SetError(-1, 0, $SQLITE_EMPTY)

EndIf

EndFunc ;==>_SQLite_ColumnCount

This is derived from the function SQLite_FetchNames which containd 2 SQLite functions ColumnCount and ColumnNames.

Like I said earlier it is better to have tham available as seperate functions.

ColumnCount is the opponent of the PHP function sqlite_num_fields.

Can we discuss this approach.

regards

ptrex

Edited September 28, 2006 by ptrex

[ptrex]

@picasso

Can you explain to me how I can test the PRAGMA command using your UDF.

I have tried either way using _SQLite_Exec and _SQLite_Query.

The command I tested was ""PRAGMA schema_version", "PRAGMA page_size" and "PRAGMA Integrity_check"

But no results.

Maybe it"s just a matter of understanding.

If I know how to get the ball rolling I will write some UDF's

regards

ptrex

[piccaso]

#include <sqlite.au3>
#include <sqlite.dll.au3>

Local $aRow, $aResult, $iDummy
_SQLite_Startup()
_SQLite_Open() ; Memory Database
_SQLite_QuerySingleRow(-1,"PRAGMA integrity_check;",$aRow)
ConsoleWrite("Integry Check: " & $aRow[0] & @LF)
_SQLite_GetTable2d(-1,"PRAGMA table_info('sqlite_master');",$aResult,$iDummy,$iDummy)
_SQLite_Display2DResult($aResult)
_SQLite_Shutdown()

if you write some udfs for this library please make them conform to the autoit udf standards as good as possible

i'm to busy now, i'll merge them in the next days...

right now i think compleating documentation is the best thing i can do.

renameing for v2 (especial the tests afrer) is to time consuming for now.

[ptrex]

@piccaso

expandcollapse popup

Local $hQuery, $iCount, $aRow, $test, $hQuery1
_SQLite_Startup ()
_SQLite_Open () ; open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c);")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');")
_SQlite_Query (-1, "SELECT ROWID,* FROM aTest ORDER BY a;", $hQuery) ; Including RowID

_SQlite_Query (-1,"PRAGMA page_size;",$hQuery1) ; Execute a PRAGMA command
_SQLite_FetchData ($hQuery1, $test)
MsgBox(0,"SQLite","Get the PAGE Size using Pragma cmd's: " & $test[0]& @CR)

_SQLite_ColumnCount ($hQuery, $iCount) ; Read out the ColumnCount
MsgBox(0,"SQLite","Get # Columns using ColumnCount : " & $iCount & @CR)

While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK   
    MsgBox(0,"SQLite","Get Data using FetchData : " &  StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
WEnd

_SQLite_Exec (-1, "DROP TABLE aTest;")
_SQLite_Close ()
_SQLite_Shutdown ()
 

Func _SQLite_ColumnCount($hQuery, ByRef $iCount)
    If Not __SQLite_hChk($hQuery, $SQLITE_QUERYHANDLE) = $SQLITE_OK Then Return SetError(3, 0, $SQLITE_MISUSE)
    $avDataCnt = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery)
    If @error > 0 Then
        Return SetError(1, 0, $SQLITE_MISUSE) ; DllCall Error (sqlite3_column_count)
    ElseIf $avDataCnt[0] > 0 Then
        $iCount =  $avDataCnt[0]
        Return $SQLITE_OK
    Else
        Return SetError(-1, 0, $SQLITE_EMPTY)
    EndIf
EndFunc   ;==>_SQLite_ColumnCount

Finally got the PRAGMA cmd's working.

But it' s far to complex !!

I am just trying to illustrate that writing an application using the UDF will take forever. I needs to be dramatically simplified.

It seems that if you are creating an appl. which uses 100 SQL statements the _SQLite_Query handle isn't reset in a way that you can reuse the handle Variable again.

This has per consequence that one need to define 100 different variables in the application for each different Query to run ?

I am correct here, please let me know of I am approaching this from the wrong end

regards

ptrex

Edited September 28, 2006 by ptrex

[ptrex]

@piccaso

if you write some udfs for this library please make them conform to the autoit udf standards as good as possible

That's why I am reusing yours.

But don't beat me if I am missing someting because I am not familiar with the UDF Standards.

And not with the Error handling you have put in there.

It's to time consuming for me now to dig into that right now. We will have to adjust to the standards later.

First priority now is to get the functionality correct and there still a long way to go.

regards

ptrex

Edited September 28, 2006 by ptrex

[piccaso]

thats why i sayd 'as good as possible'

[JSThePatriot]

@piccaso

expandcollapse popup

Local $hQuery, $iCount, $aRow, $test, $hQuery1
_SQLite_Startup ()
_SQLite_Open () ; open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c);")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');")
_SQlite_Query (-1, "SELECT ROWID,* FROM aTest ORDER BY a;", $hQuery) ; Including RowID

_SQlite_Query (-1,"PRAGMA page_size;",$hQuery1) ; Execute a PRAGMA command
_SQLite_FetchData ($hQuery1, $test)
MsgBox(0,"SQLite","Get the PAGE Size using Pragma cmd's: " & $test[0]& @CR)

_SQLite_ColumnCount ($hQuery, $iCount) ; Read out the ColumnCount
MsgBox(0,"SQLite","Get # Columns using ColumnCount : " & $iCount & @CR)

While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK   
    MsgBox(0,"SQLite","Get Data using FetchData : " &  StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
WEnd

_SQLite_Exec (-1, "DROP TABLE aTest;")
_SQLite_Close ()
_SQLite_Shutdown ()
 

Func _SQLite_ColumnCount($hQuery, ByRef $iCount)
    If Not __SQLite_hChk($hQuery, $SQLITE_QUERYHANDLE) = $SQLITE_OK Then Return SetError(3, 0, $SQLITE_MISUSE)
    $avDataCnt = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery)
    If @error > 0 Then
        Return SetError(1, 0, $SQLITE_MISUSE) ; DllCall Error (sqlite3_column_count)
    ElseIf $avDataCnt[0] > 0 Then
        $iCount =  $avDataCnt[0]
        Return $SQLITE_OK
    Else
        Return SetError(-1, 0, $SQLITE_EMPTY)
    EndIf
EndFunc   ;==>_SQLite_ColumnCount

Finally got the PRAGMA cmd's working.

But it' s far to complex !!

I am just trying to illustrate that writing an application using the UDF will take forever. I needs to be dramatically simplified.

It seems that if you are creating an appl. which uses 100 SQL statements the _SQLite_Query handle isn't reset in a way that you can reuse the handle Variable again.

This has per consequence that one need to define 100 different variables in the application for each different Query to run ?

I am correct here, please let me know of I am approaching this from the wrong end

regards

ptrex

The _SQLite_Exec() should be able to accept numerous SQL statements. If you are wanting to do single statements then use another function, as Exec's purpose is to execute a bunch of commands. Though it can handle one just fine, but you mentioned the need for 100 SQL queries.

JS

[piccaso]

@piccaso

expandcollapse popup

Local $hQuery, $iCount, $aRow, $test, $hQuery1
_SQLite_Startup ()
_SQLite_Open () ; open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c);")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');")
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');")
_SQlite_Query (-1, "SELECT ROWID,* FROM aTest ORDER BY a;", $hQuery) ; Including RowID

_SQlite_Query (-1,"PRAGMA page_size;",$hQuery1) ; Execute a PRAGMA command
_SQLite_FetchData ($hQuery1, $test)
MsgBox(0,"SQLite","Get the PAGE Size using Pragma cmd's: " & $test[0]& @CR)

_SQLite_ColumnCount ($hQuery, $iCount) ; Read out the ColumnCount
MsgBox(0,"SQLite","Get # Columns using ColumnCount : " & $iCount & @CR)

While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK   
    MsgBox(0,"SQLite","Get Data using FetchData : " &  StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
WEnd

_SQLite_Exec (-1, "DROP TABLE aTest;")
_SQLite_Close ()
_SQLite_Shutdown ()
 

Func _SQLite_ColumnCount($hQuery, ByRef $iCount)
    If Not __SQLite_hChk($hQuery, $SQLITE_QUERYHANDLE) = $SQLITE_OK Then Return SetError(3, 0, $SQLITE_MISUSE)
    $avDataCnt = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery)
    If @error > 0 Then
        Return SetError(1, 0, $SQLITE_MISUSE) ; DllCall Error (sqlite3_column_count)
    ElseIf $avDataCnt[0] > 0 Then
        $iCount =  $avDataCnt[0]
        Return $SQLITE_OK
    Else
        Return SetError(-1, 0, $SQLITE_EMPTY)
    EndIf
EndFunc   ;==>_SQLite_ColumnCount

Finally got the PRAGMA cmd's working.

But it' s far to complex !!

I am just trying to illustrate that writing an application using the UDF will take forever. I needs to be dramatically simplified.

It seems that if you are creating an appl. which uses 100 SQL statements the _SQLite_Query handle isn't reset in a way that you can reuse the handle Variable again.

This has per consequence that one need to define 100 different variables in the application for each different Query to run ?

I am correct here, please let me know of I am approaching this from the wrong end

regards

ptrex

hmm...

the PRAGMA stuff is not an UDF

and whats keeping you from reusing variables?

but if you want to prepare 100 query's and store them for later (which doesent make much sence)

you have to store the query handles somwhere, can be an array to...