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

    • mLipok
      By mLipok
      I was not lucky to find the right function so I created it by my self:
      _GUICtrlListView_GetColumnID_ByName()
      Example:
       
      #include <GUIConstantsEx.au3> #include <GuiListView.au3> #include <MsgBoxConstants.au3> Example() Func Example() GUICreate("ListView Get Column ID", 400, 300) Local $idListview = GUICtrlCreateListView("Alfa|Beta|Gama", 2, 2, 394, 268) GUISetState(@SW_SHOW) _GUICtrlListView_SetColumnOrder($idListview, "2|0|1") MsgBox($MB_SYSTEMMODAL, "Information", "Column ID: " & _GUICtrlListView_GetColumnID_ByName($idListview, 'Gama')) ; Loop until the user exits. Do Until GUIGetMsg() = $GUI_EVENT_CLOSE GUIDelete() EndFunc ;==>Example Func _GUICtrlListView_GetColumnID_ByName($hListView, $sColumnToFind) Local $iColumn_Count = _GUICtrlListView_GetColumnCount($hListView) Local $aColumns For $iColumn_idx = 0 To $iColumn_Count - 1 $aColumns = _GUICtrlListView_GetColumn($hListView, $iColumn_idx) If $aColumns[5] = $sColumnToFind Then Return $iColumn_idx EndIf Next Return SetError(1, 0, -1) EndFunc ;==>_GUICtrlListView_GetColumnID_ByName  
    • UEZ
      By UEZ
      Here a small tool I wrote to update my Sysinternal tools collection without the need to download always the whole package or visiting the site to check for updates. I know that there are several tools available (also some tools written in AutoIt) but here another one for the collection. It was good exercise for me to code it.
       
       
        
       
       
      Some files from cannot be downloaded although they are visible on the web site!
       
      Here the download link of the source code only: AutoIt Sysinternal Tools Synchronizer v0.99.1 build 2017-10-23 beta.7z  (1353 downloads previously)
      -=> Requires AutoIt version 3.3.13.20 or higher / tested on Win8.1 real machine and some VMs: Win7 / Vista / Win10
       
      Compiled exe only: @MediaFire
       
      Just select the Sysinternal Tools folder or create one and press the synchronize button to download the selected items. Click on AutoIt label (near to left upper corner) to open menu.
       
      Special thanks to LarsJ, Melba23 and mesale0077 for their help. 
       
      I've still some ideas to implement which are more gimmick related, so it is not finished yet...
      If you want to add your language please check out #Region Language. Thanks. 
       
      Please report any bug or if you have any suggestions.
       
      The language of the tool tip from each of the executable in the left list view were automatically created using Google translator and weren't checked for correctness.
       
      Br,
      UEZ
    • 0Ethan0
      By 0Ethan0
      Ahoy Autoit Community!
      After many trials and errors I am unable to solve a problem I am facing and would appreciate any kind of input or better yet a solution 
      The Premise: An embeded slideshow viewer that runs after double-clicking an item in a ListView (each item will generate a different slideshow images).
      The Setup: GUI with a ListView Control and a simple exit button.
      The Issue: Once double clicked the slide plays however the GUI "locks"/non responsive until the slide is over. Same thing if I click on the "Test" button.
      The Culprit: I believe since it's in the images loop it can't accept any other commands until that loop is over.
      The Wish: I want to be able to use the GUI functions (selecting other items, clicking on button etc.) while the slideshow plays.
      The Code (stripped and simplified as much as I could):
      #include <GuiListView.au3> #include <File.au3> #include <GDIPlus.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> Opt("GUIOnEventMode", 1) HotKeySet("{Esc}", "_Exit") Global $c=0 Global $ssGDI[3], $ssGraphic[2], $ssImage Global Const $bg_color = "000000" Global Const $ssW = 480, $ssH = 320 Global $aFiles = _FileListToArrayRec("d:\testStage\", "*.jpg;*.png;*.bmp;*.gif;*.JPG;*.PNG;*.BMP;*.GIF", $FLTAR_FILES, $FLTAR_NORECUR ,$FLTAR_SORT ,$FLTAR_FULLPATH ) $guiW = 1200 $guiH = 726 $mainWindow = GUICreate("Slideshow Viewer", $guiW, $guiH, -1, -1, $WS_POPUP) $Button1 = GUICtrlCreateButton("Exit", 0, 0, 50, 50) GUICtrlSetOnEvent($Button1, "_Exit") $Button1 = GUICtrlCreateButton("Test", 60, 0, 50, 50) GUICtrlSetOnEvent($Button1, "Test") Global $ListView = GUICtrlCreateListView("Entry Name|Category", 5, 75, 195, 280) _GUICtrlListView_SetColumnWidth ($ListView, 0, 100) _GUICtrlListView_SetColumnWidth ($ListView, 1, 100) GUICtrlSendMsg($ListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_GRIDLINES, $LVS_EX_GRIDLINES) GUICtrlSendMsg($ListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_FULLROWSELECT, $LVS_EX_FULLROWSELECT) GUICtrlCreateListViewItem("Name 1|Category 1", $ListView) GUICtrlCreateListViewItem("Name 2|Category 2", $ListView) screenshotWidgetInit($ssW,$ssH, 690, 100) GUISetState(@SW_SHOW, $mainWindow) GUIRegisterMsg($WM_NOTIFY, "WM_Notify_Events") While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE _Exit() EndSwitch WEnd Func Test() For $k = 1 To UBound($aFiles) - 1 screenshotWidgetTransition($aFiles[$k]) Next EndFunc Func ListView_Click() ConsoleWrite("Left Click") EndFunc Func ListView_DoubleClick() ConsoleWrite("Double Left Click") Test() EndFunc Func WM_Notify_Events($hWndGUI, $MsgID, $wParam, $lParam) #forceref $hWndGUI, $MsgID, $wParam Local $tagNMHDR, $event, $hwndFrom, $code $tagNMHDR = DllStructCreate("int;int;int", $lParam) If @error Then Return $event = DllStructGetData($tagNMHDR, 3) Select Case $wParam = $ListView Select Case $event = $NM_CLICK ListView_Click () Case $event = $NM_DBLCLK ListView_DoubleClick () EndSelect EndSelect Return $GUI_RUNDEFMSG EndFunc Func screenshotWidgetTransition($image, $delay = 0, $speed = 1, $sleep = 2000) Local $a, $d = $c, $iX, $iY $ssImage = _GDIPlus_ImageLoadFromFile($image) $iX = _GDIPlus_ImageGetWidth($ssImage) $iY = _GDIPlus_ImageGetHeight($ssImage) $FDesktop=$ssH/$ssW $Fact =1 If $iX > $ssW And $FDesktop > ($iY/$iX) Then $Fact=$ssW/$iX ElseIf $iY > $ssH Then $Fact=$ssH/$iY EndIf $H1 = Round(($Fact * $iY),0) $W1 = Round(($Fact * $iX),0) _GDIPlus_GraphicsDrawImageRect($ssGraphic[$d], $ssImage,($ssW - $W1)/2, ($ssH - $H1) / 2,$W1,$H1) WinSetTrans($ssGDI[$d], "", 0) WinSetOnTop($ssGDI[$d], "", 1) For $a = 0 To 254 Step $speed WinSetTrans($ssGDI[$d], "", $a) Sleep($delay) Next WinSetTrans($ssGDI[$d], "", 254) WinSetOnTop($ssGDI[Not ($d)], "", 0) WinSetTrans($ssGDI[Not ($d)], "", 0) _GDIPlus_GraphicsClear($ssGraphic[Not ($d)]) $c = 1 - $d _GDIPlus_ImageDispose ($ssImage) ; very important to realease the pics Sleep($sleep) EndFunc ;==>screenshotWidgetTransition Func screenshotWidgetInit($ssW,$ssH,$ssX,$ssY) $ssGDI[2] = GUICreate("", $ssW, $ssH, $ssX, $ssY, $WS_POPUP, $WS_EX_MDICHILD, $mainWindow) $ssGDI[0] = GUICreate("", $ssW, $ssH, 3, 3, $WS_POPUP, $WS_EX_MDICHILD, $ssGDI[2]) $ssGDI[1] = GUICreate("", $ssW, $ssH, 3, 3, $WS_POPUP, $WS_EX_MDICHILD, $ssGDI[2]) ; GUISetBkColor("0x" & $bg_color, $ssGDI[2]) GUISetState(@SW_SHOW, $ssGDI[2]) GUISetState(@SW_SHOW, $ssGDI[0]) GUISetState(@SW_SHOW, $ssGDI[1]) WinSetTrans($ssGDI[0], "", 0) WinSetTrans($ssGDI[1], "", 0) _GDIPlus_Startup() $ssGraphic[0] = _GDIPlus_GraphicsCreateFromHWND($ssGDI[0]) $ssGraphic[1] = _GDIPlus_GraphicsCreateFromHWND($ssGDI[1]) _GDIPlus_GraphicsClear($ssGraphic[0], "0xFF" & $bg_color) _GDIPlus_GraphicsClear($ssGraphic[1], "0xFF" & $bg_color) EndFunc ;==>screenshotWidgetInit Func _Exit() _GDIPlus_ImageDispose($ssImage) _GDIPlus_GraphicsDispose($ssGraphic[0]) _GDIPlus_GraphicsDispose($ssGraphic[1]) GUIDelete($ssGDI[0]) GUIDelete($ssGDI[1]) GUIDelete($ssGDI[2]) _GDIPlus_Shutdown() Exit EndFunc ;==>_Exit I hope someone can shed light on this; perhaps a different approach is needed?
      Thank you in advance!
      P.S.
      The script is patched from different scripts of different users in the forum - thank you again users!
    • Eli_jahbot
      By Eli_jahbot
      My esteemed Autoits I need your help once again.
      I'm trying to figure out how to create a loop that gets 1 value from an array and repeats until each value from the array has been used. I have never used arrays before and I know once I learn more things should get easier for me. 
      Here is what Im trying to do:
      -Have an array of values that determine what application i log into. ex: app1, app2, app3, app4 etc.
      -Have a loop that repeats a process sequentially using each value in the array to finish the process for each app1, app2, app 3 and so forth. I have 30 apps that I need to update on a regular basis and getting this sorted out is what I perceive to be the best way to do it.
      Here is my feeble attempt that obviously fails:
      #include <msgboxconstants.au3>
      #include <Constants.au3>
      #include <array.au3>
      Login()
      Func Login()
          local $array[30] = ["10", "11", "12",etc etc]
          ;;Local $site = InputBox("ERx Site","What site do you want to login as?","","")
          Local $userid = InputBox("ERx Login", "What is your username?", "", "")
          Local $Passwd = InputBox("Security Check", "Enter your UAT password.", "", "*")
      for $1 = 1 to 30(I need to do the same steps in 30 different apps)
      run("Z:launch.exe")
      WinWaitActive("Input")
      Send (Sequential ARRAY VALUE HERE)
      Send("{ENTER}")
      WinWaitActive("window")
      Send($userid)
      Send("{TAB}")
      send($Passwd)
      Send("{ENTER}")
      WinWaitActive("[CLASS:SunAwtDialog]")
      Sleep(500)
      WinClose("Home Page")
      Next
      EndFunc
       
      your help is greatly appreciated.
      Thanks for your time
    • Daka
      By Daka
      I know there are listbox and listview, but now I have?
      here is the video of infowindow 
      I tried to read mouse selection like for listbox:
      $listWindow = "[CLASS:TFormResearchGR]" $topicsList = "[CLASS:TMWrapper; INSTANCE:255]" WinWait($listWindow, "", 60) $hdlWindow = WinGetHandle($listWindow) $hdlList = ControlGetHandle($hdlWindow, "", $topicsList) _GUICtrlListBox_ClickItem($hdlList, 0, "left") $countOfList = _GUICtrlListBox_GetCount($hdlList) ConsoleWrite("Count of list: " & $countOfList & @LF) count of list is not working.
      I realiezed that there are difference between listbox and listview, but this is something different.
      and till now I never saw that you can go inside with infotool and also get instances.
       
      so Im confused how to get the list number and the names of selection?