SQLite semi Embedded database functionality in AutoIt

[randallc]

Hi,

I mean to write a whole new array (result of editing the array in lisytview as in Music.au3 above), and using (as in func above) zero row for row headings and for sql names.

ie an entirely new db.

Best, Randall

[piccaso]

@randallc

what are you trying to do?

[randallc]

Hi,

Try the GUI in my posted script mymusic.au3 (buggy as run in another function, sorry) [post 319]

I am displaying the whole DB by first changing it to an array.

then edit the listview. [at present only edit cell, sort by column or row, transpose working]

then I want to write the modified listview as array to a new db from scratch, without reference to modifying the original.

Is this easy?

(I'm using this as example to learn.)

Best, Randall

I guess my problem is;

Creating a new DB

Putting a 2D array into the DB using your commands.

Edited December 2, 2005 by randallc

[piccaso]

i dont have 'Array2D.au3'...

did you take a look at the database structure?

of course you can recreate that, but its not easy

and in the examle the database is only partly displayed/altered

it would be easyer to copy the database and alter the copy...

[randallc]

Point taken!

i dont have 'Array2D.au3'...

-see my signature

But i would love to be able to produce the simplests possible db, equivalent to a simple 2D array, from other data. [as ptrex has done by his commands, in his gridexample, just producing random numbers into an array.]

Is this possible with your commands?

Randall

[piccaso]

i'm not sure what it's good for but its possible.

take a look alt exampe_GetTable.au3 in SQLite.zip

it shows how to put data into a table and get it out to a array...

[Greenseed]

Many Tha to everyone for Mymusic7.au3

@Pic your the man! hehe

Bye bye!

[randallc]

i'm not sure what it's good for but its possible.

With the previous @ptrex wrapped function, I could sort huge databases that Excel could not load, and do it quickly; I just want to transpose that functionality and see if it is still quick.

Also quicker than listview sort or array sort.

I am still plugging away at your example to convert it.

I'll post my problem later; lost it!

Best, Randall

[piccaso]

if you want to do stuff fast use temp tables and _SQLite_Query() / _SQLite_Fetch* method (see example_query.au3)

[piccaso]

Added _SQLite_QuerySingleRow() & example

SQLite.zip

[jpm]

Added _SQLite_QuerySingleRow() & example

SQLite.zip

Always improving.

Keep your good work.

In case it help I did some housekeeping of your UDF

Edited December 3, 2005 by jpm

[randallc]

2Piccasso

How close is the syntax here?;; still not working..

Thanks Randall

Local $sErrorMsg

if FileExists($DBFile) then FileDelete($DBFile)

_SQLite_Startup ()

_SQLite_Open ($DBFile)

_SQLite_Exec (-1, "CREATE TABLE test( a INTEGER PRIMARY KEY, b TEXT COLLATE unaccented, c INTEGER, d FLOAT );", $sErrorMsg)

_SQLite_Exec (-1, "CREATE INDEX b ON test(b ASC);" , $sErrorMsg)

_SQLite_Exec (-1, "BEGIN TRANSACTION;", $sErrorMsg)

for $i=1 To 100

$s_ValueLine="Data:"&$i&"','"&Random(-10, 10, 1)&"','"&random(1, 10)

_SQLite_QueryNoResult (-1, "INSERT INTO test( b, c, d ) VALUES('"&$s_ValueLine&"');")

Next

_SQLite_Exec (-1, "COMMIT TRANSACTION;", $sErrorMsg)

_SQLite_Exec (-1, "VACUUM", $sErrorMsg)

_SQLite_Close (-1)

_SQLite_Shutdown ()

[piccaso]

Changed Standard $hDB Handling (Thank you JPM)

Fixed Return Values of _SQLite_LibVersion(),_SQLite_LastInsertRowID(),_SQLite_Changes(),_SQLite_TotalChanges()

SQLite.zip

@JPM

Thank you for cleaning up again

It's a Realy good idea moving Standard db handle handling to the internals

about -> No Value ?

Some Functions do not return anything (or 0) on error like the sqlite api does.

because they dont pass out the result byref (like most query functions do)...

some of them did return $SQLITE_MISUSE, wich was not ment to be (corrected that)

@randallc

i'll take a look at it...

@all who care about doc's

I'm having serios problems expressing myself in english, please be patient

if somewone would like to help me with this, i would appreciated any help (not only on docs)

[piccaso]

2Piccasso

How close is the syntax here?;; still not working..

Thanks Randall

Consider using _SQLite_QueryNoResult() instead of _SQLite_exec() if you dont want to handle $SQLITE_BUSY issues yourself.

and check for errors

#include "sqlite.au3"
Local $sErrorMsg, $DBFile = "randallc.db", $sSQL, $iRval
if FileExists($DBFile) then FileDelete($DBFile)
_SQLite_Startup ()
_SQLite_Open ($DBFile)
$iRval = _SQLite_QueryNoResult (-1, "CREATE TABLE test( a INTEGER PRIMARY KEY, b TEXT , c INTEGER, d FLOAT );")
if not $iRval = $SQLITE_OK Then
    MsgBox(0,"SQLite Error: " & $iRval,_SQLite_ErrMsg())
EndIf
_SQLite_QueryNoResult (-1, "CREATE INDEX b ON test(b ASC);")
_SQLite_QueryNoResult (-1, "BEGIN TRANSACTION;")
for $i=1 To 100
    $s_ValueLine="Data:"&$i&"','"&Random(-10, 10, 1)&"','"&random(1, 10)
    $iRval = _SQLite_QueryNoResult (-1, "INSERT INTO test( b, c, d ) VALUES('"&$s_ValueLine&"');")
    if not $iRval = $SQLITE_OK Then
        MsgBox(0,"SQLite Error: " & $iRval,_SQLite_ErrMsg())
    EndIf
Next
_SQLite_QueryNoResult (-1, "COMMIT TRANSACTION;"); There is no BEGIN so why COMMIT it ?
_SQLite_QueryNoResult (-1, "VACUUM;")
_SQLite_Close (-1)
_SQLite_Shutdown ()

[jpm]

@JPM

Thank you for cleaning up again

It's a Realy good idea moving Standard db handle handling to the internals

about -> No Value ?

Some Functions do not return anything (or 0) on error like the sqlite api does.

because they dont pass out the result byref (like most query functions do)...

some of them did return $SQLITE_MISUSE, wich was not ment to be (corrected that)

I don't understand what you mean by "about -> No Value" can you clarify what you want to do.

For the return AutoIt always return something whatever you want so you better to return what you want.

[piccaso]

Changed _SQLite_Open() now opens a ':memory:' database if no name specified

SQLite.zip

Memory Databases are lightning fast and perfect for examples (no files created)

but they cant be shared with other processes (AFAIK)

@JPM

with 'about -> No Value ?' i ment the func's you added '; ?JPM? no VALUE ???'

at least the doc's say that Return returns 0 by default

and if @error happens the return value should not be used (because ther was an error calling sqlite.dll)

i apologize for my bad english

(we austrian's dont even speak german right )

[jpm]

Changed _SQLite_Open() now opens a ':memory:' database if no name specified

SQLite.zip

Memory Databases are lightning fast and perfect for examples (no files created)

but they cant be shared with other processes (AFAIK)

@JPM

with 'about -> No Value ?' i ment the func's you added '; ?JPM? no VALUE ???'

at least the doc's say that Return returns 0 by default

and if @error happens the return value should not be used (because ther was an error calling sqlite.dll)

i apologize for my bad english

(we austrian's dont even speak german right )

I remember now the ?JPM?.

Your function return value in sompe case so it is always better that all return return something if one is returnning anything. Just a coherency whatever AutoIt does.

I am a French trying to speak English not speaking german/austrian just a little bit of spanish

[randallc]

Hi,

Thanks, This seems to have thje structure showing in database created, but I cannot see the data (using ptrex database reader from first post in thread).

Did it work for you?

Why do you say there is no "begin";:line 11;?

_SQLite_QueryNoResult (-1, "BEGIN TRANSACTION;")

for $i=1 To 100

randall

EDIT; works if I remove "vacuum"; Thanks

Edited December 4, 2005 by randallc

[piccaso]

@jpm

your right, i'll add a 0 on next release

@randallc

oh, i didnt see the BEGIN.

yes it worked.

ptrex's gui uses version 3.2.5 which is not compatible with version 3.2.7 (latest)

but if you want to view data with this gut you can point _SQLite_Startup("sqlite.dll") to the dll ptrex's gui is using... (with the disatvantage that you use a old version of sqlite)

or use example_gui.au3 to view data (but you need some sql(ite) skills)

@greenseed

i played around a bit with sql/perfomance to learn stuff and modified 'album scrubber'

its much faster now.

perhaps you want to offer this on some xbox forums since you sayd that xbox users would like to have someting...

(i dont care much about credits, but you could mention that it was done using AutoIT...)

and pm me a link, i would like to see what the users say...

[randallc]

@Piccaso

1. Thanks; You will see it was the "vacuum" was my problem!

You are using 'exec" in your examples, but have changed that in my script to "_SQLite_QueryNoResult";

Why, and what is the difference?

2. Why did Music.db not just use Ptrex database modifier from first post? - seems to work OK?

Thanks again, Randall