Hi, I'm back from vacation. I see you and Martin have been discussing my latest reported bug at length. I don't understand the full discussion, but I think I get the gist of the problem, i.e., that there's a fundamental probem in DBD::ODBC when a stored procedure performs heterogeneous SELECTs.
You questioned whether this scenario is realistic. My actual project has a stored procedure that performs a SELECT in order to decide whether to perform a subseqent INSERT or UPDATE, and then it returns a boolean result (for success/failure). I think this is a very realistic scenario. But there are multiple ways to return the boolean result (i.e., with a final SELECT or via an output parameter), and I don't know which is preferred, so I arbitrarily used a SELECT statement to return the boolean result, and that's how I encountered the bug. I can imagine many people doing this in MS-SQL. Your version 0.45_15 seems to fix my problem (at least in my small test case). Do you recommend that I use that version, or change my code to use an output parameter instead? Let me know if you need anything from me. -- Joe -----Original Message----- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 9:03 AM To: [EMAIL PROTECTED]; Jeff Urlwin Cc: [EMAIL PROTECTED]; Joe Tebelskis; Tim Bunce Subject: RE: Multiple Result sets in DBD::ODBC (and others?) > > On 20-Aug-2002 Jeff Urlwin wrote: > > I've been thinking a bit more about this. > > > > It *may* be a "reasonable" thing, for now, to: > > - "silently" eat result sets without columns (i.e. count of rows > > inserted/deleted/updated) > > Presumably, you mean here ignore a result if there are no > columns. Does this > mean you cannot actually see $stmt->rows? like in this case: > > my $stmt = $dbh->prepare("delete from table where col1 = 987654"); > $stmt->execute(); > print $stmt->rows, "\n"; > $stmt = $dbh->prepare("insert into table (col1) values(987654)"); > $stmt->execute(); > print $stmt->rows, "\n"; > > which in 0.45_15 works fine, outputting 0 and 1 the first run (no > col1=987654) > and 1 1 in the second case. I'm guessing this is still OK. Yes, this will work *unless* there is a second result set. e.g. $sth->prepare("delete from table where col1 = 987654; insert into table (col1) values (987654)"); Then, without looking at the code, I'll "declare" the $sth->rows to be undefined. (I *think* it will return the number of rows for the second one). > > > - provide multiple result sets to the user via the > odbc_more_results > > attributed > > So this works something like: > > prepare(sql) > execute > while(fetch) { > ;operate on rows/columns in result-set 1 > } > if (odbc_more_results set in statement) > while(fetch) { > ;operate on rows/columns in result-set 2 > } > > Yes? > > If so I think this is OK as it does not change anything for > scripts already out > there but adds ability to use drivers which batch sql (like > procedures in MS > SQL Server). Right -- that's what's out there now and works. In fact, see t/09multi.t and you'll see do { while (fetch) { do something } } while odbc_more_results. It's only been the "strange" cases where this has come up lately (count only rowsets from non-selects and repeated calls of execute for stored procs which have result sets) > > > - automatically, if multiple result sets are detected, > reset the columns > > every execute > > (works around problem with multiple calls of > multi-result set) > > So, you are detecting the multiple result-sets by calling > SQLMoreResults after > an Execute/Fetch has returned SQL_NO_DATA and setting odbc_more_results > statement attribute. > > Yes? > Yes, that's what's been happening. And, if I see that, I set an internal flag to re-bind the result set after execute, if you have (given that proc1 returns multiple result sets and the last result set may not have the same "shape" as the first one... $sth->prepare("{call proc1(?, ?)}"); $sth->execute(1, 2); get data $sth->execute(3, 4); > > - provide for a method for users to force re-setting > result columns > > every execute > > (for those stored procs which may only return one > result set, > > but potentially a differently "shaped" result set (i.e. 3 > > columns one time, 4 another, etc) > > Is this for the case in your test suite which does this sort of thing? > > procedure fred(int i) > { > if (i = 1) > select col1,col2 from table1 > else > select col1 from table1 > } > Yep...but also conditional inserts/updates. i.e. if (bad condition) insert into log table error message else select blah > > I think this is what DBD::ODBC does now (in my test version). > > Is this something changed since 0.45_15? The procedure I mailed > in this thread > which generated 2 results-sets with different numbers of columns > did not work > in 0.45_15. If you have any of the above changes I am as always happy > to try it out here. > I may have made changes after sending it to you and I can re-mail it. I thought you had the if (1) code working in t/20SqlServer.t...but I may have sent you a version before I fixed it completely. I did have to apply the latest unixODBC to get this working (version 2.2.3). > > I think if this is not stable enough or if I make the time to rework the > > whole thing to: > > - force odbc_more_results to actually call SQLMoreResults > (instead of > > automatically calling it and setting an attribute. This will > prevent the > > user from having to call fetch_xxx right now, which is the only > *current* > > way to trigger the call to SQLMoreResults and one of the problems I'm > > encountering. > > > > - have odbc_more_results get the column count and row > count and setup > the > > result set > > > > - if the column count is 0, then the user can detect this > rowcount only > > situation with $sth->{NUM_OF_FIELDS} and *not* call fetch_xxxx > again, but > > get $sth->rows, since it will not be an active statement (no > columns/rows to > > fetch) > > > > - if the column count is non-zero, then they can safely > call fetch_xxxx > get > > the rows > > > > - then, add the attribute odbc_ignore_non_select_result_sets (or > something > > similarly horribly named) > > > > - I think, then, for normal selects, I can keep the > notion that the $sth > > marks itself inactive when the result set has completed, but > > odbc_more_results will "reactivate" it... That may work and > may not, I'm > > just thinking out loud here.... > > > > Thus, a multiple result set loop could look like the following > (without the > > attribute to "eat" the empty rowsets: > > do { > > if ($sth->{NUM_OF_FIELDS}) { > > while (@data = $sth->fetchrow_array) { > > do something; > > } > > } else { > > # save the rows or print > > } > > } while ($sth->odbc_more_results}; > > > > if the attribute is set, the more simple loop below should work: > > do { > > while (@data = $sth->fetchrow_array) { > > do something; > > } while ($sth->odbc_more_results}; > > > > I'd make the attribute odbc_ignore_non_select_result_sets > default to "on", > > so that people using current code would not have to change unless they > > wanted to... > > > > Thoughts/comments welcome! > > I think the current set of changes (in your "what my test version already > does") will work for most situations. This second proposal is > slightly more > flexible and closer to ODBC but also seems to have the added advantage of > perhaps being tidier internally in DBD::ODBC. Yes, we agree...most cases will be served by what's in there, but some needs may arise for the latter, reworked version. > > Just a few getinfos you might find useful if you get to the > latter proposal > > SQLGetInfo(SQL_BATCH_ROW_COUNT) - does the server batch up the > inserts/updates/deletes into one count or allow them to be obtained > individually. See bitmask SQL_BRC_PROCEDURES and SQL_BRC_ROLLED_UP. Interesting, but may not be what we want to do. > > SQLGetInfo(SQL_BATCH_SUPPORT) - support for batches. > > SQLGetInfo(SQL_MULT_RESULT_SETS) - does the driver support > multiple results. These will be good, I think, as a more formal way to test to see *if* we should be doing the multi-result tests at all. Right now, I run a two statement query and if it the prepare/execute fails, I claim that multiple statements are not permitted. That seems to work, but this should be better. > > Any help I can be let me know. Of course!! Thanks! Regards, Jeff
