sql query only has 1 column results in array

[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

[water]

How do you determine that you only get 1 column? The script does not show how you process $avResults.

[gcue]

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

in rapid sql i get 2 column results

[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?

[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)

[water]

What do you get when you run this line?

$avResults = $oRS.GetRows()
MsgBox(0, "", "Rows: " & UBound($avResults, 1) & ", Columns: " & UBound($avResults, 2))

 

[gcue]

interesting!!!

Rows: 9, Columns: 2

but column 2 is still blank

[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

 

[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: 

 

[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 February 26, 2016 by water

[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: 

 

[water]

Looks like a problem with the Rapid SQL interface. Could you test with another language like Visual Basic?

[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?

[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.).

[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

[water]

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

[gcue]

yes numeric

[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.

[gcue]

as part of the query?  or in autoit?

[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.