FrancescoDiMuro

Read from 2 SQLite tables...

6 posts in this topic

#1 ·  Posted (edited)

Hi guys! How are you? Hope you're fine :)
I'm trying to use SQLite for managing some data, and, I would like to display my "retrieved" data, but I'm trying to do a select from 2 table, and seems to not work properly...
What I'd like to do is retrieve data from 2 tables and display in a listview...

I tried with:

Local $aRisultato, $iRighe, $iColonne, $iRVal
    $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell, Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne)
    If $iRVal = $SQLITE_OK Then
    ;_SQLite_Display2DResult($aRisultato)
    _ArrayDisplay($aRisultato)
    EndIf

... but it displays a single record 41 times, and it does this thing for every record in the database...
What do you need to help me? :)
Thanks :D 

EDIT:
Managed with a double If and double query like this:
 

$iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell;", $aRisultato, $iRighe, $iColonne)
    If $iRVal = $SQLITE_OK Then
        Local $aRisultatoRockwell = $aRisultato
        $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne)
        If $iRVal = $SQLITE_OK Then
            _ArrayConcatenate($aRisultato, $aRisultatoRockwell)
            Local $i, $sRiga, $s_LV_Item
            For $i = 1 To Ubound($aRisultato) - 1
                $sRiga = $aRisultato[$i][0] & "|" & _
                         $aRisultato[$i][1] & "|" & _
                         $aRisultato[$i][2] & "|" & _
                         $aRisultato[$i][3] & "|" & _
                         $aRisultato[$i][4] & "|" & _
                         $aRisultato[$i][5] & "|" & _
                         $aRisultato[$i][6] & "|" & _
                         $aRisultato[$i][7] & "|" & _
                         $aRisultato[$i][8] & "|" & _
                         $aRisultato[$i][9] & "|" & _
                         $aRisultato[$i][10] & "|" & _
                         $aRisultato[$i][11]
                $s_LV_Item = GUICtrlCreateListViewItem($sRiga, $lv_Lista)
            Next

And so, the listview is created :)
If anyone has another more efficient way, I'm here :) Thanks guys :D 
 

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

Hey guys!
Can someone please explain me how can I retrieve only 1 field from a table?
I.E.: I would like to retrieve the amount of a certain product... How could I set the query, in order to retrieve only the amount of that product, as a single string, and not an array... How can I do it? :) 
Thanks :D 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

Answer to 1rst post: what is the relationship between the 2 tables? You're asking for the cross product of the two tables (= sets) and that is exactly what the SQL engine yields. Maybe you want to list all rows from both tables. If so, use:

select * from table1
union all
select * from table2

Note: I suspect that if both tables have the same columns and semantics, they should be merged and a column added to discriminate between Rockwell or Siemens.

2nd post: select MYCOLUMN from ...

Note that select * from ... is 99.99% of the time a very bad idea.

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

Good morning @jchd:)
For the first question, I answer Yes.
I want to see all the data contained in the two tables...
For the second post... What should I use? I managed with, but If you have any other idea, please tell me.

_SQLite_QuerySingleRow()

 Thanks :) 

 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

It's OK as far as the row you want to get is acually the first one in the resultset returned by your query.


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

Yeah, because the result, in case of "Product found", it's always composed from 2 fields... :) 
Thanks for your time and have a good day! :D 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

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

  • Similar Content

    • Anas
      By Anas
      Hi,

      Is it possible to set a different text direction/layout (not alignment) for a Listview column?
       
      Thanks.
    • TheWizEd
      By TheWizEd
      How do I work with 2D arrays.  I've tried this but get errors.
      Local $aTest[4][4] = [[1,2,3,4],[5,6,7,8],[9,10,11,12],[13,14,15,16]]
      ;$aTest[0][] = [10,11,12]  ; Error at []
      Local $sTest = ""
      For $i = 0 To UBound($aTest)-1
        Local $aExtract = _ArrayExtract($aTest,$i,$i)
        $sTest = $sTest & MyTest($aExtract)
      Next
      Func MyTest($aTemp)
        _ArrayDisplay($aTemp)
        ; Error at    v $aTemp
        Return String($aTemp[0]) & " - " & String($aTemp[1]) & " - " & String($aTemp[2]) & @CRLF
      EndFunc
       
       
    • AdamUL
      By AdamUL
      I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples.  The examples that I have tested are throwing errors or not doing anything.  I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script.  I am copying the examples directly from the help file into a test script for testing with no edits.  The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command.  The _SQLite_FastEncode example returns an empty dialog box.  The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console.  Currently, I'm still searching for what is causing this issue.  I'm on Windows 7 Enterprise 64-bit.  Is anyone else having this issue?  
       
      Adam
       
    • Jibsbrown
      By Jibsbrown
      So trying to get an INI section in a listView with checkboxes. Was able to get the the section values to show until i add the checkboxes and now only the keys are show up. Between reading the Help file, Forum Topic and trying other code option. I have been unable to answer this problem. I'm assuming its something very basic I'm overlooking since this it's day 21 of learning Autoit.
      If someone could please advise I would appreciate the assistance.
       
      Here's the code: 
      #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> #include <GUIListView.au3> #include <Array.au3> #include <File.au3> #include <ListViewConstants.au3> Global $sIniPath = "installLog.ini" ;ConsoleWrite($sIniPath) $iniSctionNames = IniReadSectionNames($sIniPath) ;_ArrayDisplay($iniSctionNames, "$iniSctionNames") $keys2 = IniReadSection($sIniPath, $iniSctionNames[2]) _ArrayDisplay($keys2 , "$keys2") ; Create GUI $hGUI = GUICreate("Test", 300, 250) ; Create ListView $cLV = GUICtrlCreateListView("", 10, 10, 200, 200,$LVS_List,$LVS_EX_CHECKBOXES) _GUICtrlListView_AddColumn($cLV, $keys2, 100) _GUICtrlListView_AddColumn($cLV, $keys2, 100) ;_GUICtrlListView_AddItem($cLV, $keys2, 0) _GUICtrlListView_AddArray($cLV, $keys2) GUISetState() While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit EndSwitch WEnd And the INI file I'm using. 
      [OldSysInfo] 4=192.168.0.4|DESKTOP-RDIU2SN|R90M05Q8 5=192.168.0.5|SD0123456789101|R9WGP9P 6=192.168.0.6|SD0123456789102|R9WGP9PT 3=192.168.0.3|DESKTOP-3RS4LKL|R9WGP9P 23=192.168.0.23|SD0123456789102|MXL1234P5I [PrinterIp] 50=192.168.0.50 48=192.168.0.48 47=192.168.0.47 [NewSysInfo] newPC = SD0123456789adfs|192.168.0.185|2UA1234FTR Also if possible could you explain how to hide the Array count row as well.
    • buymeapc
      By buymeapc
      Hi all,
      I'm trying to create a listview with subitem icons and I achieved this by simply rearranging the order of the columns, so that the first column is in the middle, thus appearing as a subitem with an icon - this method prevents the "icon clipping" that happens when using subitem icons in a listview. My issue is that when I select a row that does not have an icon, the space where the icon would be is white. Is there a way to make this transparent, so it looks like the entire row is selected?
      Here's an example - click the second listview item/row to see a blank space where the icon would be.
      #include <GuiConstantsEx.au3> #include <GuiListView.au3> #include <GuiImageList.au3> #include <WindowsConstants.au3> _Main() Func _Main() Local $hGui, $listview, $hImage Local $iLV_EXStyle_NoChecks = BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES, $LVS_EX_DOUBLEBUFFER, $LVS_EX_HEADERDRAGDROP) $hGui = GUICreate("Listview Test With Icons", 400, 300) $listview = GUICtrlCreateListView("Col 1|Col 2|Col 3", 2, 2, 394, 268, BitOR($LVS_REPORT, $LVS_SHAREIMAGELISTS)) _GUICtrlListView_SetExtendedListViewStyle($listview, $iLV_EXStyle_NoChecks) GUISetState() ; Create the image list $hImage = _GUIImageList_Create(16, 16, 5, 3) _GUIImageList_AddIcon($hImage, @SystemDir & "\shell32.dll", 165) _GUICtrlListView_SetImageList($listview, $hImage, 1) ; Set the order so the first column is moved to the middle, ; acting like a subitem with an icon _GUICtrlListView_SetColumnOrder($listview, "1|0|2") ; Add the items GUICtrlCreateListViewItem("Item1|Item2|Item3", $listview) GUICtrlCreateListViewItem("Item4|Item5|Item6", $listview) ; Set an icon for just the first item _GUICtrlListView_SetItemImage($listview, 0, 0) Do Until GUIGetMsg() = $GUI_EVENT_CLOSE GUIDelete() EndFunc ;==>_Main