SQLite semi Embedded database functionality in AutoIt

[Zedna]

Oh nice i hadn't realized we were talkinga bout the same UDF. Anway do we still need to register a bunch of dlls if we want to distribute this with our code?

There is only one DLL: sqlite3.dll which is automatically created by _SQLite_Startup()

Look at its sources and also at:

"C:\Program Files\AutoIt3\Include\sqlite.dll.au3" 

Edited January 25, 2010 by Zedna

[Inververs]

Function _SQLite_SQLiteExe always returns an error 1.

This error is returned from the function 2 times. First time:

If $ g_hDll_SQLite = 0 Then Return SetError (1, 0, $ SQLITE_MISUSE)

Second time:

Return SetError (1, 0, $ SQLITE_CANTOPEN); Can't Create new Database

The function can only be started pre-setting

$g_hDll_SQLite = 1

[H1T1]

In another forum, I was requesting way to create UserDefinedFunction in SQLite. I was hoping to find a way to create autoit functions and call them within SQLite part of SQL.

With my limited knowledge, I was only able to get this functionality by using the "load_extension" in SQLite and calling functions written in C Language.

This is how I did it:

Due to SQLite security, this function is turn off so to activate, I had to modify the SQLite.au3 library. I added the following statement to the "_SQLite_Open" function

=======================

Func _SQLite_Open($sDatabase_Filename = Default, $iAccessMode = Default, $iEncoding = Default)

.

.

.

.

$aaa=dllcall($g_hDll_SQLite,"int:cdecl","sqlite3_enable_load_extension","ptr",SetExtended($avRval[0], $avRval[2]),"int",1)

Return SetExtended($avRval[0], $avRval[2])

EndFunc ;==>_SQLite_Open

=======================

Once this done, I downloaded an existing library that adds multiple functions from: http://www.sqlite.org/contrib : the file is called "extension-functions.c".

after downloading "extension-functions.c", I compiled it using MinGW as explained in the file and create a library called "extension-functions.so".

I have attached the modified SQLite.au3 library, the compiled SQLite library and also an example.

IT would be nice for an expert with Autoit to see if a function written in C would allow to call autoit functions.

One last favor, to the owner for the SQLite.au3 library... Please add the change above to the library so that I do not need to do it manually everytime is updated.

References:

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

http://www.mingw.org/wiki/HOWTO_Install_the_MinGW_GCC_Compiler_Suite

http://www.sqlite.org/contrib

Example.zip

Edited March 3, 2010 by H1T1

[ProgAndy]

The modification of the SQLite.au3 is not required. You can add this line in the script in wich you want to use load_extension (just right after _SQlite_Startup)

[H1T1]

The modification of the SQLite.au3 is not required. You can add this line in the script in wich you want to use load_extension (just right after _SQlite_Startup)

Hi Andy, I tried but it is not that easy for most users.. It would be make the change in library.

Thanks

[ProgAndy]

Well, make an UDF you deliver together with you userfunc. This UDF enables the load-support and loads the extension afterwards

[jchd]

This is the first of a series of posts about SQLite extensions, mostly revolving around Unicode string manipulation where non-ASCII characters are involved.

First here is a couple of functions to handle loading .DLL extensions.

I'll do my best to have them included some day in the standard SQLite UDF, as these functions are completely generic and extensions are an excellent (sometimes absolutely required) supplement to this superior tool.

Usage:

Use _SQLite_EnableExtensions($hConn, $OnOrOff) to enable or disable extension loading for a given connection. By default, SQLite doesn't allow users to load extension as a security feature. This call is needed before any extension can be loaded.

Use _SQLite_LoadExtension($hConn, $sFullPath, $sEntry = 'sqlite3_extension_init') to load an extension .dll that will be available to the $nConn connection only. $sEntry is the DLL entry point name. Loading of extensions must have been enabled before.

Use _SQLite_LoadAutoExtension($sFullPath, $sEntry = 'sqlite3_extension_init') to load an extension .dll that will be available to each new connection only, during the same SQLite session. $sEntry is the DLL entry point name.

There are advantages and drawbacks in both loading and auto-loading extensions. This will be exposed in full detail in the Unifuzz post, any time soon.

I didn't add support to "unload" a function that was loaded as part of an extension. When you load an SQLite extension, you load new functions and/or override existing scalar or collation core functions. It's possible to "forget" custom functions on an individual basis and revert to code functions which have been overriden. I didn't see anyhing healthy in such feature. I consider that if a someone actually needs to use a function on such an intermittent basis, then there's a fundamental design flaw. Anyway, you know the thing is possible. Note that there is as well an SQLite API to unload all auto-extensions. That shouldn't be necessary either.

Almost forgot to add: it is also possible to load certain extensions using SQL: "SELECT load_extension('full path of .dll', 'entry point name');"

But again, loading of extensions need to be allowed beforehand. Warning: not all extensions can be loaded this way. Future post will explain why.

SQLiteExtLoad.au3

Edited March 7, 2010 by jchd

[MvGulik]

whatever Edited February 7, 2011 by MvGulik

[ivan]

Just wanted to share this link to sqlitestudio. I found it most useful in creating the SQL to establish table relationships, triggers, and constraints with which I always struggle prior to feeding data.

Edited March 7, 2011 by ivan

[AshokkumarThankappan]

Hello all, I am new user in this forum.. I have been using Autoit for 5 years and used it extensively so far...I have developed an application using Autoit with SQLite. Attached is the source code. This application is to maintain personal activities or like sticky note which stores our acitivies/notes to SQLite Database. Hope this will be useful in demonstrating the extensive use of Autoit with SQLite.

Attached files: myactivities.au3

quote.au3

Download both the files

myactivities.rar

[hmsn]

@AshokkumarThankappan

first thank you for sharing this code and i have one question when i try to run this code i find that autoit3 try to connect to remote ip address 87.106.181.57 port:20480 and i am wondering about that so please explane why it happen

Regards

Edited January 31, 2012 by hmsn

[BrewManNH]

@AshokkumarThankappan

first thank you for sharing this code and i have one question when i try to run this code i find that autoit3 try to connect to remote ip address 87.106.181.57 port:20480 and i am wondering about that so please explane why it happen

Regards

It's because the latest version of AutoIt3 doesn't come packaged with the sqlite.dll file. If you don't already have the file, or the script can't find where it is, it will download it from the autoitscript.com site and place it in the Windowssystem32 folder.

[hmsn]

THANKS BrewManNH

i am new in autoit and i realy start to love it

[AshokkumarThankappan]

@BrewManNH

I was not aware of this behavior because i had the SQLite.au3 & SQLite.dll.au3 file already in my "include" directory. Thanks for the clarrification

[BrewManNH]

It's a recent change made in the version 3.3.8.0 and isn't mentioned in the changelog or script breaking changes when they released that version.

[reybol7]

Hi Guyz, i need help about SQLlite, How can i Display the data in a row1 column3 in a GUI, i already know how to search a string in the database, my problem now how can i display it one by one in each row|column square in a GUI not in arrayview or listview.

[GreenCan]

Hi Guyz, i need help about SQLlite, How can i Display the data in a row1 column3 in a GUI, i already know how to search a string in the database, my problem now how can i display it one by one in each row|column square in a GUI not in arrayview or listview.

Hi Guyz, i need help about SQLlite, How can i Display the data in a row1 column3 in a GUI, i already know how to search a string in the database, my problem now how can i display it one by one in each row|column square in a GUI not in arrayview or listview.

Look here and you'll know it.

Not sure what the difference is to get the data in a listview or anywhere else on a GUI...

SQLite Report Generator

[icadea]

thank you for providing this

[willichan]

I was finding that _SQLite_Startup() was failing on Win7x64 machines.  After a lot of debugging, I determined that is was the function that downloads the DLL that was having trouble. It kept trying to save the downloaded file to @SystemDir, but that is not writeable unless run as administrator.  The fix is a simple one, and I am recommending it for the next release (unless a better improvement is already in the works.)

First, in SQLite.au3, change line 242 from

If FileExists($sDll_Filename) Or $sDll_Filename = "" Then

to

If FileExists($sDll_Filename) Or $sDll_Filename = "" Or Not __SQLite_DirWriteAccessible(@SystemDir)Then

then add the following function

; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name ..........: __SQLite_DirWriteAccessible
; Description ...: Determins if a directory is write accessible
; Syntax ........: __SQLite_DirWriteAccessible($sPath)
; Parameters ....: $sPath  - Directory path to check
; Return values .: True    - Directory is write accessible
;                  False   - Directory is not write accessible, or does not exist
; Author ........: KaFu
; Modified ......: Willichan
; Remarks .......:
; Related .......:
; Link ..........: http://www.autoitscript.com/forum/topic/128642-how-to-check-accessibility-of-a-folder/#entry892739
; Example .......: No
; ===============================================================================================================================
Func __SQLite_DirWriteAccessible($sPath)
    If Not StringInStr(FileGetAttrib($sPath), "D", 2) Then Return SetError(1, 0, False)
    Local $iEnum = 0
    While FileExists($sPath & "\_test_" & $iEnum)
        $iEnum += 1
    WEnd
    Local $iCreate = DirCreate($sPath & "\_test_" & $iEnum)
    If $iCreate = 1 Then
        Local $iRemove = DirRemove($sPath & "\_test_" & $iEnum)
        Return ($iRemove = 1)
    Else
        Return False
    EndIf
EndFunc

After these changes, the scripts work for all 32 and 64 bit versions of XP and Win7 that I have tested on.

[jchd]

I'm reviewing SQLite.au3 and there are a number of things that need change. In fact I'm not a fan of downloading the DLL in the back of the application. Accessing the Internet without much notice isn't good in many environments.

I believe that if a piece of code needs a DLL, then this DLL has to be setup during the application install process. With the advent of 32/64 bit versions it's harder to justify packing both DLLs in SQLite.Dll.au3 as well. As a parallel, OCR UDFs (even if not part of the standard package) leave it to the developper/user to install tesseract.dll or some other dll needed. Same for other external DLLs. Anyway, devs/users can still FileInstall whatever DLL their code need.

Another grief is that installing SQLite3***.dll in @systemdir is a bad thing. I'd prefer installing it in @scriptdir. Reason is that on the average computer there are many programs which use SQLite (e.g. FireFox, Adobe, Thunderbird, and so many more) and [re]placing a given version for default system-wide use may break things elsewhere (DLL hell). Just the name of the DLL doesn't show which compilation options were used, nor which extension fonctions/collations/virtual tables/VFS are part of the module.

Having possibly several distinct copies of SQLite3***.dll carrying different versions on a PC seems to deny the purpose of shared libraries but we must remember that SQLite was designed as an embedded RDBMS, that is: was essentially meant to be statically linked with the applications using it. Relying on DLL for languages unable to link the module is a side-effect.