Skip site navigation (1) Skip section navigation (2)

Re: [Q] SQLMoreResults causes error in SQLFetchScroll

From: "V S P" <toreason(at)fastmail(dot)fm>
To: "Christophe Garault" <christophe(at)garault(dot)org>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [Q] SQLMoreResults causes error in SQLFetchScroll
Date: 2009-05-17 17:33:37
Message-ID: 1242581617.21226.1315882123@webmail.messagingengine.com (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi,
for some reason link does not work
but I did read yesterday abo0ut the SQLMoreResults

and as long as it returns
SQL_NO_DATA it means that it finished getting the results
and the MS website was saying that is the correct way to know
that no more data is there to be retreived.

While I do not have another ODBC driver to test with, I do not
see any reason why another ODBC driver would fail.


The goal for me is to be able to use a standard function to get
the number of rows affected by a given SQL operation

In practically all the ODBC drivers for any vendor
getting a result for a Bulk or single SQL statement is
simply calling SQLRowCount once is sufficient.

In more complex scenarios where a stored proc was involed that
could affect rows outise of the set that was passed in
SQLMoreResults and SQLRowCount in the loop is suggested at least for
MS SQL but not for DB2 ODBC or anybody else.

When I talked to Hiroshi he explained that for pgODBC even for
standard bulk operations I must use SQLRowCount+SQLMoreResults
in a loop -- or else it would not work (because PG native driver
does not support bulk operation so pgODBC simply calls generates
multiple single statements for the array data).

So I worked back and forth with OTL maintainer to implement this
functionality and pound define it (to use or not to use, because
some other odbc drivers did not like that pair ).


So we have put that functionality in OTL (to get the row count using
the pair) -- and now every single Select statement fails (workes
for inserts/deletes though because SQLFetchScroll is not called)

It fails with the error I outlined, so I went through the OTL
source debugging yesterday and created a sequence of ODBC statements
that cause the problem -- and that's how I created the test case.

It shows that row count using the pair of SQL statements does not work
with selects (because SQLFetchScroll is needed).

So that was the long version... since you asked.

So I am basically hoping to get pgODBC fixed (if this
is a bug) or a suggested workaround
how to get the row count in generic, oDBC compliant way (that is I
cannot have different ways for getting rowcount for different db
operations).


Vlad





On Sun, 17 May 2009 17:53 +0200, "Christophe Garault"
<christophe(at)garault(dot)org> wrote:
> Hi Vlad,
> 
> V S P wrote :
> > Therefore I am pretty certain that it is SQLMoreResults and
> > not SQLRowCount that cause a problem for pgODBC.
> >   
> Sorry I didn't pay enough attention to your code this morning.
> And yes SQLMoreResults could be the cause of your problem: this function 
> is supposed to move to the next resultset !
> So calling SQLFetchScroll after SQLMoreResults when having only one 
> resultset is not a good idea. Btw I'm not sure of what your code is 
> supposed to do...
> Have a look at Ms's site if you want more information:
> http://msdn.microsoft.com/en-us/library/ms714673(VS.85).aspx
> 
> > But going back to your question, SQLFetchScroll will error out
> > if you do not use select (because it is typically Select that returns
> > result rows).
> Sure, I thought you were only interested in SQLRowCount.
> A lack of caffeine on Sunday morning causes apologies. ;)
> 
> -- 
> Christophe Garault
-- 
  V S P
  toreason(at)fastmail(dot)fm

-- 
http://www.fastmail.fm - Email service worth paying for. Try it for free


In response to

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2009-05-18 03:27:12
Subject: Re: [Q] SQLMoreResults causes error in SQLFetchScroll
Previous:From: Christophe GaraultDate: 2009-05-17 15:53:42
Subject: Re: [Q] SQLMoreResults causes error in SQLFetchScroll

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group