Search the Community

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