Jump to content

sqlite array columns


Recommended Posts

Hi, im working on a small application that opens an sqlite3 database and passes out some querys like "SELECT phone_number,call_duration FROM call_log"

but when i do an _ArrayDisplay it puts both select results in the same column like

Row| Col 0

[1] | phone_number

[2] | call_duration

[3] | 0661990700

[4] | 54

[5] | 0955877402

[6] | 0

and id like it to show as

Row | Col 0 | Col 1

[1] | phone_number | call_duration

[2] | 0661990700 | 54

[3] | 0955877402 | 0

i wonder if it"s possible, and if so how would i get to this result? ;)

Here's my current code:

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

$message = "Choisi ton fichier de logs."
$var = FileOpenDialog($message, @DesktopCommonDir & "\", "Sqlite3 (*.db)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
Else
    $var = StringReplace($var, "|", @CRLF)
EndIf

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
_SQLite_Open ($var); Open a :memory: database
If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

; Query

$iRval = _SQLite_GetTable (-1, "SELECT phone_number,call_duration FROM call_log", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
  _ArrayDisplay($aResult, "Query Result")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf
_SQLite_Close ()
_SQLite_Shutdown ()

PS: After that i'd like to do some maths and add up all the call duration but thats not for now :)

Edited by meekaah
Link to comment
Share on other sites

Have you tried _SQLite_GetTable2d()

Surely there's a transpose function in the Array UDF???

a quick search showed a transpose flag in _ArrayDisplay()

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Link to comment
Share on other sites

Have you tried _SQLite_GetTable2d()

well the _SQLite_GetTable2d() did it, well actually i cant get it to show up on screen like an _ArrayDisplay does,

but in SciTE4Autoit3 i can see it perfectly in the output tab

phone_number call_duration

0661990700-----54

0955877402-----0

**1----------------0

123----------------0

would there be any way to show these results in a listview like an _ArrayDisplay does

so i could do some maths with it

Edited by meekaah
Link to comment
Share on other sites

which version of autoit?

As an example this works fine for me, is it that you don't want to actually arraydisplay it but in actual fact you want to put it in your own listview?

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

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
_SQLite_Open (); Open a :memory: database
If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

 _SQLite_Exec(-1, "CREATE TABLE call_log (ID INTEGER PRIMARY KEY,phone_number,call_duration);")
 _SQLite_Exec(-1,'INSERT INTO call_log (phone_number,call_duration) VALUES ("012345678910","1.32");')
_SQLite_Exec(-1,'INSERT INTO call_log (phone_number,call_duration) VALUES ("01234555123","2.12");')
; Query

$iRval = _SQLite_GetTable2d (-1, "SELECT phone_number,call_duration FROM call_log", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
  _ArrayDisplay($aResult, "Query Result")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf
_SQLite_Close ()
_SQLite_Shutdown ()
Link to comment
Share on other sites

a crude example of putting data in to your own listview

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


$gui = GuiCreate("MyGUI", 633, 252,-1, -1 , BitOR($WS_OVERLAPPEDWINDOW, $WS_CLIPSIBLINGS))
$listview = GUICtrlCreateListView("phone_number|call_duration",10,10,600,200)

GuiSetState()


Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
_SQLite_Open (); Open a :memory: database
If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

 _SQLite_Exec(-1, "CREATE TABLE call_log (ID INTEGER PRIMARY KEY,phone_number,call_duration);")
 _SQLite_Exec(-1,'INSERT INTO call_log (phone_number,call_duration) VALUES ("012345678910","1.32");')
_SQLite_Exec(-1,'INSERT INTO call_log (phone_number,call_duration) VALUES ("01234555123","2.12");')
; Query

sql("SELECT phone_number,call_duration FROM call_log")

While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    Case Else
;;;
    EndSelect
WEnd

_SQLite_Close ()
_SQLite_Shutdown ()

Func SQL($command, $setData = 1)

    Local $hQuery, $aRow, $msg
    _SQlite_Query (-1, $command, $hQuery)

    While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
        
        For $i = 0 to Ubound($aRow) - 1
            $msg &= $aRow[$i] & "|"
        Next
        
        If StringRight ($msg,1) = "|" then $msg = StringTrimRight($msg,1)
                
        If $setData = 1 then 
            _GUICtrlListViewInsertItem ( $listview, -1, $msg)
        EndIf
        
        $msg = ""
    WEnd
    _SQLite_QueryFinalize($hQuery)

EndFunc

Edit: took out some crap you don't need

Edited by ChrisL
Link to comment
Share on other sites

a crude example of putting data in to your own listview

#include <guiConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>
$gui = GuiCreate("MyGUI", 633, 252,-1, -1 , BitOR($WS_OVERLAPPEDWINDOW, $WS_CLIPSIBLINGS))
$listview = GUICtrlCreateListView("phone_number|call_duration",10,10,600,200)

GuiSetState()
Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
_SQLite_Open (); Open a :memory: database
If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

 _SQLite_Exec(-1, "CREATE TABLE call_log (ID INTEGER PRIMARY KEY,phone_number,call_duration);")
 _SQLite_Exec(-1,'INSERT INTO call_log (phone_number,call_duration) VALUES ("012345678910","1.32");')
_SQLite_Exec(-1,'INSERT INTO call_log (phone_number,call_duration) VALUES ("01234555123","2.12");')
; Query

sql("SELECT phone_number,call_duration FROM call_log")

While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    Case Else
;;;
    EndSelect
WEnd

_SQLite_Close ()
_SQLite_Shutdown ()

Func SQL($command, $setData = 1)

    Local $hQuery, $aRow, $msg
    _SQlite_Query (-1, $command, $hQuery)

    While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
        
        For $i = 0 to Ubound($aRow) - 1
            $msg &= $aRow[$i] & "|"
        Next
        
        If StringRight ($msg,1) = "|" then $msg = StringTrimRight($msg,1)
                
        If $setData = 1 then 
            _GUICtrlListViewInsertItem ( $listview, -1, $msg)
        EndIf
        
        $msg = ""
    WEnd
    _SQLite_QueryFinalize($hQuery)

EndFunc

Edit: took out some crap you don't need

thanks ill try that :)
Link to comment
Share on other sites

thanks ill try that :)

Allright, so i tested your example and it doesnt work at all...well at least I didnt get it working. anyways i got pretty much what i wanted now but not in a listview, il got 2D arrays id like to put in a listview so i could so some math like to sum up the call_duration

...well if its possible to do that without going through a listvew i dont mind at all ;)

Link to comment
Share on other sites

Allright, so i tested your example and it doesnt work at all...well at least I didnt get it working. anyways i got pretty much what i wanted now but not in a listview, il got 2D arrays id like to put in a listview so i could so some math like to sum up the call_duration

...well if its possible to do that without going through a listvew i dont mind at all :)

What version of autoit are you using?

Link to comment
Share on other sites

the latest ...

If my exmple above did not work at all, which it does on mine, is it that the sqlite3.dll in the system32 folder is an older one from a previous version of Autoit. The sqlite.dll.au3 file as far as I'm aware doesn't overwrite when there is a newer version unless you delete the old one

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...