Trouble with Informix JDBC 3.00 and Java

Forum Dear Colleagues: I have a problem with Java and JDBC Informix. I am developing a Java application in a window that displays a table of Informix, and has buttons to add records, modify and delete them. What is often said an ABM. The data is a ResultSet which is accessed by a class that is the model and the events controlled by another class that would be the control. Now if I choose either option, the updated data in the Informix database, but not in the ResulSet, and therefore not in the table, which is aesthetically appalling and very messy for the operator. What confuses me is that using the same code with MySQL, it works perfectly as the data changes are reflected immediately in the table. I hope some of you have to program in Java and is found in the same situation to see if I can solve it. I send a greeting and a thank you for your attention! Gustavo Echenique

Hi, we need more info to give an answer: What type of logging is used, what isolation level ? Is the modified data committed explicitely and then the ResultSet processed completely new ? Maybe code fragments can help. Marcus > Forum Dear Colleagues: > > I have a problem with Java and JDBC Informix. I am developing a Java > application in a window that displays a table of Informix, and has buttons to > add records, modify and delete them. What is often said an ABM. The data is a > ResultSet which is accessed by a class that is the model and the events > controlled by another class that would be the control. > > Now if I choose either option, the updated data in the Informix database, but > not in the ResulSet, and therefore not in the table, which is aesthetically > appalling and very messy for the operator. > > What confuses me is that using the same code with MySQL, it works perfectly as > the data changes are reflected immediately in the table. > > I hope some of you have to program in Java and is found in the same situation > to see if I can solve it. > > I send a greeting and a thank you for your attention! > > Gustavo Echenique > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi Marcus!, thanks for replying. You're right, maybe some code with a better understanding of the problem. The following is part of the controller class: import java.util.Observable; import java.sql.*; import javax.swing.*; import com.informix.jdbc.*; import java.io.*; import java.io.FileWriter; import java.io.PrintWriter; //Estas dos últimas importaciones son para el trace del JDBC public class ConexionMotor{ public void cargarDriver() throws ClassNotFoundException{ try{ Class.forName("com.informix.jdbc.IfxDriver"); //System.out.println("Se conectó perfectamente con el Informix JDBC driver"); } catch(ClassNotFoundException excepcion){ JOptionPane.showMessageDialog(null, "ERROR: falló la carga del Informix JDBC driver. \\ " + excepcion.getMessage(), "Atención", JOptionPane.INFORMATION_MESSAGE); return; } } public void crearConexion(String nombreMaquina, String nombreBase, String nombreInstancia, String nombreUsuario, String passwordUsuario) throws SQLException{ try{ connection = DriverManager.getConnection("jdbc:informix-sqli://" + nombreMaquina + ":1526/" + nombreBase + ":INFORMIXSERVER=" + nombreInstancia + ";user=" + nombreUsuario + ";password=" + passwordUsuario); //connection = DriverManager.getConnection("jdbc:informix-sqli://" + nombreMaquina + ":1526/" + nombreBase + ":INFORMIXSERVER=" + nombreInstancia + ";user=" + nombreUsuario + ";password=" + passwordUsuario + ";TRACE=3;TRACEFILE=C:\\\\\\\\tmp\\\\\\\\traza.log;PROTOCOLTRACE=2;PROTOCOLTRACEFILE=C:\\\\\\\\tmp \\\\\\\\trace.out"); connection.setAutoCommit(true); //connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); //Acá hacemos el trace del JDBC //JDBC trace to file FileWriter fwTrace = new FileWriter("c:\\\\\\\\tmp\\\\\\\\JDBCTrace.log"); PrintWriter pwTrace= new PrintWriter(fwTrace); DriverManager.setLogWriter(pwTrace); } catch(SQLException excepcion){ JOptionPane.showMessageDialog(null, "ERROR: No se pudo establecer la conexión con la base. \\ " + excepcion.getMessage(), "Atención", JOptionPane.INFORMATION_MESSAGE); return; } catch(Exception e){ JOptionPane.showMessageDialog(null, "ERROR: No se pudo cargar el trazador de JDBC. \\ " + e.getMessage(), "Atención", JOptionPane.INFORMATION_MESSAGE); return; } } public ResultSet generarResultSet(String sentenciaPasada) throws SQLException{ //aquí la variable "sentencia" pasada como parámetro, debe ser del tipo SQL. //este método se utiliza solamente para los queries comunes (que no involucran INSERT, UPDATE y DELETE), es decir, para los queries que generarán el ResultSet try { resultado = null; sentenciaPreparada = null; metaResultado = null; sentenciaPreparada = connection.prepareStatement(sentenciaPasada, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); resultado = sentenciaPreparada.executeQuery(); return resultado; } catch(SQLException excepcion){ JOptionPane.showMessageDialog( null, "ERROR: fallo la ejecución de la sentencia. \\ " + excepcion.getMessage(), "Atención", JOptionPane.INFORMATION_MESSAGE); //System.out.println("ERROR: " + e.getMessage()); return null; } } ----- What follows below is the model class: import java.awt.*; import java.awt.event.*; import javax.swing.*; import javax.swing.event.*; import javax.swing.table.*; import java.sql.*; import java.util.*; public class ModeloTablaMarcas extends AbstractTableModel implements TableModel, TableModelListener{ public ModeloTablaMarcas(){ conexion = new ConexionMotor(); try{ conexion.cargarDriver(); } catch(ClassNotFoundException cnfe){ JOptionPane.showMessageDialog(null,"Error al cargar el Driver JDBC de Informix:" + cnfe.getMessage(), "Atención",JOptionPane.INFORMATION_MESSAGE ); } try{ conexion.crearConexion( "proliant", "pruebas", "ol_develop", "informix", "informix"); } catch(SQLException sqle){ JOptionPane.showMessageDialog(null,"Error de lenguaje SQL al intentar la conexión:" + sqle.getMessage(), "Atención", JOptionPane.INFORMATION_MESSAGE ); } try{ resultado = conexion.generarResultSet("SELECT idmarca, descripcion FROM \\\\"informix\\\\".garr_marcas ORDER BY 1"); } catch(SQLException sqle){ JOptionPane.showMessageDialog(null,"Error de lenguaje SQL al ejecutar la consulta:" + sqle.getMessage(),"Atención", JOptionPane.INFORMATION_MESSAGE ); } try{ metaResultado = conexion.obtenerMetaDatos(resultado); } catch(SQLException sqle){ JOptionPane.showMessageDialog(null,"Error de lenguaje SQL al obtener MetaDatos:" + sqle.getMessage(), "Atención", JOptionPane.INFORMATION_MESSAGE ); } JOptionPane.showMessageDialog(null,resultado.toString() +"\\ " + "Cantidad de Filas:" + getRowCount() + "\\ " + "Cantidad de Columnas:" + getColumnCount(), "Atención", JOptionPane.INFORMATION_MESSAGE ); suscriptores = new LinkedList(); } public void addTableModelListener (TableModelListener oyente) { suscriptores.add (oyente); JOptionPane.showMessageDialog(null, "Se ha agregado un nuevo oyente de eventos. \\ En este momento hay: " + suscriptores.size(),"JDBC-Atención", JOptionPane.INFORMATION_MESSAGE); } public void removeTableModelListener (TableModelListener oyente) { suscriptores.remove(oyente); } public void setValueAt(Object datos, int fila, int columna){ ; } public Object getValueAt( int fila, int columna ){ try{ if(resultado.isBeforeFirst()){ resultado.first(); } else if(resultado.isAfterLast()){ resultado.last(); } resultado.absolute(fila+1); return resultado.getObject(columna+1); } catch(SQLException sqle){ //System.out.println(resultado.getObject(columna+1)); JOptionPane.showMessageDialog( null, "Error de lenguaje SQL al obtener el valor de la celda:" + sqle.getMessage(), "Atención-getValueAt", JOptionPane.INFORMATION_MESSAGE); return null; } } public boolean isCellEditable( int fila, int columna ){ return false; } public Class getColumnClass( int clase ){ try{ return Class.forName(metaResultado.getColumnClassName(clase+1)); } catch(ClassNotFoundException cnfe){ JOptionPane.showMess

Hi Gustavo, I cannot see from the code where the update/insert/delete takes place and what happens afterwards. You have autoCommit set, which should result in an immediaty flush of data in the DB. Check out this page http://www.exampledepot.com/egs/java.sql/RefreshRow.html Also check if updateable ResultSets are supported by your server version. Good luck, Marcus -----Original Message----- From: GUSTAVO ECHENIQUE [mailto:gustavo.echenique@cemdo.com.ar] Sent: Monday, September 26, 2011 1:13 AM To: ids@iiug.org Subject: Re: Trouble with Informix JDBC 3.00 and Java [25027] Hi Marcus!, thanks for replying. You're right, maybe some code with a better understanding of the problem. The following is part of the controller class: import java.util.Observable; import java.sql.*; import javax.swing.*; import com.informix.jdbc.*; import java.io.*; import java.io.FileWriter; import java.io.PrintWriter; //Estas dos ltimas importaciones son para el trace del JDBC public class ConexionMotor{ public void cargarDriver() throws ClassNotFoundException{ try{ Class.forName("com.informix.jdbc.IfxDriver"); //System.out.println("Se conect perfectamente con el Informix JDBC driver"); } catch(ClassNotFoundException excepcion){ JOptionPane.showMessageDialog(null, "ERROR: fall la carga del Informix JDBC driver. \\ " + excepcion.getMessage(), "Atencin", JOptionPane.INFORMATION_MESSAGE); return; } } public void crearConexion(String nombreMaquina, String nombreBase, String nombreInstancia, String nombreUsuario, String passwordUsuario) throws SQLException{ try{ connection = DriverManager.getConnection("jdbc:informix-sqli://" + nombreMaquina + ":1526/" + nombreBase + ":INFORMIXSERVER=" + nombreInstancia + ";user=" + nombreUsuario + ";password=" + passwordUsuario); //connection = DriverManager.getConnection("jdbc:informix-sqli://" + nombreMaquina + ":1526/" + nombreBase + ":INFORMIXSERVER=" + nombreInstancia + ";user=" + nombreUsuario + ";password=" + passwordUsuario + ";TRACE=3;TRACEFILE=C:\\\\\\\\tmp\\\\\\\\traza.log;PROTOCOLTRACE=2;PROTOCOLTRACEFILE =C:\\\\\\\\tmp\\\\\\\\trace.out"); connection.setAutoCommit(true); //connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_R EAD); //Ac hacemos el trace del JDBC //JDBC trace to file FileWriter fwTrace = new FileWriter("c:\\\\\\\\tmp\\\\\\\\JDBCTrace.log"); PrintWriter pwTrace= new PrintWriter(fwTrace); DriverManager.setLogWriter(pwTrace); } catch(SQLException excepcion){ JOptionPane.showMessageDialog(null, "ERROR: No se pudo establecer la conexin con la base. \\ " + excepcion.getMessage(), "Atencin", JOptionPane.INFORMATION_MESSAGE); return; } catch(Exception e){ JOptionPane.showMessageDialog(null, "ERROR: No se pudo cargar el trazador de JDBC. \\ " + e.getMessage(), "Atencin", JOptionPane.INFORMATION_MESSAGE); return; } } public ResultSet generarResultSet(String sentenciaPasada) throws SQLException{ //aqu la variable "sentencia" pasada como parmetro, debe ser del tipo SQL. //este mtodo se utiliza solamente para los queries comunes (que no involucran INSERT, UPDATE y DELETE), es decir, para los queries que generarn el ResultSet try { resultado = null; sentenciaPreparada = null; metaResultado = null; sentenciaPreparada = connection.prepareStatement(sentenciaPasada, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); resultado = sentenciaPreparada.executeQuery(); return resultado; } catch(SQLException excepcion){ JOptionPane.showMessageDialog( null, "ERROR: fallo la ejecucin de la sentencia. \\ " + excepcion.getMessage(), "Atencin", JOptionPane.INFORMATION_MESSAGE); //System.out.println("ERROR: " + e.getMessage()); return null; } } ----- What follows below is the model class: import java.awt.*; import java.awt.event.*; import javax.swing.*; import javax.swing.event.*; import javax.swing.table.*; import java.sql.*; import java.util.*; public class ModeloTablaMarcas extends AbstractTableModel implements TableModel, TableModelListener{ public ModeloTablaMarcas(){ conexion = new ConexionMotor(); try{ conexion.cargarDriver(); } catch(ClassNotFoundException cnfe){ JOptionPane.showMessageDialog(null,"Error al cargar el Driver JDBC de Informix:" + cnfe.getMessage(), "Atencin",JOptionPane.INFORMATION_MESSAGE ); } try{ conexion.crearConexion( "proliant", "pruebas", "ol_develop", "informix", "informix"); } catch(SQLException sqle){ JOptionPane.showMessageDialog(null,"Error de lenguaje SQL al intentar la conexin:" + sqle.getMessage(), "Atencin", JOptionPane.INFORMATION_MESSAGE ); } try{ resultado = conexion.generarResultSet("SELECT idmarca, descripcion FROM \\\\"informix\\\\".garr_marcas ORDER BY 1"); } catch(SQLException sqle){ JOptionPane.showMessageDialog(null,"Error de lenguaje SQL al ejecutar la consulta:" + sqle.getMessage(),"Atencin", JOptionPane.INFORMATION_MESSAGE ); } try{ metaResultado = conexion.obtenerMetaDatos(resultado); } catch(SQLException sqle){ JOptionPane.showMessageDialog(null,"Error de lenguaje SQL al obtener MetaDatos:" + sqle.getMessage(), "Atencin", JOptionPane.INFORMATION_MESSAGE ); } JOptionPane.showMessageDialog(null,resultado.toString() +"\\ " + "Cantidad de Filas:" + getRowCount() + "\\ " + "Cantidad de Columnas:" + getColumnCount(), "Atencin", JOptionPane.INFORMATION_MESSAGE ); suscriptores = new LinkedList(); } public void addTableModelListener (TableModelListener oyente) { suscriptores.add (oyente); JOptionPane.showMessageDialog(null, "Se ha agregado un nuevo oyente de eventos. \\ En este momento hay: " + suscriptores.size(),"JDBC-Atencin", JOptionPane.INFORMATION_MESSAGE); } public void removeTableModelListener (TableModelListener oyente) { suscriptores.remove(oyente); } public void setValueAt(Object datos, int fila, int columna){ ; } public Object getValueAt( int fila, int columna ){ try{ if(resultado.isBeforeFirst()){ resultado.first(); } else if(resultado.isAfterLast()){ resultado.last(); } resultado.absolute(fila+1); return resultado.get

Hello Marcus! Again thank you for your answer. You know, the method "refreshRow ()" does not support the JDBC driver. I tried the following code: try { modelo.resultado.moveToInsertRow (); modelo.resultado.updateInt (1, type); modelo.resultado.updateString (2, description); modelo.resultado.insertRow (); modelo.resultado.moveToCurrentRow (); modelo.resultado.updateRow (); And I do not throw any error but does not refresh data in the table. However, the data is stored in the Informix database. With the interface DefaultTableModel I could do that work, but I do not like this interface because it has the most, if not all synchronized methods, and also to all the data interface are pure objects. That's why AbstractTableModel implement the interface for my project. May you help me a bit more. I send my sincere greetings. Gustavo Echenique

Hello Marcus! Again thank you for your answer. You know, the method "refreshRow ()" does not support the JDBC driver. I tried the following code: try { modelo.resultado.moveToInsertRow (); modelo.resultado.updateInt (1, tipo); modelo.resultado.updateString (2, description); modelo.resultado.insertRow (); modelo.resultado.moveToCurrentRow (); modelo.resultado.updateRow (); And I do not throw any error but does not refresh data in the table. However, the data is stored in the Informix database. With the interface DefaultTableModel I could do that work, but I do not like this interface because it has the most, if not all synchronized methods, and also to all the data interface are pure objects. That's why AbstractTableModel implement the interface for my project. May you help me a bit more. I send my sincere greetings. Gustavo Echenique

Hi Gustavo, I am sorry, cannot help you more because I have no further experience with updatable ResultSets. Maybe there is a workaround, but I cannot advice. According to JDBC Doc refreshRow() is not supported, same for rowDeleted/rowInserted/rowUpdated. At least I found a passage in http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.j dbc_pg.doc/sii-03query-26048.htm where this is described. Newer versions might not have this restriction, check the release notes. Marcus -----Original Message----- From: GUSTAVO ECHENIQUE [mailto:gustavo.echenique@cemdo.com.ar] Sent: Monday, September 26, 2011 7:08 PM To: ids@iiug.org Subject: Re: RE: Re: Trouble with Informix JDBC 3.00 and Ja [25033] Hello Marcus! Again thank you for your answer. You know, the method "refreshRow ()" does not support the JDBC driver. I tried the following code: try { modelo.resultado.moveToInsertRow (); modelo.resultado.updateInt (1, tipo); modelo.resultado.updateString (2, description); modelo.resultado.insertRow (); modelo.resultado.moveToCurrentRow (); modelo.resultado.updateRow (); And I do not throw any error but does not refresh data in the table. However, the data is stored in the Informix database. With the interface DefaultTableModel I could do that work, but I do not like this interface because it has the most, if not all synchronized methods, and also to all the data interface are pure objects. That's why AbstractTableModel implement the interface for my project. May you help me a bit more. I send my sincere greetings. Gustavo Echenique ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.