[Sqlite] SELECT: Exec or Query?

[littlebigman]

Hello,

I found no hits in the forum.

I notice the SQLite UDF provides two functions to send a SELECT: Query(), or Exec() + callback function.

Should Exce() be prefered when running a lot of data?

Thank you.

expandcollapse popup

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

Opt('MustDeclareVars', 1)

Const $SQLITEDB = "c:\dummy.sqlite"
Local $hDskDb, $hQuery, $aRow, $sMsg

_SQLite_Startup()
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

$hDskDb = _SQLite_Open($SQLITEDB)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't open the database!")
    Exit -1
EndIf

_SQLite_Query(-1, "SELECT id,title FROM content;", $hQuery) ; the query
$sMsg = ""
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        $sMsg &= $aRow[0] & "," & $aRow[1] & @CRLF
WEnd
ConsoleWrite($sMsg)
_SQLite_QueryFinalize($hQuery)

Local $d = _SQLite_Exec(-1, "SELECT id,title FROM content", "_cb") ; _cb will be called for each row
Func _cb($aResult)
        For $s In $aResult
                ConsoleWrite($s & @TAB)
        Next
        ConsoleWrite(@CRLF)
        ; Return $SQLITE_ABORT ; Would Abort the process and trigger an @error in _SQLite_Exec()
EndFunc   ;==>_cb

_SQLite_Close($hDskDb)
_SQLite_Shutdown()

---

Edit: Is where a way to tell the UDF to not display the query as the first row?

---

Edit: Is there a way to access rows with column names instead of indexes?

Local $d = _SQLite_Exec(-1, $QUERY, "_cb") ; _cb will be called for each row
Func _cb($aResult)
    ;? ConsoleWrite($aResult["id"] & @TAB & $aResult["title"])
    ConsoleWrite($aResult[0] & @TAB & $aResult[3])

 

Edited February 7, 2023 by littlebigman

[mistersquirrle]

Probably depends on how and when you want to use the data. With _SQLite_Query you need to use _SQLite_FetchData to read the data, but you don't have to do it as the SQL is ran. So you can do the query now and use the data later.

To me _SQLite_Query + _SQLite_FetchData is probably the correct way to go for reading/selecting data (or I prefer _SQLite_GetTable2D). _SQLite_Exec would be for anything that you aren't expecting to get data back for, like inserting, creating, updating, etc.

 

I added on some code to give an example of how you could reference a column by name, instead of by ID:

expandcollapse popup

;https://www.autoitscript.com/forum/topic/209644-sqlite-select-exec-or-query/

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

;~ Opt('MustDeclareVars', 1)

Const $SQLITEDB = ":memory:"
Local $hDskDb, $hQuery, $aRow, $sMsg

_SQLite_Startup()
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

$hDskDb = _SQLite_Open($SQLITEDB)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't open the database!")
    Exit -1
EndIf

_SQLite_Exec(-1, ";create table if not exists content ( id integer primary key, title text null, animal text null ) ;")
_SQLite_Exec(-1, "; insert into content ( title, animal ) values ('meow', 'cat'), ('woof', 'dog'), ('oink', 'pig'), ('ribbit', 'frog');")

_SQLite_Query(-1, "SELECT id,title,animal FROM content;", $hQuery) ; the query
$sMsg = ""
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    $sMsg &= $aRow[0] & "," & $aRow[1] & @CRLF
WEnd
ConsoleWrite($sMsg)
_SQLite_QueryFinalize($hQuery)

Local $d = _SQLite_Exec(-1, "SELECT id,title,animal FROM content", "_cb") ; _cb will be called for each row
Func _cb($aResult)
    For $s In $aResult
        ConsoleWrite($s & @TAB)
    Next
    ConsoleWrite(@CRLF)
    ; Return $SQLITE_ABORT ; Would Abort the process and trigger an @error in _SQLite_Exec()
EndFunc   ;==>_cb

_SQLite_Query(-1, "SELECT id,title,animal FROM content;", $hQuery) ; the query
Local $aColNames
_SQLite_FetchNames($hQuery, $aColNames)
;~ _ArrayDisplay($aColNames)
_GetColumnByName($aColNames)
_SQLite_QueryFinalize($hQuery)

Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM content;", $aResult, $iRows, $iColumns)
;~ _ArrayDisplay($aResult)

ConsoleWrite(@CRLF & 'Referencing columns by name' & @CRLF)
ConsoleWrite('    id column id: ' & _GetColumnByName('id') & @CRLF)
ConsoleWrite(' title column id: ' & _GetColumnByName('title') & @CRLF)
ConsoleWrite('animal column id: ' & _GetColumnByName('animal') & @CRLF)

For $iRow = 1 To UBound($aResult) - 1
    ConsoleWrite('Loading row: ' & $iRow & @CRLF)
    ConsoleWrite('     id: ' & $aResult[$iRow][_GetColumnByName('id')] & @CRLF)
    ConsoleWrite('  title: ' & $aResult[$iRow][_GetColumnByName('title')] & @CRLF)
    ConsoleWrite(' animal: ' & $aResult[$iRow][_GetColumnByName('animal')] & @CRLF)
Next

Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM content;", $aResult, $iRows, $iColumns)
;~ _ArrayDisplay($aResult)
_GetColumnByName($aResult)

ConsoleWrite(@CRLF & 'Referencing columns by name (from _SQLite_GetTable2D)' & @CRLF)
ConsoleWrite('    id column id: ' & _GetColumnByName('id') & @CRLF)
ConsoleWrite(' title column id: ' & _GetColumnByName('title') & @CRLF)
ConsoleWrite('animal column id: ' & _GetColumnByName('animal') & @CRLF)

For $iRow = 1 To UBound($aResult) - 1
    ConsoleWrite('Loading row: ' & $iRow & @CRLF)
    ConsoleWrite('     id: ' & $aResult[$iRow][_GetColumnByName('id')] & @CRLF)
    ConsoleWrite('  title: ' & $aResult[$iRow][_GetColumnByName('title')] & @CRLF)
    ConsoleWrite(' animal: ' & $aResult[$iRow][_GetColumnByName('animal')] & @CRLF)
Next


Func _GetColumnByName($vColumnName = '')
    Local Static $aColumns[1]
    If IsArray($vColumnName) Then
        If UBound($vColumnName, 2) > 0 Then
            ReDim $aColumns[UBound($vColumnName, 2)]

            For $iIndex = 0 To UBound($vColumnName, 2) - 1
                $aColumns[$iIndex] = $vColumnName[0][$iIndex]
            Next
        Else ; 1d array, such as from _SQLite_FetchNames
            ReDim $aColumns[UBound($vColumnName)]

            For $iIndex = 0 To UBound($vColumnName) - 1
                $aColumns[$iIndex] = $vColumnName[$iIndex]
            Next
        EndIf
        Return SetError(0, 0, True)
    EndIf

    For $iIndex = 0 To UBound($aColumns) - 1
        If StringCompare($vColumnName, $aColumns[$iIndex], 0) = 0 Then
            Return SetError(0, 0, $iIndex)
        EndIf
    Next

    Return SetError(1, 0, -1)
EndFunc   ;==>_GetColumnByName


_SQLite_Close($hDskDb)
_SQLite_Shutdown()

 

It uses _SQLite_FetchNames, however you could skip that step and instead use _SQLite_GetTable2D to 'build' the column names in _GetColumnByName (would require changing it a bit to utilize/search the 2d array).

However to use it as is, get the column names with _SQLite_FetchNames and pass that array into _GetColumnByName, then you can call _GetColumnByName('ColName') and it should return the index for that column name (though if your column doesn't exist it'll have a bad time)

Edited February 8, 2023 by mistersquirrle
Updated code, changed comment

[jchd]

As a general rule, use *_Exec to issue SQL returning no data and *_GetTable[2d] in the general case, including write queries (insert, update, upsert, replace, ... when they have a RETURNING clause).

Leave *_Query and friends alone since this way is paved with pitfalls, well unless you have a good reason like when you have frequently running queries you don't want to prepare everytime. But then it's up to you to manage your queries and use *_QueryReset and *_QueryFinalize correctly. Note you also have to handle unexpected errors yourself at each step.

Quote

Is where a way to tell the UDF to not display the query as the first row?

I don't get you: the query isn't "displayed".

Quote

Is there a way to access rows with column names instead of indexes?

Forget *_Exec and use *_GetTable[2d]. *_Exec is NOT for returning data, even less to process the data row by row. This makes the transaction last longer for no good. Grab the data and process it outside a transaction. This way concurrency is less affected.

[littlebigman]

Thanks much for the code, and the infos about what functions to use when.

Edited February 8, 2023 by littlebigman

[littlebigman]

Please excuse the newbie question but… what if I need to call _GetColumnByName() for a different table, ie. different columns?

Since it uses a Static variable, it remembers the contents between calls.

Func _GetColumnByName($vColumnName = '')
    Local Static $aColumns[1]

 

[mistersquirrle]

21 hours ago, littlebigman said:

Please excuse the newbie question but… what if I need to call _GetColumnByName() for a different table, ie. different columns?

Since it uses a Static variable, it remembers the contents between calls.

 

Yes! Exactly, the static variable is how we are able to feed in the column names for it to remember, then we can call it however many times needed for that same table. Once we want a new list/index of column names to reference, just feed the new table/column data in. Check out this example:

expandcollapse popup

;https://www.autoitscript.com/forum/topic/209644-sqlite-select-exec-or-query/

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

;~ Opt('MustDeclareVars', 1)

Const $SQLITEDB = ":memory:"
Local $hDskDb, $hQuery, $aRow, $sMsg

_SQLite_Startup()
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

$hDskDb = _SQLite_Open($SQLITEDB)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't open the database!")
    Exit -1
EndIf

_SQLite_Exec(-1, ";create table if not exists animalNoises ( id integer primary key, noise text null, animal text null ) ;")
_SQLite_Exec(-1, "; insert into animalNoises ( noise, animal ) values ('meow', 'cat'), ('woof', 'dog'), ('oink', 'pig'), ('ribbit', 'frog');")

_SQLite_Exec(-1, ";create table if not exists peopleInfo ( id integer primary key, personName text null, personAge int null ) ;")
_SQLite_Exec(-1, "; insert into peopleInfo ( personName, personAge ) values ('john', 23), ('Alex', 31), ('Gary', 76), ('Tyler', 12);")

Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM animalNoises;", $aResult, $iRows, $iColumns)
;~ _ArrayDisplay($aResult)
_GetColumnByName($aResult) ; This sets our function to load and use the column names from 'animalNoises', since that's what in $aResults right now

ConsoleWrite(@CRLF & 'Referencing columns by name' & @CRLF)
ConsoleWrite('    id column id: ' & _GetColumnByName('id') & @CRLF)
ConsoleWrite(' noise column id: ' & _GetColumnByName('noise') & @CRLF)
ConsoleWrite('animal column id: ' & _GetColumnByName('animal') & @CRLF)

For $iRow = 1 To UBound($aResult) - 1
    ConsoleWrite('Loading row: ' & $iRow & @CRLF)
    ConsoleWrite('      id: ' & $aResult[$iRow][_GetColumnByName('id')] & @CRLF)
    ConsoleWrite('   noise: ' & $aResult[$iRow][_GetColumnByName('noise')] & @CRLF)
    ConsoleWrite('  animal: ' & $aResult[$iRow][_GetColumnByName('animal')] & @CRLF)
Next

$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM peopleInfo;", $aResult, $iRows, $iColumns)
;~ _ArrayDisplay($aResult)
_GetColumnByName($aResult) ; Now we change the function to store and use the column names from 'peopleInfo' because we've replace the data in $aResult with it

ConsoleWrite(@CRLF & 'Referencing columns by name (from _SQLite_GetTable2D)' & @CRLF)
ConsoleWrite('         id column id: ' & _GetColumnByName('id') & @CRLF)
ConsoleWrite(' personName column id: ' & _GetColumnByName('personName') & @CRLF)
ConsoleWrite('  personAge column id: ' & _GetColumnByName('personAge') & @CRLF)

For $iRow = 1 To UBound($aResult) - 1
    ConsoleWrite('Loading row: ' & $iRow & @CRLF)
    ConsoleWrite('           id: ' & $aResult[$iRow][_GetColumnByName('id')] & @CRLF)
    ConsoleWrite('   personName: ' & $aResult[$iRow][_GetColumnByName('personName')] & @CRLF)
    ConsoleWrite('    personAge: ' & $aResult[$iRow][_GetColumnByName('personAge')] & @CRLF)
Next

_SQLite_Close($hDskDb)
_SQLite_Shutdown()

Func _GetColumnByName($vColumnName = '')
    Local Static $aColumns[1]
    If IsArray($vColumnName) Then
        If UBound($vColumnName, 2) > 0 Then
            ReDim $aColumns[UBound($vColumnName, 2)]

            For $iIndex = 0 To UBound($vColumnName, 2) - 1
                $aColumns[$iIndex] = $vColumnName[0][$iIndex]
            Next
        Else ; 1d array, such as from _SQLite_FetchNames
            ReDim $aColumns[UBound($vColumnName)]

            For $iIndex = 0 To UBound($vColumnName) - 1
                $aColumns[$iIndex] = $vColumnName[$iIndex]
            Next
        EndIf
        Return SetError(0, 0, True)
    EndIf

    For $iIndex = 0 To UBound($aColumns) - 1
        If StringCompare($vColumnName, $aColumns[$iIndex], 0) = 0 Then
            Return SetError(0, 0, $iIndex)
        EndIf
    Next

    Return SetError(1, 0, -1)
EndFunc   ;==>_GetColumnByName

 

There's also nothing stopping you from doing something as silly as switching which table/column names you're pulling from on each loop of a row, as long as you stored the data from each table/query in a different variable. You can easily switch between which table/column data you're asking for by name:

expandcollapse popup

;https://www.autoitscript.com/forum/topic/209644-sqlite-select-exec-or-query/

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

;~ Opt('MustDeclareVars', 1)

Const $SQLITEDB = ":memory:"
Local $hDskDb, $hQuery, $aRow, $sMsg

_SQLite_Startup()
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

$hDskDb = _SQLite_Open($SQLITEDB)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't open the database!")
    Exit -1
EndIf

_SQLite_Exec(-1, ";create table if not exists animalNoises ( id integer primary key, noise text null, animal text null ) ;")
_SQLite_Exec(-1, "; insert into animalNoises ( noise, animal ) values ('meow', 'cat'), ('woof', 'dog'), ('oink', 'pig'), ('ribbit', 'frog');")

_SQLite_Exec(-1, ";create table if not exists peopleInfo ( id integer primary key, personName text null, personAge int null ) ;")
_SQLite_Exec(-1, "; insert into peopleInfo ( personName, personAge ) values ('John', 23), ('Alex', 31), ('Gary', 76), ('Tyler', 12), ('Jenny', 46);")

Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM animalNoises;", $aResult, $iRows, $iColumns)
;~ _ArrayDisplay($aResult)
;~ _GetColumnByName($aResult) ;

Local $aResult2
$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM peopleInfo;", $aResult2, $iRows, $iColumns)
;~ _ArrayDisplay($aResult2)
;~ _GetColumnByName($aResult2) ;

Local $iMaxRows = (UBound($aResult) > UBound($aResult2) ? UBound($aResult) : UBound($aResult2))

For $iRow = 1 To $iMaxRows - 1
    ConsoleWrite('Loading row: ' & $iRow & @CRLF)
    If $iRow < UBound($aResult) Then
        _GetColumnByName($aResult)
        ConsoleWrite('      id: ' & $aResult[$iRow][_GetColumnByName('id')] & @CRLF)
        ConsoleWrite('   noise: ' & $aResult[$iRow][_GetColumnByName('noise')] & @CRLF)
        ConsoleWrite('  animal: ' & $aResult[$iRow][_GetColumnByName('animal')] & @CRLF)
    EndIf
    If $iRow < UBound($aResult2) Then
        _GetColumnByName($aResult2)
        ConsoleWrite('           id: ' & $aResult2[$iRow][_GetColumnByName('id')] & @CRLF)
        ConsoleWrite('   personName: ' & $aResult2[$iRow][_GetColumnByName('personName')] & @CRLF)
        ConsoleWrite('    personAge: ' & $aResult2[$iRow][_GetColumnByName('personAge')] & @CRLF)
    EndIf
Next

_SQLite_Close($hDskDb)
_SQLite_Shutdown()

Func _GetColumnByName($vColumnName = '')
    Local Static $aColumns[1]
    If IsArray($vColumnName) Then
        If UBound($vColumnName, 2) > 0 Then
            ReDim $aColumns[UBound($vColumnName, 2)]

            For $iIndex = 0 To UBound($vColumnName, 2) - 1
                $aColumns[$iIndex] = $vColumnName[0][$iIndex]
            Next
        Else ; 1d array, such as from _SQLite_FetchNames
            ReDim $aColumns[UBound($vColumnName)]

            For $iIndex = 0 To UBound($vColumnName) - 1
                $aColumns[$iIndex] = $vColumnName[$iIndex]
            Next
        EndIf
        Return SetError(0, 0, True)
    EndIf

    For $iIndex = 0 To UBound($aColumns) - 1
        If StringCompare($vColumnName, $aColumns[$iIndex], 0) = 0 Then
            Return SetError(0, 0, $iIndex)
        EndIf
    Next

    Return SetError(1, 0, -1)
EndFunc   ;==>_GetColumnByName

 

Do these examples help make sense for how you would use this function? _SQLite_GetTable2D retrieves data, with column names as the first 'row'. When you pass an array into _GetColumnByName instead of a string/column name, it updates that Static variable with the new column names from the data you passed in. You can then call _GetColumnByName with the column name and it'll return the index/column that it's at in the last query data you passed into it.

[littlebigman]

Yes, makes sense:

_GetColumnByName($aResult) ; Now we change the function to store and use the column names from 'peopleInfo' because we've replace the data in $aResult with it

ConsoleWrite('id column id: ' & _GetColumnByName('id') & @CRLF)

Since it's a common need to be able to access columns by their name instead of index, it might be a good idea to add this function to the UDF.

Thank you.

 

--

Edit: Copying the data from the query output to the clipboard (about ten lines of text), and then calling Send() with the contents of the clipboard is no faster. Am I doing it wrong?

Local $iRval,$aResult, $aNames, $iRows,$iCols
$iRval = _SQLite_GetTableData2D(-1, $QUERY, $aResult, $iRows, $aNames)
If $iRval = $SQLITE_OK Then
    Sleep(500)
    ClipPut($aResult[0][1])
    Sleep(5000)
    Send(ClipGet())
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

--

Edit: Much better :-p

;Send(ClipGet())
;CTRL+V
Send("^v")

 

Edited February 10, 2023 by littlebigman

[littlebigman]

BTW, since it can be called with either an array or a string, shouldn't the function be refactored thusly?

Func _GetColumnByName($vColumnName = '')
    Local Static $aColumns[1]

    #Array? Fill $aColumns[] with data from new table
    If IsArray($vColumnName) Then
        If UBound($vColumnName, 2) > 0 Then
            ReDim $aColumns[UBound($vColumnName, 2)]

            For $iIndex = 0 To UBound($vColumnName, 2) - 1
                $aColumns[$iIndex] = $vColumnName[0][$iIndex]
            Next
        Else ; 1d array, such as from _SQLite_FetchNames
            ReDim $aColumns[UBound($vColumnName)]

            For $iIndex = 0 To UBound($vColumnName) - 1
                $aColumns[$iIndex] = $vColumnName[$iIndex]
            Next
        EndIf
        Return SetError(0, 0, True)
    #Not an array, so it's a string -> return index of corresponding column
    Else
        For $iIndex = 0 To UBound($aColumns) - 1
            If StringCompare($vColumnName, $aColumns[$iIndex], 0) = 0 Then
                Return SetError(0, 0, $iIndex)
            EndIf
        Next

        Return SetError(1, 0, -1)
    EndIf
EndFunc   ;==>_GetColumnByName

 

[jchd]

Sorry if I'm a bit thick, but why do you need >_GetColumnByName() at all?

You are the one who builds the SQL select queries, so you know which columns you request under which name and you also know their order.

[littlebigman]

Because it's easier to use names than indexes, and it makes it possible to slightly modify the query without changing the code.

[mistersquirrle]

2 hours ago, jchd said:

Sorry if I'm a bit thick, but why do you need >_GetColumnByName() at all?

You are the one who builds the SQL select queries, so you know which columns you request under which name and you also know their order.

You're a bit of a SQL wizard from what I've seen @jchd, it may not make sense to you and I can understand why you wouldn't see a need for it. However I personally find it's easier to remember a column name, rather than what index the column I want is at. Sure, you could scroll up to your query/code and go "Oh right, it's index 4", but I hate doing that. So much so that I've started to use Enums a lot more to name indexes of my arrays in AutoIt.

I professionally use T-SQL, only recently started looking into SQLite for some personal things, and mostly just for use through AutoIt. I still haven't gotten far into it, the furthest project is a click tracker. A couple years ago I made a "heat map" of clicks on the screen, but all the clicks were stored in an array, so lost when the script ended. I started working a new script using SQLite to hold the 'pixel' or click data, but the arrays in AutoIt have their indexes as Enums:

Local Enum $ePressed_Button_LeftMouse, $ePressed_Button_RightMouse, $ePressed_Button_MiddleMouse, _
        $ePressed_Button_Max
Local Enum $ePressed_Info_IsPressed, $ePressed_Info_Code, $ePressed_Info_Name, _
        $ePressed_Info_CoordX, $ePressed_Info_CoordY, $ePressed_Info_Count, $ePressed_Info_Index, $ePressed_Info_DateNow, _
        $ePressed_Info_Max

Local $aPressedArray[$ePressed_Button_Max][$ePressed_Info_Max]

So my point is, it's just a personal preference thing that I can reference either columns of data, or indexes of an array by a name. And, now I get SciTE helping me along with remembering indexes, because as long as I remember "$ePressed_", I can just cycle through all the available enums. Now I don't have to remember that I made the indexes Left (0), Right (1), Middle (2), instead of Left (0), Middle (1), Right (2), which would probably make more sense. Make more sense now for the approach?

 

@littlebigman Yes, you could certainly write it like:

Func _GetColumnByName($vColumnName = '')
    Local Static $aColumns[1]
    If IsArray($vColumnName) Then
        If UBound($vColumnName, 2) > 0 Then
            ReDim $aColumns[UBound($vColumnName, 2)]

            For $iIndex = 0 To UBound($vColumnName, 2) - 1
                $aColumns[$iIndex] = $vColumnName[0][$iIndex]
            Next
        Else ; 1d array, such as from _SQLite_FetchNames
            ReDim $aColumns[UBound($vColumnName)]

            For $iIndex = 0 To UBound($vColumnName) - 1
                $aColumns[$iIndex] = $vColumnName[$iIndex]
            Next
        EndIf
        Return SetError(0, 0, True)
    Else
        For $iIndex = 0 To UBound($aColumns) - 1
            If StringCompare($vColumnName, $aColumns[$iIndex], 0) = 0 Then
                Return SetError(0, 0, $iIndex)
            EndIf
        Next
    EndIf

    Return SetError(1, 0, -1)
EndFunc   ;==>_GetColumnByName

However (and I'm going to sound like jchd here) I don't see a reason why you would need to. Either it is an array, or it isn't, there's no need for the 'Else' condition. It's not a more 'complex' statement. If it's an array, it's returned in that If block. If it's not an array it goes through the loop and if there's a match, returns it. If there's no match, then it returns an error. It's the same behavior with or without the Else, and personally I like (usually) to keep code out of nested/If blocks. I don't want something like:

If $bMeow = True Then
    If $iCount > 3 Then
        If IsArray($aArray) Then
            If UBound($aArray) > 3 Then
                ; Now do something

I'd rather have something like this:

If Not $bMeow Then Return False
If $iCount <= 3 Then Return False
If Not IsArray($aArray) Then Return False
If UBound($aArray) <= Then Return False
; Now do something

The the rest of the code that I'm writing isn't indented 4/5 times. Does it matter? No, not really. It's just personal preference. Feel free to change the code to be in the Else statement if you want, if it makes more sense to you.

[littlebigman]

I didn't write the code, I just asked if there were a way to call columns by names instead of indexes :-p

 

----

Edit: Neat idea

Local Enum $eTitle, $eIntro, $eFull, $eName
ConsoleWrite(StringFormat("First row: %s %s",$aResult[0][$eTitle],$aResult[0][$eName]))

 

Edited February 12, 2023 by littlebigman

[jchd]

3 hours ago, littlebigman said:

I just asked if there were a way to call columns by names instead of indexes

Yes! Just use _SQLite_GetTable2d and you're done. In your SELECT you can give any column any name of your choice.

[littlebigman]

Without any extra code, how am I done accessing columns by their names instead of the index (topic of this whole thread)?

(from help file)
"SQLite_GetTable2D
Passes out a 2Dimensional array containing column names and data of executed query

[…]

; Query
$iRval = _SQLite_GetTable2D(-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
        ; ? ConsoleWrite($aResult[0]["title"])
Else
        MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

 

[jchd]

4 hours ago, littlebigman said:

SELECT * FROM ...

Is best avoided in the general case. By explicitely specifying which columns of the table you're interessed in and in which order you avoid issues when/if the table is refactored by reordering columns (for optimization for instance).

Else if you insist accessing data columns "by name" (more or less), use an Enum.

[littlebigman]

… which was already answered before your reply.

I'm done.

Thank you.

[littlebigman]

Not totally :-p

Are the two following lines needed? Also, am I right in assuming the script also needs the sqlite3.dll?

#include <SQLite.au3>
#include <SQLite.dll.au3> ;needed?

[mistersquirrle]

#include <SQLite.dll.au3> ;needed?

This line probably is not, though it may depend on your setup. The SQLite.au3 include definitely it, since it's what has all the functions for SQLite. Unless you're using a different SQLite UDF for AutoIt.

But from SciTE you can always remove a line and then run SyntaxCheck Prod (Ctrl + F5) and see if there's any errors. There could still be a problem when it's actually run, but it's a quick way to see if an include is really "needed". It also usually doesn't hurt to include a file multiple times or have unneeded files.

[littlebigman]

Thx

--

Edit: Based on the help file, it looks like if "SQLite.dll.au3" is included, the UDF will use sqlite3.exe instead of sqlite3.dll to interact with an SQLite database:

Quote

 

_SQLite_SQLiteExe
Executes commands in sqlite3.exe

[…] Run sqlite3.exe, press enter and type .Help for more info about the SQLite3.exe Commands.

If #include <SQLite.dll.au3> is included the SQLite version is used. Other versions can be found at the SQLite Web site.

 

 

Edited July 11, 2023 by littlebigman