Re: Connection string problems

I seem to have a problem forming a suitable connect string for the PostgreSQL ODBC
driver. I am using Windows XP and visual studio.net 2003 with C#.

If I have a connection string as:

string strConnect = "DSN=PostgreSQL30;UID=postgres;SERVER=192.168.1.102;
Port=5432;";

And I have defined the database to connec to in the DSN then everything works fine.
What I want to do however is not have to define a DSN for every database that I have or
when new ones are created. Normally (with MS SQLServer etc.) I would specify the
driver name and database in the connection string itself. So I use:

string strConnect = "DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;
Port=5432; Database=webroster";

I get an exception (OdbcException) thrown in C# that has NO error information at all in it
and the connection fails.

I get this same problem if I specify the DSN but the DSN definition itself does not specify
a database. Am I using the right keywords in the connection string? I have tried to look
around for definitions of the keywords that the ODBC driver accepts, but I can only find
various examples like the one I am using.

If I specify a DSN in the connect string and the DSN specifies a database then the
connection and all other functions work just fine. Any ODBC exceptions thrown at any
other point in my code have all the correct error information in them.

Any suggestions appreciated.

Thanks
Gary.

Quick question: Why aren't you using the .NET data provider?

If you're set on using ODBC, try using the Microsoft data link properties:

1. Create a file, call it postgresql.udl (make sure its NOT postgresql.udl.txt)
2. Double click on it.
3. Set your connection options.
4. Click OK.
5. Open with Notepad. There you'll find a connection string.

HTH

--- Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> wrote:
> I seem to have a problem forming a suitable connect string for the PostgreSQL
> ODBC
> driver. I am using Windows XP and visual studio.net 2003 with C#.
>
> If I have a connection string as:
>
> string strConnect = "DSN=PostgreSQL30;UID=postgres;SERVER=192.168.1.102;
> Port=5432;";
>
> And I have defined the database to connec to in the DSN then everything works
> fine.
> What I want to do however is not have to define a DSN for every database that
> I have or
> when new ones are created. Normally (with MS SQLServer etc.) I would specify
> the
> driver name and database in the connection string itself. So I use:
>
> string strConnect = "DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;
> Port=5432; Database=webroster";
>
> I get an exception (OdbcException) thrown in C# that has NO error information
> at all in it
> and the connection fails.
>
> I get this same problem if I specify the DSN but the DSN definition itself
> does not specify
> a database. Am I using the right keywords in the connection string? I have
> tried to look
> around for definitions of the keywords that the ODBC driver accepts, but I
> can only find
> various examples like the one I am using.
>
> If I specify a DSN in the connect string and the DSN specifies a database
> then the
> connection and all other functions work just fine. Any ODBC exceptions thrown
> at any
> other point in my code have all the correct error information in them.
>
> Any suggestions appreciated.
>
> Thanks
> Gary.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

Quick (ish) answer. I am currently investigating migrating from SQLServer to
PostgresSQL. I could edit my source code (around 150,000 lines in 40 assemblies) to
refer to PgSqlConnection, PgSqlCommand etc.etc. but I may also want to have the
option of other backends. If I use ODBC I have minimal changes to make to the source
modules to get this done.

I ported over a 500 MB database from SQLServer to PostgreSQL yesterday (80 tables,
well normalised)

All my performance testing between SQLServer with Native drivers and ODBC gives
marginal (5-10%) performance differences. In some cases ODBC is faster! Similar
results with PostgreSQL.

I used your suggestion on the connection string and I get back:

DRIVER={PostgreSQL};DATABASE=webroster;SERVER=192.168.1.102;PORT=5432;U
ID=postgres;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersio
ning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=
0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0
;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;Bools
AsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;
UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinar
y=0;UseServerSidePrepare=0

Which acually works! Do you think I need all the extra stuff? I guess I might just start
removing things until it breaks again to see what is the minimum I can get away with.

On 9 Feb 2004 at 12:07, Chris Gamache wrote:

>
> Quick question: Why aren't you using the .NET data provider?
>
> If you're set on using ODBC, try using the Microsoft data link properties:
>
> 1. Create a file, call it postgresql.udl (make sure its NOT postgresql.udl.txt)
> 2. Double click on it.
> 3. Set your connection options.
> 4. Click OK.
> 5. Open with Notepad. There you'll find a connection string.
>
> HTH
>
> --- Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> wrote:
> > I seem to have a problem forming a suitable connect string for the PostgreSQL
> > ODBC
> > driver. I am using Windows XP and visual studio.net 2003 with C#.
> >
> > If I have a connection string as:
> >
> > string strConnect = "DSN=PostgreSQL30;UID=postgres;SERVER=192.168.1.102;
> > Port=5432;";
> >
> > And I have defined the database to connec to in the DSN then everything works
> > fine.
> > What I want to do however is not have to define a DSN for every database that
> > I have or
> > when new ones are created. Normally (with MS SQLServer etc.) I would specify
> > the
> > driver name and database in the connection string itself. So I use:
> >
> > string strConnect = "DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;
> > Port=5432; Database=webroster";
> >
> > I get an exception (OdbcException) thrown in C# that has NO error information
> > at all in it
> > and the connection fails.
> >
> > I get this same problem if I specify the DSN but the DSN definition itself
> > does not specify
> > a database. Am I using the right keywords in the connection string? I have
> > tried to look
> > around for definitions of the keywords that the ODBC driver accepts, but I
> > can only find
> > various examples like the one I am using.
> >
> > If I specify a DSN in the connect string and the DSN specifies a database
> > then the
> > connection and all other functions work just fine. Any ODBC exceptions thrown
> > at any
> > other point in my code have all the correct error information in them.
> >
> > Any suggestions appreciated.
> >
> > Thanks
> > Gary.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html

Subject:
Re: [ODBC] Connection string problems
Date:
Mon, 09 Feb 2004 21:24:10 -0000
From:
"Gary Doades" <gpd(at)gpdnet(dot)co(dot)uk>
To:
pgsql-odbc(at)postgresql(dot)org, cgg007(at)yahoo(dot)com
References:
1

No not all that stuff is required.
PostgreSQL has about the longest connection string I've seen, but using
ADO at least I've been able to connect with

connectionString="DRIVER={PostgreSQL};DATABASE=my_database_name;SERVER=my_server_name(or
IP_address);UID=my_user_name;PWD=my_user_password;"

--given a standard port configuration (5432)

and a typical username, password authentication past pg_hba.conf

Some of the usefull paramters are
MaxLongVarSize,MaxVarCharsize,UpdatableCursors,BoolAsChar,Fetch,UseDeclareFetch,TrueIsMinus1,UseServerSidePrepare,
and Debug
for some of the more complicated stuff,depending on what you are doing.

>Quick (ish) answer. I am currently investigating migrating from SQLServer to PostgresSQL. I could edit my source code (around 150,000 lines >in 40 assemblies) to refer to
>PgSqlConnection, PgSqlCommand etc.etc. but I may also want to have the option of other backends. If I use ODBC I have minimal changes to make >to the source
>modules to get this done.
>I ported over a 500 MB database from SQLServer to PostgreSQL yesterday (80 tables, well normalised)
>All my performance testing between SQLServer with Native drivers and ODBC gives marginal (5-10%) performance differences. In some cases ODBC >is faster! Similar
>results with PostgreSQL.
>I used your suggestion on the connection string and I get back:
>DRIVER={PostgreSQL};DATABASE=webroster;SERVER=192.168.1.102;PORT=5432;UID=postgres;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0
>Which acually works! Do you think I need all the extra stuff? I guess I might just start removing things until it breaks again to see what is >the minimum I can get away with.
>On 9 Feb 2004 at 12:07, Chris Gamache wrote:
>
> Quick question: Why aren't you using the .NET data provider?
>
> If you're set on using ODBC, try using the Microsoft data link properties:
>
> 1. Create a file, call it postgresql.udl (make sure its NOT postgresql.udl.txt)
> 2. Double click on it.
> 3. Set your connection options.
> 4. Click OK.
> 5. Open with Notepad. There you'll find a connection string.
>
> HTH
>
> --- Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> wrote:
> > I seem to have a problem forming a suitable connect string for the PostgreSQL
> > ODBC
> > driver. I am using Windows XP and visual studio.net 2003 with C#.
> >
> > If I have a connection string as:
> >
> > string strConnect = "DSN=PostgreSQL30;UID=postgres;SERVER=192.168.1.102;
> > Port=5432;";
> >
> > And I have defined the database to connec to in the DSN then everything works
> > fine.
> > What I want to do however is not have to define a DSN for every database that
> > I have or
> > when new ones are created. Normally (with MS SQLServer etc.) I would specify
> > the
> > driver name and database in the connection string itself. So I use:
> >
> > string strConnect = "DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;
> > Port=5432; Database=webroster";
> >
> > I get an exception (OdbcException) thrown in C# that has NO error information
> > at all in it
> > and the connection fails.
> >
> > I get this same problem if I specify the DSN but the DSN definition itself
> > does not specify
> > a database. Am I using the right keywords in the connection string? I have
> > tried to look
> > around for definitions of the keywords that the ODBC driver accepts, but I
> > can only find
> > various examples like the one I am using.
> >
> > If I specify a DSN in the connect string and the DSN specifies a database
> > then the
> > connection and all other functions work just fine. Any ODBC exceptions thrown
> > at any
> > other point in my code have all the correct error information in them.
> >
> > Any suggestions appreciated.
> >
> > Thanks
> > Gary.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html

Tracked the problem down!!! Small bug I think.

This works:

"DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;Port=5432;Database=
webroster"

This doesn't:

"DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;Port=5432;
Database=webroster"

Spot the difference. There is a space between the semicolon after the 5432; and the
Database= keyword! "Port=5432;Database=webroster" works,
but "Port=5432; Database=webroster" doesn't. Checked it lots of times....

Cheers,
Gary.

On 9 Feb 2004 at 17:17, Greg Campbell wrote:

>
>
> Subject:
> Re: [ODBC] Connection string problems
> Date:
> Mon, 09 Feb 2004 21:24:10 -0000
> From:
> "Gary Doades" <gpd(at)gpdnet(dot)co(dot)uk>
> To:
> pgsql-odbc(at)postgresql(dot)org, cgg007(at)yahoo(dot)com
> References:
> 1
>
>
> No not all that stuff is required.
> PostgreSQL has about the longest connection string I've seen, but using
> ADO at least I've been able to connect with
>
> connectionString="DRIVER={PostgreSQL};DATABASE=my_database_name;SERVER=my_server_name(or
> IP_address);UID=my_user_name;PWD=my_user_password;"
>
> --given a standard port configuration (5432)
>
> and a typical username, password authentication past pg_hba.conf
>
> Some of the usefull paramters are
> MaxLongVarSize,MaxVarCharsize,UpdatableCursors,BoolAsChar,Fetch,UseDeclareFetch,TrueIsMinus1,UseServerSidePrepare,
> and Debug
> for some of the more complicated stuff,depending on what you are doing.
>
>
> >Quick (ish) answer. I am currently investigating migrating from SQLServer to PostgresSQL. I could edit my source code (around 150,000 lines >in 40 assemblies) to refer to
> >PgSqlConnection, PgSqlCommand etc.etc. but I may also want to have the option of other backends. If I use ODBC I have minimal changes to make >to the source
> >modules to get this done.
> >I ported over a 500 MB database from SQLServer to PostgreSQL yesterday (80 tables, well normalised)
> >All my performance testing between SQLServer with Native drivers and ODBC gives marginal (5-10%) performance differences. In some cases ODBC >is faster! Similar
> >results with PostgreSQL.
> >I used your suggestion on the connection string and I get back:
>
>DRIVER={PostgreSQL};DATABASE=webroster;SERVER=192.168.1.102;PORT=5432;UID=postgres;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLo
ngVarBinary=0;UseServerSidePrepare=0
> >Which acually works! Do you think I need all the extra stuff? I guess I might just start removing things until it breaks again to see what is >the minimum I can get away with.
> >On 9 Feb 2004 at 12:07, Chris Gamache wrote:
> >
> > Quick question: Why aren't you using the .NET data provider?
> >
> > If you're set on using ODBC, try using the Microsoft data link properties:
> >
> > 1. Create a file, call it postgresql.udl (make sure its NOT postgresql.udl.txt)
> > 2. Double click on it.
> > 3. Set your connection options.
> > 4. Click OK.
> > 5. Open with Notepad. There you'll find a connection string.
> >
> > HTH
> >
> > --- Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> wrote:
> > > I seem to have a problem forming a suitable connect string for the PostgreSQL
> > > ODBC
> > > driver. I am using Windows XP and visual studio.net 2003 with C#.
> > >
> > > If I have a connection string as:
> > >
> > > string strConnect = "DSN=PostgreSQL30;UID=postgres;SERVER=192.168.1.102;
> > > Port=5432;";
> > >
> > > And I have defined the database to connec to in the DSN then everything works
> > > fine.
> > > What I want to do however is not have to define a DSN for every database that
> > > I have or
> > > when new ones are created. Normally (with MS SQLServer etc.) I would specify
> > > the
> > > driver name and database in the connection string itself. So I use:
> > >
> > > string strConnect = "DRIVER={PostgreSQL};UID=postgres;SERVER=192.168.1.102;
> > > Port=5432; Database=webroster";
> > >
> > > I get an exception (OdbcException) thrown in C# that has NO error information
> > > at all in it
> > > and the connection fails.
> > >
> > > I get this same problem if I specify the DSN but the DSN definition itself
> > > does not specify
> > > a database. Am I using the right keywords in the connection string? I have
> > > tried to look
> > > around for definitions of the keywords that the ODBC driver accepts, but I
> > > can only find
> > > various examples like the one I am using.
> > >
> > > If I specify a DSN in the connect string and the DSN specifies a database
> > > then the
> > > connection and all other functions work just fine. Any ODBC exceptions thrown
> > > at any
> > > other point in my code have all the correct error information in them.
> > >
> > > Any suggestions appreciated.
> > >
> > > Thanks
> > > Gary.
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Finance: Get your refund fast by filing online.
> > http://taxes.yahoo.com/filing.html