Jump to content

Connecting to a PostgreSQL database


Recommended Posts

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?

Link to comment
Share on other sites

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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Here you can find the ADO Object Model.

Here you can find the ADO Open method.

HTH

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 4 years later...

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 by LarsJ
Link to comment
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...