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






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

Re: ODBC driver and refcursors

Any ideas? Anyone?

--- On Thu, 5/28/09, Farooq wrote:

> From: Farooq
> Subject: ODBC driver and refcursors
> To: pgsql-odbc@postgresql.org
> Date: Thursday, May 28, 2009, 10:25 AM
> Hi Everyone,
>=20
> 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.
>=20
> Here is the scenario:
>=20
> create table users (userid int, username varchar(10));
>=20
> 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');
>=20
> -- And I have functions similar to this
>=20
> create or replace function test_cursor( refcursor, uid
> int)
> returns refcursor as
> $$
> BEGIN
> =A0 =A0 =A0 open $1 for
>=20
> select userid, username
> from=A0 users
> where userid >uid;
>=20
> =A0 =A0 =A0 return $1;
> END;
> $$
> LANGUAGE 'plpgsql';
>=20
> -- My application sends this query
>=20
> select * from test_cursor('curs',3);
> fetch all in "curs";
>=20
> With 8.1 odbc driver, I get the below rows:
>=20
> userid=A0 |=A0 username
> --------------------
> 4=A0 =A0 |=A0 user4
> 5=A0 =A0 |=A0 user5
>=20
> And with 8.3 odbc driver, I get
>=20
> test_cursor
> ---------------
> curs
>=20
>=20
> 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?
>=20
> Thanks for your help!
>=20
> Regards,
> Farooq
>=20
>=20
>=20
> =A0 =A0  
>=20





--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ODBC driver and refcursors

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

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

Re: ODBC driver and refcursors

Farooq wrote:
> Any ideas? Anyone?
>
> --- On Thu, 5/28/09, Farooq wrote:
>
>> From: Farooq
>> 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


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

Re: ODBC driver and refcursors

--- On Mon, 6/1/09, Craig Ringer wrote:

> From: Craig Ringer
> Subject: Re: [ODBC] ODBC driver and refcursors
> To: "Farooq"
> Cc: pgsql-odbc@postgresql.org
> Date: Monday, June 1, 2009, 5:49 PM
> Farooq wrote:
>=20
> >> select * from test_cursor('curs',3);
> >> fetch all in "curs";
> >>
> >> With 8.1 odbc driver, I get the below rows:
> >>
> >> userid=A0 |=A0 username
> >> --------------------
> >> 4=A0 =A0 |=A0 user4
> >> 5=A0 =A0 |=A0 user5
> >>
> >> And with 8.3 odbc driver, I get
> >>
> >> test_cursor
> >> ---------------
> >> curs
>=20
> Try dispatching this in two separate calls, instead of a
> single string
> containing two semicolon-separated statements.
>=20

With two separate calls; the second statement complains that the cursor doe=
s not exist.=20


> I have the vague feeling the ODBC driver may have options
> that affect
> multi-statement queries.
>=20
>=20
> --
> Craig Ringer
>=20
> --=20
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>=20





--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ODBC driver and refcursors

--- On Tue, 6/2/09, Hiroshi Inoue wrote:

> From: Hiroshi Inoue
> Subject: Re: [ODBC] ODBC driver and refcursors
> To: "Farooq"
> Cc: pgsql-odbc@postgresql.org
> Date: Tuesday, June 2, 2009, 8:39 AM
> Farooq wrote:
> > Any ideas? Anyone?
> >=20
> > --- On Thu, 5/28/09, Farooq
> wrote:
> >=20
> >> From: Farooq
> >> 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
> >>=A0 =A0   =A0open $1 for
> >>
> >> select userid, username
> >> from=A0 users
> >> where userid >uid;
> >>
> >>=A0 =A0   =A0return $1;
> >> END;
> >> $$
> >> LANGUAGE 'plpgsql';
> >>
> >> -- My application sends this query
> >>
> >> select * from test_cursor('curs',3);
> >> fetch all in "curs";
>=20
> 8.2 or later drivers produces 2 result sets for the
> query.
>=20
> >> With 8.1 odbc driver, I get the below rows:
> >>
> >> userid=A0 |=A0 username
> >> --------------------
> >> 4=A0 =A0 |=A0 user4
> >> 5=A0 =A0 |=A0 user5
> >>
> >> And with 8.3 odbc driver, I get
> >>
> >> test_cursor
> >> ---------------
> >> curs
>=20
> 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 correspo=
nding to SQLMoreReuslts(). I am using crystal reports so can't use SQLMoreR=
esults() directly from there. Any more ideas?=20



Regards,
Farooq

>=20
> regards,
> Hiroshi Inoue
>=20
>=20
> --=20
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>=20





--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ODBC driver and refcursors

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

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