guinness Posted January 7, 2013 Share Posted January 7, 2013 24 hours ago I had never used the SQLite functions, didn't have any requirement to as I was content with the INI fuctions. But in a quest to expand my knowledge of programming and see if the Forum + Help file was a valuable tool, I took a subject I knew nothing about and managed to produce a workable UDF + example. So in conclusion, the Forum has plenty of information to get you started, you just have to know how to search effectively. Now back to why this is in support. I want to know is there any improvements that could be made to making this more efficient with the SQLite query strings? I've looked through the SQLite docs/diagrams and it suggests these are pretty much the "basics of the basic." Any help will be much appreciated. Thanks. expandcollapse popup#AutoIt3Wrapper_UseX64=N ; Using the 32-bit DLL of SQLite. #include <SQLite.au3> #cs Help page: http://zetcode.com/db/sqlite/ Idea was initially by KaFu in SMF. Thankfully this gave me the spark to learn some SQL commands. A difficult, but rewarding challenge. BEGIN IMMEDIATE;QUERY_STRING;COMMIT; The functions have the same syntax as the INI functions, but instead of a filepath, they use a handle returned by _SQLite_Open. #ce Example() Func Example() ; Start SQLite. _SQLite_Startup(@ScriptDir & '\SQLite3.dll') ; Download from: http://www.autoitscript.com/autoit3/files/beta/autoit/archive/ ; Open a database. Local $hSQLiteDB = _SQLite_Open(@ScriptDir & '\Example.db') ; Write Key_1=Value_1 to the 'Example' section. _INIWrite_SQL($hSQLiteDB, 'Example', 'Key_1', 'Value_1') ConsoleWrite('Key_1=' & _INIRead_SQL($hSQLiteDB, 'Example', 'Key_1', 'Default_Value_1') & @CRLF) ; Write Key_2=Value_2 to the 'Example' section. _INIWrite_SQL($hSQLiteDB, 'Example', 'Key_2', 'Value_2') ConsoleWrite('Key_2=' & _INIRead_SQL($hSQLiteDB, 'Example', 'Key_2', 'Default_Value_2') & @CRLF) ; Write Key_3=Value_3 to the 'Example' section. _INIWrite_SQL($hSQLiteDB, 'Example', 'Key_3', 'Value_3') ConsoleWrite('Key_3=' & _INIRead_SQL($hSQLiteDB, 'Example', 'Key_3', 'Default_Value_3') & @CRLF) ; Delete the key 'Key_2' for the 'Example' section. _INIDelete_SQL($hSQLiteDB, 'Example', 'Key_2') ; Write Key_1=Value_1 to the 'Example_2' section. _INIWrite_SQL($hSQLiteDB, 'Example_2', 'Key_1', 'Value_1') ; Write Key_1=Value_1 to the 'Example_3' section. _INIWrite_SQL($hSQLiteDB, 'Example_3', 'Key_1', 'Value_1') ; Display an array of key/value pairs for the 'Example' section. Local $aArray = _INIReadSection_SQL($hSQLiteDB, 'Example') _ArrayDisplay($aArray) ; Display an array of section names. $aArray = _INIReadSectionNames_SQL($hSQLiteDB) _ArrayDisplay($aArray) ; Close the database. _SQLite_Close($hSQLiteDB) ; Shutdown SQLite. _SQLite_Shutdown() ; Delete the database. FileDelete(@ScriptDir & '\Example.db') EndFunc ;==>Example Func _INIDelete_SQL(ByRef Const $hSQLiteDB, $sSection, $sKey = '') __INITableVerify($hSQLiteDB, $sSection, True) Local $sQuery = 'DELETE FROM ' & $sSection & ' WHERE vKey = "' & $sKey & '";' If StringStripWS($sKey, 8) = '' Then $sQuery = 'DELETE FROM ' & $sSection & ';' EndIf Return _SQLite_Exec($hSQLiteDB, $sQuery) = $SQLITE_OK EndFunc ;==>_INIDelete_SQL Func _INIRead_SQL(ByRef Const $hSQLiteDB, $sSection, $sKey, $sDefault) __INITableVerify($hSQLiteDB, $sSection, False) Local $hQuery = 0, $aRow = 0 _SQLite_Query($hSQLiteDB, 'SELECT vValue FROM ' & $sSection & ' WHERE vKey = "' & $sKey & '";', $hQuery) _SQLite_FetchData($hQuery, $aRow) _SQLite_QueryFinalize($hQuery) If @error = 0 Then $sDefault = $aRow[0] Return $sDefault EndFunc ;==>_INIRead_SQL Func _INIReadSection_SQL(ByRef Const $hSQLiteDB, $sSection) __INITableVerify($hSQLiteDB, $sSection, False) Local $aArray = 0, $iColumns = 0, $iRows = 0 If _SQLite_GetTable2d($hSQLiteDB, 'SELECT * FROM ' & $sSection & ';', $aArray, $iRows, $iColumns) = $SQLITE_OK Then $aArray[0][0] = $iRows $aArray[0][1] = '' Else Local $aError[1][2] = [[0]] $aArray = $aError $aError = 0 EndIf Return $aArray EndFunc ;==>_INIReadSection_SQL Func _INIReadSectionNames_SQL(ByRef Const $hSQLiteDB) Local $aArray = 0, $iColumns = 0, $iRows = 0 If _SQLite_GetTable2d($hSQLiteDB, 'SELECT * FROM SQLITE_MASTER WHERE TYPE = "table";', $aArray, $iRows, $iColumns) = $SQLITE_OK Then Local $aReturn[$iRows + 1] = [$iRows] For $i = 1 To $iRows $aReturn[$i] = $aArray[$i][2] Next $aArray = $aReturn $aReturn = 0 Else Local $aError[1] = [0] $aArray = $aError $aError = 0 EndIf Return $aArray EndFunc ;==>_INIReadSectionNames_SQL Func _INIWrite_SQL(ByRef Const $hSQLiteDB, $sSection, $sKey, $sValue) __INITableVerify($hSQLiteDB, $sSection, True) Return _SQLite_Exec($hSQLiteDB, 'INSERT OR REPLACE INTO ' & $sSection & ' VALUES("' & $sKey & '","' & StringReplace($sValue, "'", "''", 0, 2) & '");') = $SQLITE_OK EndFunc ;==>_INIWrite_SQL ; Internal function for verifying a table exists and removing whitespace from the section (table) name. Func __INITableVerify(ByRef Const $hSQLiteDB, ByRef $sSection, $fCreate) Local $fReturn = True $sSection = StringStripWS($sSection, 8) If $fCreate Then $fReturn = _SQLite_Exec($hSQLiteDB, 'CREATE TABLE IF NOT EXISTS ' & $sSection & '(vKey TEXT, vValue TEXT);') = $SQLITE_OK EndIf Return $fReturn EndFunc ;==>__INITableVerify UDF List:  _AdapterConnections() • _AlwaysRun() • _AppMon() • _AppMonEx() • _ArrayFilter/_ArrayReduce • _BinaryBin() • _CheckMsgBox() • _CmdLineRaw() • _ContextMenu() • _ConvertLHWebColor()/_ConvertSHWebColor() • _DesktopDimensions() • _DisplayPassword() • _DotNet_Load()/_DotNet_Unload() • _Fibonacci() • _FileCompare() • _FileCompareContents() • _FileNameByHandle() • _FilePrefix/SRE() • _FindInFile() • _GetBackgroundColor()/_SetBackgroundColor() • _GetConrolID() • _GetCtrlClass() • _GetDirectoryFormat() • _GetDriveMediaType() • _GetFilename()/_GetFilenameExt() • _GetHardwareID() • _GetIP() • _GetIP_Country() • _GetOSLanguage() • _GetSavedSource() • _GetStringSize() • _GetSystemPaths() • _GetURLImage() • _GIFImage() • _GoogleWeather() • _GUICtrlCreateGroup() • _GUICtrlListBox_CreateArray() • _GUICtrlListView_CreateArray() • _GUICtrlListView_SaveCSV() • _GUICtrlListView_SaveHTML() • _GUICtrlListView_SaveTxt() • _GUICtrlListView_SaveXML() • _GUICtrlMenu_Recent() • _GUICtrlMenu_SetItemImage() • _GUICtrlTreeView_CreateArray() • _GUIDisable() • _GUIImageList_SetIconFromHandle() • _GUIRegisterMsg() • _GUISetIcon() • _Icon_Clear()/_Icon_Set() • _IdleTime() • _InetGet() • _InetGetGUI() • _InetGetProgress() • _IPDetails() • _IsFileOlder() • _IsGUID() • _IsHex() • _IsPalindrome() • _IsRegKey() • _IsStringRegExp() • _IsSystemDrive() • _IsUPX() • _IsValidType() • _IsWebColor() • _Language() • _Log() • _MicrosoftInternetConnectivity() • _MSDNDataType() • _PathFull/GetRelative/Split() • _PathSplitEx() • _PrintFromArray() • _ProgressSetMarquee() • _ReDim() • _RockPaperScissors()/_RockPaperScissorsLizardSpock() • _ScrollingCredits • _SelfDelete() • _SelfRename() • _SelfUpdate() • _SendTo() • _ShellAll() • _ShellFile() • _ShellFolder() • _SingletonHWID() • _SingletonPID() • _Startup() • _StringCompact() • _StringIsValid() • _StringRegExpMetaCharacters() • _StringReplaceWholeWord() • _StringStripChars() • _Temperature() • _TrialPeriod() • _UKToUSDate()/_USToUKDate() • _WinAPI_Create_CTL_CODE() • _WinAPI_CreateGUID() • _WMIDateStringToDate()/_DateToWMIDateString() • Au3 script parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018 Link to comment Share on other sites More sharing options...
jchd Posted January 8, 2013 Share Posted January 8, 2013 I don't have much time right now to dissect everything but my first impression is that you shouldn't design it this way. In fact I've thought myself a number of times about an SQLite replacement to .INI but never made it real.I'd create a table Sections (SectionId integer primary key autoincrement, SectionName text) and a table Contents (ContentID integer primary key autoincrement, SectionId integer references Sections(SectionId), ContentKey text, ContentValue text). This way the section is a foreign key of the {key,value} tuple. Add collation as needed (e.g. COLLATE NOCASE). Create required indexes, if any is actually efficient.While standard queries specifying a given section may look more contrived than absolutely minimal, that setup allows looking up which section contain a given key, where lie a given value, etc, without having table names as "parameters". Remember that in SQL you canSELECT * from Sections natural join Contents where <some complex condition>but you can't doSELECT <this column> from <all tables> where <some complex condition>In SQL schema names can't be parametrized, hence if you have 5000 sections (tables in your schema) you'd have to perform 5000 SELECT then work on the results.The benefit of making distinct tables for sections (a duplicate attribute of every entry in a given section) and content is that it allows much more flexible searches/updates/deletes with very little cost. Say for instance that you whish to change every containig 'C:' to 'NAS3PublicSpecial' in all entries of all sections. You have one section for every machine in a corporate network. OK it's more complex in reality but you feel the idea.Note that I'm typing without checking hard. My SQL above is just a sketch. Use SQLite Expert (freeware version is fine for that) to build and experiment without having to write painful code. Once it works robust in Expert (schema, triggers, constraints, queries, ...) , then only start thinking about coding. 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 hereRegExp tutorial: enough to get startedPCRE 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 More sharing options...
kylomas Posted January 8, 2013 Share Posted January 8, 2013 (edited) guinness, So in conclusion, the Forum has plenty of information to get you started, you just have to know how to search effectively. I found this to be true also as far as syntax/function is concerned. Then, as you can see from jchd's comments, came the "data" part of any DB project. While the idea of one table per section troubled me, I cannot neither clearly define why, nor what an alternative approach would be. jchd's initial take of a table of sections with each row in the table pointing at one or more rows of a table of key/value pairs dispells this concern, albeit vague. As for the code: - I was unable to get the app to run as posted because _sqlite_startup() was failing. However, this was not obvious as the console messages were printing "default" values. I only noticed it because the arrays were displaying blank entries. I changed the startup function and added some error checking. - I changed the query, fetchdata, query finalize sequence to querysinglerow because it seems simpler (maybe more efficient?) The following is the code with changes boxed in comments. expandcollapse popup#AutoIt3Wrapper_UseX64=N ; Using the 32-bit DLL of SQLite. #include <SQLite.au3> #cs Help page: http://zetcode.com/db/sqlite/ Idea was initially by KaFu in SMF. Thankfully this gave me the spark to learn some SQL commands. A difficult, but rewarding challenge. BEGIN IMMEDIATE;QUERY_STRING;COMMIT; The functions have the same syntax as the INI functions, but instead of a filepath, they use a handle returned by _SQLite_Open. #ce Example() Func Example() ; Start SQLite. ; changed this --------------------------------------------------------------------------------------------------------------------- ;_SQLite_Startup(@ScriptDir & '\SQLite3.dll') ; Download from: http://www.autoitscript.com/autoit3/files/beta/autoit/archive/ ; to this local $ret = _SQLite_Startup() if not @error then ConsoleWrite('Starting SQLite using ' & $ret & @LF) Else ConsoleWrite('SQLite3.dll notloaded' & @LF) Exit endif ; end change ------------------------------------------------------------------------------------------------------------------------ ; Open a database. Local $hSQLiteDB = _SQLite_Open(@ScriptDir & '\Example.db3') ; changed name to view with sqlite expert ; Write Key_1=Value_1 to the 'Example' section. _INIWrite_SQL($hSQLiteDB, 'Example', 'Key_1', 'Value_1') ConsoleWrite('Key_1=' & _INIRead_SQL($hSQLiteDB, 'Example', 'Key_1', 'Default_Value_1') & @CRLF) ; Write Key_2=Value_2 to the 'Example' section. _INIWrite_SQL($hSQLiteDB, 'Example', 'Key_2', 'Value_2') ConsoleWrite('Key_2=' & _INIRead_SQL($hSQLiteDB, 'Example', 'Key_2', 'Default_Value_2') & @CRLF) ; Write Key_3=Value_3 to the 'Example' section. _INIWrite_SQL($hSQLiteDB, 'Example', 'Key_3', 'Value_3') ConsoleWrite('Key_3=' & _INIRead_SQL($hSQLiteDB, 'Example', 'Key_3', 'Default_Value_3') & @CRLF) ; Delete the key 'Key_2' for the 'Example' section. _INIDelete_SQL($hSQLiteDB, 'Example', 'Key_2') ; Write Key_1=Value_1 to the 'Example_2' section. _INIWrite_SQL($hSQLiteDB, 'Example_2', 'Key_1', 'Value_1') ; Write Key_1=Value_1 to the 'Example_3' section. _INIWrite_SQL($hSQLiteDB, 'Example_3', 'Key_1', 'Value_1') ; Display an array of key/value pairs for the 'Example' section. Local $aArray = _INIReadSection_SQL($hSQLiteDB, 'Example') _ArrayDisplay($aArray) ; Display an array of section names. $aArray = _INIReadSectionNames_SQL($hSQLiteDB) _ArrayDisplay($aArray) ; Close the database. _SQLite_Close($hSQLiteDB) ; Shutdown SQLite. _SQLite_Shutdown() ; Delete the database. ;FileDelete(@ScriptDir & '\Example.db3') ; commented out to keep db and view with sqlite expert EndFunc ;==>Example Func _INIDelete_SQL(ByRef Const $hSQLiteDB, $sSection, $sKey = '') __INITableVerify($hSQLiteDB, $sSection, True) Local $sQuery = 'DELETE FROM ' & $sSection & ' WHERE vKey = "' & $sKey & '";' If StringStripWS($sKey, 8) = '' Then $sQuery = 'DELETE FROM ' & $sSection & ';' EndIf Return _SQLite_Exec($hSQLiteDB, $sQuery) = $SQLITE_OK EndFunc ;==>_INIDelete_SQL Func _INIRead_SQL(ByRef Const $hSQLiteDB, $sSection, $sKey, $sDefault) __INITableVerify($hSQLiteDB, $sSection, False) Local $hQuery = 0, $aRow = 0 ; changed this ------------------------------------------------------------------------------------------------ ;_SQLite_Query($hSQLiteDB, 'SELECT vValue FROM ' & $sSection & ' WHERE vKey = "' & $sKey & '";', $hQuery) ;_SQLite_FetchData($hQuery, $aRow) ;_SQLite_QueryFinalize($hQuery) ; to this _SQLite_QuerySingleRow($hSQLiteDB, 'SELECT vValue FROM ' & $sSection & ' WHERE vKey = "' & $sKey & '";', $aRow) ; end change --------------------------------------------------------------------------------------------------- If @error = 0 Then $sDefault = $aRow[0] Return $sDefault EndFunc ;==>_INIRead_SQL Func _INIReadSection_SQL(ByRef Const $hSQLiteDB, $sSection) __INITableVerify($hSQLiteDB, $sSection, False) Local $aArray = 0, $iColumns = 0, $iRows = 0 If _SQLite_GetTable2d($hSQLiteDB, 'SELECT * FROM ' & $sSection & ';', $aArray, $iRows, $iColumns) = $SQLITE_OK Then $aArray[0][0] = $iRows $aArray[0][1] = '' Else Local $aError[1][2] = [[0]] $aArray = $aError $aError = 0 ConsoleWrite(_sqlite_errmsg() & @LF) EndIf Return $aArray EndFunc ;==>_INIReadSection_SQL Func _INIReadSectionNames_SQL(ByRef Const $hSQLiteDB) Local $aArray = 0, $iColumns = 0, $iRows = 0 If _SQLite_GetTable2d($hSQLiteDB, 'SELECT * FROM SQLITE_MASTER WHERE TYPE = "table";', $aArray, $iRows, $iColumns) = $SQLITE_OK Then Local $aReturn[$iRows + 1] = [$iRows] For $i = 1 To $iRows $aReturn[$i] = $aArray[$i][2] Next $aArray = $aReturn $aReturn = 0 Else Local $aError[1] = [0] $aArray = $aError $aError = 0 EndIf Return $aArray EndFunc ;==>_INIReadSectionNames_SQL Func _INIWrite_SQL(ByRef Const $hSQLiteDB, $sSection, $sKey, $sValue) __INITableVerify($hSQLiteDB, $sSection, True) Return _SQLite_Exec($hSQLiteDB, 'INSERT OR REPLACE INTO ' & $sSection & ' VALUES("' & $sKey & '","' & StringReplace($sValue, "'", "''", 0, 2) & '");') = $SQLITE_OK EndFunc ;==>_INIWrite_SQL ; Internal function for verifying a table exists and removing whitespace from the section (table) name. Func __INITableVerify(ByRef Const $hSQLiteDB, ByRef $sSection, $fCreate) Local $fReturn = True $sSection = StringStripWS($sSection, 8) If $fCreate Then $fReturn = _SQLite_Exec($hSQLiteDB, 'CREATE TABLE IF NOT EXISTS ' & $sSection & '(vKey TEXT, vValue TEXT);') = $SQLITE_OK EndIf Return $fReturn EndFunc ;==>__INITableVerify Finally, I don't really know enough about "ini" file usage to comment on whether this is needed, or practical. As a learning excercise and determination of the usefulness of the help file, I say "Well Done!!!". kylomas Edited January 8, 2013 by kylomas Forum Rules        Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Moderators SmOke_N Posted January 8, 2013 Moderators Share Posted January 8, 2013 While trying to use SQLite to suppliment INI functions may seem like a good idea, you need to understand more DB options available to you. Joining tables, Distinct searches, Unique Keys/Columns, Limiting search returns, multiple If/And ( not really that syntax ) conditions to return a value, etc.Ini functions have their place, but SQLite is NOT a replacement for them, it has it's own place.More an extension of what you already know. Request, Condition, Limit. Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer. Link to comment Share on other sites More sharing options...
kylomas Posted January 8, 2013 Share Posted January 8, 2013 If you are contemplating a replacement/augmentation of "ini" files then you might ask these two questions: "What are "ini"'s not doing that a DB solution might provide?" "Is there demand for this?" Forum Rules        Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
AZJIO Posted January 8, 2013 Share Posted January 8, 2013 http://autoit-script.ru/index.php/topic,3698.0.html My other projects or all Link to comment Share on other sites More sharing options...
jdelaney Posted January 8, 2013 Share Posted January 8, 2013 It seems to me you would only need 1 table, with three columns (section,key,value) where the table's 'key' is a combonation of the section + key IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
guinness Posted January 8, 2013 Author Share Posted January 8, 2013 (edited) http://autoit-script.ru/index.php/topic,3698.0.htmlBriefly looking at the function(s) it seems very similar to what I posted with certain elements to what jchd suggested.kylomas,The error is because I placed SQLite.dll next to the location of the script as I wanted to save on downloading from the AutoIt website. I'm also not contemplating on having this as a replacement, just wanted an opportunity to learn with creating something meaningful. Thanks for the changes by the way.SmOke_N,I agree, it was just an idea that presented itself when looking at SMF. I will continue to use ini files, but needed to put learning SQLite into a practical situation.jchd,You and SmOke_N are the SQLite gurus, so your input is much appreciated. I will have a look at your suggestions and apply to the code above.It's clear I've only scratched the surface of SQLite. Edited January 8, 2013 by guinness UDF List:  _AdapterConnections() • _AlwaysRun() • _AppMon() • _AppMonEx() • _ArrayFilter/_ArrayReduce • _BinaryBin() • _CheckMsgBox() • _CmdLineRaw() • _ContextMenu() • _ConvertLHWebColor()/_ConvertSHWebColor() • _DesktopDimensions() • _DisplayPassword() • _DotNet_Load()/_DotNet_Unload() • _Fibonacci() • _FileCompare() • _FileCompareContents() • _FileNameByHandle() • _FilePrefix/SRE() • _FindInFile() • _GetBackgroundColor()/_SetBackgroundColor() • _GetConrolID() • _GetCtrlClass() • _GetDirectoryFormat() • _GetDriveMediaType() • _GetFilename()/_GetFilenameExt() • _GetHardwareID() • _GetIP() • _GetIP_Country() • _GetOSLanguage() • _GetSavedSource() • _GetStringSize() • _GetSystemPaths() • _GetURLImage() • _GIFImage() • _GoogleWeather() • _GUICtrlCreateGroup() • _GUICtrlListBox_CreateArray() • _GUICtrlListView_CreateArray() • _GUICtrlListView_SaveCSV() • _GUICtrlListView_SaveHTML() • _GUICtrlListView_SaveTxt() • _GUICtrlListView_SaveXML() • _GUICtrlMenu_Recent() • _GUICtrlMenu_SetItemImage() • _GUICtrlTreeView_CreateArray() • _GUIDisable() • _GUIImageList_SetIconFromHandle() • _GUIRegisterMsg() • _GUISetIcon() • _Icon_Clear()/_Icon_Set() • _IdleTime() • _InetGet() • _InetGetGUI() • _InetGetProgress() • _IPDetails() • _IsFileOlder() • _IsGUID() • _IsHex() • _IsPalindrome() • _IsRegKey() • _IsStringRegExp() • _IsSystemDrive() • _IsUPX() • _IsValidType() • _IsWebColor() • _Language() • _Log() • _MicrosoftInternetConnectivity() • _MSDNDataType() • _PathFull/GetRelative/Split() • _PathSplitEx() • _PrintFromArray() • _ProgressSetMarquee() • _ReDim() • _RockPaperScissors()/_RockPaperScissorsLizardSpock() • _ScrollingCredits • _SelfDelete() • _SelfRename() • _SelfUpdate() • _SendTo() • _ShellAll() • _ShellFile() • _ShellFolder() • _SingletonHWID() • _SingletonPID() • _Startup() • _StringCompact() • _StringIsValid() • _StringRegExpMetaCharacters() • _StringReplaceWholeWord() • _StringStripChars() • _Temperature() • _TrialPeriod() • _UKToUSDate()/_USToUKDate() • _WinAPI_Create_CTL_CODE() • _WinAPI_CreateGUID() • _WMIDateStringToDate()/_DateToWMIDateString() • Au3 script parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018 Link to comment Share on other sites More sharing options...
kylomas Posted January 8, 2013 Share Posted January 8, 2013 guinness,but needed to put learning SQLite into a practical situation.Yea, this is how I "grok" shit too!I don't think anyone is saying that it is a "bad" idea (except maybe smoke_n), the questions are just to see if it is a "good" idea.kylomas Forum Rules        Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
jchd Posted January 8, 2013 Share Posted January 8, 2013 @jdelaney, I regard heavy uncalled duplication of data in a DB as a no-no, unless there are really strong reasons for doing so. Normalizing a DB design is always a good idea in the first place, de-normalizing it a little must be dictated later by practical evidence of degraded efficiency in the precise use case. In our case, use of a foreign key will prove more efficient in the case of a growing DB, which may be the reason for switching from .INI to a DB due to size/efficiency limitations. It's due to stacking more entries per SQLite page and moving less data around. Yet the speed difference can only be noticeable in largish DBs. 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 hereRegExp tutorial: enough to get startedPCRE 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 More sharing options...
jdelaney Posted January 8, 2013 Share Posted January 8, 2013 That's fine, you can make lookup tables for sections/key name. I'm no DBA, but I can't imagine having so many section+token combinations that any performace issue would occur. The strong reason would be to easily query one table rather than table*count(Section) IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
jchd Posted January 8, 2013 Share Posted January 8, 2013 I'm not getting overly anal about it, that's just basic SQL normalization (reasonable avoidance of duplication of the same information). 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 hereRegExp tutorial: enough to get startedPCRE 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 More sharing options...
matwachich Posted January 10, 2013 Share Posted January 10, 2013 Hi all! This thread gave me the idea of implementing my own SQLite-based Ini-style data storage. But I have a small problem with Foreign Key. Here is my schem _SQLite_Exec($hIni, 'CREATE TABLE IF NOT EXISTS sqlini_sections (id INTEGER PRIMARY KEY, name UNIQUE ON CONFLICT ABORT)') _SQLite_Exec($hIni, 'CREATE TABLE IF NOT EXISTS sqlini_data (section_id INTEGER REFERENCES sqlini_sections(id) ON DELETE CASCADE ON UPDATE CASCADE, key UNIQUE ON CONFLICT ABORT, data, data_type)') ; --- _SQLite_Exec($hIni, 'CREATE UNIQUE INDEX IF NOT EXISTS sqlini_index_uniqueData ON sqlini_data (section_id, key)') _SQLite_Exec($hIni, 'CREATE INDEX IF NOT EXISTS sqlini_index_data_key ON sqlini_data (key ASC)') I want that when I delete a section, the ON CASCADE close automatically deletes all key/data (in sqlini_data) that are in that section (mean: sqlini_data.section_id = sqlini_section.id). But nothing happens. Any help? Thanks! PS: I use the latest SQLite3.dll from the AutoIt Repository Link to comment Share on other sites More sharing options...
KaFu Posted January 10, 2013 Share Posted January 10, 2013 Just read this and wanted to point you to SMF, but then I saw the acknowledgment ... Well, the first and main reason for me to add a SQLite Ini replacement to SMF is speed... do a test, some dozens inireads and write in a tight loop and look at the timers, ini operations are really, really sloooooow. The replacement reduced the SMF report startup time from some 5 seconds to ~ 2 seconds on my machine. I use just a single table for all ini operations, and like for ini files this means you have to keep a good track of the ini-key names . Here are the relevant functions from SMF: expandcollapse popupIf _SQLite_Exec_Ex("CREATE TABLE IF NOT EXISTS smf_settings(key text unique, value text);") <> $SQLITE_OK Then _ MsgBox(16 + 8192 + 262144, $sSource & " - " & $s_mainguititel_short & " - SQLite Error", "CREATE TABLE smf_settings" & @CRLF & "SQLite Error Code: " & _SQLite_ErrCode() & @CRLF & "SQLite Error Message: " & _SQLite_ErrMsg()) Func _IniRead_SQL($sKey, $sDefaultValue, $bForceDefault = False) If $bForceDefault Then Return $sDefaultValue Local $hQuery, $aRow _SQLite_Query_Ex("SELECT value FROM smf_settings WHERE key = '" & $sKey & "';", $hQuery) _SQLite_FetchData_Ex($hQuery, $aRow) _SQLite_QueryFinalize_Ex($hQuery) If IsArray($aRow) Then Return $aRow[0] Return $sDefaultValue EndFunc ;==>_IniRead_SQL Func _IniReadSection_SQL($sSection) Local $hQuery, $aRow, $aResult, $iEnum = 0 _SQLite_Query_Ex('SELECT count() FROM smf_settings WHERE key like "' & $sSection & '%";', $hQuery) If _SQLite_FetchData_Ex($hQuery, $aRow) <> $SQLITE_OK Then Local $aRow[1] $aRow[0] = 0 EndIf _SQLite_QueryFinalize_Ex($hQuery) If $aRow[0] > 0 Then Dim $aResult[$aRow[0]][2] _SQLite_Query_Ex("SELECT key, value FROM smf_settings WHERE key like '" & $sSection & "%';", $hQuery) While _SQLite_FetchData_Ex($hQuery, $aRow) = $SQLITE_OK $aResult[$iEnum][0] = $aRow[0] $aResult[$iEnum][1] = $aRow[1] $iEnum += 1 WEnd _SQLite_QueryFinalize_Ex($hQuery) EndIf If IsArray($aResult) Then Return $aResult Return SetError(1) EndFunc ;==>_IniReadSection_SQL Func _IniWrite_SQL($sKey, $sValue, $b_Return_Query_as_String = False) $sKey = __SQLite_Ini_FastEscapeEx($sKey) If $b_Return_Query_as_String Then Return "INSERT OR REPLACE into smf_settings VALUES('" & $sKey & "','" & __SQLite_Ini_FastEscapeEx($sValue) & "');" Local $sQuery = "BEGIN IMMEDIATE;INSERT OR REPLACE into smf_settings VALUES('" & $sKey & "','" & __SQLite_Ini_FastEscapeEx($sValue) & "');COMMIT;" If _SQLite_Exec_Ex($sQuery) <> $SQLITE_OK Then MsgBox(16 + 8192 + 262144, $s_mainguititel_short & " - SQLite Error", "Statement _IniWrite_SQL" & @CRLF & "SQLite Error Code: " & _SQLite_ErrCode() & @CRLF & "SQLite Error Message: " & _SQLite_ErrMsg()) EndFunc ;==>_IniWrite_SQL Func _IniDelete_SQL($sKey = "", $b_Return_Query_as_String = False) If $b_Return_Query_as_String Then Return "DELETE FROM smf_settings WHERE key like " & $sKey & ";" Local $sQuery = 'BEGIN IMMEDIATE;DELETE FROM smf_settings WHERE key like "' & $sKey & '";COMMIT;' If _SQLite_Exec_Ex($sQuery) <> $SQLITE_OK Then MsgBox(16 + 8192 + 262144, $s_mainguititel_short & " - SQLite Error", "Statement _IniWrite_SQL" & @CRLF & "SQLite Error Code: " & _SQLite_ErrCode() & @CRLF & "SQLite Error Message: " & _SQLite_ErrMsg()) EndFunc ;==>_IniDelete_SQL Func __SQLite_Ini_FastEscapeEx($sString) If IsNumber($sString) Then $sString = String($sString) ; don't raise error if passing a numeric parameter If Not IsString($sString) Then Return SetError(1, 0, "") Return StringReplace($sString, "'", "''", 0, 1) EndFunc ;==>__SQLite_Ini_FastEscapeEx The _SQLite_* functions I use are all customized (again... speed ...), replace the appeded "_Ex(" with "(" to get the standard UDF functions. Esp. note the parameter "$b_Return_Query_as_String" I use in some functions. When I have several changes in a row I just concatenate the returned strings to a buffer and issue them to the DB in a single _SQLite_Exec at the end (of course that call is enclosed in something like this: "BEGIN IMMEDIATE;QUERY_STRING;COMMIT;". Â OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26)Â BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16)Â ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
jchd Posted January 10, 2013 Share Posted January 10, 2013 Reminder: at the moment foreign key are only enforced after PRAGMA foreign_keys = boolean; be set to ON (or 1). 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 hereRegExp tutorial: enough to get startedPCRE 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 More sharing options...
matwachich Posted January 10, 2013 Share Posted January 10, 2013 Thanks! It works! I have done my version of SQLite-Based Ini-Style data storage. Gonna document it and then post it. Link to comment Share on other sites More sharing options...
kylomas Posted January 10, 2013 Share Posted January 10, 2013 @kafu - you are using the query, fetchdata, queryfinalize sequence to get a single row (much the same as guinness in his example). Is there some reason to use this over querysinglerow? And you use the same sequence instead of _sqlite_gettable2d for returning multiple rows. Can you help me understand where I would prefer one method over the other? Thanks, kylomas Forum Rules        Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
guinness Posted January 10, 2013 Author Share Posted January 10, 2013 As I said KaFu's code was the inspiration, hence the comparison. UDF List:  _AdapterConnections() • _AlwaysRun() • _AppMon() • _AppMonEx() • _ArrayFilter/_ArrayReduce • _BinaryBin() • _CheckMsgBox() • _CmdLineRaw() • _ContextMenu() • _ConvertLHWebColor()/_ConvertSHWebColor() • _DesktopDimensions() • _DisplayPassword() • _DotNet_Load()/_DotNet_Unload() • _Fibonacci() • _FileCompare() • _FileCompareContents() • _FileNameByHandle() • _FilePrefix/SRE() • _FindInFile() • _GetBackgroundColor()/_SetBackgroundColor() • _GetConrolID() • _GetCtrlClass() • _GetDirectoryFormat() • _GetDriveMediaType() • _GetFilename()/_GetFilenameExt() • _GetHardwareID() • _GetIP() • _GetIP_Country() • _GetOSLanguage() • _GetSavedSource() • _GetStringSize() • _GetSystemPaths() • _GetURLImage() • _GIFImage() • _GoogleWeather() • _GUICtrlCreateGroup() • _GUICtrlListBox_CreateArray() • _GUICtrlListView_CreateArray() • _GUICtrlListView_SaveCSV() • _GUICtrlListView_SaveHTML() • _GUICtrlListView_SaveTxt() • _GUICtrlListView_SaveXML() • _GUICtrlMenu_Recent() • _GUICtrlMenu_SetItemImage() • _GUICtrlTreeView_CreateArray() • _GUIDisable() • _GUIImageList_SetIconFromHandle() • _GUIRegisterMsg() • _GUISetIcon() • _Icon_Clear()/_Icon_Set() • _IdleTime() • _InetGet() • _InetGetGUI() • _InetGetProgress() • _IPDetails() • _IsFileOlder() • _IsGUID() • _IsHex() • _IsPalindrome() • _IsRegKey() • _IsStringRegExp() • _IsSystemDrive() • _IsUPX() • _IsValidType() • _IsWebColor() • _Language() • _Log() • _MicrosoftInternetConnectivity() • _MSDNDataType() • _PathFull/GetRelative/Split() • _PathSplitEx() • _PrintFromArray() • _ProgressSetMarquee() • _ReDim() • _RockPaperScissors()/_RockPaperScissorsLizardSpock() • _ScrollingCredits • _SelfDelete() • _SelfRename() • _SelfUpdate() • _SendTo() • _ShellAll() • _ShellFile() • _ShellFolder() • _SingletonHWID() • _SingletonPID() • _Startup() • _StringCompact() • _StringIsValid() • _StringRegExpMetaCharacters() • _StringReplaceWholeWord() • _StringStripChars() • _Temperature() • _TrialPeriod() • _UKToUSDate()/_USToUKDate() • _WinAPI_Create_CTL_CODE() • _WinAPI_CreateGUID() • _WMIDateStringToDate()/_DateToWMIDateString() • Au3 script parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018 Link to comment Share on other sites More sharing options...
KaFu Posted January 10, 2013 Share Posted January 10, 2013 (edited) Here are the two functions _SQLite_QuerySingleRow() and _SQLite_GetTable2d(). If you take a close look you will see that they are nothing more than wrappers for the query, fetchdata, queryfinalize sequence. _SQLite_QuerySingleRow() and _SQLite_GetTable2d() have the advantage of additional error checking... which also takes additional time and I've chosen to strip this extra security . Also the _SQLite_*_Ex( functions I use in SMF are heavily customized, I've stripped most error checking from them too and additionally I initialize global buffers to use instead of creating local buffers for each call (see "func _SQLite_Exec_Ex(" in the SMF source). Func _SQLite_QuerySingleRow($hDB, $sSQL, ByRef $aRow) $aRow = '' If __SQLite_hChk($hDB, 2) Then Return SetError(@error, 0, $SQLITE_MISUSE) Local $hQuery Local $iRval = _SQLite_Query($hDB, $sSQL, $hQuery) If @error Then _SQLite_QueryFinalize($hQuery) Return SetError(1, 0, $iRval) Else $iRval = _SQLite_FetchData($hQuery, $aRow) If $iRval = $SQLITE_OK Then _SQLite_QueryFinalize($hQuery) If @error Then Return SetError(4, 0, $iRval) Else Return $SQLITE_OK EndIf Else _SQLite_QueryFinalize($hQuery) Return SetError(3, 0, $iRval) EndIf EndIf EndFunc ;==>_SQLite_QuerySingleRow expandcollapse popupFunc _SQLite_GetTable2d($hDB, $sSQL, ByRef $aResult, ByRef $iRows, ByRef $iColumns, $iCharSize = -1, $fSwichDimensions = False) If __SQLite_hChk($hDB, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) If $iCharSize = "" Or $iCharSize < 1 Or IsKeyword($iCharSize) Then $iCharSize = -1 Local $sCallBack = "", $fCallBack = False If IsString($aResult) Then If StringLeft($aResult, 16) = "SQLITE_CALLBACK:" Then $sCallBack = StringTrimLeft($aResult, 16) $fCallBack = True EndIf EndIf $aResult = '' If IsKeyword($fSwichDimensions) Then $fSwichDimensions = False Local $hQuery Local $r = _SQLite_Query($hDB, $sSQL, $hQuery) If @error Then Return SetError(2, @error, $r) If $r <> $SQLITE_OK Then __SQLite_ReportError($hDB, "_SQLite_GetTable2d", $sSQL) _SQLite_QueryFinalize($hQuery) Return SetError(-1, 0, $r) EndIf $iRows = 0 Local $iRval_Step, $err While True $iRval_Step = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery) If @error Then $err = @error _SQLite_QueryFinalize($hQuery) Return SetError(3, $err, $SQLITE_MISUSE) ; Dllcall error EndIf Switch $iRval_Step[0] Case $SQLITE_ROW $iRows += 1 Case $SQLITE_DONE ExitLoop Case Else _SQLite_QueryFinalize($hQuery) Return SetError(3, $err, $iRval_Step[0]) EndSwitch WEnd Local $ret = _SQLite_QueryReset($hQuery) If @error Then $err = @error _SQLite_QueryFinalize($hQuery) Return SetError(4, $err, $ret) EndIf Local $aDataRow $r = _SQLite_FetchNames($hQuery, $aDataRow) If @error Then $err = @error _SQLite_QueryFinalize($hQuery) Return SetError(5, $err, $r) EndIf $iColumns = UBound($aDataRow) If $iColumns <= 0 Then _SQLite_QueryFinalize($hQuery) Return SetError(-1, 0, $SQLITE_DONE) EndIf If Not $fCallBack Then If $fSwichDimensions Then Dim $aResult[$iColumns][$iRows + 1] For $i = 0 To $iColumns - 1 If $iCharSize > 0 Then $aDataRow[$i] = StringLeft($aDataRow[$i], $iCharSize) EndIf $aResult[$i][0] = $aDataRow[$i] Next Else Dim $aResult[$iRows + 1][$iColumns] For $i = 0 To $iColumns - 1 If $iCharSize > 0 Then $aDataRow[$i] = StringLeft($aDataRow[$i], $iCharSize) EndIf $aResult[0][$i] = $aDataRow[$i] Next EndIf Else Local $iCbRval $iCbRval = Call($sCallBack, $aDataRow) If $iCbRval = $SQLITE_ABORT Or $iCbRval = $SQLITE_INTERRUPT Or @error Then $err = @error _SQLite_QueryFinalize($hQuery) Return SetError(7, $err, $iCbRval) EndIf EndIf If $iRows > 0 Then For $i = 1 To $iRows $r = _SQLite_FetchData($hQuery, $aDataRow, 0, 0, $iColumns) If @error Then $err = @error _SQLite_QueryFinalize($hQuery) Return SetError(6, $err, $r) EndIf If $fCallBack Then $iCbRval = Call($sCallBack, $aDataRow) If $iCbRval = $SQLITE_ABORT Or $iCbRval = $SQLITE_INTERRUPT Or @error Then $err = @error _SQLite_QueryFinalize($hQuery) Return SetError(7, $err, $iCbRval) EndIf Else For $j = 0 To $iColumns - 1 If $iCharSize > 0 Then $aDataRow[$j] = StringLeft($aDataRow[$j], $iCharSize) EndIf If $fSwichDimensions Then $aResult[$j][$i] = $aDataRow[$j] Else $aResult[$i][$j] = $aDataRow[$j] EndIf Next EndIf Next EndIf Return (_SQLite_QueryFinalize($hQuery)) EndFunc ;==>_SQLite_GetTable2d Here's my version of _SQLite_Exec(). I've stripped most error checking. Also I use three different buffers. Buffer 1 and 2 are globally defined (and thus I save time for initializing them), Buffer 3 is the default approach where a local buffer is created, I've left it there to have a fallback solution for query strings larger than 1 MB. Func _SQLite_Exec_Ex($sSQL) Local $iSize = (StringLen($sSQL) * 2) + 1 If $iSize < 513 Then Local $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sSQL, "int", -1, "struct*", $_SQLite_Exec_Ex_Buffer_Small_S, "int", 512, "ptr", 0, "ptr", 0) Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_exec", "ptr", $g_hDB_SQLite, "struct*", $_SQLite_Exec_Ex_Buffer_Small_S, "ptr", 0, "ptr", 0, "long*", 0) DllCall($h_DLL_msvcrt, "ptr:cdecl", "memset", "struct*", $_SQLite_Exec_Ex_Buffer_Small_S, "int", 0, "int", $aResult[0]) ; clean buffer Return $avRval[0] ElseIf $iSize < 1048577 Then Local $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sSQL, "int", -1, "struct*", $_SQLite_Exec_Ex_Buffer_Large_S, "int", 1048576, "ptr", 0, "ptr", 0) Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_exec", "ptr", $g_hDB_SQLite, "struct*", $_SQLite_Exec_Ex_Buffer_Large_S, "ptr", 0, "ptr", 0, "long*", 0) DllCall($h_DLL_msvcrt, "ptr:cdecl", "memset", "struct*", $_SQLite_Exec_Ex_Buffer_Large_S, "int", 0, "int", $aResult[0]) ; clean buffer Return $avRval[0] Else Local $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sSQL, "int", -1, "ptr", 0, "int", 0, "ptr", 0, "ptr", 0) Local $tSQL8 = DllStructCreate("char[" & $aResult[0] & "]") $aResult = DllCall("Kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sSQL, "int", -1, "struct*", $tSQL8, "int", $aResult[0], "ptr", 0, "ptr", 0) Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_exec", "ptr", $g_hDB_SQLite, "struct*", $tSQL8, "ptr", 0, "ptr", 0, "long*", 0) Return $avRval[0] EndIf EndFunc ;==>_SQLite_Exec_Ex Edited January 10, 2013 by KaFu  OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26) BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16) ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
kylomas Posted January 10, 2013 Share Posted January 10, 2013 kafu, Thanks, I'm an idiot for not looking at the function, duh! kylomas Forum Rules        Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now