Connecting to a PostgreSQL database

[newbie210]

I spent a lot of time mucking around, reading the forums, but finally managed to connect to a PostgreSQL database from Autoit.

I wrote this post to help others, however there are still some things I's like some help with.

From this a found out the following:

Firstly if you're trying to connect to postgreSQL from Windows, you need to make sure your DSN in setup properly. Control Panel -> Administrative Tools -> Data Sources(ODBC). If you don't see a User DSN called PostgreSQL, this is likely part of the issue. Details on setting this up here: http://www.developer.com/open/article.php/631251

Then reading I learnt about making the database connection.

Finally I learnt about reading data from a SQL SELECT statement from

My code for reading the database:

$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$strConn = "DSN=PostgreSQL;DATABASE=databasename;SERVER=localhost;Port=5432;Uid=databaseusername;Pwd=dbpassword"

$oConn.Open($strConn)
If @error Then MsgBox(0,"","Error Opening Connection")

$strReturn=""

$sql="SELECT * FROM table WHERE column = 'value'"
$oRS.open($sql,$oConn,1,3)
If @error Then MsgBox(0,"","Error Executing SQL")

$oRS.MoveFirst
MsgBox(0,"","RecordCount: "&$oRS.RecordCount)

For $iIndex = 1 To $oRS.RecordCount
    $strReturn = $oRS.Fields("column").value
    $oRS.MoveNext
Next

$oConn.Close
msgbox(0,"",$strReturn)

My question is with the line:

$oRS.open($sql,$oConn,1,3)

What are the 3rd and 4th function parameters? I found them in somebody's code, and noticed that my code wouldn't work with out them, but don't know what they are for.

Secondly is there a help topic somewhere which lists the various ADODB contention and ADODB recordset functions?

[jchd]

Using that it seems to me that you're only getting values from the column named 'column'.

You can read the whole 2D recordset into an 2D array in one shot by replacing the For..Next loop by:

$aReturn = $oRS.GetRows()

The parameters should be cursortype and locktype.

FYI I join an ADO UDF with ready-to-use wrappers modelled on the SQLite UDF functions (see help file and this UDF functions headers). Once you have your connection string working, you should be able to use it with little issue(s) if any.

AdoSQL.au3

[water]

Here you can find the ADO Object Model.

Here you can find the ADO Open method.

HTH

[pvnn]

jchd, in UDF AdoSQL.au3 function _AdoSQL_ErrFunc() is the string _ByeBox($AdoSQLErr) error: _ByeBox(): undefined function.

 

[LarsJ]

I've not been able to find the function _ByeBox. From AdoSQL.au3 you can see that the function prints the error message from the COM error handler and exits the script.
 

You can replace _ByeBox with these two lines:

MsgBox( 0, "AdoSQL.au3 COM error", $AdoSQLErr )
Exit

Or these two lines:

ConsoleWrite( "AdoSQL.au3 COM error:" & @CRLF & $AdoSQLErr & @CRLF )
Exit

 

Edited February 22, 2016 by LarsJ

[jchd]

Oh yes, I'm sorrry. This was extracted from larger working code and the bye function was elsewhere. Yet it's name and purpose is clear, as LarsJ showed.