RE: Trouble with JDBC/PreparedStatements

To extend Jonathan's answer ... You don't need to keep it in StringBuffer all the time - convert to String for store until after you've actually prepare'd it. initialise() { String prep_statement[ n ] = StringBuffer_prep_statement.toString(); } use_prep_statement( int n ) { if( prep_statement[ n ].length() ) { prepare_query prep_statement[ n ] = ""; } execute_query } Textbook-wise - it depends on circumstances. Set up frequent use statements early, set up low use statements - and variable structure statements - late. Note the semantic difference between prepare'ing and setting up. Using i4gl I'd prepare the statements as soon as they are set up, but i4gl is single threaded. Java is multi-threaded. Use your debugger to check your thread stack - I suspect that your initialisation is establishing a connection in a separate thread, but not waiting until the connection is complete. Is there some way of checking the connection is complete? (What does the manual say?) -----Original Message----- From: Jonathan Leffler [mailto:jleffler@informix.com] Sent: 02 February 2001 00:13 To: informix-list@iiug.org Subject: Re: Trouble with JDBC/PreparedStatements Frederik Ramm wrote: > I'm using IDS2000 9.21.UC2 for Linux and the Informix JDBC driver. > My program creates 9 "PreparedStatements" upon startup, for later use. > I've noted strange behaviour when using these - a prepared SELECT > that returns only 3 rows when there are 5 in the database, and a > prepared INSERT that puts a value set with setInt into the wrong > column, for example. > > I've replaced the statements in question with a code sequence that > builds the literal query in a StringBuffer and then executes it > via a "normal" statement - works perfectly. Pass; I don't know about this. > I've then moved the connection.prepare() calls from the initialization > routine to the places I'm actually using the prepared statements - > i.e. now I'm doing prepare-execute-prepare-execute-prepare-execute > instead of prepare-execute-execute-execute. This works perfectly > as well. > > Is it possible that the JDBC driver cannot handle more than X > PreparedStatements simultaneously, with X <10? Conceivable, but unlikely. It should report errors on the later prepared statements at the time the statements are prepared if it cannot manage them; it should not go around breaking or invalidating previously prepared statements. > Textbook-wise, > preparing all your statements at the beginning of the program > seems sensible, doesn't it? If you're eager to do as much work as possible during program startup rather than defer it until it is actually necessary to use it, then yes, you can prepare everything at startup. Well, nearly everything -- any statements that depend on temporary tables that do not exist yet (or permanent tables, or views, come to that) have to be prepared after the table is created. Personally, I'd rather have the program get going and prepare each statement using JIT technology - just in time. That way, you don't spend time preparing 50 statements and only using 3 of them. Traditionally, what I do in I4GL is have a function to handle the SQL statement. If the function has not been called before, then it prepares the statement or cursor and records the fact that it has been prepared. Then it can use the statement or cursor as required. One big advantage is that if the SQL is never needed, it is never seen by the server, which increases the startup speed of the application. The other big advantage is that the information about the statement's existence is localized to just the function(s) that need to work on it, not to a startup function as well. This means there is less maintenance of the startup function -- it doesn't have to be changed just because some extra SQL statement became necessary somewhere in the system. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"