SQLite semi Embedded database functionality in AutoIt

[McGod]

SQLite will not work for me anymore. I ALWAYS get Error Returned : Library used incorrectly. The example_browser won't work ethier. I've tried

-Reinstalling Sqlite3.dll

-Reverting to old Autoit installs

-Downloading latest SQLite.au3

It won't work and I need it bad :X:X:X:X

[ptrex]

@Chip

I jusst did a test running :

- SQLite Version: 3.3.5

- Au3 Version : 3.2.1.14

And no problems here.

Maybe some more users can do some tests

regards,

ptrex

[smashly]

Hi,

Dumb question , where do I obtain excelcom.au3 that is used with SQLite_Gui.au3 ?

I did do a forum search for it , but the closest I come is Yet Another -- ExcelCOM UDF....

Slap and point me please.

Thank you.

Cheers

[ptrex]

@smashly

The "excelcom.au3" has been replaced over time by the Yet Another -- ExcelCOM UDF....

So you can't just copy and paste the code anymore out of the example as it is.

What you can do is look at my example in my signature, called Excel Grid in AU3 or something.

This should get you going.

regards

ptrex

[DjDeep00]

Out of curiosity, why would the client not talk to the server, and the server work with the database?

I think that this could possibly be built in some how, but SQLite isn't setup as a server application. You could create a wrapper that would give it some functionality that you are looking for. SQLite is a single file database. The ODBC fix is one way, and one persons response to what you are wanting to do. Unfortunately it seems you have decided to go with MySQL. Another great database option, but requires an install on a machine for it to work. I am personally a fan of small, fast, and portable.

I hope this helps a bit more,

JS

JSThePatriot and Piccaso,

What is the ODBC fix and how do I get started using this in Windows? The whole point of me using the Sqlite ODBC is probably because my sqlite database is on a network drive and somehow I am trying to make it faster when multiple people are querying the database. If there is another way then please point me to it.

Please advise.

Thanks in advance.

[JSThePatriot]

JSThePatriot and Piccaso,

What is the ODBC fix and how do I get started using this in Windows? The whole point of me using the Sqlite ODBC is probably because my sqlite database is on a network drive and somehow I am trying to make it faster when multiple people are querying the database. If there is another way then please point me to it.

Please advise.

Thanks in advance.

Sorry that this has taken me a few days to respond to. ODBC is a driver based layer used to access different databases. Look on SQLite.org for more information about their ODBC driver.

I recommend if you are trying to allow multiple users to use the SQLite database at the same time you have a program that actually accepts all the requests, and processes them as they come in. (Basically creating your own server application) This I think would help.

I hope I have helped you further,

JS

[randallc]

hi,

sorry I missed this; link in my sig;

Best, randall

(BTW the sql there was before sql was in AutoIt and there is probably a clash; haven't tried it since...)

[Valuater]

posts = 666 views = 49,666

Just wanted to say thanks ptrex for the efforts and change the post & count form 666

8)

Edited June 6, 2007 by Valuater

[ptrex]

@all

Those who are interested in the ODBC drive, look here

SQLite ODBC driver

@Valuater

Thanks for the kind words, but actually most credits should go to Piccaso for making it work through DLLCall in AU3 !!

See you all around.

regards

ptrex

[DjDeep00]

Sorry that this has taken me a few days to respond to. ODBC is a driver based layer used to access different databases. Look on SQLite.org for more information about their ODBC driver.

I recommend if you are trying to allow multiple users to use the SQLite database at the same time you have a program that actually accepts all the requests, and processes them as they come in. (Basically creating your own server application) This I think would help.

I hope I have helped you further,

JS

Thanks JSThePatriot....I guess ODBC is not the way that I want to go since I am not accessing different database. Just one database but with lots of users doing (Update, Insert, and Selects) on the same database at the same time. Like you said, I need to come up with a way to put all of those requests in some kind of a queue and handle them one by one. Hmmmm....I wonder if anyone has already sort of kind of done this already?...

[jokke]

Iwe got the exact same problem as Chip wrote on top of this page (45) iwe not tested with V.3.2.1.14 yet though since i just cant find where to DL it.

Edited June 16, 2007 by jokke

[james3mg]

Thanks JSThePatriot....I guess ODBC is not the way that I want to go since I am not accessing different database. Just one database but with lots of users doing (Update, Insert, and Selects) on the same database at the same time. Like you said, I need to come up with a way to put all of those requests in some kind of a queue and handle them one by one. Hmmmm....I wonder if anyone has already sort of kind of done this already?...

I've been using the _SQLite functions from this thread in a (rather large) DB program of mine that is accessible to many people over the network at a time - I found that since many people can have the DB file OPEN at a time (it's only locked when reading/modifying the DB file) and the actual times for reading/writing the DB are very quick, I wrote in something like:

For $i=1 To 26
    $_DBResult=_SQLite_Exec($TheDB,"Replace into TableA Values('a,b,c')")
    If $_DBResult=$SQLITE_OK Then ExitLoop
    Sleep(50)
Next
If $_DBResult<>$SQLITE_OK Then
    MsgBox(16,"26 attempts failed","Unable to write record - make sure database is not locked")
EndIfoÝ÷ Ù©ÝÝý³
+çiz·ÞrÙrz÷«ÊئzÊj´áÈî²Û^["¶Ú-¯+©¶º¶)²Ú0®+^Ê«u«Zjwb~+]¡ë'ßÛp¢¹0j+yÒk-¢)yû¥&¥-àÁ¶Þ­é^jÇmçè­ëkÊ)àj¢.r¥u©l¡«­¢+Ù¼(M±À ÔÀ¤)U¹Ñ¥°}ME1¥Ñ}á ÀÌØíQ¡°ÅÕ½ÐíIÁ±¥¹Ñ¼Q±Y±ÕÌ Ìäí±±Ìäì¤ÅÕ½Ðì¤ôÀÌØíME1%Q}=

if you want it to keep trying until it works (not recommended). However, I only have a dozen or so machines running this at a time - if you need more than that or your script is nearly CONSTANTLY reading/writing (mine sits idle most of the time, with periodic user-triggered reads/writes) the DB file, then you'll need another way. This just worked for me, and I hoped it might help others

[piccaso]

james3mg check out _SQLite_SetTimeout() which should handle things like this internal.

About this 'library used incorrect' trouble: Could you provide a bit more detail ?

edit: in here

Edited June 19, 2007 by piccaso

[gadzail]

Hi all,

a news from the main site of SQLite.

I found this problem in SQLite site:

http://www.sqlite.org/cvstrac/wiki?p=Corru...lowingBusyError

He strongly recommend update SQLite to version 3.4.0 (see "news 2007-Jun-18 - Version 3.4.0")

[Edek]

I don't know where is good place to talk about SQLite3 UDF's.

Here's a link to my post created in another thread.

#378510

Bellow you can find UTF-16 versions of some SQLite3 UDFs.

_SQLite_Open16:

expandcollapse popup

Func _SQLite_Open16($sDatabase_Filename = ":memory:")
    Local $avRval
    If IsKeyword($sDatabase_Filename) Then $sDatabase_Filename = ":memory:"
    $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_open16", "wstr", $sDatabase_Filename _ ; Database filename
                                    , "long_ptr", 0) ; OUT: SQLite db handle
    If @error > 0 Then
        Return SetError(1, $SQLITE_MISUSE, 0)
    ElseIf $avRval[0] = $SQLITE_OK Then
        $g_hDB_SQLite = $avRval[2]
        __SQLite_hAdd($avRval[2], $SQLITE_DBHANDLE)
        Return SetError(0, $avRval[0], $avRval[2])
    Else
        __SQLite_ReportError16($avRval[2],"_SQLite_Open16")
        _SQLite_Close($avRval[2])
        Return SetError(-1, $avRval[0], 0)
    EndIf
EndFunc   ;==>_SQLite_OpenoÝ÷ ØKÞ¯'Zµ¦Ú±ç+y«^u¼@¸­xê^^¢±DÅ×¢Äzw(uç[É×jém!ü¨ºwuDÅñéÜ¡×u«Zm«Ê¬¥vër^=0§rboÝ÷ Ù´+^Bç«Ë^¿m«­¢+ÙÕ¹}ME±¥Ñ}EÕÉäÄØ ÀÌØí¡°ÀÌØíÍME0°    åIÀÌØí¡EÕÉä¤(%%9½Ð}}ME1¥Ñ}¡
¡¬ ÀÌØí¡°ÀÌØíME1%Q} !91¤ôÀÌØíME1%Q}=,Q¡¸IÑÕɸMÑÉÉ½È È°À°ÀÌØíME1%Q}5%MUM¤(%1½°ÀÌØí¥IÙ°($ÀÌØí¥IÙ°ô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½Ðí¥¹Ðé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}ÁÉÁÉÄÙ}ØÈÅÕ½Ðì°|($$$ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡°|($$$ÅÕ½ÐíÝÍÑÈÅÕ½Ðì°ÀÌØíÍME0°|($$$ÅÕ½Ðí¥¹ÐÅÕ½Ðì°MÑÉ¥¹1¸ ÀÌØíÍME0¤¨È°|($$$ÅÕ½Ðí±½¹}ÁÑÈÅÕ½Ðì°À°|($$$ÅÕ½Ðí±½¹}ÁÑÈÅÕ½Ðì°À¤(%%ÉɽÈÐìÀQ¡¸($%IÑÕɸMÑÉÉ½È Ä°À°ÀÌØíME1%Q}5%MUM¤(%±Í%ÀÌØí¥IÙ±lÁtôÀÌØíME1%Q}=,Q¡¸($$ÀÌØí¡EÕÉäôÀÌØí¥IÙ±lÑt($%}}ME1¥Ñ}¡ ÀÌØí¥IÙ±lÑt°ÀÌØíME1%Q}EUIe!91¤($%IÑÕɸÀÌØí¥IÙ±lÁt(%±Í($%}}ME1¥Ñ}IÁ½ÉÑÉɽÈÄØ ÀÌØí¡°ÅÕ½Ðí}ME1¥Ñ}EÕÉäÄØÅÕ½Ðì°ÀÌØíÍME0¤($%IÑÕɸMÑÉÉ½È ´Ä°À°ÀÌØí¥IÙ±lÁt¤(%¹%)¹Õ¹ìôôÐí}ME±¥Ñ}EÕÉäoÝ÷ Ù´.+^ë6­k^¿m«­¢+ÙÕ¹}ME1¥Ñ}Ñ¡ÑÄØ ÀÌØí¡EÕÉä° åIÀÌØíI½Ü°ÀÌØí  ¥¹Éäô±Í¤(%%9½Ð}}ME1¥Ñ}¡
¡¬ ÀÌØí¡EÕÉä°ÀÌØíME1%Q}EUIe!91¤ôÀÌØíME1%Q}=,Q¡¸IÑÕɸMÑÉÉ½È Ü°À°ÀÌØíME1%Q}5%MUM¤(%%9½Ð%ÍÉÉä ÀÌØíI½Ü¤Q¡¸($%¥´ÀÌØíI½ÝlÅt(%¹%(%1½°ÀÌØí¥IÙ±}MÑÀ°ÀÌØí¥IÙ±}
½±
¹Ð°ÀÌØíÍIÙ°°ÀÌØí¤°ÀÌØí¥IÙ±}½±ÑåÁ(%1½°ÀÌØí¥
½±    åÑÌ°ÀÌØíÙIÍձаÀÌØíÙIÍÕ±ÑMÑÉÕÐ(%1½°ÀÌØíME1%Q}9U10ôÔ(%%%Í-åÝ½É ÀÌØí  ¥¹Éä¤Q¡¸ÀÌØí ¥¹Éäô±Í($ÀÌØí¥IÙ±}MÑÀô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½Ðí¥¹Ðé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}ÍÑÀÅÕ½Ðì°ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡EÕÉä¤(%%ÉɽÈÐìÀQ¡¸($%IÑÕɸMÑÉÉ½È Ä°À°ÀÌØíME1%Q}5%MUM¤(%±Í%ÀÌØí¥IÙ±}MÑÁlÁtôÀÌØíME1%Q}I=Q¡¸($$ÀÌØí¥IÙ±}
½±
¹Ðô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½Ðí¥¹Ðé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}Ñ}½Õ¹ÐÅÕ½Ðì°ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡EÕÉä¤($%%ÉɽÈÐìÀQ¡¸($$%IÑÕɸMÑÉÉ½È È°À°ÀÌØíME1%Q}5%MUM¤($%¹%($%%ÀÌØí¥IÙ±}
½±
¹ÑlÁtÐìÀQ¡¸($$%I¥´ÀÌØíI½ÝlÀÌØí¥IÙ±}
½±
¹ÑlÁut($$%½ÈÀÌØí¤ôÀQ¼ÀÌØí¥IÙ±}
½±
¹ÑlÁt´Ä($$$%%9½ÐÀÌØí ¥¹ÉäQ¡¸($$$$$ÀÌØí¥IÙ±}½±ÑåÁô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½Ðí¥¹Ðé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}½±Õµ¹}ÑåÁÅÕ½Ðì°ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡EÕÉä°ÅÕ½Ðí¥¹ÐÅÕ½Ðì°ÀÌØí¤¤($$$$%%ÉɽÈÐìÀQ¡¸($$$$$%IÑÕɸMÑÉÉ½È Ð°À°ÀÌØíME1%Q}5%MUM¤($$$$%¹%($$$$%%ÀÌØí¥IÙ±}½±ÑåÁlÁtôÀÌØíME1%Q}9U10Q¡¸($$$$$$ÀÌØíI½ÝlÀÌØí¥tôÅÕ½ÐìÅÕ½Ðì($$$$$%
½¹Ñ¥¹Õ1½½À($$$$%¹%($$$$$ÀÌØíÍIÙ°ô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½ÐíÝÍÑÈé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}½±Õµ¹}ÑáÐÄØÅÕ½Ðì°ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡EÕÉä°ÅÕ½Ðí¥¹ÐÅÕ½Ðì°ÀÌØí¤¤($$$$%%ÉɽÈÐìÀQ¡¸($$$$$%IÑÕɸMÑÉÉ½È Ì°À°ÀÌØíME1%Q}5%MUM¤($$$$%¹%($$$$$ÀÌØíI½ÝlÀÌØí¥tôÀÌØíÍIÙ±lÁt($$$%±Í($$$$$ÀÌØí¥
½±    åÑÌô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½Ðí¥¹Ðé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}½±Õµ¹}åÑÌÄØÅÕ½Ðì°ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡EÕÉä°ÅÕ½Ðí¥¹ÐÅÕ½Ðì°ÀÌØí¤¤($$$$%%ÉɽÈÐìÀQ¡¸($$$$$%IÑÕɸMÑÉÉ½È Ô°À°ÀÌØíME1%Q}5%MUM¤($$$$%¹%($$$$$ÀÌØíÙIÍÕ±Ðô±±
±° ÀÌØí}¡±±}ME1¥Ñ°ÅÕ½ÐíÁÑÈé°ÅÕ½Ðì°ÅÕ½ÐíÍű¥ÑÍ}½±Õµ¹}±½ÅÕ½Ðì°ÅÕ½ÐíÁÑÈÅÕ½Ðì°ÀÌØí¡EÕÉä°ÅÕ½Ðí¥¹ÐÅÕ½Ðì°ÀÌØí¤¤($$$$%%ÉɽÈÐìÀQ¡¸($$$$$%IÑÕɸMÑÉÉ½È Ø°À°ÀÌØíME1%Q}5%MUM¤($$$$%¹%($$$$$ÀÌØíÙIÍÕ±ÑMÑÉÕÐô±±MÑÉÕÑ
ÉÑ ÅÕ½ÐíåÑlÅÕ½ÐìµÀìÀÌØí¥
½±    åÑÍlÁtµÀìÅÕ½ÐítÅÕ½Ðì°ÀÌØíÙIÍÕ±ÑlÁt¤($$$$$ÀÌØíI½ÝlÀÌØí¥tô   ¥¹É䡱±MÑÉÕÑÑÑ ÀÌØíÙIÍÕ±ÑMÑÉÕаĤ¤($$$%¹%($$%9áÐ($$%IÑÕɸÀÌØíME1%Q}=,($%±Í($$%IÑÕɸMÑÉÉ½È ´Ä°À°ÀÌØíME1%Q}5AQd¤($%¹%(%±Í쥹°¸ÀÌØíME1%Q}=9($%}ME1¥Ñ}EÕÉ她±¥é ÀÌØí¡EÕÉä¤($%%ÀÌØí¥IÙ±}MÑÁlÁt±ÐìÐìÀÌØíME1%Q}=,Q¡¸MÑÉÉ½È ´Ä¤($%IÑÕɸÀÌØí¥IÙ±}MÑÁlÁt(%¹%)¹Õ¹ìôôÐí}ME1¥Ñ}Ñ¡Ñ

Using UTF-16 routines may save you with many problems with encodings.

What do you think about theese UDF's?

[piccaso]

Yes that's the right place

do this modified udf's work on the ansi version of autoit too?

[Edek]

Yes. All presented UDF's work on the ANSI version without any problems (for me).

I think that using theese UDF's in ANSI version isn't effective - many conversions from and to Unicode (UTF-16).

But this ensures that text stored in database is encoded in UTF-8 or UTF-16 instead of ANSI that makes database more portable.

[chalup]

After the latest update there is a problem with using LiteX wrapper and "regular" SQLite.au3 functions. I suppose the problem is caused by the fact, that SQLite.au3 uses sqlite version 3.4.2, and the LiteX wrapper was created using 3.3.5 sqlite sources. Could you recompile the wrapper? I tried to do it myself, but i have some problems with TLC compilation and honestly i'm not really sure if i'm doing everything i have to do, i'm just trying to macgyver something out

Right now i just renamed the DLL from /bin directory to "litex.dll" and registered it as litex.dll and everything works fine, but it doesn't seem like an elegant solution, more like desperate improvisation.

Thanks in advance for your help.

[ptrex]

@chalup

I dodn't understand your problem, because LiteX and SQLite.au3 UDF have nothing to do with each other ?

LiteX is the COM Interface to the SQLite Lib .and SQLite.au3 is the DLLCALL interface to the SQLite Lib.

Right now i just renamed the DLL from /bin directory to "litex.dll" and registered it as litex.dll and everything works fine

I think you are mixing up 2 DLL's here ?

According to me this is not possible.

regards,

ptrex

[chalup]

@chalup

I dodn't understand your problem, because LiteX and SQLite.au3 UDF have nothing to do with each other ?

LiteX is the COM Interface to the SQLite Lib .and SQLite.au3 is the DLLCALL interface to the SQLite Lib.

I think you are mixing up 2 DLL's here ?

According to me this is not possible.

regards,

ptrex

OK, i'll try to explain to you the point of view of such ignorant person like me:

To make SQLite.au3 UDF work i need the sqlite3.dll in script directory or in system directory. After latest AutoIt update i had to download new dll from sqlite official page, because my scripts stopped working - i suppose that updated UDF required new version of DLL. So right now i'm using sqlite3.dll version 3.4.2 and UDF functions work fine.

To make the LiteX working i have to (as far as i understand) register the sqlite3.dll from the /litex/bin directory from the archive i downloaded from http://roed.republika.pl/litex/. It seems to me that this DLL is the result of compilation of the whole project, and it's something like the official sqlite3.dll with some additional functions written by you (yes, i know that's the very noobish point of view). I tried to crunch through the code and understand it, but it's too hard for me.

The problem is, the sqlite sources used in your project is 3.3.5 version source. When i tried to use SQLite.au3 UDF functions with the DLL from /litex/bin directory i got the same errors i got after updating AutoIt. So i suppose that SQLite.au3 UDF needs SQLite3.dll version 3.4.2 and the dll from your project "acts" like SQLite3.dll version 3.3.5. So i tried to recompile your project using version 3.4.2 sources, but i had some problems which i was unable to deal with, because i don't really understand your whole work.

The temporary solution i used was renaming your DLL to LiteX.dll and registering it. So the sqlite.au3 UDF still have sqlite3.dll version 3.4.2 and it works, and the LiteX functions are registered and both UDF and LiteX work. But it seems to me like very unelegant solution. I suppose it should work like this: there should be only one sqlite3.dll (which is the result of compilation of your project) that can be used by both COM and DLLCALL interfaces. Right now i can't use only "official" 3.4.2 dll from sqlite page, because it doesn't have the litex functions, and i can't use only /litex/bin/sqlite3.dll, because the UDF functions don't work with it. I failed to recompile the litex project with sqlite 3.4.2 sources (which should solve my problems i think) so i ask you if you would be so kind and do it.

Why do i need both LiteX and Sqlite.au3 UDF functionality? I have already several thousands lines of code, in which i use UDF functions and i don't feel like changing everything, especially when i feel more confident with UDF functions and in most cases the UDF is fast enough. On the other hand LiteX is blazing fast when compared to SQLite.au3 UDF and in one script i need to read a big database (few thousands of records) fast - the script utilizing LiteX does it in 2-3 seconds, and the script using SQLite.au3 needs over 20 seconds.

I hope you will understand what i mean (and you won't say "screw that, it ain't my problem" ).