Jump to content

SQLite semi Embedded database functionality in AutoIt


ptrex
 Share

Recommended Posts

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 by Zedna
Link to comment
Share on other sites

  • 4 weeks later...
  • 2 weeks later...

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 by H1T1
Link to comment
Share on other sites

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)

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to comment
Share on other sites

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

  • 9 months later...
whatever Edited by MvGulik

"Straight_and_Crooked_Thinking" : A "classic guide to ferreting out untruths, half-truths, and other distortions of facts in political and social discussions."
"The Secrets of Quantum Physics" : New and excellent 2 part documentary on Quantum Physics by Jim Al-Khalili. (Dec 2014)

"Believing what you know ain't so" ...

Knock Knock ...
 

Link to comment
Share on other sites

  • 2 months later...
  • 10 months later...

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

Link to comment
Share on other sites

  • 3 weeks later...

@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.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

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.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

  • 1 year later...

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.

Link to comment
Share on other sites

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

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

  • 5 weeks later...
  • 4 weeks later...

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.

Link to comment
Share on other sites

  • 1 month later...

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...