gcue

sql query only has 1 column results in array

39 posts in this topic

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



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

interesting!!!

Rows: 9, Columns: 2

but column 2 is still blank

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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
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

#10 ·  Posted (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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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
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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

yes numeric

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

as part of the query?  or in autoit?

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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