Thread: Defining a Connection String in Windows XP
I would like to learn how to set a connection string.
I am running pgsql 9.0 on an XP. I found a list of connection string
examples:
» dotConnect for PostgreSQL (former Core Labs PostgreSQLDirect)
(PgSqlConnection)
» Npgsql (NpgsqlConnection)
» PostgreSQL OLE DB Provider
» .NET Framework Data Provider for OLE DB (OleDbConnection)
» PostgreSQL ODBC Driver (psqlODBC)
» .NET Framework Data Provider for ODBC (OdbcConnection)
I am trying to connect with SQL Maestro. Since pgsql provides an ODBC
driver, it seemed appropriate to work with the PostgreSQL ODBC Driver.
The suggested connection string is:
Driver={PostgreSQL};Server=IP
address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
I do not know if I should leave the {PostgreSQL} as is or should it be
replaced with something.
For Server, the connection is on the same machine so I would think the
value should be ‘localhost’ (without quotes).
For Database, I don’t know if this should be the name of a database
inside of my pgsql server or something else.
When I run:
Driver={PostgreSQL};Server=localhost;Port=5432;Database=myDataBase;Uid=user101;Pwd=pw101;
I get a message window that says “[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified”.
The driver is supposed to be inside of a pgsql DLL but I don’t know
how Windows is supposed to find it. How should I set up the
connection string?
ray
On 13/12/2010 18:46, ray wrote:
> I do not know if I should leave the {PostgreSQL} as is or should it be
> replaced with something.
> For Server, the connection is on the same machine so I would think the
> value should be ‘localhost’ (without quotes).
Yes, that's right.
> For Database, I don’t know if this should be the name of a database
> inside of my pgsql server or something else.
Yes, it's the specific database to which you want to connect.
> When I run:
> Driver={PostgreSQL};Server=localhost;Port=5432;Database=myDataBase;Uid=user101;Pwd=pw101;
> I get a message window that says “[Microsoft][ODBC Driver Manager]
> Data source name not found and no default driver specified”.
What you're doing above is creating a DSN-less connection. I haven't
done it that way, but have had no problems when creating a DSN first. Go
to Administrative Tools -> Data sources (ODBC) and create a data source
for the database. You can then use a connection string something like this:
dsn=yourDsnName;uid=myUsername;pwd=myPassword;
HTH,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
On Dec 13, 1:15 pm, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 13/12/2010 18:46, ray wrote:
>
> > I do not know if I should leave the {PostgreSQL} as is or should it be
> > replaced with something.
> > For Server, the connection is on the same machine so I would think the
> > value should be localhost (without quotes).
>
> Yes, that's right.
>
> > For Database, I don t know if this should be the name of a database
> > inside of my pgsql server or something else.
>
> Yes, it's the specific database to which you want to connect.
>
> > When I run:
> > Driver={PostgreSQL};Server=localhost;Port=5432;Database=myDataBase;Uid=user101;Pwd=pw101;
> > I get a message window that says [Microsoft][ODBC Driver Manager]
> > Data source name not found and no default driver specified .
>
> What you're doing above is creating a DSN-less connection. I haven't
> done it that way, but have had no problems when creating a DSN first. Go
> to Administrative Tools -> Data sources (ODBC) and create a data source
> for the database. You can then use a connection string something like this:
>
> dsn=yourDsnName;uid=myUsername;pwd=myPassword;
>
> HTH,
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Ray,
Thank you for responding. OK, that was my first trip into DSN setup
and I got lost:
The first step is to choose and existing MS driver for an Office app
or 'Add' one from this 'User DSN' tab. The other tabs are:
System dSN, File DSN, Drivers, Tracing, and Connection Pooling.
I did not see a pgsql listed in any - what is the next step (or two)?
ray
On 13/12/2010 19:30, ray wrote:
> Thank you for responding. OK, that was my first trip into DSN setup
> and I got lost:
> The first step is to choose and existing MS driver for an Office app
> or 'Add' one from this 'User DSN' tab. The other tabs are:
> System dSN, File DSN, Drivers, Tracing, and Connection Pooling.
> I did not see a pgsql listed in any - what is the next step (or two)?
>
Hi Ray,
If you don't see the PostgreSQL driver listed under "Drivers", then it
hasn't been installed properly. How did you install it? You usually need
to run the downloadable installer:
http://www.postgresql.org/ftp/odbc/versions/msi
Once the driver is installed, you create a DSN of the type of your
choice (system, user or file) by going to the appropriate tab and click
"Add".
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
>Ray, > >Thank you for responding. OK, that was my first trip into DSN setup >and I got lost: >The first step is to choose and existing MS driver for an Office app >or 'Add' one from this 'User DSN' tab. The other tabs are: >System dSN, File DSN, Drivers, Tracing, and Connection Pooling. >I did not see a pgsql listed in any - what is the next step (or two)? > >ray I usually use a System DSN. If, when you click Add, you don't see 'PostgreSQL ANSI' as one of the choices, you don't havethe ODBC driver installed. If you do, just fill in the boxes with the database name, password, etc. Susan C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> -----Original Message----- > From: Susan Cassidy [mailto:scassidy@stbernard.com] > Sent: Monday, December 13, 2010 2:06 PM > To: ray; pgsql-general@postgresql.org > Subject: RE: [GENERAL] Defining a Connection String in Windows XP > > >Ray, > > > >Thank you for responding. OK, that was my first trip into DSN setup > >and I got lost: > >The first step is to choose and existing MS driver for an Office app > >or 'Add' one from this 'User DSN' tab. The other tabs are: > >System dSN, File DSN, Drivers, Tracing, and Connection Pooling. > >I did not see a pgsql listed in any - what is the next step (or two)? > > > >ray > > I usually use a System DSN. If, when you click Add, you don't see > 'PostgreSQL ANSI' as one of the choices, you don't have the ODBC driver > installed. If you do, just fill in the boxes with the database name, > password, etc. > > Susan C. > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general This is great! Just for reference, the bat file would not run because there was not an existing pgsql driver. The msi file worked right off. I used a System DSN and it worked. Now to figure out what to do with it. BTW, what are some of the considerations for choosing DSN types System, File, User? Thank you very much, ray
On 12/13/10 6:32 PM, ray joseph wrote: > > I used a System DSN and it worked. > Now to figure out what to do with it. > BTW, what are some of the considerations for choosing DSN types System, > File, User? all three do the same thing in the end, they specify a ODBC database connection. the system DSN is available to all users of that system. the file DSN is just that, a file, that contains a few lines of text with the DSN information, you can bundle it with your app, and you reference it by the fully qualified path to the file. a user DSN is only available to the user that defined it. for some dorky reason, I don't think you can save the database password with these ad it has to be specified when you use the connection for various reasons, system DSN's are generally easier to use in microsoft-land.