Daka

sqlite results how to get columns

17 posts in this topic

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Hi Daka,

what does

_ArrayDisplay($aResult, "Query Result")

show?

Maby you should youse

_SQLite_GetTable2d (...)

ajag
 

Edited by ajag

Rule #1: Always do a backup         Rule #2: Always do a backup (backup of rule #1)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
_SQLite_Display2DResult($aResult) gives good result,

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

...

 

1 person likes this

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)

Share this post


Link to post
Share on other sites

#16 ·  Posted

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

Share this post


Link to post
Share on other sites

#17 ·  Posted

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
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