gmane.comp.db.postgresql.odbc

Re: ODBC does not handle WITH clause

Joe Conway — 2013-05-23T18:00:26

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/23/2013 08:15 AM, Hiroshi Inoue wrote:

Sure, absolutely.

Joe


- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with undefined - http://www.enigmail.net/

iQIcBAEBAgAGBQJRnlk6AAoJEDfy90M199hl8m8P/12H2hjmHKNc5PuZ7Fvwvrrr
xBW8KZE41CitD32dK0+UpaGa3mceTiZsy35zKS/gYAk6uiN6dSa3nkKc7Vj0OO/f
ctngY5jbM40eqpRY5FmOJP7FsNhFZ6Q/MXuooTi8gJmyHU1oLuo9AE+G0GyUkvEz
sYa8nNVFc6hWvGrX8sETSefeaQPJlZDVOGUFhlQkuBpukyMfRznX9hEDLUxUT7uv
F+9ARmGxZl89ehwD6kVk8Yct50oCfvaw3gDdegeT0GbXDpgkbBlw8iYnuor0oUtn
uao2brOTsP/tw2Rkr44RgloP2n5uR7yxtkEu2dkWM4XUmdC1khav+YOK2vpXSeHF
4m8N+fv9hR8Ip0EA71qqnOS2GORhHiT2Tw1K7G1rn1t174n+9BBIxt9a61ryUlEt
ZOa66kWYYg4gtRgORqh8CA5N2+kzM1oQ6aZlCzju4YTG+6YPBubsKO87EbzzqSRE
jnAkTS1hIDmeeYDUfiDTEn/BGsavGsgGb62hjQm3Db2TWi6e6ATHTfREmM5Fb9pN
AUaiajTc9jYW1JAUDdgkoga4WIqpFLywVjfyumutr5RALd6v

Re: Cursor for a positioned update: "cursor does not exist" error

Hiroshi Inoue — 2013-05-23T13:20:07

Hi Sam,

(2013/05/23 7:11), Sam Varshavchik wrote:

SQL_CA1_POS_UPDATE stands for thr capability about SQLSetPos function
not about positioned update.
Maybe we had better not support SQLSetCursorName to avoid the confusion.

regards,
Hiroshi Inoue

Re: ODBC does not handle WITH clause

Hiroshi Inoue — 2013-05-23T13:15:27

Hi Jack,

(2013/05/22 7:28), Joe Conway wrote:

.
.


Thanks for the report.


AFAIK no.

Psqlodbc drivers take care of with clause just a little.
As for the ERROR message, possibly I found at least one of the cause.
Could you test the change when I make a patch?

regards,
Hiroshi INoue

Cursor for a positioned update: "cursor does not exist" error

Sam Varshavchik — 2013-05-22T22:11:57

I'm using postgresql-odbc-09.01.0200 with postgresl-9.2.4

According to the documention for SQLSetCursorName
(http://msdn.microsoft.com/en-us/library/windows/desktop/ms711707%28v=vs.85%29.aspx)
I don't really need to use it, and ODBC will use a default name.

With unixODBC and a Postgresql connection handle, SQLGetInfo() shows  
SQL_CA1_POS_UPDATE capability for SQL_STATIC_CURSOR_ATTRIBUTES1, and  
SQL_KEYSET_CURSOR_ATTRIBUTES1.

I tried using both a static and a keyset cursor, by setting  
SQL_ATTR_CURSOR_TYPE accordingly, on a new statement handle; then executing  
a "SELECT [..] FOR UPDATE", fetching multiple rows, using SQLSetPos to  
position on a given row, using SQLGetCursorName to retrieve the cursor's  
name, then attempting to execute an "UPDATE [..] WHERE CURRENT OF  
<cursor_name>" on another statement handle.

I'm basically following the script given here:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709389%28v=vs.85%29.aspx
 
The original SELECT is a simple select on a table with a

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-22T21:38:06


Yes, its RHE without subscription. Hard time!
On a different box, I did the other way and installed unixODBC64 using the
above links. There were some libtdl* dependencies and installed package
libtool-ltdl-1.5.22-7.el5_4.x86_64.rpm for that.
unixODBC is done with the above method.

Can you list steps for psqlODBC?

Should i get packages in this way
http://wiki.postgresql.org/wiki/YUM_Installation?

Regards...

ODBC SELECT Timeout

ter062424 — 2013-05-22T17:08:47

Hello,

I have a very large, long running query that is being run on a CentOS 6
Linux box from an application using unixODBC 2.2.14 and postgresql-odbc 8.4. 
The target database is a postgreSQL 8.4 DB on another CentOS 6 Linux box.

When run, the query runs for about one hour and then dies with the following
error:

for SQL input object "SQL-AR":  SQLNumResultCols: -1/1; S1000/[unixODBC]No
query has been executed with that handle

I'm no overly familiar with using ODBC and have been researching this as
best as I can.  I've tried to add UseDeclareFetch to /etc/odbcinst.ini as
well, thinking that may be the cause.  Note that other queries run fine. 
I'm not sure if it is timing out, or what is happening.  Does anyone know
what this error could mean, and if there is anything I can put in my
/etc/odbcinst.ini to help resolve the problem.

My /etc/odbcinst.ini currently looks like this:

[PostgreSQL]
Description             = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbc.so
Setup           = /usr/lib/li

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-22T02:41:39


Hey am on RHEL 5. The above you state is for Windows.

Re: [GENERAL] ODBC constructs

Dann Corbit — 2013-05-22T02:40:36

From: pgsql-general-owner< at >postgresql.org [mailto:pgsql-general-owner< at >postgresql.org] On Behalf Of Dev Kumkar
Sent: Tuesday, May 21, 2013 7:27 PM
To: John R Pierce
Cc: pgsql-general< at >postgresql.org; pgsql-odbc< at >postgresql.org
Subject: Re: [ODBC] [GENERAL] ODBC constructs

[snip]

Suggestions?
https://rhn.redhat.com/errata/RHEA-2012-0222.html
<<

Re: [GENERAL] ODBC constructs

John R Pierce — 2013-05-22T02:35:25

after installing postgres for windows with that installer, run the Stack 
Builder tool, it will install additional postgres related packages, one 
of which is pgsqlODBC

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-22T02:27:28

 Ya it should have been with base registry. Where to get
unixODBC-libs-2.2.11-10.el5.i386?

Since ODBC driver was missing, downloaded unixODBC-2.3.0.tar.gz and
configured/make this. Now "which odbc_config" works
       # which odbc_config
       /usr/local/bin/odbc_config
       # odbc_config --libs
       -L/usr/local/lib -lodbc

But still 'yum install postgresql91-odbc' is giving errors.
Error: Missing Dependency: libodbc.so.1()(64bit) is needed by package
postgresql91-odbc-09.00.0310-1PGDG.rhel5.x86_64 (pgdg91)
Error: Missing Dependency: libodbcinst.so.1()(64bit) is needed by package
postgresql91-odbc-09.00.0310-1PGDG.rhel5.x86_64 (pgdg91)

I tried the other way then, building psqlodbc itself:
Downloaded psqlodbc driver from
http://psqlodbc.projects.postgresql.org/and tried configuring it. It
also gave error saying 'libpq' not found. Then
ran "yum -y install postgresql91-devel" and then configured with "$
./configure --with-libpq=/usr/pgsql-9.1/bin/pg_config" this worked without
'libpq error'. But still

Re: [GENERAL] ODBC constructs

Dann Corbit — 2013-05-22T02:25:50

From: Dev Kumkar [mailto:devdas.kumkar< at >gmail.com]
Sent: Tuesday, May 21, 2013 7:13 PM
To: Dann Corbit
Cc: John R Pierce; pgsql-general< at >postgresql.org; pgsql-odbc< at >postgresql.org
Subject: Re: [ODBC] [GENERAL] ODBC constructs

On Tue, May 21, 2013 at 7:09 PM, Dann Corbit <DCorbit< at >connx.com<mailto:DCorbit< at >connx.com>> wrote:
The ODBC library is contained in either the iodbc or unixodbc RPMs.
The correct RPM to use will depend on your operating system.

Where are these located?
I do not know if you have 64 bit or 32 bit Linux and if it is Redhat or Mandrake or whatever.
To be clear:
The PostgreSQL distribution allows you to download the PostgreSQL ODBC driver.  The PostgreSQL ODBC driver is not a library.  It is a shared object that uses a standardized API to communicate with the PostgreSQL database.  This allows standards based tools to access the data by linking to a single library (the ODBC library) and then connecting to as many different database systems as you like that have well written ODBC drivers.
In ord

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-22T02:13:18


Where are these located?

Re: [GENERAL] ODBC constructs

Dann Corbit — 2013-05-22T02:09:10

From: Dev Kumkar [mailto:devdas.kumkar< at >gmail.com]
Sent: Tuesday, May 21, 2013 7:03 PM
To: Dann Corbit
Cc: John R Pierce; pgsql-general< at >postgresql.org; pgsql-odbc< at >postgresql.org
Subject: Re: [ODBC] [GENERAL] ODBC constructs
[snip]
The link http://www.enterprisedb.com/products-services-training/pgdownload#windows which you had earlier provided was postgres installation. It installed libpq but odbc library was not installed.  I hope am looking at correct things.
The ODBC library is contained in either the iodbc or unixodbc RPMs.
The correct RPM to use will depend on your operating system.
<<

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-22T02:02:31


The link
http://www.enterprisedb.com/products-services-training/pgdownload#windows which
you had earlier provided was postgres installation. It installed libpq but
odbc library was not installed.  I hope am looking at correct things.

Re: [GENERAL] ODBC constructs

Dann Corbit — 2013-05-22T00:21:54

From: Dev Kumkar [mailto:devdas.kumkar< at >gmail.com]
Sent: Tuesday, May 21, 2013 1:59 PM
To: Dann Corbit
Cc: John R Pierce; pgsql-general< at >postgresql.org; pgsql-odbc< at >postgresql.org
Subject: Re: [ODBC] [GENERAL] ODBC constructs

[snip]
I already have installed this on Linux from above link some days back but not able to find library. Can you point me to the same?
Also this link says to compile if its non-windows - http://psqlodbc.projects.pgfoundry.org/faq.html#1.2 Check 1.3

Can you point me to samples out there?

Regards...
When you install an ODBC driver, it does not install a library.  It installs a DLL or shared object.
If you have already installed the driver, then set up your data source with a data source manager.

IODBC is a popular driver manager:
http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/

and UNIXODBC is another popular choice for an ODBC driver manager:
http://www.unixodbc.org/

You link your application against the ODBC library for your platform.  Then it works with any data source you have co

ODBC does not handle WITH clause

Joe Conway — 2013-05-21T22:28:06

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It seems the ODBC driver does not deal well with a WITH clause in a
statement:

8<----------------------------------
SQL> select id from generate_series(1,2) as t(id)
+------------+
| id         |
+------------+
| 1          |
| 2          |
+------------+
SQLRowCount returns -1
2 rows fetched
SQL> select w.id from (select id from generate_series(1,2) as t(id)) as w
+------------+
| id         |
+------------+
| 1          |
| 2          |
+------------+
SQLRowCount returns -1
2 rows fetched
SQL> with w as (select id from generate_series(1,2) as t(id)) select
w.id from w
SQLRowCount returns 0
SQL> select id from generate_series(1,2) as t(id)
[ISQL]ERROR: Could not SQLExecute
8<----------------------------------

The last statement fails (according to the logs) because:
    ERROR: cursor "SQL_CUR0x17c4bd0" already exists

At that point the only recovery is ABORT.

8<----------------------------------
SQL> abort
SQLRowCount returns 0
SQL> select id from generate_s

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-21T20:59:24


I already have installed this on Linux from above link some days back but
not able to find library. Can you point me to the same?
Also this link says to compile if its non-windows -
http://psqlodbc.projects.pgfoundry.org/faq.html#1.2 Check 1.3

Can you point me to samples out there?

Regards...

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-21T19:42:47



Ah I think our emails crossed.

http://www.postgresql.org/ftp/odbc/versions/src/ has sources for windows or
am I missing something here* *for linux? If Windows, then I think its just
download and run the distribution.
But for linux are those the same archives listed in link and then recompile
is the only option?

Regards...

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-21T19:37:44



Edited: Added some more text related to installing psqlODBC driver.

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-21T19:32:18

 for pooling, check out pgbouncer.   IMHO its a better basic pooler than

Sure, looks like its more light weight. And I hope, its not related to the
decision between libpq and psqlODBC. And will work with both?

thats the perfect use case for ODBC.    you'll probably still need to

Yeah, hope you got my confusion and inclination now.
Well to work with psqlODBC, will I need to recompile the psqlODBC binary
and any additional stuff to get ODBC working?
http://www.postgresql.org/ftp/odbc/versions/src/ has sources for windows or
am I missing something here?

Regards...

Re: [GENERAL] ODBC constructs

Dev Kumkar — 2013-05-21T18:58:48

On Mon, May 20, 2013 at 11:32 PM, Heikki Linnakangas <
hlinnakangas< at >vmware.com> wrote:


Thanks everyone for sharing comments, really useful ones. Yes the
requirement is not connecting to other DBMS but only Postgres and libpq is
the correct way so far from comments here. Regarding libpq performance
wanted to discuss about connection polling like pgpool but I think a
separate post of performance will make sense.

So, coming back to the original question actually I am exploring on both
fronts here - libpq and psqlODBC.
Just one thought here, there is an existing ODBC business logic code which
is talking to sybase database and now same code need to be ported to talk
to postgres, then in this case will the use of psqlODBC quick way?
Otherwise porting of ODBC business logic to libpq constructs needs to be
done.

Regards...