Jump to content
gcue

sql query only has 1 column results in array

Recommended Posts

gcue

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

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

when i display the results with _arraydisplay i only see one column

in rapid sql i get 2 column results

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

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)

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

interesting!!!

Rows: 9, Columns: 2

but column 2 is still blank

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

 

Share this post


Link to post
Share on other sites
water

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

 

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

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?

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

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

Share this post


Link to post
Share on other sites
water

What kind of data do you expect in column 2? Numeric data?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

yes numeric

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

as part of the query?  or in autoit?

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

×