I agree that your "insane" example is unjustifiable, and I wouldn't complain if you choose to disregard it. I don't think I would ever use "odbc_force_rebind".
> Now -- a question for you: would you *like* to know how many rows were > affected by the inserts/updates inside your stored procs or would you prefer > to ignore them as now..? I have never yet needed to know how many rows were affected by any insert/update, and I don't expect I ever will, unless I were to see a good example of when it's really useful. -- Joe -----Original Message----- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 21, 2002 6:21 PM To: Joe Tebelskis; Jeff Urlwin; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Tim Bunce Subject: RE: Multiple Result sets in DBD::ODBC (and others?) > > 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. Yes, I think so -- and it's because, partly, that I try to determine for you (after a "unsuccessful" fetch finishing a result set) if there are more result sets. Unfortunately, that notion really has some issues with row-count only result sets. Hence, some of the code introduced recently > > 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. Yes -- it's not the "reality" that I was really questioning, it's the sanity of what *could* happen, i.e. select int1, int2, blob from table if (param = 2) then select char1, char2, char3 from table else select blob3, int2, char4 from table That, to me, is insane... then again, I'm more of an Oracle person than a SQL Server person, so this is strange to me. I agree that it's fairly normal to select to determine what's there, then update or insert as necessary. Sometimes that's faster than just trying to insert and wait for the failure. The other difference w/SQL Server is that it returns the result sets (plural), whereas Oracle would only return the result set at the end (normally, although you *can* find ways to do this) Additionally, I had a bug where calling that stored proc a second time would cause things to fail because I wasn't re-checking the output columns. That should be fixed as I now, automatically reset the result columns after execute if I detect multiple result sets and repeated execution of the same sql statement. That's just sanity, actually, but it doesn't catch all the cases. Hence the new attribute "odbc_force_rebind" which forces a re-bind of the output columns after each execute. That's for the situation where you do something like this (hypothetical): if (param_is_type_a) then select varchar1, int2, blob3 from table; else select int3, blob4 from table; (i.e. each call to the procedure *may* return a different "shape" result set, but I can't detect it, since it's ONLY returning one result set per execute. > > 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. Well -- I would suggest that if it fixes your problem, let's move on and I'll keep making it work. Besides, I'm getting the feeling that if I get it working for you flawlessly, then I'll get it working right overall :) Now -- a question for you: would you *like* to know how many rows were affected by the inserts/updates inside your stored procs or would you prefer to ignore them as now..? Regards, Jeff > > > > > > 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 > > >
