SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-15-2008, 11:56 PM
| ||||
| ||||
 JDBC support for CALL / PERFORM Moved from the INTERFACES list. Kris Jurka wrote: > On Mon, 13 Feb 2006, Guy Rouillier wrote: > >> I have some Java code that I'm trying to convert from Oracle to PG. >> This code uses the JDBC batch functionality to submit batches of >> stored procedures invocations using the "call" syntax. I implemented >> the same stored functions in PG, having them return void. I >> converted the batch statements to use "select" with these stored >> functions. Even though the stored functions return void, the select >> is still producing a result set, and JDBC does not allow results >> with batches. >> >> I'd like to take a crack at adding CALL (for Oracle and general JDBC >> compatibility) and/or PERFORM (for PL/SQL compatibility) to the JDBC >> driver. My approach would be to simply substitute SELECT, then >> discard the result set upon completion. > > You shouldn't need to do anything other than discard the results > instead of erroring for CallableStatement batches. You shouldn't do > any messing with the SQL and CALL or PERFORM. This should be handled > by the standard {call } syntax. > > Also JDBC messages should go to the jdbc list, > pgsql-jdbc@postgresql.org. > > Kris Jurka Ok, thanks. I haven't begun to look at the code yet, but the general approach will be (1) Execute each statement in the batch one at a time. (2) If a particular statement encounters this one error (result not allowed in batch), and if the statement is a callable statement, then absorb the error, discard the result set and continue. (3) Otherwise (not this particular error or not a callable statement), then allow the error to propagate as currently implemented. The reason I started out with the idea of introducing CALL and/or PERFORM is that a non-trivial amount of Java code working with Oracle uses SQL strings that begin "call ...", i.e., not an escaped call but an actual Oracle-proprietary SQL call. While doing as I suggested make converting such code easier, I understand that introducing one kludge to accommodate another is probably not a great idea. Better to have the programmer go back and fix the original kludge. -- Guy Rouillier ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend      |
|
04-15-2008, 11:56 PM
| ||||
| ||||
| Re: JDBC support for CALL / PERFORM On Mon, 13 Feb 2006, Guy Rouillier wrote: > > Ok, thanks. I haven't begun to look at the code yet, but the general > approach will be > > (1) Execute each statement in the batch one at a time. > (2) If a particular statement encounters this one error (result not > allowed in batch), and if the statement is a callable statement, then > absorb the error, discard the result set and continue. > (3) Otherwise (not this particular error or not a callable statement), > then allow the error to propagate as currently implemented. > All you should have to modify is org.postgresql.jdbc2.AbstractJdbc2Statement.BatchR esultHandler.handleResultRows to not throw an Exception if you've got a CallableStatement. Kris Jurka ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match |
Linear Mode
