Обсуждение: ODBC driver and refcursors
Hi Everyone,
I've been using 8.1 server and odbc driver with my
application on windows and it has been working fine. I
upgraded to 8.3.4 and started seeing issues with some of my
queries.
Here is the scenario:
create table users (userid int, username varchar(10));
insert into users values (1,'user1');
insert into users values (2,'user2');
insert into users values (3,'user3');
insert into users values (4,'user4');
insert into users values (5,'user5');
-- And I have functions similar to this
create or replace function test_cursor( refcursor, uid
int)
returns refcursor as
$$
BEGIN
open $1 for
select userid, username
from users
where userid >uid;
return $1;
END;
$$
LANGUAGE 'plpgsql';
-- My application sends this query
select * from test_cursor('curs',3);
fetch all in "curs";
With 8.1 odbc driver, I get the below rows:
userid | username
--------------------
4 | user4
5 | user5
And with 8.3 odbc driver, I get
test_cursor
---------------
curs
The database server is the same with both drivers; what has changed
in the new odbc driver? Do I need to configure something
while creating the DSN?
Thanks for your help!
Regards,
Farooq
Any ideas? Anyone?
--- On Thu, 5/28/09, Farooq <shorrt_circuit@yahoo.com> wrote:
> From: Farooq <shorrt_circuit@yahoo.com>
> Subject: ODBC driver and refcursors
> To: pgsql-odbc@postgresql.org
> Date: Thursday, May 28, 2009, 10:25 AM
> Hi Everyone,
>
> I've been using 8.1 server and odbc driver with my
> application on windows and it has been working fine. I
> upgraded to 8.3.4 and started seeing issues with some of
> my
> queries.
>
> Here is the scenario:
>
> create table users (userid int, username varchar(10));
>
> insert into users values (1,'user1');
> insert into users values (2,'user2');
> insert into users values (3,'user3');
> insert into users values (4,'user4');
> insert into users values (5,'user5');
>
> -- And I have functions similar to this
>
> create or replace function test_cursor( refcursor, uid
> int)
> returns refcursor as
> $$
> BEGIN
> open $1 for
>
> select userid, username
> from users
> where userid >uid;
>
> return $1;
> END;
> $$
> LANGUAGE 'plpgsql';
>
> -- My application sends this query
>
> select * from test_cursor('curs',3);
> fetch all in "curs";
>
> With 8.1 odbc driver, I get the below rows:
>
> userid | username
> --------------------
> 4 | user4
> 5 | user5
>
> And with 8.3 odbc driver, I get
>
> test_cursor
> ---------------
> curs
>
>
> The database server is the same with both drivers; what has
> changed
> in the new odbc driver? Do I need to configure something
> while creating the DSN?
>
> Thanks for your help!
>
> Regards,
> Farooq
>
>
>
>
>
Farooq wrote:
>> select * from test_cursor('curs',3);
>> fetch all in "curs";
>>
>> With 8.1 odbc driver, I get the below rows:
>>
>> userid | username
>> --------------------
>> 4 | user4
>> 5 | user5
>>
>> And with 8.3 odbc driver, I get
>>
>> test_cursor
>> ---------------
>> curs
Try dispatching this in two separate calls, instead of a single string
containing two semicolon-separated statements.
I have the vague feeling the ODBC driver may have options that affect
multi-statement queries.
--
Craig Ringer
Farooq wrote:
> Any ideas? Anyone?
>
> --- On Thu, 5/28/09, Farooq <shorrt_circuit@yahoo.com> wrote:
>
>> From: Farooq <shorrt_circuit@yahoo.com>
>> Subject: ODBC driver and refcursors
>> To: pgsql-odbc@postgresql.org
>> Date: Thursday, May 28, 2009, 10:25 AM
>> Hi Everyone,
>>
>> I've been using 8.1 server and odbc driver with my
>> application on windows and it has been working fine. I
>> upgraded to 8.3.4 and started seeing issues with some of
>> my
>> queries.
>>
>> Here is the scenario:
>>
>> create table users (userid int, username varchar(10));
>>
>> insert into users values (1,'user1');
>> insert into users values (2,'user2');
>> insert into users values (3,'user3');
>> insert into users values (4,'user4');
>> insert into users values (5,'user5');
>>
>> -- And I have functions similar to this
>>
>> create or replace function test_cursor( refcursor, uid
>> int)
>> returns refcursor as
>> $$
>> BEGIN
>> open $1 for
>>
>> select userid, username
>> from users
>> where userid >uid;
>>
>> return $1;
>> END;
>> $$
>> LANGUAGE 'plpgsql';
>>
>> -- My application sends this query
>>
>> select * from test_cursor('curs',3);
>> fetch all in "curs";
8.2 or later drivers produces 2 result sets for the
query.
>> With 8.1 odbc driver, I get the below rows:
>>
>> userid | username
>> --------------------
>> 4 | user4
>> 5 | user5
>>
>> And with 8.3 odbc driver, I get
>>
>> test_cursor
>> ---------------
>> curs
Please call SQLMoreResults() or the command which corresponds
to it before calling fetch operation.
regards,
Hiroshi Inoue
--- On Mon, 6/1/09, Craig Ringer <craig@postnewspapers.com.au> wrote:
> From: Craig Ringer <craig@postnewspapers.com.au>
> Subject: Re: [ODBC] ODBC driver and refcursors
> To: "Farooq" <shorrt_circuit@yahoo.com>
> Cc: pgsql-odbc@postgresql.org
> Date: Monday, June 1, 2009, 5:49 PM
> Farooq wrote:
>
> >> select * from test_cursor('curs',3);
> >> fetch all in "curs";
> >>
> >> With 8.1 odbc driver, I get the below rows:
> >>
> >> userid | username
> >> --------------------
> >> 4 | user4
> >> 5 | user5
> >>
> >> And with 8.3 odbc driver, I get
> >>
> >> test_cursor
> >> ---------------
> >> curs
>
> Try dispatching this in two separate calls, instead of a
> single string
> containing two semicolon-separated statements.
>
With two separate calls; the second statement complains that the cursor does not exist.
> I have the vague feeling the ODBC driver may have options
> that affect
> multi-statement queries.
>
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>
--- On Tue, 6/2/09, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
> From: Hiroshi Inoue <inoue@tpf.co.jp>
> Subject: Re: [ODBC] ODBC driver and refcursors
> To: "Farooq" <shorrt_circuit@yahoo.com>
> Cc: pgsql-odbc@postgresql.org
> Date: Tuesday, June 2, 2009, 8:39 AM
> Farooq wrote:
> > Any ideas? Anyone?
> >
> > --- On Thu, 5/28/09, Farooq <shorrt_circuit@yahoo.com>
> wrote:
> >
> >> From: Farooq <shorrt_circuit@yahoo..com>
> >> Subject: ODBC driver and refcursors
> >> To: pgsql-odbc@postgresql.org
> >> Date: Thursday, May 28, 2009, 10:25 AM
> >> Hi Everyone,
> >>
> >> I've been using 8.1 server and odbc driver with
> my
> >> application on windows and it has been working
> fine. I
> >> upgraded to 8.3.4 and started seeing issues with
> some of
> >> my
> >> queries.
> >>
> >> Here is the scenario:
> >>
> >> create table users (userid int, username
> varchar(10));
> >>
> >> insert into users values (1,'user1');
> >> insert into users values (2,'user2');
> >> insert into users values (3,'user3');
> >> insert into users values (4,'user4');
> >> insert into users values (5,'user5');
> >>
> >> -- And I have functions similar to this
> >>
> >> create or replace function test_cursor( refcursor,
> uid
> >> int)
> >> returns refcursor as
> >> $$
> >> BEGIN
> >> open $1 for
> >>
> >> select userid, username
> >> from users
> >> where userid >uid;
> >>
> >> return $1;
> >> END;
> >> $$
> >> LANGUAGE 'plpgsql';
> >>
> >> -- My application sends this query
> >>
> >> select * from test_cursor('curs',3);
> >> fetch all in "curs";
>
> 8.2 or later drivers produces 2 result sets for the
> query.
>
> >> With 8.1 odbc driver, I get the below rows:
> >>
> >> userid | username
> >> --------------------
> >> 4 | user4
> >> 5 | user5
> >>
> >> And with 8.3 odbc driver, I get
> >>
> >> test_cursor
> >> ---------------
> >> curs
>
> Please call SQLMoreResults() or the command which
> corresponds
> to it before calling fetch operation.
Thanks for the reply Hiroshi!
I did some search on the net and wasn't able to find a sql command corresponding to SQLMoreReuslts(). I am using
crystalreports so can't use SQLMoreResults() directly from there. Any more ideas?
Regards,
Farooq
>
> regards,
> Hiroshi Inoue
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>
Farooq wrote: > With two separate calls; the second statement complains that the cursor does not exist. You'd have to wrap them in a transaction (explicit BEGIN / COMMIT). In any case, you've since received a better suggestion than mine. -- Craig Ringer