Обсуждение: 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