Thread: Database connections

Database connections

From
"George Weaver"
Date:
In pgAdmin II, if I understand it correctly, the program determines the databases present on the server using pg_Database when the user selects Connect.  I assume that the program then connects to a specific database when one is chosen by the user from the database tree. 
 
As far as I can tell, this is done without using a User, System, or File DSN. 
 
Can you direct me to a source of information or documentation which describes how this is accomplished?
 
Thanks for you help (and a super product!)
 
George

Re: Database connections

From
"Dave Page"
Date:
Hi George


From: George Weaver [mailto:georgew1@mts.net]
Sent: 14 October 2003 14:58
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Database connections

In pgAdmin II, if I understand it correctly, the program determines the databases present on the server using pg_Database when the user selects Connect.  I assume that the program then connects to a specific database when one is chosen by the user from the database tree.   
 
In a nutshell, yes. 
 
As far as I can tell, this is done without using a User, System, or File DSN.   
 
Yes, this is called a DSN-less connection. All the DSN actually does is store pre-determined settings that the driver can use when connecting to the database, as well as the driver name itself. Those settings can also be passed to the driver through the connection string, and the DSN name omitted. 
 
Can you direct me to a source of information or documentation which describes how this is accomplished? 
 
There is an example of some VB code that uses a DSN at http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-visualbasic. The same code can be used DSN-less simply by modifying the connection string - instead of
 
  cn.Open "DSN=<MyDataSourceName>;UID=<MyUsername>;PWD=<MyPassword>;Database=<MyDatabaseName>"
you might try something like:
 
  cn.Open "DRIVER={PostgreSQL};UID=<MyUsername>;PWD=<MyPassword>;Database=<MyDatabaseName>"
 
pgAdmin also sets the following options in the connection string above (they are in no particular order, and may be omitted to get the defaults).
 
READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;
 
Thanks for you help (and a super product!)
 
You're welcome.
 
Regards Dave.

Re: Database connections

From
"George Weaver"
Date:
Hi Dave,
 
Thanks!  Just what I was looking for.
 
I have a follow up question.  In the pgAdmin II query window, there is the option to Execute to "OLE Link to MS Excel".  Is this accomplished via ODBC, or is an actual Worksheet Object being created and populated using Visual Basic?
 
Regards,
George
----- Original Message -----
From: Dave Page
Sent: Tuesday, October 14, 2003 1:46 PM
Subject: Re: [pgadmin-support] Database connections

Hi George


From: George Weaver [mailto:georgew1@mts.net]
Sent: 14 October 2003 14:58
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Database connections

In pgAdmin II, if I understand it correctly, the program determines the databases present on the server using pg_Database when the user selects Connect.  I assume that the program then connects to a specific database when one is chosen by the user from the database tree.   
 
In a nutshell, yes. 
 
As far as I can tell, this is done without using a User, System, or File DSN.   
 
Yes, this is called a DSN-less connection. All the DSN actually does is store pre-determined settings that the driver can use when connecting to the database, as well as the driver name itself. Those settings can also be passed to the driver through the connection string, and the DSN name omitted. 
 
Can you direct me to a source of information or documentation which describes how this is accomplished? 
 
There is an example of some VB code that uses a DSN at http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-visualbasic. The same code can be used DSN-less simply by modifying the connection string - instead of
 
  cn.Open "DSN=<MyDataSourceName>;UID=<MyUsername>;PWD=<MyPassword>;Database=<MyDatabaseName>"
you might try something like:
 
  cn.Open "DRIVER={PostgreSQL};UID=<MyUsername>;PWD=<MyPassword>;Database=<MyDatabaseName>"
 
pgAdmin also sets the following options in the connection string above (they are in no particular order, and may be omitted to get the defaults).
 
READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=65536;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;
 
Thanks for you help (and a super product!)
 
You're welcome.
 
Regards Dave.