Обсуждение: [Q] SQLMoreResults causes error in SQLFetchScroll
Hello, I am using the method recommended to obtain a row count from an operation. That method relies on call SQLMoreResults It appears, however, that that mechanism causes an error down the stream (that I do not undersand) in SQLFetchScroll I have an example source file that demonstrates the problem http://pastebin.com/m2b11b28d It is an 'extract' of the actual ODBC calls that are going on in from within OTL C++ library. Essentailly non of SQL statements are working (because OTL tries to obtain the row count and then deploys the sequence of ODBC statements described in the example) Wanted to ask if somebody has ran into this or if it is a bug, how to log it (if it is allowed to be logged) and if there is a workaround. Thank you in advance for any help, Vlad -- V S P toreason@fastmail.fm -- http://www.fastmail.fm - A no graphics, no pop-ups email service
Sorry forgot to mention the actual error I am getting: http://pastebin.com/d49e830c0 SQLSTATE = HY010 NATIVE ERROR = 0 MSG = [Microsoft][ODBC Driver Manager] Function sequence error this is winXP 32 against postgres 8.4 latest candidate. using pgODBC 8.03.04 ANSI On Sun, 17 May 2009 01:58 -0400, "V S P" <toreason@fastmail.fm> wrote: > Hello, > > I am using the method recommended to obtain a row count > from an operation. > > That method relies on call SQLMoreResults > > > It appears, however, that that mechanism causes > an error down the stream (that I do not undersand) > in SQLFetchScroll > > > I have an example source file that demonstrates the problem > > http://pastebin.com/m2b11b28d > > It is an 'extract' of the actual ODBC calls that are going on > in from within OTL C++ library. > > Essentailly non of SQL statements are working (because OTL > tries to obtain the row count and then deploys the sequence of > ODBC statements described in the example) > > > Wanted to ask if somebody has ran into this or if it is a bug, > how to log it (if it is allowed to be logged) and if there is a > workaround. > > Thank you in advance for any help, > Vlad > -- > V S P > toreason@fastmail.fm > > -- > http://www.fastmail.fm - A no graphics, no pop-ups email service > > > -- > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-odbc -- V S P toreason@fastmail.fm -- http://www.fastmail.fm - The way an email service should be
Hello Vlad, I'm not sure that a call to SQLRowCount is allowed after a simple SELECT. What if you change your SELECT by an UPDATE or a DELETE ? -- Christophe Garault
Вложения
Hi thank you for looking at this, SQLRowCount actually returns correct results, and SQLMoreResults(hstmt) returns SQL_NO_DATA which is also perfectly correct So if I only comment out SQLMoreResults then SQLFetchScroll works fine. Therefore I am pretty certain that it is SQLMoreResults and not SQLRowCount that cause a problem for pgODBC. also there is no other way to get the number of rows returned by select (of if it would be, it would certainly not be ODBC compliant). I emailed to Hiroshi in general about the row count, and calling SQLMoreResults is the only way, otherwise I get 1 for bulk operations. 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). Vlad On Sun, 17 May 2009 11:14 +0200, "Christophe Garault" <christophe@garault.org> wrote: > Hello Vlad, > > I'm not sure that a call to SQLRowCount is allowed after a simple SELECT. > What if you change your SELECT by an UPDATE or a DELETE ? > > -- > Christophe Garault > -- V S P toreason@fastmail.fm -- http://www.fastmail.fm - A fast, anti-spam email service.
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
Вложения
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@garault.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@fastmail.fm -- http://www.fastmail.fm - Email service worth paying for. Try it for free
V S P wrote: > Sorry forgot to mention the actual error I am getting: > > http://pastebin.com/d49e830c0 > > SQLSTATE = HY010 > NATIVE ERROR = 0 > MSG = [Microsoft][ODBC Driver Manager] Function sequence error Please do fetch operations for each result. For exmaple you can do fetch operations at *// Do fetch operations here * below. regards, Hiroshi Inoue 174./***********************************************************/ 175./* PROBLEM: */ 176./* IF YOU COMMENT THE BELOW LOOP OUT SQLFetchScroll works */ 177./***********************************************************/ 178. 179. do 180. { 181. rc = SQLRowCount(hstmt, &rpc); 182. if (rc!=SQL_SUCCESS) 183. { 184. break; 185. } 186. rowsum += rpc; // Do fetch operations here 187. rc = SQLMoreResults(hstmt); 188. } while (rc==SQL_SUCCESS);
Hi thank you, unfortunately there is no way to do that (as the OTL library relies on RowCount to be an independent function and it cannot be mixed with other operations (because then getting row count for Insert/Delete/Update would require a completely different function flow then for selects) So cannot even suggest OTL maintainer to implement this, as there is no feasable way. Is this an expected behavior (that is an ODBC spec recommends this)? thank you, Vlad On Mon, 18 May 2009 12:27 +0900, "Hiroshi Inoue" <inoue@tpf.co.jp> wrote: > V S P wrote: > > Sorry forgot to mention the actual error I am getting: > > > > http://pastebin.com/d49e830c0 > > > > SQLSTATE = HY010 > > NATIVE ERROR = 0 > > MSG = [Microsoft][ODBC Driver Manager] Function sequence error > > Please do fetch operations for each result. For exmaple you > can do fetch operations at *// Do fetch operations here * > below. > > regards, > Hiroshi Inoue > > 174./***********************************************************/ > 175./* PROBLEM: */ > 176./* IF YOU COMMENT THE BELOW LOOP OUT SQLFetchScroll works */ > 177./***********************************************************/ > 178. > 179. do > 180. { > 181. rc = SQLRowCount(hstmt, &rpc); > 182. if (rc!=SQL_SUCCESS) > 183. { > 184. break; > 185. } > 186. rowsum += rpc; > > // Do fetch operations here > > 187. rc = SQLMoreResults(hstmt); > 188. } while (rc==SQL_SUCCESS); > > -- V S P toreason@fastmail.fm -- http://www.fastmail.fm - Accessible with your email software or over the web