Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

SQLite semi Embedded database functionality in AutoIt


  • Please log in to reply
772 replies to this topic

#741 Zedna

Zedna

    AutoIt rulez!

  • MVPs
  • 8,557 posts

Posted 25 January 2010 - 05:43 PM

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, 25 January 2010 - 05:43 PM.








#742 Inververs

Inververs

    Wayfarer

  • Active Members
  • Pip
  • 74 posts

Posted 18 February 2010 - 04:18 PM

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


#743 H1T1

H1T1

    Seeker

  • Active Members
  • 6 posts

Posted 03 March 2010 - 03:24 PM

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

Attached Files


Edited by H1T1, 03 March 2010 - 03:48 PM.


#744 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 03 March 2010 - 03:39 PM

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* Posted Image [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

#745 H1T1

H1T1

    Seeker

  • Active Members
  • 6 posts

Posted 03 March 2010 - 04:14 PM

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

#746 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 03 March 2010 - 04:46 PM

Well, make an UDF you deliver together with you userfunc. This UDF enables the load-support and loads the extension afterwards :mellow:
*GERMAN* Posted Image [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

#747 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,570 posts

Posted 07 March 2010 - 03:20 AM

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.

Attached File  SQLiteExtLoad.au3   4.8KB   871 downloads

Edited by jchd, 07 March 2010 - 11:13 AM.

  • PincoPanco likes this

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here


#748 MvGulik

MvGulik

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 2,795 posts

Posted 11 December 2010 - 10:26 PM

whatever

Edited by MvGulik, 07 February 2011 - 11:21 AM.


#749 ivan

ivan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 402 posts

Posted 07 March 2011 - 09:49 AM

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 by ivan, 07 March 2011 - 09:57 AM.


#750 AshokkumarThankappan

AshokkumarThankappan

    Seeker

  • Normal Members
  • 5 posts

Posted 15 January 2012 - 06:25 PM

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

Attached Files



#751 hmsn

hmsn

    Seeker

  • New Members
  • 2 posts

Posted 31 January 2012 - 08:26 PM

@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 by hmsn, 31 January 2012 - 08:38 PM.


#752 BrewManNH

BrewManNH

    באָבקעס מיט קודוצ׳ה

  • MVPs
  • 9,548 posts

Posted 31 January 2012 - 08:41 PM

@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 an XP machine, and I'm not going to.

 

How to ask questions the smart way!

 

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 editorGUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.
GUIToolTip UDF Demo - Demo script to show how to use the GUIToolTip UDF to create and use customized tooltips. Latin Square password generator

 

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.


#753 hmsn

hmsn

    Seeker

  • New Members
  • 2 posts

Posted 31 January 2012 - 08:50 PM

THANKS BrewManNH

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

#754 AshokkumarThankappan

AshokkumarThankappan

    Seeker

  • Normal Members
  • 5 posts

Posted 01 February 2012 - 12:00 PM

@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

#755 BrewManNH

BrewManNH

    באָבקעס מיט קודוצ׳ה

  • MVPs
  • 9,548 posts

Posted 01 February 2012 - 05:01 PM

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 an XP machine, and I'm not going to.

 

How to ask questions the smart way!

 

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 editorGUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.
GUIToolTip UDF Demo - Demo script to show how to use the GUIToolTip UDF to create and use customized tooltips. Latin Square password generator

 

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.


#756 reybol7

reybol7

    Seeker

  • Active Members
  • 27 posts

Posted 01 April 2013 - 07:37 PM

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.

#757 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 304 posts

Posted 07 April 2013 - 01:40 PM

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

#758 icadea

icadea

    Prodigy

  • Active Members
  • PipPipPip
  • 160 posts

Posted 07 May 2013 - 04:47 AM

thank you for providing this

#759 willichan

willichan

    Go ahead. You know you want to scan it.

  • Active Members
  • PipPipPipPipPipPip
  • 647 posts

Posted 03 June 2013 - 06:58 PM

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

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

to

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

then add the following function

AutoIt         
  1. ; #INTERNAL_USE_ONLY# ===========================================================================================================
  2. ; Name ..........: __SQLite_DirWriteAccessible
  3. ; Description ...: Determins if a directory is write accessible
  4. ; Syntax ........: __SQLite_DirWriteAccessible($sPath)
  5. ; Parameters ....: $sPath  - Directory path to check
  6. ; Return values .: True    - Directory is write accessible
  7. ;                  False   - Directory is not write accessible, or does not exist
  8. ; Author ........: KaFu
  9. ; Modified ......: Willichan
  10. ; Remarks .......:
  11. ; Related .......:
  12. ; Link ..........: http://www.autoitscript.com/forum/topic/128642-how-to-check-accessibility-of-a-folder/#entry892739
  13. ; Example .......: No
  14. ; ===============================================================================================================================
  15. Func __SQLite_DirWriteAccessible($sPath)
  16.     If Not StringInStr(FileGetAttrib($sPath), "D", 2) Then Return SetError(1, 0, False)
  17.     Local $iEnum = 0
  18.     While FileExists($sPath & "\_test_" & $iEnum)
  19.         $iEnum += 1
  20.     WEnd
  21.     Local $iCreate = DirCreate($sPath & "\_test_" & $iEnum)
  22.     If $iCreate = 1 Then
  23.         Local $iRemove = DirRemove($sPath & "\_test_" & $iEnum)
  24.         Return ($iRemove = 1)
  25.     Else
  26.         Return False
  27.     EndIf

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


  • shaqan likes this

#760 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,570 posts

Posted 11 July 2013 - 09:54 AM

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.


SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users