Thread: ODBC driver and refcursors

ODBC driver and refcursors

From

Farooq

Date:

28 May 2009, 05:26:02

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

Re: ODBC driver and refcursors

From

Farooq

Date:

01 June 2009, 11:15:14

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
>
>
>
>      
>

Re: ODBC driver and refcursors

From

Craig Ringer

Date:

01 June 2009, 12:49:30

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

Re: ODBC driver and refcursors

From

Hiroshi Inoue

Date:

02 June 2009, 03:39:57

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

Re: ODBC driver and refcursors

From

Farooq

Date:

02 June 2009, 10:15:11



--- 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
>

Re: ODBC driver and refcursors

From

Farooq

Date:

02 June 2009, 10:25:35



--- 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
>

Re: ODBC driver and refcursors

From

Craig Ringer

Date:

02 June 2009, 15:13:24

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