I have been able to successfully connect to PostgreSQL using D7 and ADO.
Environment:
D7 on W2K workstation
MDAC v2.8
PostgreSQL ODBC driver v7.03.01.00
PostgeSQL v7.3.4 on SuSE Linux 8.2
(Compiled with odbc switch, odbc.sql applied to template1
before creating new databse)
With ODBC
Create system DSN pointing to databse on server
(use standard driver)
In D7
New project
Add ADOConnection
Build Connection String (using the system DSN you just
created)
- Mine looks like this:
Provider=MSDASQL.1;Password=sysdba;Persist
Security Info=True;
User ID=postgres;Data Source=Manpower-pg;
Extended
Properties="DSN=Manpower-pg;DATABASE=manpower;
SERVER=192.168.0.3;PORT=5432;
UID=postgres;PWD=sysdba;ReadOnly=0;
Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=1;
ShowSystemTables=0;ConnSettings=;Fetch=100;
Socket=4096;UnknownSizes=2;MaxVarcharSize=254;
MaxLongVarcharSize=32764;Debug=0;CommLog=0;
Optimizer=1;Ksqo=1;UseDeclareFetch=1;
TextAsLongVarchar=1;UnknownsAsLongVarchar=1;
BoolsAsChar=1;Parse=1;CancelAsFreeStmt=0;
ExtraSysTablePrefixes=dd_;;LFConversion=1;
UpdatableCursors=1;DisallowPremature=1;TrueIsMinus1=0;
BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1"
Test the connection after building it - it should test ok
Set the connection to active - you should have a valid
connection
Add some other stuff to the form to verify you are getting data
In my case, I added an ADOTable, DataSource, and DataGrid
(grid points to source, source to table, table to
connection)
You can view the full table contents.
Some other things to check:
1. Check your pg_hba.conf - are you allowing access to your client
machine?
2. Check your postgresql.conf - do you allow tcpip_sockets,
have you changed your default port from 5432?
3. Do you have proper rights to the database - are you logging in
with the
same user name and password as you used to create the database?
You can always try using dbExpress using the openODBC driver available
on sourceforge.
However, I have had problems getting all rows of a table to return.
Only the first row
shows up. Checking logs and packet sniffing reveals PostgreSQL is
returning all rows.
IMHO dbExpress is not yet ready for primetime - it is too quirky. Too
bad as I would like
to use it in place of ADO.
HTH
