Thread: Help with version/behavior quirks.

Here's the story....

We have a product that was developed using MsSQL server. We recently
changed over to PostgreSQL in an ongoing effort to get away from MS
products. We have one workstation that this port was done on, and we are
now trying to deploy on other machines.

Environment is pg under cygwin, odbc, and pgadmin II.

On the development/test machine (pg 7.2.3, odbc 7.1.9, pgadmin 1.2.0),
you can put a query inside begin/end statements and get the results. e.g.

 begin;
 select * from table_name;
 end;

returns the table contents.

We have several other machines, running various combinations, e.g.
 pg 7.3.1, odbc 7.2.5, pgadmin 1.4.12
 pg 7.2.1, odbv 7.2.3, pgadmin 1.2.0

and with those we just get a "query OK" message. It obviously does the
query, but it doesn't display the results.

Which behavior is "right", and is there something we can do to see the
results?

Our application has a function that returns a cursor with a result set.
This works with the first system (the development system this was coded
on) but we can't get a similar behavior to work on the platform we want
to deploy/develop on now.

Alternatively, is there another way to return a set of rows/columns from
a function? We are converting from an MsSQL app that had a stored
procedure that returned several results all at once. This was no problem
in MsSQL, but seems to require explicit cursor use in postgresql?

Thanks in advance!

-Jon

Jon V <jon@cecorp.com> writes:
> On the development/test machine (pg 7.2.3, odbc 7.1.9, pgadmin 1.2.0),
> you can put a query inside begin/end statements and get the results. e.g.
>  begin;
>  select * from table_name;
>  end;
> returns the table contents.

> We have several other machines, running various combinations, e.g.
>  pg 7.3.1, odbc 7.2.5, pgadmin 1.4.12
>  pg 7.2.1, odbv 7.2.3, pgadmin 1.2.0

> and with those we just get a "query OK" message. It obviously does the
> query, but it doesn't display the results.

Ugh.

> Which behavior is "right", and is there something we can do to see the
> results?

I'd definitely say the former is right.

It's hard to be sure which component is at fault, but just comparing
version numbers the odbc 7.1.x -> 7.2.x update seems like the biggest
change.  I'd suggest seeking help on the pgsql-odbc mailing list.

            regards, tom lane

Tom Lane wrote:

>It's hard to be sure which component is at fault, but just comparing
>version numbers the odbc 7.1.x -> 7.2.x update seems like the biggest
>change.  I'd suggest seeking help on the pgsql-odbc mailing list.
>
>
Tom,

I installed the version 7.1.x ODBC driver on my other test machines and
everything started working, so you were right. I now have a machine with
pg 7.3.1, odbc 7.1.10, and pgadmin 1.4.12, and all seems to be well.

Thanks for your help,
  Jon

Jon V <jon@cecorp.com> writes:
> I installed the version 7.1.x ODBC driver on my other test machines and
> everything started working, so you were right.

Ah-hah.  Please do pass that on to the odbc list; they need to know they
have a bug to fix.

            regards, tom lane