sqlite results how to get columns

[Daka]

Ok here I got one query:
 

#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include <Array.au3>
$path_to_sqlite = @ScriptDir & "\sqlite\sqlite3.dll";sqlite3.dll
;ConsoleWrite($path_to_sqlite)
Local $sLocalSQLiteDll = $path_to_sqlite ; to be change to an existing .dll to have no error

Local $sSQliteDll = _SQLite_Startup($sLocalSQLiteDll, False, 1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "'SQLite3.dll' Can't be Loaded!")
    Exit -1
EndIf
$DB = _SQLite_Open('db.sqlite')
Local $aResult, $iRows, $iColumns, $iRval
; Query
$iRval = _SQLite_GetTable(-1, "SELECT * FROM organs;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    ;_ArrayDisplay($aResult, "Query Result")
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
;_SQLite_Display2DResult($aResult)

;Local $Query, $Row
;_SQLite_Query($DB, "Select * from organs", $Query)
;_SQLite_FetchData($Query, $Row)
For $r In $aResult
   ConsoleWrite($r & @LF)
Next
;MsgBox(0, "Info", $Row[0] & " " & $Row[1])
;MsgBox($MB_SYSTEMMODAL, "SQLite3.dll Loaded", $sSQliteDll & " (" & _SQLite_LibVersion() & ")")
;ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Shutdown()

When I export the results I got next:
 

15
id
name
organ_id
x
y
1
PAN
0
0
0
2
ULTRAS
1
129
662

first I get the some id of 15? then I get the name of my columns like id, name, organ_id, x, y value and then I get the results.
Is there a way to loop the results like

 

For $r In $aResult
   ConsoleWrite($r->id & @LF)
   ConsoleWrite($r->name & @LF)
   ConsoleWrite($r->x & @LF)
   ConsoleWrite($r->y & @LF)
Next

I understood that in AutoIt there is no object, but I find very difficult to get those results like:

15
id
name
organ_id
x
y
1
PAN
0
0
0
2
ULTRA
1
129
662

And then to know that first 6 results are the name of the columns
and then to know now I have results, but to also know what name are used by which result.

So is there better way to read result from database?

[ajag]

Hi Daka,

what does

_ArrayDisplay($aResult, "Query Result")

show?

Maby you should youse

_SQLite_GetTable2d (...)

ajag
 

Edited September 29, 2017 by ajag

[Daka]

Row|Col 0
[0]|15
[1]|id
[2]|name
[3]|organ_id
[4]|x
[5]|y
[6]|1
[7]|PAN
[8]|0
[9]|0
[10]|0
[11]|2
[12]|ULTRA
[13]|1
[14]|129
[15]|662

I was thinking to do next:

$count = 0
For $r In $aResult
   $count += 1
   ConsoleWrite($count & @LF)
   If $count > 6 Then
      ConsoleWrite($r & @LF)
   EndIf
Next

So I know the number of columns and then I can escape them,
but this does not solve the problem of later on to know exactly what is the
id, name, organ_id, x, y

[Daka]

to use _SQLite_GetTable2d

How do you use For loop to get some results? like:
 

For $r In $aResult
   ConsoleWrite($r->id & @LF)
   ConsoleWrite($r->name & @LF)
   ConsoleWrite($r->x & @LF)
   ConsoleWrite($r->y & @LF)
Next

_SQLite_GetTable2d

[SlackerAl]

Check out https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Query.htm

There is a loop example here (you can just select * to get all)

[Daka]

8 minutes ago, SlackerAl said:

Check out https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Query.htm

There is a loop example here (you can just select * to get all)

Still don't solve my problem to be able to loop it and get proper data.

[Daka]

_SQLite_Display2DResult($aResult) gives good result,

but how to go in for loop or while loop to get the data?

[Daka]

with _SQLite_GetTable2d I get 

_ArrayDisplay($aResult, "Query Result")

Row|Col 0|Col 1|Col 2|Col 3|Col 4
[0]|id|name|organ_id|x|y
[1]|1|PAN|0|0|0
[2]|2|ULTRA|1|129|662

but how to get results in loop?

[SlackerAl]

Have you looked at 

It seems to add the functionality you wanted with a _col reference. I'm pretty sure I made this work in the past without the UDF, but I can't get to my data at the moment...

[Daka]

SQLite_easy did work on some level, what I wanted:
 

sqlite_query ("SELECT * FROM organs;")

    while sqlite_read() = $SQLITE_OK
        ;_ArrayDisplay($sqlite_data_query_rows, "SQLite_Read Result")
        $count = 0
        For $r In $sqlite_data_query_rows
            $count += 1
            ;ConsoleWrite("Count is: " & $count & @LF)
            If $count == 1 Then
               ConsoleWrite("id: " & $r & @LF)
            ElseIf $count == 2 Then
               ConsoleWrite("name: " & $r & @LF)
            ElseIf $count == 3 Then
               ConsoleWrite("organ_id: " & $r & @LF)
            ElseIf $count == 4 Then
               ConsoleWrite("x: " & $r & @LF)
            ElseIf $count == 5 Then
               ConsoleWrite("y: " & $r & @LF)
            EndIf
         Next
        ; The data is provided in an array and you can access the fields in your processing
        ; You can uncomment the line below to see this
        ; msgbox (0,"Demo","The last person processed was " & $sqlite_data_query_rows[0] & " " & $sqlite_data_query_rows[1])
    wend

still I can't find the way to say like $r->name, $r->id etc or $r['name'], $r['id']

Above I did it with $count, because I know what result is what number.

[Daka]

or like this without count:

ConsoleWrite("id:" & $sqlite_data_query_rows[0] & @LF)
        ConsoleWrite("name:" & $sqlite_data_query_rows[1] & @LF)
        ConsoleWrite("organ_id:" & $sqlite_data_query_rows[2] & @LF)
        ConsoleWrite("x:" & $sqlite_data_query_rows[3] & @LF)
        ConsoleWrite("y:" & $sqlite_data_query_rows[4] & @LF)

 

[kylomas]

Daka,

Try something like this (not tested)...

#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include <Array.au3>
$path_to_sqlite = @ScriptDir & "\sqlite\sqlite3.dll" ;sqlite3.dll
;ConsoleWrite($path_to_sqlite)
Local $sLocalSQLiteDll = $path_to_sqlite ; to be change to an existing .dll to have no error

Local $sSQliteDll = _SQLite_Startup($sLocalSQLiteDll, False, 1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "'SQLite3.dll' Can't be Loaded!")
    Exit -1
EndIf
$DB = _SQLite_Open('db.sqlite')
Local $aResult, $iRows, $iColumns, $iRval
; Query
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM organs;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    ;_ArrayDisplay($aResult, "Query Result")
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
;_SQLite_Display2DResult($aResult)

;Local $Query, $Row
;_SQLite_Query($DB, "Select * from organs", $Query)
;_SQLite_FetchData($Query, $Row)
For $r In $aResult
    For $c = 0 To UBound($aResult, 2) - 1       ; loop through columns
        ConsoleWrite($aResult[$r][$c] & @LF)
    Next
Next
;MsgBox(0, "Info", $Row[0] & " " & $Row[1])
;MsgBox($MB_SYSTEMMODAL, "SQLite3.dll Loaded", $sSQliteDll & " (" & _SQLite_LibVersion() & ")")
;ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Shutdown()

kylomas

[Daka]

14 hours ago, kylomas said:

Daka,

Try something like this (not tested)...

#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include <Array.au3>
$path_to_sqlite = @ScriptDir & "\sqlite\sqlite3.dll" ;sqlite3.dll
;ConsoleWrite($path_to_sqlite)
Local $sLocalSQLiteDll = $path_to_sqlite ; to be change to an existing .dll to have no error

Local $sSQliteDll = _SQLite_Startup($sLocalSQLiteDll, False, 1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "'SQLite3.dll' Can't be Loaded!")
    Exit -1
EndIf
$DB = _SQLite_Open('db.sqlite')
Local $aResult, $iRows, $iColumns, $iRval
; Query
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM organs;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    ;_ArrayDisplay($aResult, "Query Result")
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
;_SQLite_Display2DResult($aResult)

;Local $Query, $Row
;_SQLite_Query($DB, "Select * from organs", $Query)
;_SQLite_FetchData($Query, $Row)
For $r In $aResult
    For $c = 0 To UBound($aResult, 2) - 1       ; loop through columns
        ConsoleWrite($aResult[$r][$c] & @LF)
    Next
Next
;MsgBox(0, "Info", $Row[0] & " " & $Row[1])
;MsgBox($MB_SYSTEMMODAL, "SQLite3.dll Loaded", $sSQliteDll & " (" & _SQLite_LibVersion() & ")")
;ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Shutdown()

kylomas

It didn't work  but I will go with above code it works well..... Maybe later if there is some like PHP/MySQL handels to work with data, I will switch

[Daka]

Ok I still dont give up on this one to get it like in PHP

 

$returnArray = []

sqlite_query ("SELECT * FROM organs;")
   $returnArray = []
   $count = 0
   while sqlite_read() = $SQLITE_OK
      ;_ArrayDisplay($sqlite_data_query_rows, "SQLite_Read Result")
      $returnArray[$count]['id'] = $sqlite_data_query_rows[0]
      $returnArray[$count]['name'] = $sqlite_data_query_rows[1]
      $returnArray[$count]['organ_id'] = $sqlite_data_query_rows[2]
      $returnArray[$count]['x'] = $sqlite_data_query_rows[3]
      $returnArray[$count]['y'] = $sqlite_data_query_rows[4]
      $count += 1
      ;ConsoleWrite("id:" & $sqlite_data_query_rows[0] & @LF)
      ;ConsoleWrite("name:" & $sqlite_data_query_rows[1] & @LF)
      ;ConsoleWrite("organ_id:" & $sqlite_data_query_rows[2] & @LF)
      ;ConsoleWrite("x:" & $sqlite_data_query_rows[3] & @LF)
      ;ConsoleWrite("y:" & $sqlite_data_query_rows[4] & @LF)
   wend
   Return $returnArray

First error is that $returnArray = [] can NOT be done.

Second error it gives me next:
 

==> Expected a "=" operator in assignment statement.:
$returnArray[$count]['id'] = $sqlite_data_query_rows[0]
$returnArray^ ERROR

So to get return value like in PHP or Ruby it is kind of difficult
I think I really need to study arrays in AutoIt to get this working,
any help on above would be nice and I'm already going to study arrays of AutoIt

[jchd]

Using _SQLite_GetTable2d you do obtain a 2D array as result, which you can massage, dissect, display, whatever process at will.

Can you explain what you want to do with the result?

Note that AutoIt doesn't offer 2D associative arrays. However you can achieve a similar-looking coding style by simply using basic AutoIt features:

Enum $_ID, $_name, $_organ_ID, $_x, $_y
; now $_ID = 0, $_name = 1, $_organ_ID = 2, $_x = 3, $_y = 4

...

Local $aRows, $iRows, $iCols
_SQLite_GetTable2d($hDB, "select * from organs", $aRows, $iRows, $iCols)
; here check that result isn't empty
If Not @error Then
    For $i = 0 to UBound($aRows) - 1
        ConsoleWrite("Row " & $i & " ID = " & $aRows[$i][$_ID] & @LF)
        ConsoleWrite("Row " & $i & " name = " & $aRows[$i][$_name] & @LF)
        ConsoleWrite("Row " & $i & " organ_ID = " & $aRows[$i][$_organ_ID] & @LF)
        ConsoleWrite("Row " & $i & " x = " & $aRows[$i][$_x] & @LF)
        ConsoleWrite("Row " & $i & " y = " & $aRows[$i][$_y] & @LF)
    Next
EndIf

...

 

[Daka]

Ok now why it doesnt give me results?

Local $aRows, $iRows, $iCols
    $name  = "bob"
   $query = "SELECT id FROM organs WHERE name = '" & $name & "';"
   ConsoleWrite("Query: " & $query & @LF)
   _SQLite_GetTable2d($db, $query, $aRows, $iRows, $iCols)
   ;disconnectDB()
   ConsoleWrite("Total aRows:" & UBound($aRows) & @LF)
   ConsoleWrite("Total iRows:" & UBound($iRows) & @LF)
   ConsoleWrite("Total iCols:" & UBound($iCols) & @LF)

it returns all 0!

Total aRows:0
Total iRows:0
Total iCols:0

And if I run in my firefox sql manager it returns me good results

[BrewManNH]

I believe that $iRows and $iCols aren't arrays, so that's not unexpected. Try using _SQLite_Display2DResult after the query to see if you have anything in the $aRows array, and check for errors from the GetTable2D function to see why you're not getting what you're expecting.