gcue Posted February 26, 2016 Share Posted February 26, 2016 i run the query in rapid sql and get 2 columns but not sure why the same query only gives me one column in autoit $oRS.Open($oQuery, $oConn,2) If $oRS.EOF Then $oRS.Close SetError(1) Return EndIf $oRS.ReQuery() $avResults = $oRS.GetRows() $oRS.Close Return $avResults thank you in advance Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 How do you determine that you only get 1 column? The script does not show how you process $avResults. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 when i display the results with _arraydisplay i only see one column in rapid sql i get 2 column results Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 Do you get the expected row/column orientation? According to the DevGuru ADO Quickref GetRows "To allow backwards compatibility with earlier versions of ADO, the columns are placed in the first dimension of the array and the rows are placed in the second dimension." Maybe Rapid SQL works the same way? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 yes orientation is correct the row count is correct and the values are also correct for the first column rapid sql orientation looks like what i would expect in autoit (2 columns with a row for each result) Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 What do you get when you run this line? $avResults = $oRS.GetRows() MsgBox(0, "", "Rows: " & UBound($avResults, 1) & ", Columns: " & UBound($avResults, 2)) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 interesting!!! Rows: 9, Columns: 2 but column 2 is still blank Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 Next try: $avResults = $oRS.GetRows() For $i = 1 to UBound($avResults, 1) - 1 For $j = 1 UBound($avResults, 2) - 1 ConsoleWrite("Row: " & $i & ", Column: " & $j & ", Datatype: " & Vargettype($avResults[$i][$j]) & ", Value: " & $avResults[$i][$j]) Next Next My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 Quote Row: 1, Column: 1, Datatype: String, Value: Row: 2, Column: 1, Datatype: String, Value: Row: 3, Column: 1, Datatype: String, Value: Row: 4, Column: 1, Datatype: String, Value: Row: 5, Column: 1, Datatype: String, Value: Row: 6, Column: 1, Datatype: String, Value: Row: 7, Column: 1, Datatype: String, Value: Row: 8, Column: 1, Datatype: String, Value: Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 (edited) OMG, my fault. Should be: $avResults = $oRS.GetRows() For $i = 0 to UBound($avResults, 1) - 1 For $j = 0 UBound($avResults, 2) - 1 ConsoleWrite("Row: " & $i+1 & ", Column: " & $j+1 & ", Datatype: " & Vargettype($avResults[$i][$j]) & ", Value: " & $avResults[$i][$j]) Next ConsoleWrite(@CRLF) Next Edited February 26, 2016 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 Quote Row: 0, Column: 0, Datatype: String, Value: AN Row: 0, Column: 1, Datatype: String, Value: Row: 1, Column: 0, Datatype: String, Value: BE Row: 1, Column: 1, Datatype: String, Value: Row: 2, Column: 0, Datatype: String, Value: CAB Row: 2, Column: 1, Datatype: String, Value: Row: 3, Column: 0, Datatype: String, Value: CK Row: 3, Column: 1, Datatype: String, Value: Row: 4, Column: 0, Datatype: String, Value: JF Row: 4, Column: 1, Datatype: String, Value: Row: 5, Column: 0, Datatype: String, Value: JUN Row: 5, Column: 1, Datatype: String, Value: Row: 6, Column: 0, Datatype: String, Value: MC Row: 6, Column: 1, Datatype: String, Value: Row: 7, Column: 0, Datatype: String, Value: NG Row: 7, Column: 1, Datatype: String, Value: Row: 8, Column: 0, Datatype: String, Value: vPFw Row: 8, Column: 1, Datatype: String, Value: Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 Looks like a problem with the Rapid SQL interface. Could you test with another language like Visual Basic? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 what do you mean problem with rapid sql? rapid sql shows the right results. not sure how to connect to sql with visual basic.. i can try searching internet for an example unless you had one handy? Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 The array has the correct size but only the first column gets filled. If you query a table to return e.g. 6 columns do you get 5 plus an empty column? It could be configuration problem or a bug in the way Rapid SQL connects the database to your script. BTW: We do not know how you connect to the database (connection string etc.). My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 the autoitscript isnt connecting to rapid sql.. i just ran the query seperately in rapid sql to see if it was a problem with the query and its not.. seems to be something with autoit Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 What kind of data do you expect in column 2? Numeric data? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 yes numeric Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 So I guess the numeric data is encoded. In this case I fear you have to loop through each field of the record and translate the data to either a string or numeric data (integer, float) understandable by AutoIt. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted February 26, 2016 Author Share Posted February 26, 2016 as part of the query? or in autoit? Link to comment Share on other sites More sharing options...
water Posted February 26, 2016 Share Posted February 26, 2016 I'm no SQL guru but I think you specify the format how the database should return data. So I would try to modify the query so that numeric data is being returned as string. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now