How to get result of "SELECT" query in 2d array?

[Sonia]

Hi,

I made a connection to postgreSQl database. Connection is successfull and insert query successfully inserts data in database table. But when i used "SELECT" query, how can i store this result into 2-darray. Following is my code:

$oconn = ObjCreate("ADODB.Connection")

$DSN = "DRIVER={PostgreSQL Ansi};SERVER=192.168.2.197;DATABASE=lms_db;UID=postgres;PWD=;"

$d = $oconn.Open($DSN)

$oconn.Execute("insert into t1Table values('d');")

Above code suuuessfully inserts the data into database table. But i want to get results from database in 2-darray. How could i do that?

$oconn.Execute("Select * from t1Table;")

I need to store values of table "t1Table" into 2-darray. How can i do that?

I used _SQLite_GetTable2d to get table in 2-daray but _SQLite.au3 is not working and it could not make a connection to database.

Please specify how to create connection through SQLite.au3? and also explain how to store results of "SELECT"query into 2-darray?

Sonia,

Edited January 21, 2009 by Sonia

[99ojo]

Hi,

I made a connection to postgreSQl database. Connection is successfull and insert query successfully inserts data in database table. But when i used "SELECT" query, how can i store this result into 2-darray. Following is my code:

$oconn = ObjCreate("ADODB.Connection")

$DSN = "DRIVER={PostgreSQL Ansi};SERVER=192.168.2.197;DATABASE=lms_db;UID=postgres;PWD=;"

$d = $oconn.Open($DSN)

$oconn.Execute("insert into t1Table values('d');")

Above code suuuessfully inserts the data into database table. But i want to get results from database in 2-darray. How could i do that?

$oconn.Execute("Select * from t1Table;")

I need to store values of table "t1Table" into 2-darray. How can i do that?

I used _SQLite_GetTable2d to get table in 2-daray but _SQLite.au3 is not working and it could not make a connection to database.

Please specify how to create connection through SQLite.au3? and also explain how to store results of "SELECT"query into 2-darray?

Sonia,

Hi,

you have the includes:

#include <SQLite.au3>

#include <SQLite.dll.au3>

If yes, then define :

Local $aResult, $iRows, $iColumns, $iRval

and then add (after your execute statement):

$iRval = _SQLite_GetTable2d ($d, "Select * from t1Table;", $aResult, $iRows, $iColumns)

If $iRval = $SQLITE_OK Then

_SQLite_Display2DResult($aResult)

Else

MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())

EndIf

;-))

[Sonia]

I have tried this code but it is not working. Because _SQLite is only working for sqlite database. Please tell me another solution?

Sonia,

[Spiff59]

I think you can only use the .execute object when you are doing an insert, or delete, a singleton query that will return only one row of data, commands that are executed in a single pass. A query that returms multiple rows requires you to create a cursor, and then poll the cursor (or recordset) until end-of-file. This code isn't tweaked for your application, but it is an example of what works for me (using the Advantage database Server):

$oADO = ObjCreate("ADODB.Connection")
    $oADO.mode = 2;(2=adModeRead; 3=adModeReadWrite; 12=adModeShareExcludive)1
    If Not IsObj($oADO) Then MsgBox(1,"ERROR", "ADO ObjCreate error")
    $oADO.Open($ADS_Path)
    If @error Then
        MsgBox(0, "ERROR", "Failed to connect to the database")
        Exit
    EndIf
    $SQL = "SELECT [CHART NUMBER] FROM MWCAS WHERE [GUARANTOR] = '" & $GL_Chart & "' GROUP BY [CHART NUMBER] ORDER BY [CHART NUMBER];"
    $oRS = ObjCreate("ADODB.Recordset")
    $oRS.cursortype = 3;   adOpenForwardOnly = 0, adOpenKeyset = 1, adOpenDynamic = 2, adOpenStatic = 3
    $oRS.locktype = 1; adLockReadOnly = 1, adLockPessimistic = 2, adLockOptimistic = 3, adLockBatchOptimistic = 4
    $oRS.Open($SQL, $oADO)
    $x = 0
    While Not $oRS.EOF
        $x += 1
        GUICtrlSetData($Label_M_GuarantorFor[$x], $oRS("CHART NUMBER").value)
        $oRS.movenext
    WEnd
    $oRS.Close
    $oADO.Close

The .mode, .cursortype and .locktype values may differ for Postgres, and I often leave those statements out entirerly and allow the defaults to be used. But this should be the basic method to return multiple rows from a database query.

EDIT: PS - I don't think you want to have anything to do with SQLite or it's related include files. They are not compatible with your Postgres Database Server.

Edited January 22, 2009 by Spiff59

[ResNullius]

Hi,

I made a connection to postgreSQl database. Connection is successfull and insert query successfully inserts data in database table. But when i used "SELECT" query, how can i store this result into 2-darray. Following is my code:

$oconn = ObjCreate("ADODB.Connection")

$DSN = "DRIVER={PostgreSQL Ansi};SERVER=192.168.2.197;DATABASE=lms_db;UID=postgres;PWD=;"

$d = $oconn.Open($DSN)

$oconn.Execute("insert into t1Table values('d');")

Above code suuuessfully inserts the data into database table. But i want to get results from database in 2-darray. How could i do that?

$oconn.Execute("Select * from t1Table;")

I need to store values of table "t1Table" into 2-darray. How can i do that?

I used _SQLite_GetTable2d to get table in 2-daray but _SQLite.au3 is not working and it could not make a connection to database.

Please specify how to create connection through SQLite.au3? and also explain how to store results of "SELECT"query into 2-darray?

Sonia,

Using the ADODB .GetRows method might do the job for you:

#include <array.au3>

$oconn = ObjCreate("ADODB.Connection")
$DSN = "DRIVER={PostgreSQL Ansi};SERVER=192.168.2.197;DATABASE=lms_db;UID=postgres;PWD=;"
$d = $oconn.Open($DSN)
$oconn.Execute("insert into t1Table values('d');")
;;$oconn.Execute("Select * from t1Table;")

;instead of "Executing" the Select Command, we'll open a recordset with it
$rst = ObjCreate("ADODB.Recordset")
$rst.Open("Select * from t1Table", $d, 2, 3)
$aResultsArray = $rst.GetRows()
_ArrayDisplay($aResultsArray, "Results")

More on .GetRows here: http://www.w3schools.com/ado/met_rs_getrows.asp

Might also check out the .GetString method: http://www.w3schools.com/ado/met_rs_getstring.asp

Edit: Had last two lines of code transposed.

Edited January 22, 2009 by ResNullius

[Xand3r]

$obj.execute("select field1,field2,field3 from table")
with $obj
    $count=0
    while not .EOF
        $var[$count][0]=.fields(0).value;field1
        $var[$count][1]=.fields(1).value;field2
        $var[$count][2]=.fields(2).value;field3
        $count+=1
        .MoveNext
    wend
endwith

edit:forgot .MoveNext silly me

Edited January 22, 2009 by TheMadman

[DaRam]

Not related to OP, but just to clarify...

1. You can use .execute on any valid SQL statement.

Presuming these definitions: $adoRs = ObjCreate( "ADODB.RecordSet" ) and $adoCn = ObjCreate( "ADODB.Connection" ),

Example1: $adoRs = $adoCn.Execute("SELECT * FROM some_table")

Example2: $adoRs = $adoCn.Execute("DELETE * FROM some_table WHERE some_column='blah'") ; Obviously there would no useful data here

2. You can use the .NextRecordset to obtain the second pass output of say a stored procedure.

I think you can only use the .execute object when you are doing an insert, or delete, a singleton query that will return only one row of data, commands that are executed in a single pass. A query that returms multiple rows requires you to create a cursor, and then poll the cursor (or recordset) until end-of-file. This code isn't tweaked for your application, but it is an example of what works for me (using the Advantage database Server):

[Spiff59]

I'm finding the previous 3 posts quite interesting.

I was unable to find much here months ago to get me going via my Advantage Database Server, so I figured things out by trial-and-error. I probably should have spent more time in the ADO tutorials...

So these two commands, as long as both the connection and recordset objects have been created, are equivalent?

1. Pass the SQL query to the RecordSet object via the .open method

2. Pass the SQL query to the connection object via the .execute method

?

I hadn't seen the .GetRows method anywhere before, me thinks I'll find use for that one before too long!

Thanks all.

[DaRam]

They might be, you would still prefer the .open method on a SELECT and .execute method for CREATE, DELETE, executing Stored Procedures, etc.

Some other methods that are useful:

Dump retrieved data to a file:

$adoRs.Save "d:\path\filename.dat", 0 ; Advance Data TableGram format

Or

$adoRs.Save "d:\path\filename.xml", 1 ; XML Format

The files can be opened later using $adoRs.Open "d:\path\filename.xml"

Convert ADO Datastream to CSV format using this method:

$Text = $adoRs.GetString(2, -1, ",", @CRLF, "")

Or Tab delimited $Text = $adoRs.GetString(2, -1, @TAB, @CRLF, "")

Also, in Excel you can use the ADO Recordset object's CopyFromRecordset method to dump data into a worksheet.

Something like $oXLWorksheet.Range("A1").CopyFromRecordset $adoRs

I'm finding the previous 3 posts quite interesting.

I was unable to find much here months ago to get me going via my Advantage Database Server, so I figured things out by trial-and-error. I probably should have spent more time in the ADO tutorials...

So these two commands, as long as both the connection and recordset objects have been created, are equivalent?

1. Pass the SQL query to the RecordSet object via the .open method

2. Pass the SQL query to the connection object via the .execute method

?

I hadn't seen the .GetRows method anywhere before, me thinks I'll find use for that one before too long!

Thanks all.