TRANSACTIONS: THE RETURN

Hello, This is my problem. I have to implement a transaction in some steps. I mean, the aim is to save the state of the database (Informix Online Dynamic Server 7.2). Then call some stored procedures that doesn´t commit, and if everything was ok, commit the transaction. How can I achieve this using JDBC? I have tried with: try { con.setAutoCommit(false); call ... call... call... con.commit(); }catch(Exception e) { con.rollback(); } but I receive a "Not in a transaction" exception while committing. How can I init a transaction in JDBC? Any idea would be appreciated. Please, some pseudocode.

On Mon, 08 Mar 1999 10:10:37 +0100, ASC <i52sacaa@uco.es> wrote: >Hello, > This is my problem. I have to implement a transaction in some steps. > >I mean, the aim is to save the state of the database (Informix Online >Dynamic Server 7.2). Then call some stored procedures that doesn´t >commit, and if everything was ok, commit the transaction. How can I >achieve this using JDBC? >I have tried with: > >try { >con.setAutoCommit(false); > >call ... >call... >call... > >con.commit(); >}catch(Exception e) { con.rollback(); } > >but I receive a "Not in a transaction" exception while committing. >How can I init a transaction in JDBC? >Any idea would be appreciated. Please, some pseudocode. Your database was created without transactions. Until you alter the database to support transactions, your Java code won't work. Peter Wiley

Peter Wiley wrote: > On Mon, 08 Mar 1999 10:10:37 +0100, ASC <i52sacaa@uco.es> wrote: > > >Hello, > > This is my problem. I have to implement a transaction in some steps. > > > >I mean, the aim is to save the state of the database (Informix Online > >Dynamic Server 7.2). Then call some stored procedures that doesn´t > >commit, and if everything was ok, commit the transaction. How can I > >achieve this using JDBC? > >I have tried with: > > > >try { > >con.setAutoCommit(false); > > > >call ... > >call... > >call... > > > >con.commit(); > >}catch(Exception e) { con.rollback(); } > > > >but I receive a "Not in a transaction" exception while committing. > >How can I init a transaction in JDBC? > >Any idea would be appreciated. Please, some pseudocode. > > Your database was created without transactions. Until you alter the > database to support transactions, your Java code won't work. > > Peter Wiley My database is created with logging, and if I execute some procedures (which include transaction control) from SQL Editor they work. The problem is when calling the procedures from JDBC. I have tried to execute a "BEGIN WORK" statement before, but it doesn´t work. I don't have a clue.

On Wed, 10 Mar 1999 11:05:31 +0100, ASC <i52sacaa@uco.es> wrote: > > >Peter Wiley wrote: > >> On Mon, 08 Mar 1999 10:10:37 +0100, ASC <i52sacaa@uco.es> wrote: >> >> >Hello, >> > This is my problem. I have to implement a transaction in some steps. >> > >> >I mean, the aim is to save the state of the database (Informix Online >> >Dynamic Server 7.2). Then call some stored procedures that doesn´t >> >commit, and if everything was ok, commit the transaction. How can I >> >achieve this using JDBC? >> >I have tried with: >> > >> >try { >> >con.setAutoCommit(false); >> > >> >call ... >> >call... >> >call... >> > >> >con.commit(); >> >}catch(Exception e) { con.rollback(); } >> > >> >but I receive a "Not in a transaction" exception while committing. >> >How can I init a transaction in JDBC? >> >Any idea would be appreciated. Please, some pseudocode. >> >> Your database was created without transactions. Until you alter the >> database to support transactions, your Java code won't work. >> >> Peter Wiley > > My database is created with logging, and if I execute some procedures >(which include transaction control) from SQL Editor they work. The problem is >when calling the procedures from JDBC. I have tried to execute a "BEGIN WORK" >statement before, but it doesn´t work. >I don't have a clue. Well, I'm currently using Oracle 8 and use setAutoCommit( true ); or (false ) as the case may be, with commit() every so often as I need. This definitely does work. Here's a chunk of code from one of my programs. It's not terribly pretty, as I keep hacking it about, but it definitely works. I just loaded 1,000,000+ rows with it today, committing every 1K rows. I still think it's your database but it might be the JDBC driver. Try doing a transaction using DBaccess and see if it works there. If it doesn't, you've no hope with Java. ------------------------------------------------ code --------------- try { int rowCount = 0; conn.setAutoCommit( false ); BufferedReader input = new BufferedReader( new FileReader( fin )); while (true) { lookline = input.readLine(); if (lookline == null) { messageArea.append( "EOF on file " + fin.getName() + '\\n' ); //System.out.println( "EOF on file " + fin.getName() ); break; } // // get the fields - each one is separated by a comma. the data items are // first, followed by a corresponding quality indicator for each one, except // for the timestamp which is used as the primary key field // java.util.StringTokenizer st = new java.util.StringTokenizer( lookline,"," ); try { timestmp = st.nextToken(); true_track = st.nextToken(); magnetic_track = st.nextToken(); speed_knots = st.nextToken(); speed_kmh = st.nextToken(); } catch (NoSuchElementException nse ) { // something wrong with the data string - skip messageArea.append( "LoadVTGData: data error - " + nse.toString() + '\\n' ); //nse.printStackTrace(); continue; } // // next step is to rearrange the timestamp to a form that Oracle can accept // there are better ways of doing this - see SimpleDateFormat // java.util.StringTokenizer stdate = new java.util.StringTokenizer( timestmp," " ); sday = stdate.nextToken() ; smonth = stdate.nextToken() ; syear = stdate.nextToken() ; stime = stdate.nextToken(); java.util.StringTokenizer sttime = new java.util.StringTokenizer( stime,":" ); shour = sttime.nextToken(); sminute = sttime.nextToken(); sjunk = sttime.nextToken(); // cut off the millisecs java.util.StringTokenizer split_nanos = new java.util.StringTokenizer( sjunk,"." ); ssecond = split_nanos.nextToken(); double second_val = Double.valueOf(ssecond).doubleValue(); double modulosecs = second_val % 10 ; int intSecs = (int) (second_val - modulosecs) ; int nanosecs = (int) (modulosecs * 1000000); String thisDateTime = syear + "-" + smonth + "-" + sday + " " + shour + ":" + sminute + ":" + intSecs; // correct for 0 secs which will be only 1 char; we need 2 if ( thisDateTime.length() == 18 ) thisDateTime = thisDateTime + "0" ; if ( ! last_DateTime.equals( thisDateTime ) ) { // // do database stuff // NB: all data are assumed good as we don't have a flag // //********************************************** try { prepSQL.setString( 1, thisDateTime ); prepSQL.setString( 2, setCode.trim() ); prepSQL.setString( 3, "GPS_COG" ); prepSQL.setInt( 4, nanosecs ); prepSQL.setString( 5, true_track ); prepSQL.setInt( 6, 1 ); prepSQL.execute(); prepSQL.setString( 3, "GPS_SOG" ); prepSQL.setString( 5, speed_knots ); prepSQL.setInt( 6, 1 ); prepSQL.execute(); } catch (SQLException e) { // ignore if it's a "table already exists" message, otherwise do something messageArea.append( "LoadVTGData: SQL error - " + e.toString() + '\\n' ); e.printStackTrace(); } //**********************************************/ last_DateTime = thisDateTime; rowCount++; if ( (rowCount % 1000) == 0 ) { conn.commit(); messageArea.append( rowCount + " rows written to database" + '\\n'

Please see the setAutoCommit(boolean) method of Connection objects. The informix engine must be doing logging (try ontape -c) otherwise setAutoCommit will throw an SQL Exception. HTH Charles ASC wrote in message <36E643EB.4BD9B786@uco.es>... > My database is created with logging, and if I execute some procedures >(which include transaction control) from SQL Editor they work. The problem is >when calling the procedures from JDBC. I have tried to execute a "BEGIN WORK" >statement before, but it doesn't work. >I don't have a clue. >