SQLite semi Embedded database functionality in AutoIt

[ptrex]

@Greenseed

I have tried to open the DB with SQLite.DLL and SQLite_GUI, it does not open correctly.

This means that the PySQL is using a different version of SQLite to create a DB. The problem is that Database that are created with other versions of SQLite, are NOT compatible with each other.

Example : once a DB is created with Version 2.7 for instance can not be accessed with Tools that use SQLite version 3.2.

The only way to access the data is to use the command line tool and export (DUMP) the data and import again in the DB of a newer version.

I hope this explains to you why you cannot use these SQLite Tools which use the latest veriosn of SQLite.

PS : What you can try is to download an older version for the SQLite Homepage, and try a DLL call using that version. There is still a version 2.xx available on the site.

[Greenseed]

thx i will try! but when i open a db file created with SQLite3.dll registered on my computer! i can view this file from any db viewer

and if i open in HEX workshop i see the signature of SQLite3 version

with the db created with pySQLite3, when i open with HEX workshop i got signature SQLite3 version.

then im assuming is the same version! and on the XBMC developer site the say he use SQLite3 from python! i will try your idea!

This project is for 1 of my freind who are noob with pc! it for him, i'm searching a simple interface like Spreedsheet to edit table in the db like deleting! nothing really big!

juste a delete function will be nice,just to be able to delete bad entry!

anyway thx for the help!

i've juste try version 2 and 3 both available on the web site! no one work! but the version 2 do not crash like version 3 when i try open.

Edited November 29, 2005 by Greenseed

[piccaso]

@ptrex

its the latest version and its the standard version, its just upx'ed

@Greenseed

the database you posted is/was most likely created with sqlite > 3.2.5

3.2.7 work's fine for me...

3.2.5 (using litex dll) gives errors

(try example_gui.au3 i included in SQLite.zip

you dont need to register sqlite3.dll, just put it in the same folder)

or use latest sqlite.exe console tool

[ptrex]

@picasso

I am not sure where you' ve got the SQLite from, but I downloaded this :

sqlitedll-3_2_7.zip (134.62 KiB) form here http://www.sqlite.org/download.html

If you open de the ZIP file you will see that the DLL is 245 Kb large.

The one you uploaded is 120 Kb large ?

[piccaso]

@ptrex

its just compressed using upx

btw. did you realize that the litex.dll can be recompiled using 3.2.7 ?

i'm not sure about legal stuff, but if i can compile it i'll include that one...

(because it also exports the sqlite3.dll functions and can be used the same way even without registering)

@blink314

i think you will like the new option of _SQLite_GetTable2d()

(dont ask me why i never thought of this bevore...)

update:

Changed _SQLite_GetTable2d(), Ability to Swich Dimensions

Fixed _SQLite_Display2DResult() $iCellWidth was ignored

SQLite.zip

Edited December 1, 2005 by piccaso

[ptrex]

@picasso

Thanks a lot for the feedback.

Seems that you know a lot of interesting things.

- Compressing a DLL without compromizing it' s funtionality is great !! I didn' t try that before.

btw. did you realize that the litex.dll can be recompiled using 3.2.7 ?

How can this be done. I am interesting in learning these techniques as well.

What kind of tools do you use for that ?

Thanks for the update as well.

[piccaso]

What kind of tools do you use for that ?

msvc/c++ which i dont have..

and some c skills are nedded

which i dont have either

[gcriaco]

Very nice freeware Tool: Sqlite Browser

Edited December 1, 2005 by gcriaco

[ptrex]

Thanks for the tip.

I know that there are 10 or more SQLite browsers availble elsewhere.

But over here we aim to get SQLite into AutoIT.

[Greenseed]

thx to everyone for the help! but i found how to convert to version 3.2.5 to 3.2.7

simply use this command

sqlite3 OLD.DB .dump | sqlite3 NEW.DB

like on the site web of sqlite but i use for both sqlite3.2.7 and it work fine! my db before was 420Kbytes and unreadble with sqlite browser

now it about 37Kbytes and everyone read it!

thx everyone for the help

[Greenseed]

hello everyone!

im trying to play with my xbox database now im able to convert it to Sqlite 3.2.7

but i don't understand to much Sqlite!

if anyone can wrote me a script to be able!

to see all albumname is in there! and delete it!

when i delete a album i wan, all song and anything associated to this album deleted also!

im a little lost in this! help will be apreciated!

and im sure every XBMC Xbox user will be very happy because no one for the moment have been able to open it database! and delete wrong entry!

thx in advance! i've put a attachement of the convert database and some entry in there!

mymusic7.rar

[piccaso]

@ptrex

could you please link here (SQLite.zip) in your 1'st post.

thanks

@greenseed

i'll take a look at it.

maybe...

[piccaso]

@greenseed

try this

it should be able to open th db in its original state to.

but it doesent take care about ex*, genere and artist tables

you have to improve it yourself but it should give you the right start

edit: sorry uploaded something wrong

Edited December 1, 2005 by piccaso

[Greenseed]

sorry dude! im understand nothing at Sqlite Query! for the moment! can you just add the display of artis name after the album name and multiple selection if possible!

and as you see the db is very weird with many wrong entry!

thx a lot dude!

and superb nice job! thx many many time!

bye bye!

Edited December 2, 2005 by Greenseed

[piccaso]

multiline isnt sqlite spesific, so you could approach this your own if you realy want it...

there is still no cleanup for artist,genere and i still dont know what the ex* tables are for.

(and there never was great error handling...)

[Greenseed]

everything work fine! the database apear correctely on my xbox after modification! many thx!

what i have to add to the sql query if i wan to sort a-z the artis name

thx in advance!

bye bye!

[piccaso]

ok it wasnt that hard

still is messy...

listview sorts on its own, check autoit docs on _GUICtrlListViewSort()

Edited December 2, 2005 by piccaso

[ptrex]

@picasso

I added the link in the first post.

Thanks

[randallc]

@Green

Note for Display with listview sorting, you could change function to;

Func updatelist1()

Local $vRval, $hQ1, $asAlbum, $Tmp

local $ar1_Array_Arrays[1]$vRval = _SQlite_Query (-1, "SELECT strAlbum,idAlbum FROM album ORDER BY strAlbum", $hQ1)

If $vRval = $SQLITE_OK Then

$i=0

$ar1_Array_Arrays[$i]=_Stringsplit_B0("idAlbum | strAlbum | strArtist")

While _SQLite_FetchData ($hQ1, $asAlbum) = $SQLITE_OK

$i=$i+1

$s_Line=$asAlbum[1] & "|" & $asAlbum[0] & "|" & _QuerySingleResult("SELECT strArtist FROM albumview WHERE idAlbum ='" & $asAlbum[1] & "';")

if ($i>ubound($ar1_Array_Arrays)-1) then

redim $ar1_Array_Arrays[$i+1]

EndIf

$ar1_Array_Arrays[$i]=_Stringsplit_B0($s_Line)

WEnd

Else

MsgBox(0, "SQlite Error", _SQLite_ErrMsg ())

EndIf

;$tmp = 0

$ar2_Array=_Array2DCreateFromArray($ar1_Array_Arrays )

_ArrayViewText($ar2_Array, 'Albums; Sort by click, edit ListView in site',0,0,0,0)

return $ar2_Array

EndFunc ;==>updatelist1

I would like to know from @Picasso how to write any listview changes back to a new DB using his commands (the Array Display could itself be enhanced to put out an Array after editing)

best, Randall

Edited December 2, 2005 by randallc

[piccaso]

@randallc

if you mean changing the album name (strAlbum) you need new album name and idAlbum and do a update like this:

_SQLite_QueryNoResult(-1,"UPDATE album SET strAlbum='$newalbum' WHERE idAlbum = '$idAlbum';")

Edited December 2, 2005 by piccaso