WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
Miscellaneous

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

PostGreSQL Tips

 Keywords: PostGre PostGreSQL Database SQL OLEDB
Posted to the WebBoard by Stan Littlefield

Our company just abandoned a licensed server version of SQL Server for open-source coding with PostGreSQL. Of course, this left my Winbatch apps in the lurch, especially since the 'programmers' interpret open-source as 'secret-system'. Having finally been allowed access to the DB I've spent a little time trying to figure out what's there:

  1. There is an OLEDB Provider for PostGre but IMHO it is 'crapware' - a beta that was abandoned in 2006.
  2. There are about 50 versions of the ODBC drivers for PostGre, so watch the version carefully. The latest offers both ANSI and Unicode.
  3. Connectionstrings are 'iffy' - don't rely on sites like connectionstrings.com as you will be frustrated.
  4. Does not fully support OpenSchema(), so initial frustration with tables, columns and views.
  5. Does not support updateable cursors - the infamous 'column "ctid" does not exists'
But to be honest, Winbatch can and does work with PostGre.

Below are some initial findings based on inheriting a Postgre database with no data-dictionary. I wanted to use ADO since I have used that with Winbatch for queries on a number of DB’s, i.e. Oracle, SQL Server, DB2, Informix, Sybase.

First, the drivers: Postgre has both an OLEDB and ODBC driver download (for free). Being Open-Source, you get what you pay for. The OLEDB driver is more Beta and has not been improved since 2006. The latest ODBC driver comes in both Unicode and ANSI.

The connection string: (I am going to use % substitution) 

Src = 999.999.999.999 ;ip or server address
DB = “mydb” ;name of database
Usr = “admin” ;user name
Pwd = “password”
;optional
Timeout = 1000
Ex = “extended properties”
Port = “5432”
cConnP="Driver={PostgreSQL ANSI};Server=%Src%;Port=%port%;Database=%DB%;Uid=%Usr%;Pwd=%Pwd%;"
;optionally add ;timeout=%Timeout%;Extended Properties=%Ex%
If using the provider: 
cConnP =”Provider=PostgreSQL OLE DB Provider;Password=%Pwd%;User ID=%Usr%;Data Source=%Src%;Location=%DB%;”
Getting Basic Schema Information:

ADO has a connection method OpenSchema() which can extract essential information from a database and help quickly build a data-dictionary if one is lacking. Postgre has limited support for OpenSchema() as illustrated in the snippet below where OpenSchema is used to persist table, column and view structures to XML files: 

cXML=dirscript():"PG_Tables.xml"
If FileExist(cXML) Then FileDelete(cXML)
cXML1=dirscript():"PG_Fields.xml"
If FileExist(cXML1) Then FileDelete(cXML1)
cXML2=dirscript():"PG_Views.xml"
If FileExist(cXML2) Then FileDelete(cXML2)
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConnP)
oRS=oConn.OpenSchema(20) ;to get tables
oRS.Save(cXML,1)
oRS.Close()
oRS=0
oRS=oConn.OpenSchema(4) ;to get columns
oRS.Save(cXML1,1)
oRS.Close()
oRS=0

Article ID:   W17980

Filename:   PostGreSQL Tips.txt
File Created: 2009:06:16:07:49:42
Last Updated: 2009:06:16:07:49:42