Sonia Posted January 21, 2009 Share Posted January 21, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
99ojo Posted January 21, 2009 Share Posted January 21, 2009 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, $iRvaland 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;-)) Link to comment Share on other sites More sharing options...
Sonia Posted January 22, 2009 Author Share Posted January 22, 2009 I have tried this code but it is not working. Because _SQLite is only working for sqlite database. Please tell me another solution? Sonia, Link to comment Share on other sites More sharing options...
Spiff59 Posted January 22, 2009 Share Posted January 22, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
ResNullius Posted January 22, 2009 Share Posted January 22, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
Xand3r Posted January 22, 2009 Share Posted January 22, 2009 (edited) $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 Only two things are infinite, the universe and human stupidity, and i'm not sure about the former -Alber EinsteinPractice makes perfect! but nobody's perfect so why practice at all?http://forum.ambrozie.ro Link to comment Share on other sites More sharing options...
DaRam Posted January 22, 2009 Share Posted January 22, 2009 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 here2. 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): Link to comment Share on other sites More sharing options...
Spiff59 Posted January 22, 2009 Share Posted January 22, 2009 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. Link to comment Share on other sites More sharing options...
DaRam Posted January 22, 2009 Share Posted January 22, 2009 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 formatOr $adoRs.Save "d:\path\filename.xml", 1 ; XML FormatThe 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 $adoRsI'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 method2. 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. 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