Thread: ODBC SELECT Timeout

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/libodbcpsqlS.so
Driver64                = /usr/lib64/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
UseDeclareFetch         = 1
Fetch           = 100
UseServerSidePrepare            = 0
FileUsage               = 1
UsageCount              = 2

I'm have the 64 bit drivers installed and it sees those okay.  Thanks for
any help or a point in the right direction.  I'm just starting to really dig
into postgreSQL, so do not know much quite yet.

Regards,
Tom



--
View this message in context: http://postgresql.1045698.n5.nabble.com/ODBC-SELECT-Timeout-tp5756490.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Hello,

(2013/05/23 2:08), ter062424 wrote:
> 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/libodbcpsqlS.so
> Driver64                = /usr/lib64/psqlodbc.so
> Setup64         = /usr/lib64/libodbcpsqlS.so
> UseDeclareFetch         = 1
> Fetch           = 100
> UseServerSidePrepare            = 0

Could please try
   UseServerSidePrepare = 1
?

regards,
Hiroshi Inoue

Hello Hiroshi,

I set UseServerSidePrepare=1 in /etc/odbcinst.ini and then ran odbcinst -i
-d -f /etc/odbcinst.ini.  The SELECT query over ODBC ran for about 1 hour,
and then died with:

SQLNumResultCols: -1/1; S1000/[unixODBC]No query has been executed with that
handle

One thing I have noticed is that it always runs for about 1 hour (usually
55-59 minutes) before it dies.  Is it possible that something is timing out
on the postgreSQL server side, or ODBC side?  I really appreciate the help
with this, if you have any other advice, I'll be glad to give it a try.  I
did research timeouts on the postgreSQL Server end, but it seemed to
indicate that the default is for it not to time out, and I haven't modified
anything in postgresql.conf except to have the system listen on 5432.

Regards,
Tom



--
View this message in context: http://postgresql.1045698.n5.nabble.com/ODBC-SELECT-Timeout-tp5756490p5757210.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Sorry for teh late reply.

(2013/05/29 5:20), ter062424 wrote:
> Hello Hiroshi,
>
> I set UseServerSidePrepare=1 in /etc/odbcinst.ini and then ran odbcinst -i
> -d -f /etc/odbcinst.ini.

I don't know well about odbcinst.
Does the above operation update odbc.ini?

 > The SELECT query over ODBC ran for about 1 hour,
> and then died with:

Could you try
   explain <the query>
using psql etc?

regards,
Hiroshi Inoue