Is _SQLite_QueryFinalize really necessary?

[benners]

I only ask because I am having issues with the return code _SQLite_QueryFinalize gives me. I know its probably my faulty coding or understanding but I can't seem to get it to return 0
 
Using the posted code I carry out a query and get the expected value for the query. I try to finalize the query so as not to get SQLITE_BUSY when trying to close the db but the returned code is 21 which is $SQLITE_MISUSE (Library used incorrectly) and the database closes without error. If I comment the finalizing code out the database still closes without error.
 
What am I not understanding correctly?.

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup ()
_SQLite_Open ("")
_SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS Architecture ('Architecture');")
_SQLite_Exec(-1,"INSERT INTO Architecture VALUES ('x64');")
_SQLite_Exec(-1,"INSERT INTO Architecture VALUES ('x32');")

Local $sRet = _Database_Query("SELECT * FROM Architecture;")
;~ $sRet = _Database_Query("SELECT * FROM Architecture;") ; added to run another query without closing the first and try to error
MsgBox(0, 'Result', $sRet)
MsgBox(0,'Close', _SQLite_Close ())
MsgBox(0,'Shutdown',_SQLite_Shutdown ())

Func _Database_Query($sSQL, $iOutput = 1) ; output 1 = delimited string, 2 = array
    Local $vRet, $hQuery

    $vRet = _SQLite_Query(-1, $sSQL, $hQuery)
    If @error Then Return MsgBox(0,'', 'Error: ' & _SQLite_ErrMsg())

    Local $aRow, $sRet = ''

    ; loop and get all the data
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        $sRet &= $aRow[0] & '|'
    WEnd

    If @error And _SQLite_ErrMsg() <> 'not an error' Then
        ; finalise the query
;~      _SQLite_QueryFinalize($hQuery)
        Return MsgBox(0,'', 'Error: ' & _SQLite_ErrMsg())
    EndIf

    ; finalise the query
    MsgBox(0,'Finalize', 'Returned: ' & _SQLite_QueryFinalize($hQuery))

    ; trim any trailing |
    $sRet = StringRegExpReplace($sRet, '[\|]\z', '')

    If $iOutput = 1 Then Return $sRet
    Return StringSplit($sRet, '|')
EndFunc   ;==>_Database_Query

[jchd]

Leaving a query not finalized is an optional parameter in _SQLite_FetchData.

I always advise not using low-level functions (*_Query, *_Fetch*, and such) and favor _SQlite_GetTable[2d] or _SQLite_QuerySingleRow instead. Well, unless you really have at least one good reason or doing so and fully understand what that implies.

If I were you I'd rewrite your code and use _SQLite_GetTable2d.

[MHz]

I get the error with using #include <SQLite.dll.au3>, otherwise this works fine as I have SQLite3.dll in path.

expandcollapse popup

#include <SQLite.au3>
;~ #include <SQLite.dll.au3>

_SQLite_Startup ()
_SQLite_Open ("")
_SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS Architecture ('Architecture');")
_SQLite_Exec(-1,"INSERT INTO Architecture VALUES ('x64');")
_SQLite_Exec(-1,"INSERT INTO Architecture VALUES ('x32');")

Local $sRet = _Database_Query("SELECT * FROM Architecture;")
$sRet = _Database_Query("SELECT * FROM Architecture;") ; added to run another query without closing the first and try to error
MsgBox(0, 'Result ' & @error, $sRet)
MsgBox(0,'Close', _SQLite_Close ())
MsgBox(0,'Shutdown',_SQLite_Shutdown ())

Func _Database_Query($sSQL, $iOutput = 1) ; output 1 = delimited string, 2 = array
    Local $vRet, $hQuery, $aRow, $sRet, $bEarlyReturn
    If _SQLite_Query(-1, $sSQL, $hQuery) = $SQLITE_OK Then
        ; loop and get all the data
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            $sRet &= $aRow[0] & '|'
            ; If Fetchdata does not finalize as to early exit from the loop then
            ; you may need to use _SQLite_QueryFinalize() to inform SQLite to release
            ; from the query.
            If Random(0, 2, 1) Then
                ; if this is not called then SQLite may stay busy with the open query
                ; and you may get the error when you close SQLite or do another query
                MsgBox(0, 'Finalize', 'Returned: ' & _SQLite_QueryFinalize($hQuery))
                Return $sRet
            EndIf
        WEnd
        If $sRet == '' Then Return SetError(1, 0, '')
        ; trim any trailing |
        $sRet = StringRegExpReplace($sRet, '[\|]\z', '')
        ;
        If $iOutput = 1 Then Return $sRet
        Return StringSplit($sRet, '|')
    Else
        MsgBox(0,'', 'Error: ' & _SQLite_ErrMsg())
        Return SetError(2, 0, '')
    EndIf
EndFunc   ;==>_Database_Query

You initiate the query so SQLite is busy doing the query. You call FetchData in a loop to get the data of the query until finalized. If you do not loop until finalized, then SQLite remains in the busy state with the query and as such it errors if you attempt another query or attempt to close the handle. This is when you call QueryFinalize to end the query. I would not expect use of QueryFinalize in your UDF as you loop through with FetchData until finalized. So if you attempt to use QueryFinalize when it is already finalized then you get an error on close. If the query errors then their is nothing to finalize so no need to use on error either.

[jchd]

Thanks to MHz for taking the time to explain why it is so.

Now here is another different, simpler way to obtain the same result:

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup ()
_SQLite_Open ("")
_SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS Architectures (Architecture text);")
; architecture was ambiguous as it was the name of both a table and a column
; while this is valid SQL, it is error prone and best avoided
; also don't enclose schema names (columns or tables or such) in single quotes
; only string literals should be enclosed in single quotes, with escaping (meaningful single quotes doubled)
; use _SQLite_FastEscape() to escape strings.
; schema names containing whitespaces or other special character (but Unicode symbols and letters are allowed) 
; must be enclosed with double quotes "my table" or square brackets [♈♉♊♋♌♍♎♏♐♑♒♓ (zodiac)]

_SQLite_Exec(-1,"INSERT INTO Architectures VALUES ('x64');")
_SQLite_Exec(-1,"INSERT INTO Architectures VALUES ('x32');")
_SQLite_Exec(-1,"INSERT INTO Architectures VALUES ('ARM');")

Local $aRow
_SQLite_QuerySingleRow(-1, "SELECT group_concat(architecture, '|') FROM Architectures group by 'constant';", $aRow)
MsgBox(0, 'Result', $aRow[0])
MsgBox(0,'Close', _SQLite_Close ())
MsgBox(0,'Shutdown',_SQLite_Shutdown ())

Edited November 6, 2013 by jchd

[jchd]

@MHz,

Can you debug what error you get with the (now empty) DLL include?

[benners]

Thanks jchd and MHz for your replies. The posted code was stripped from the main file to show the problem

@MHz

Thank you for the explanation. After jchd's post I looked at the help file and noticed that the query was finalized by default but your explanation made it clearer.

@jchd

Thank you for putting me on the right track originally and for your commented suggestions in the last post. I have gone through my full code to change what you suggested. After your last post I now have something else to research (group_concat) I have been reading the help file and making little functions to test to see which ones would be useful for the information I need. I had tried _SQlite_GetTable[2d] or _SQLite_QuerySingleRow but had a few problems with the returned results when I used a variable in the sql string. After a few hours of staring at the screen unable to fathom it, I decided to try other methods.

The code I had issues with was this

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>

Local $aRow, $skb = 'KB2083821'
Local $sSQL1 = 'SELECT * FROM Windows6_1 WHERE KBArticleNumber=''KB2803821'';' ; works
Local $sSQL2 = 'SELECT * FROM Windows6_1 WHERE KBArticleNumber=''' & $skb & ''';' ; fails

;~ if ($sSQL1 == $sSQL2) then MsgBox(0,'Equal', $sSQL1 & @crlf & $sSQL2) ; test to display if strings are equal just in case my eyes are bad :)
_SQLite_Startup()
_SQLite_Open(@scriptdir & '\updates.db')

Local $vRet = _SQLite_QuerySingleRow(-1, $sSQL1, $aRow) ; works
;~ Local $vRet = _SQLite_QuerySingleRow(-1, $sSQL2, $aRow) ; fails

If @error Then
    MsgBox(0, 'Error: ' & @error, _SQLite_ErrMsg())
Else
    _ArrayDisplay($aRow)
EndIf

_SQLite_Close()
_SQLite_Shutdown()

and the small database is attached (zipped for upload purposes)

 

updates.7z

[jchd]

Again, don't use single quotes around schema names. Also it is best to declare a relevant type for columns.

Your current table declaration is:

CREATE TABLE Windows6_1 ('KBArticleNumber','Title','Products','Classification','LastUpdated','Version','Size','AllowedOffline','FileName','FileSize','SupportLink','SearchResultsLink','UpdateDetailsLink','MoreInfoLink','FullPath','supersededbyInfo','supersedesInfo');

Make that:

CREATE TABLE "Windows6_1" (
  "KBArticleNumber" TEXT, 
  "Title" TEXT, 
  "Products" TEXT, 
  "Classification" TEXT, 
  "LastUpdated" TEXT, 
  "Version" TEXT, 
  "Size" TEXT, 
  "AllowedOffline" TEXT, 
  "FileName" TEXT, 
  "FileSize" TEXT, 
  "SupportLink" TEXT, 
  "SearchResultsLink" TEXT, 
  "UpdateDetailsLink" TEXT, 
  "MoreInfoLink" TEXT, 
  "FullPath" TEXT, 
  "supersededbyInfo" TEXT, 
  "supersedesInfo" TEXT);

Use SQL NULL to represent "n/a".

Use a sensible format for dates: 8/13/2013 --> 2013-08-13.

Use _SQL_FastEscape() to escape string variables, e.g.

$sSQL2 doesn't work due to a typo in $kb

Local $aRow, $skb = 'KB2083821'  ; note: ..208... should be ..280...

Make a better use of allowed quotes for AutoIt strings:

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>

Local $aRow, $skb = 'KB2803821'
Local $sSQL1 = "SELECT * FROM Windows6_1 WHERE KBArticleNumber='KB2803821';" ; works
Local $sSQL2 = "SELECT * FROM Windows6_1 WHERE KBArticleNumber=" & _SQLite_FastEscape($skb) & ";" ; fails

;~ if ($sSQL1 = $sSQL2) then MsgBox(0,'Equal', $sSQL1 & @crlf & $sSQL2) ; test to display if strings are equal just in case my eyes are bad :)
_SQLite_Startup()
_SQLite_Open(@SCRIPTDIR & '\updates.db')

Local $vRet = _SQLite_QuerySingleRow(-1, $sSQL2, $aRow) ; works

If @error Then
    MsgBox(0, 'Error: ' & @error, _SQLite_ErrMsg())
Else
    _ArrayDisplay($aRow)
EndIf

_SQLite_Close()
_SQLite_Shutdown()

[benners]

DOH!, how could  I miss the typo   I spent ages staring at the screen and returned strings and still missed it now I do feel stupid.

Been through the code and double quoted the SQL statements, an example is

Local $sSQL = "INSERT INTO " & $sTable & " VALUES ('" & $svalues & "');"

and the $sValues string is

"Windows XP','WindowsXP"

I looked at the _SQLite_FastEscape function but decided it was just as easy to quote them myself, saves a function call.

 

Use SQL NULL to represent "n/a".

Use a sensible format for dates: 8/13/2013 --> 2013-08-13.

 

The strings are pulled from the MS website and are only going to be displayed, no manipulation so I would like them to stay as they are retrieved. Its just a simple database that only holds strings (at the moment)

I also updated the Create Table code adding the column type declaration, but I didn't use the way you posted. I use and array to loop through and create tables that should always be present so I declared by

$aDefaultTables[2][2] = [["Products", "Product Text,WinVersion Text"],["Architectures", "Architecture Text"]]

and the values are declared like

Local $aDefaultValues[9] = ["Windows XP','WindowsXP", "Windows Server 2003','WindowsServer2003", "Windows Vista','Windows6.0", "Windows 2008','Windows6.0", _
                        "Windows 7','Windows6.1", "Windows 7 Embedded Standard','Windows6.1", "Windows Server 2008 R2','Windows6.1", _
                        "Windows Server 2012','Windows8-RT", "Windows 8','Windows8-RT"]

Is there a reason why you double quoted the table name and column names but not the type?. I have been reading the W3Schools website and that has influenced my use of single quotes is your method a better way?. I have searched and there seem to be conflicting views some for single others for double quotes.

Thanks again for your help. The script is currently working as I expect and this is really my first foray into database manipulation apart from scripting msi files. The program is for Windows updates, mainly Windows 7 and came about after I recently started to make the XP -> 7 shift.

The idea is to always have an up to date repository of updates. I download new updates as they are released. The download folder is selected and new updates are scanned and information scraped from the update catalog website is added to the database. (In progress) ->The updates are then sorted by type i.e Critical, Security, non updates etc and copied to folders of the same name. any that are superseded by new updates are removed from the database and the folders so later they can be integrated.

 

[jchd]

_SQLite_FastEscape also escapes single quotes in literals, some thing important to consider.

Even if storing the data verbatim is OK for you right now, that doesn't mean that is will be in some future. Especially dates: the US format is completely unworkable and should never be use as storage format. Just imagine the work if you want to list all critical updates in the last 3 months...

I use SQLite Expert to experiment with SQLite DBs. This management tool (which I highly recommend) automatically puts schema names in either double quotes or square brackets. SQLite is a bit special in that only 4 basic datatypes are available, but type names can be anything of your choice.

MySQL uses back-quotes for schema names `like this` and SQLite is smart enough to recognize this deviant useage.

Sorry for the dalay in answering.

[benners]

_SQLite_FastEscape also escapes single quotes in literals, some thing important to consider.

Even if storing the data verbatim is OK for you right now, that doesn't mean that is will be in some future. Especially dates: the US format is completely unworkable and should never be use as storage format. Just imagine the work if you want to list all critical updates in the last 3 months...

I use SQLite Expert to experiment with SQLite DBs. This management tool (which I highly recommend) automatically puts schema names in either double quotes or square brackets. SQLite is a bit special in that only 4 basic datatypes are available, but type names can be anything of your choice.

MySQL uses back-quotes for schema names `like this` and SQLite is smart enough to recognize this deviant useage.

Sorry for the dalay in answering.

 

Thanks for the explanations. I have been using SQLite Expert and before that, sqlitebrowser to test queries and check the tables when they are added, they do make things easier. I may in the future add functionality to the program so will keep your advise in mind.

No need to apologise for the reply delay, we all have lives and other stuff that gets in the way and I appreciate people who give their time to help others (especially me)