-710 trying to alter just-created tables
Hi Folks,
We're using IDS.2000 9.20.UC1 on RedHat Linux 6.2 from a Java program
and Informix's JDBC driver version 2.11. The program creates a database
(via a connection to the database server itself, i.e. no database name
specified in JDBC URL), gets a new connection to it, creates some
tables, then tries to add some constraints. This last step throws an
exception with error number -710. Reading the documentation doesn't give
me any hints as to why this happens. The SQL is all OK, since I tried
running it in dbaccess without any problems. (Note: I'm not using any
prepared statements, just dynamic generation and execution of SQL.) I
saw some posts that suggested reconnecting to the database after the
creation - is this the best solution? And isn't this a bug? Thanks for
any help!
matt
In article <3A26B483.C2BC85C2@cs.umass.edu>,
Matthew Cornell <cornell@cs.umass.edu> wrote:
> Hi Folks,
>
> We're using IDS.2000 9.20.UC1 on RedHat Linux 6.2 from a Java program
> and Informix's JDBC driver version 2.11. The program creates a
database
> (via a connection to the database server itself, i.e. no database name
> specified in JDBC URL), gets a new connection to it, creates some
> tables, then tries to add some constraints. This last step throws an
> exception with error number -710. Reading the documentation doesn't
give
> me any hints as to why this happens. The SQL is all OK, since I tried
> running it in dbaccess without any problems. (Note: I'm not using any
> prepared statements, just dynamic generation and execution of SQL.) I
> saw some posts that suggested reconnecting to the database after the
> creation - is this the best solution? And isn't this a bug? Thanks for
> any help!
>
> matt
>
I ran finderr(a handy utility which should be on your box.)
here is a snip of the output.
--
$ finderr 710-710 Table <table-name> has been dropped, altered or renamed.
This error can occur with explicitly prepared statements. These
statements
have the following form:
PREPARE statement id FROM quoted string
After a statement has been prepared in the database server and before
execution of the statement, a table to which the statement refers might
have been renamed or altered, possibly changing the structure of the
table. Problems might occur as a result. Adding an index to the table
after preparing the statement can also invalidate the statement. A
subsequent OPEN command for a cursor will fail if the cursor refers to
the invalid prepared statement; the failure will occur even if the OPEN
command has the WITH REOPTIMIZATION clause.
--
My guess would be everything was prepared before it was executed. Just
a guess. Try doing your prepares right before the execution of each
statement. If all else fails post the code, I am sure someone will be
able to help.
Hope this helps,
Will
Sent via Deja.com http://www.deja.com/
Before you buy.
Thanks for the pointer to finderr. I was using the html file that lists
them all. Regarding prepared statements, I'm not using any explicitly.
However, I notice that the driver or the engine creates some for me...
Not sure if this helps solve the problem, though.
> $ finderr 710> -710 Table <table-name> has been dropped, altered or renamed.
...
>
> My guess would be everything was prepared before it was executed. Just
> a guess. Try doing your prepares right before the execution of each
> statement. If all else fails post the code, I am sure someone will be
> able to help.
I created a small java program that demonstrates the problem. I'm
including it here in case it helps. If you read the comments you'll see
that after creating a table, the second ALTER TABLE causes the -710
error. Ugh! BTW, I'm not using any JDBC PreparedStatements, but the
error message description talks about them...
matt
/*
Purpose: Demonstrates getting a -710 error.
Usage: Edit the values in connect() and run the program, making sure to
DROP
"testdb" if necessary (e.g., $ echo "DROP DATABASE testdb" | dbaccess ).
Requirements:
OS: RedHat Linux 6.2
Informix engine: Informix Dynamic Server 2000 Version 9.20.UC1
JDK: java version "1.3.0"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0)
Classic VM (build 1.3.0, J2RE 1.3.0 IBM build cx130-20000815 (JIT
enabled: jitc))
JDBC Driver: Informix 2.11
Results: I get error -710 when I run it:
main(): SQLState, Severity, Message: IX000, -710, null
java.sql.SQLException
...
at InformixTest.main(InformixTest.java:41)
Remarks:
o The error occurs in addConstraints() when the second ALTER TABLE is
run. If
you comment out the section between the comments "--> ok to here" and
"--> fails here" you do *not* see the error!
o I tried two work-arounds, neither of which worked. They are
commented out in createDB().
o When I run the corresponding SQL separately in dbaccess, there are no
errors. Here's the SQL:
DROP DATABASE testdb;
CREATE DATABASE testdb IN workdbs;
CONNECT TO 'testdb';
CREATE TABLE attribute (
id SERIAL NOT NULL,
item_type CHAR(1) NOT NULL,
data_type VARCHAR(8) NOT NULL,
table_name VARCHAR(8) NOT NULL,
name VARCHAR(64) NOT NULL);
ALTER TABLE attribute
ADD CONSTRAINT PRIMARY KEY (name) CONSTRAINT attr_pk;
ALTER TABLE attribute
ADD CONSTRAINT CHECK (id >= 0) CONSTRAINT attr_id_check;
*/
import java.sql.*;
public class InformixTest {
// no instance vars
static {
try {
String driver = "com.informix.jdbc.IfxDriver";
Class.forName(driver);
} catch(Exception exc) {
System.err.println("error loading driver: " + exc);
}
}
public static void main(String[] args) {
try {
new InformixTest();
} catch(SQLException sqlExc) {
printSQLException("main(): Failed", sqlExc);
sqlExc.printStackTrace();
}
}
InformixTest() throws SQLException {
String dbName = "testdb";
createDB(dbName);
System.out.println("Worked!");
}
public void addConstraints(Connection connection) throws SQLException {
StringBuffer sqlSB = new StringBuffer();
sqlSB.append("ALTER TABLE attribute\\n\\t");
sqlSB.append(sqlForConstraint("PRIMARY KEY (name)", "attr_pk"));
sqlSB.append(";\\n\\n");
// --> ok to here
sqlSB.append("ALTER TABLE attribute\\n\\t");
sqlSB.append(sqlForConstraint("CHECK (id >= 0)", "attr_id_check"));
sqlSB.append(";\\n\\n");
// --> fails here
Statement stmt = connection.createStatement();
stmt.execute(sqlSB.toString());
stmt.close();
}
Connection connect(String dbName) throws SQLException {
String host = "localhost";
String port = "3000";
String server = "local_tcp";
String url = "jdbc:informix-sqli://" + host + ":" + port +
(dbName == null ? "" : "/" + dbName) + ":INFORMIXSERVER=" +
server + ";";
Connection connection = DriverManager.getConnection(url);
return connection;
}
public void createDB(String dbName) throws SQLException {
// create db
createDBProper(dbName); // doesn't create tables
// create tables
Connection connection = connect(dbName);
createTables(connection);
/*
// TEST1: try calling UPDATE STATISTICS. result: doesn't fix problem
Statement stmt = connection.createStatement();
stmt.execute("UPDATE STATISTICS");
stmt.close();
*/
/*
// TEST2: try reconnecting. result: doesn't fix problem
connection.close();
connection = connect(dbName);
*/
// add constraints
addConstraints(connection);
// create indexes
//createIndexes(connection);
// finish
connection.close();
}
public void createDBProper(String dbName) throws SQLException {
String dbspace = "workdbs";
Connection connection = connect(null); // nb: null dbName -> connect
to server
Statement stmt = connection.createStatement();
String query = "CREATE DATABASE " + dbName + " IN " + dbspace;
stmt.execute(query);
stmt.close();
connection.close();
}
public void createTables(Connection connection) throws SQLException {
StringBuffer sqlSB = new StringBuffer();
sqlSB.append("CREATE TABLE attribute (\\n");
sqlSB.append(" id SERIAL NOT NULL,\\n");
sqlSB.append(" item_type CHAR(1) NOT NULL,\\n");
sqlSB.append(" data_type VARCHAR(8) NOT NULL,\\n");
sqlSB.append(" table_name VARCHAR(8) NOT NULL,");
sqlSB.append(" name VARCHAR(64) NOT NULL);\\n\\n");
Statement stmt = connection.createStatement();
stmt.execute(sqlSB.toString());
stmt.close();
}
public static void printSQLException(String prefix, SQLException
sqlExc) {
String message = prefix + ": SQLState, Severity, Message: " +
sqlExc.getSQLState() + ", " + sqlExc.getErrorCode() + ", " +
sqlExc.getMessage();
System.out.println(message);
if(sqlExc.getNextException() != null)
printSQLException(prefix, sqlExc.getNextException());
}
public String sqlForConstraint(String constraint, String name) {
return "ADD CONSTRAINT " + constraint + " CONSTRAINT " + name;
}
}
// EOF
After opening the connection to the database, try executing
SET LOCK MODE TO WAIT;
HTH,
Paul Tilles
Matthew Cornell wrote:
> I created a small java program that demonstrates the problem. I'm
> including it here in case it helps. If you read the comments you'll see
> that after creating a table, the second ALTER TABLE causes the -710
> error. Ugh! BTW, I'm not using any JDBC PreparedStatements, but the
> error message description talks about them...
>
> matt
>
> /*
> Purpose: Demonstrates getting a -710 error.
>
> Usage: Edit the values in connect() and run the program, making sure to
> DROP
> "testdb" if necessary (e.g., $ echo "DROP DATABASE testdb" | dbaccess ).
>
> Requirements:
>
> OS: RedHat Linux 6.2
>
> Informix engine: Informix Dynamic Server 2000 Version 9.20.UC1
>
> JDK: java version "1.3.0"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0)
> Classic VM (build 1.3.0, J2RE 1.3.0 IBM build cx130-20000815 (JIT
> enabled: jitc))
>
> JDBC Driver: Informix 2.11
>
> Results: I get error -710 when I run it:
>
> main(): SQLState, Severity, Message: IX000, -710, null
> java.sql.SQLException
> ...
> at InformixTest.main(InformixTest.java:41)
>
> Remarks:
>
> o The error occurs in addConstraints() when the second ALTER TABLE is
> run. If
> you comment out the section between the comments "--> ok to here" and
> "--> fails here" you do *not* see the error!
>
> o I tried two work-arounds, neither of which worked. They are
> commented out in createDB().
>
> o When I run the corresponding SQL separately in dbaccess, there are no
> errors. Here's the SQL:
>
> DROP DATABASE testdb;>
> CREATE DATABASE testdb IN workdbs;>
> CONNECT TO 'testdb';
>
> CREATE TABLE attribute (
> id SERIAL NOT NULL,
> item_type CHAR(1) NOT NULL,
> data_type VARCHAR(8) NOT NULL,
> table_name VARCHAR(8) NOT NULL,
> name VARCHAR(64) NOT NULL);>
> ALTER TABLE attribute
> ADD CONSTRAINT PRIMARY KEY (name) CONSTRAINT attr_pk;>
> ALTER TABLE attribute
> ADD CONSTRAINT CHECK (id >= 0) CONSTRAINT attr_id_check;>
> */
>
> import java.sql.*;
>
> public class InformixTest {
>
> // no instance vars
>
> static {
> try {
> String driver = "com.informix.jdbc.IfxDriver";
> Class.forName(driver);
> } catch(Exception exc) {
> System.err.println("error loading driver: " + exc);
> }
> }
>
> public static void main(String[] args) {
> try {
> new InformixTest();
> } catch(SQLException sqlExc) {
> printSQLException("main(): Failed", sqlExc);
> sqlExc.printStackTrace();
> }
> }
>
> InformixTest() throws SQLException {
> String dbName = "testdb";
> createDB(dbName);
> System.out.println("Worked!");
> }
>
> public void addConstraints(Connection connection) throws SQLException {
> StringBuffer sqlSB = new StringBuffer();
> sqlSB.append("ALTER TABLE attribute\\n\\t");
> sqlSB.append(sqlForConstraint("PRIMARY KEY (name)", "attr_pk"));
> sqlSB.append(";\\n\\n");
> // --> ok to here
> sqlSB.append("ALTER TABLE attribute\\n\\t");
> sqlSB.append(sqlForConstraint("CHECK (id >= 0)", "attr_id_check"));
> sqlSB.append(";\\n\\n");
> // --> fails here
> Statement stmt = connection.createStatement();
> stmt.execute(sqlSB.toString());
> stmt.close();
> }
>
> Connection connect(String dbName) throws SQLException {
> String host = "localhost";
> String port = "3000";
> String server = "local_tcp";
> String url = "jdbc:informix-sqli://" + host + ":" + port +
> (dbName == null ? "" : "/" + dbName) + ":INFORMIXSERVER=" +
> server + ";";
> Connection connection = DriverManager.getConnection(url);
> return connection;
> }
>
> public void createDB(String dbName) throws SQLException {
> // create db
> createDBProper(dbName); // doesn't create tables
> // create tables
> Connection connection = connect(dbName);
> createTables(connection);
> /*
> // TEST1: try calling UPDATE STATISTICS. result: doesn't fix problem
> Statement stmt = connection.createStatement();
> stmt.execute("UPDATE STATISTICS");
> stmt.close();
> */
> /*
> // TEST2: try reconnecting. result: doesn't fix problem
> connection.close();
> connection = connect(dbName);
> */
> // add constraints
> addConstraints(connection);
> // create indexes
> //createIndexes(connection);
> // finish
> connection.close();
> }
>
> public void createDBProper(String dbName) throws SQLException {
> String dbspace = "workdbs";
> Connection connection = connect(null); // nb: null dbName -> connect
> to server
> Statement stmt = connection.createStatement();
> String query = "CREATE DATABASE " + dbName + " IN " + dbspace;
> stmt.execute(query);
> stmt.close();
> connection.close();
> }
>
> public void createTables(Connection connection) throws SQLException {
> StringBuffer sqlSB = new StringBuffer();
> sqlSB.append("CREATE TABLE attribute (\\n");
> sqlSB.append(" id SERIAL NOT NULL,\\n");
> sqlSB.append(" item_type CHAR(1) NOT NULL,\\n");
> sqlSB.append(" data_type VARCHAR(8) NOT NULL,\\n");
> sqlSB.append(" table_name VARCHAR(8) NOT NULL,");
> sqlSB.append(" name VARCHAR(64) NOT NULL);\\n\\n");
> Statement stmt = connection.createStatement();
> stmt.execute(sqlSB.toString());
> stmt.close();
> }
>
> public static void printSQLException(String prefi