gmane.comp.db.postgresql.odbc

Re: Configure script unable to find PgSQL dir

Adrian Klaver — 2012-05-23T14:18:17

My guess is you do not have the Postgres dev libraries for libpq. That 
the libpq you are pointing at is the client binary. Verify whether you 
have postgresql-dev(or equivalent) installed.

Configure script unable to find PgSQL dir

Joe Tseng — 2012-05-23T13:56:43

I'm attempting to compile and install this from source but I'm getting a "library not found" error:

# ls -l /export/appl/pkgs/pgsql
total 4
lrwxrwxrwx   1 root     other          6 May 23 09:29 latest -> v9.1.3
drwxr-xr-x   6 root     other        512 May  8 16:02 v9.1.3

./configure \
  --prefix=/export/appl/pkgs/psqlodbc/v09.01.0100 \
  --with-libpq=/export/appl/pkgs/pgsql/latest \
  --with-unixodbc=/export/appl/pkgs/unixodbc/latest \
  --enable-unicode \
  --host=sparc-sun-solaris2.10 \
  --disable-openssl
...
checking for PQconnectdb in -lpq... no
configure: error: libpq library not found

I also configured using --without-libpq and the package was made and installed correctly, but I don't think it knows how to connect:

# ./isql pidcim11 odbcuser xxx
[ISQL]ERROR: Could not SQLConnect

If this is not the right address to ask questions is there a forum that I can post my problem to?

thx,

 - Joe


If you type "Google" into Google, you can break the Internet.  -- Jen Barber

Re: cannot copy very large bytea data

Hiroshi Inoue — 2012-05-23T03:53:21

Hi Michael,

(2012/05/22 22:57), Michael Vitale wrote:

Please try the type lo instead of bytea.

regards,
Hiroshi Inoue

[ psqlodbc-Bugs-1011196 ] Cannot insert very large blobs

2012-05-22T14:13:34

Bugs item #1011196, was opened at 2012-05-21 21:57
You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Michael Vitale (pgdude)
Assigned to: Nobody (None)
Summary: Cannot insert very large blobs

Initial Comment:
I can use the PostgreSQL ODBC driver to insert a blob (bytea) of 128 megabytes, but when I try to insert one that is 399 megabytes it fails with:

Query buffer overflow in copy_statement_with_parameters

The actuall call that fails is SQLExecute.


Here is the snippet of code I see in convert.c:

enlarge_query_statement(QueryBuild *qb, size_t newsize)
{
size_tnewalsize = INIT_MIN_ALLOC;
CSTR func = "enlarge_statement";

if (qb->str_size_limit > 0 && qb->str_size_limit < (int) newsize)
{
         < here is the branch that is executed>

----------------------------------------------------------------------

Comment By: Michael Vitale (pgdude)
Date: 2012-05-21 22:13

Message:
[4272-42.487]SQL_VARBINARY: about to call
convert_to_pgbinary, used = 399966009
[4272-42.488]STATEMENT ERROR: func=enlarge_statement,
desc='', errnum=1, errmsg='Query buffer allocate error in
copy_statement_with_parameters'
[4272-42.496]CONN ERROR: func=enlarge_statement, desc='',
errnum=0, errmsg='(NULL)'
[4272-42.499]retval=-1
[4272-42.500][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=1
Id=4 info=(09BF6788,12)
[4272-42.500]PGAPI_GetDiagField entering
rec=1[4272-42.500]ER_ReturnError: status = 1, msg = #Query
buffer allocate error in copy_statement_with_parameters#
[4272-42.501]         szSqlState = 'HY000',len=61,
szError='(null)'
[4272-42.501]PGAPI_GetDiagField exiting 0
[4272-42.502][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=2
Id=4 info=(09BF6788,12)
[4272-42.502]PGAPI_GetDiagField entering
rec=2[4272-42.502]ER_ReturnError: status =


----------------------------------------------------------------------

You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

[ psqlodbc-Bugs-1011196 ] Cannot insert very large blobs

2012-05-22T14:13:04

Bugs item #1011196, was opened at 2012-05-21 21:57
You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Michael Vitale (pgdude)
Assigned to: Nobody (None)
Summary: Cannot insert very large blobs

Initial Comment:
I can use the PostgreSQL ODBC driver to insert a blob (bytea) of 128 megabytes, but when I try to insert one that is 399 megabytes it fails with:

Query buffer overflow in copy_statement_with_parameters

The actuall call that fails is SQLExecute.


Here is the snippet of code I see in convert.c:

enlarge_query_statement(QueryBuild *qb, size_t newsize)
{
size_tnewalsize = INIT_MIN_ALLOC;
CSTR func = "enlarge_statement";

if (qb->str_size_limit > 0 && qb->str_size_limit < (int) newsize)
{
         < here is the branch that is executed>

----------------------------------------------------------------------

Comment By: Michael Vitale (pgdude)
Date: 2012-05-21 22:13

Message:
[4272-42.487]SQL_VARBINARY: about to call
convert_to_pgbinary, used = 399966009
[4272-42.488]STATEMENT ERROR: func=enlarge_statement,
desc='', errnum=1, errmsg='Query buffer allocate error in
copy_statement_with_parameters'
[4272-42.496]CONN ERROR: func=enlarge_statement, desc='',
errnum=0, errmsg='(NULL)'
[4272-42.499]retval=-1
[4272-42.500][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=1
Id=4 info=(09BF6788,12)
[4272-42.500]PGAPI_GetDiagField entering
rec=1[4272-42.500]ER_ReturnError: status = 1, msg = #Query
buffer allocate error in copy_statement_with_parameters#
[4272-42.501]         szSqlState = 'HY000',len=61,
szError='(null)'
[4272-42.501]PGAPI_GetDiagField exiting 0
[4272-42.502][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=2
Id=4 info=(09BF6788,12)
[4272-42.502]PGAPI_GetDiagField entering
rec=2[4272-42.502]ER_ReturnError: status =


----------------------------------------------------------------------

You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

cannot copy very large bytea data

Michael Vitale — 2012-05-22T13:57:29

I am able to copy a row with a 127megabyte column, but not with one that is 399 megabytes.

Here is the log output of the one that fails:

[4272-42.487]SQL_VARBINARY: about to call convert_to_pgbinary, used = 399966009
[4272-42.488]STATEMENT ERROR: func=enlarge_statement, desc='', errnum=1, errmsg='Query buffer allocate error in copy_statement_with_parameters'
[4272-42.496]CONN ERROR: func=enlarge_statement, desc='', errnum=0, errmsg='(NULL)'
[4272-42.499]retval=-1
[4272-42.500][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=1 Id=4 info=(09BF6788,12)
[4272-42.500]PGAPI_GetDiagField entering rec=1[4272-42.500]ER_ReturnError: status = 1, msg = #Query buffer allocate error in copy_statement_with_parameters#
[4272-42.501]         szSqlState = 'HY000',len=61, szError='(null)'
[4272-42.501]PGAPI_GetDiagField exiting 0

[ psqlodbc-Bugs-1011196 ] Cannot insert very large blobs

2012-05-21T22:13:19

Bugs item #1011196, was opened at 2012-05-21 21:57
You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Michael Vitale (pgdude)
Assigned to: Nobody (None)
Summary: Cannot insert very large blobs

Initial Comment:
I can use the PostgreSQL ODBC driver to insert a blob (bytea) of 128 megabytes, but when I try to insert one that is 399 megabytes it fails with:

Query buffer overflow in copy_statement_with_parameters

The actuall call that fails is SQLExecute.


Here is the snippet of code I see in convert.c:

enlarge_query_statement(QueryBuild *qb, size_t newsize)
{
size_tnewalsize = INIT_MIN_ALLOC;
CSTR func = "enlarge_statement";

if (qb->str_size_limit > 0 && qb->str_size_limit < (int) newsize)
{
         < here is the branch that is executed>

----------------------------------------------------------------------

Date: 2012-05-21 22:13

Message:
[4272-42.487]SQL_VARBINARY: about to call
convert_to_pgbinary, used = 399966009
[4272-42.488]STATEMENT ERROR: func=enlarge_statement,
desc='', errnum=1, errmsg='Query buffer allocate error in
copy_statement_with_parameters'
[4272-42.496]CONN ERROR: func=enlarge_statement, desc='',
errnum=0, errmsg='(NULL)'
[4272-42.499]retval=-1
[4272-42.500][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=1
Id=4 info=(09BF6788,12)
[4272-42.500]PGAPI_GetDiagField entering
rec=1[4272-42.500]ER_ReturnError: status = 1, msg = #Query
buffer allocate error in copy_statement_with_parameters#
[4272-42.501]         szSqlState = 'HY000',len=61,
szError='(null)'
[4272-42.501]PGAPI_GetDiagField exiting 0
[4272-42.502][[SQLGetDiagFieldW]] Handle=(3,08F47DD0) Rec=2
Id=4 info=(09BF6788,12)
[4272-42.502]PGAPI_GetDiagField entering
rec=2[4272-42.502]ER_ReturnError: status =


----------------------------------------------------------------------

You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

[ psqlodbc-Bugs-1011196 ] Cannot insert very large blobs

2012-05-21T21:57:28

Bugs item #1011196, was opened at 2012-05-21 21:57
You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Michael Vitale (pgdude)
Assigned to: Nobody (None)
Summary: Cannot insert very large blobs

Initial Comment:
I can use the PostgreSQL ODBC driver to insert a blob (bytea) of 128 megabytes, but when I try to insert one that is 399 megabytes it fails with:

Query buffer overflow in copy_statement_with_parameters

The actuall call that fails is SQLExecute.


Here is the snippet of code I see in convert.c:

enlarge_query_statement(QueryBuild *qb, size_t newsize)
{
size_tnewalsize = INIT_MIN_ALLOC;
CSTR func = "enlarge_statement";

if (qb->str_size_limit > 0 && qb->str_size_limit < (int) newsize)
{
         < here is the branch that is executed>

----------------------------------------------------------------------

You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1011196&group_id=1000125

Re: Update For Outdated Win98

BGoebel — 2012-05-16T18:06:41

...

not so far failed

...
Interesting. Is there a list of what APIs it adds or adjusts?

i did not found what is implemented, but some challenging applications are
"supported" :Firefox 8, Open Office ,.... 
   


Mark Morgan Lloyd-2 wrote


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Update-For-Outdated-Win98-tp5693458p5708956.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Re: Update For Outdated Win98

Mark Morgan Lloyd — 2012-05-09T11:21:09

To remove ambiguity: do you mean you've had it running and it's not so 
far failed, or you've had it running and it's recently failed? :-)


Interesting. Is there a list of what APIs it adds or adjusts?


All of us have much to be grateful to him for.

Update For Outdated Win98

BGoebel — 2012-05-08T11:38:07

I admit, nowadays an exotic szenario:

Running a PostgreSQL client on an Windows 98 machine.

The 9.1 pSQLOdbc Driver uses some Kernel32.DLL functions which were
introduced with  WindowsXP.
such as TryEnterCriticalSection().

I updated Windows 98 with KernelEx 4.5.2 http://kernelex.sourceforge.net/
and the brandnew 2011 pSQLOdbc driver runs with our application without a
problem until now. We are using UTF8 Charsets with the psqlodbca.dll, UTF 16
with psqlodbcw.dll might be an issue.

KernelEx is a compatibility layer for running Win2000+ apps.

I have tested it with Win98 running in a VM, but i am sure pSQLODBC will
work with a real machine too.

Special thanks go to Hiroshi Inoue for his hint.
 




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Update-For-Outdated-Win98-tp5693458.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Re: using pgsql-odbc using client certificate auth

Stephen Frost — 2012-05-04T16:53:53

Hiroshi, all,

* Stephen Frost (sfrost< at >snowman.net) wrote:

Ok, I've been able to make it use the Windows certificate store for the
SSL Key (at least..).  Unfortunately, it won't use the certificate store
for the actual certificate or the root chain (yet...).  When it comes to
the ODBC distribution, here's what I'd really like to see:

Please add the 'capi.dll' file to the ODBC distribution, it's part of
OpenSSL and should be installed next to libeay32.dll.  Unfortunately,
that's not all that's needed to make it work- you also need an
openssl.cfg file to be installed, ideally with the ODBC driver too,
with these contents:

---------------------------------------------------
openssl_conf = openssl_init

[openssl_init]
oid_section = new_oids
engines = engine_section

[engine_section]
capi = capi_config

[capi_config]
engine_id = capi
dynamic_path = "c:\\program\ files\ \(x86\)\\psqlodbc\\0901\\bin\\capi.dll"
init=1
---------------------------------------------------

We also need to tell OpenSSL where to find that config file by setting
an environment variable called "OPENSSL_CONF" and putting the path to
the .cfg file there, like so:

OPENSSL_CONF="C:\Program Files (x86)\psqlODBC\0901\bin\openssl.cfg"

Once all of *that* is done, you configure the PG environment variables
like so:

PGSSLCERT="C:\path\to\my.crt"
PGSSLROOTCERT="C:\path\to\myrootchain.crt"
PGSSLKEY="capi:My Name"

(eg: "capi:Stephen P Frost")

Not sure how much of the environment variable stuff we want to include
in the distribution of the ODBC driver vs. just having it in the
documentation.  The more we put into the distro, the less documentation
and the fewer steps that I'll have to deal with, so I'd be inclined to
include more rather than less.

I'm going to look into what it'd take to have CAPI be used for the
actual certificate and root chain..  That really should be very simple
as OpenSSL has support for all of this stuff, we just need to use it.
That'll likely be a libpq change though.

Thanks!

Stephen

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

George Weaver — 2012-04-25T15:12:03



I think you'll find that it is not ADO.Net's OdbcDataAdapter that expands 
the table name, but
the CommandBuilder - I use ADO Net's OdbcDataAdapter extensively without 
problem,
but I write my own OdbcCommands.

George

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Nils Gösche — 2012-04-25T15:02:01


[...]


I used to have the same problem and gave up on it a long time ago. I
just tried to reproduce it again and...failed!

I suspect it's because we have switched to VS 2010 in the meantime. Could
you perhaps check if your problem goes away, too, if you compile with VS
2010?

Thanks for bringing it up, anyway :-) :-) :-)

Regards,

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Rob Richardson — 2012-04-25T14:52:40

The issue is that ADO.Net's OdbcDataAdapter object automatically expands the 
table name "MyTable" into "MyDatabase.public.MyTable", and PostgreSQL cannot 
handle that.

In PgAdmin, "insert into cycle (cycle) values ('Another cycle')" succeeds, but 
"insert into Anneal.public.cycle (cycle) values ('Another one')" fails with the 
cross-database references not implemented error.  The OdbcDataAdapter object 
automatically converts a query of the first form into a query of the second 
form, and I want to know how to stop it from doing that.

RobR

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Barry Bell — 2012-04-25T14:35:11

Your SQL"select * from DBNAME.TABLENAME"  
Instead of just " select * from TABLENAME"  ?

Does the user use a different DB Login then you do?
Try logging as the user into pgadmin and run the SQL.

If you have the same issues, the issue is with the user access rights (or under which right a view/table was created).


Thanks
Barry Bell, IT Department 
Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell< at >harte-hanks.com


-----Original Message-----
From: pgsql-odbc-owner< at >postgresql.org [mailto:pgsql-odbc-owner< at >postgresql.org] On Behalf Of Rob Richardson
Sent: Wednesday, April 25, 2012 10:25 AM
To: pgsql-odbc< at >postgresql.org
Subject: Re: [ODBC] How can I keep an OdbcDataAdapter from using fully qualified tble names?

I want to stick with DSNs because we change databases frequently, mainly on our own systems.  Develop first using a test database, then do final testing on a copy of the customer's production database, then, if problems crop up, get an up-to-date copy of the customer's database and debug using that.  That's what DSNs are for.  They work.  (At least until the .Net world apparently decided it knows better.)

I think you're thinking of a different issue.  I'm not talking about a fully qualified name of a server, with "mycompany.com" appended to it.  I'm talking about a fully qualified table name, with the database name and schema prepended to it.

RobR

--
Sent via pgsql-odbc mailing list (pgsql-odbc< at >postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Rob Richardson — 2012-04-25T14:25:10

I want to stick with DSNs because we change databases frequently, mainly on our 
own systems.  Develop first using a test database, then do final testing on a 
copy of the customer's production database, then, if problems crop up, get an 
up-to-date copy of the customer's database and debug using that.  That's what 
DSNs are for.  They work.  (At least until the .Net world apparently decided it 
knows better.)

I think you're thinking of a different issue.  I'm not talking about a fully 
qualified name of a server, with "mycompany.com" appended to it.  I'm talking 
about a fully qualified table name, with the database name and schema prepended 
to it.

RobR

Re: How can I keep an OdbcDataAdapter from using fully qualified tble names?

Barry Bell — 2012-04-25T14:11:58

It maybe a dns issue, The below connection works because server "my-server"
Resolves in my land from "my-server" to "my-server.mycompany.com".

Driver={PostgreSQL ANSI};Server=my-server;Port=5432;Database=dbname;Uid=user;Pwd=password;BI=2;TextAsLongVarchar=1;UnknownSizes=2;UnknownsAsLongVarchar=1;UseServerSidePrepare=1;

Does this help?

Why not go DSN-less? (Put the connection string into an INI disturbed with the program?) 
Otherwise, you will goto every computer and Update the DSN whenever the server or db name is changed


Thanks
Barry Bell, IT Department 
Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell< at >harte-hanks.com


-----Original Message-----
From: pgsql-odbc-owner< at >postgresql.org [mailto:pgsql-odbc-owner< at >postgresql.org] On Behalf Of Rob Richardson
Sent: Wednesday, April 25, 2012 10:06 AM
To: pgsql-odbc< at >postgresql.org
Subject: [ODBC] How can I keep an OdbcDataAdapter from using fully qualified tble names?

Hello!

How can I keep an OdbcDataAdapter from insisting on the fully qualified table name?


In VS 2008, I am trying to use the System.Data.Odbc namespace to work with a 
PostGres database.  I don't want to use npgsql because I want to work with DSNs 
so that I get the flexibility of changing databases without changing my 
program.  


When I use an OdbcCommandBuilder object to create the insert, update and delete 
commands for me, the table names are fully qualified.  For example, an insert 
command might begin with "insert into MyDatabase.public.MyTable...".  When I try 
to use that, I get an exception thrown that complains that cross-database 
references are not implemented (even though the connection I am using is to the 
MyDatabase database).  


So, I wrote a little function that converts a string containing 
"MyDatabase.public.MyTable" into "MyTable", and use that to replace the fully 
qualified name in the command text with the bare table name.  But when it comes 
to time to call the adapter's Update() method to add a new row into my table, 
the first thing the adapter does is to change the bare table name back to the 
full "MyDatabase.public.MyTable" name, and then I get the same exception.

Thank you very much.

RobR

How can I keep an OdbcDataAdapter from using fully qualified tble names?

Rob Richardson — 2012-04-25T14:05:50

Hello!

How can I keep an OdbcDataAdapter from insisting on the fully qualified table 
name?


In VS 2008, I am trying to use the System.Data.Odbc namespace to work with a 
PostGres database.  I don't want to use npgsql because I want to work with DSNs 
so that I get the flexibility of changing databases without changing my 
program.  


When I use an OdbcCommandBuilder object to create the insert, update and delete 
commands for me, the table names are fully qualified.  For example, an insert 
command might begin with "insert into MyDatabase.public.MyTable...".  When I try 
to use that, I get an exception thrown that complains that cross-database 
references are not implemented (even though the connection I am using is to the 
MyDatabase database).  


So, I wrote a little function that converts a string containing 
"MyDatabase.public.MyTable" into "MyTable", and use that to replace the fully 
qualified name in the command text with the bare table name.  But when it comes 
to time to call the adapter's Update() method to add a new row into my table, 
the first thing the adapter does is to change the bare table name back to the 
full "MyDatabase.public.MyTable" name, and then I get the same exception.

Thank you very much.

RobR

Advanced Options - OID

Jan-Peter Seifert — 2012-04-23T11:21:59

Hello,

if 'Advanced Options' -> 'OID Options' -> 'Show Column' is activated ODBC
function SQLColumns() retrieves one more row than expected if you use the function's filter ColumnName.
If you check for the existence of a column that does not exist you get one
instead of no result row.
If you check for the existence of a column that does exist you get two instead of one row and the correct row is the second and not the first one.

Could you check this, please?

Thank you very much,

Peter Seifert

Re: Passing numeric Bind variables to ODBC driver convers to "Double precision"

Hiroshi Inoue — 2012-04-13T22:27:42

Could you send me the Mylog output?
You can take the log by adding Debug=1; in your connection string.

regards,
Hiroshi Inoue