Jump to content

INI like syntax using SQLite.


Recommended Posts

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.

#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

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 can

SELECT * from Sections natural join Contents where <some complex condition>

but you can't do

SELECT <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 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

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.

#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 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

  • Moderators

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

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

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

http://autoit-script.ru/index.php/topic,3698.0.html

Briefly 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 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

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

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

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

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

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

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:

If _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 :lol:...), 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;".

Link to comment
Share on other sites

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

@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

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

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