aspose file tools*
The moose likes JDBC and the fly likes DB2 Insert Problem - Invalid Table State Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB2 Insert Problem - Invalid Table State" Watch "DB2 Insert Problem - Invalid Table State" New topic
Author

DB2 Insert Problem - Invalid Table State

Shane Parker
Greenhorn

Joined: Dec 14, 2004
Posts: 2
Hi All,

I am trying to insert some records into a table using the Universal JDBC Driver. When I go to execute the prepared statement I get the following error:

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -7008, SQLSTATE: 55019, SQLERRMC: TABLE1 ;LIBRARY ;3

SQL State 55019 is 'The table is in an invalid state for the operation.' From the info I've found, this problem seems to relate to journaling. The physical file I want to insert records into is not journaled (and I don't want it to be). A proposed solution has been to turn off commitment control (ie. use Connection.setAutoCommit(false)) but this doesn't work either.


Example code that gives the error:

//...set up url, username, password.

Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
Connection con = DriverManager.getConnection(url,username,password);

con.setAutoCommit(false);

String SQL = "insert into LIBRARY.TABLE1 values(?,?)";
PreparedStatement ps = con.prepareStatement(SQL);

ps.setString(1,"A");
ps.setString(2,"B");
ps.executeUpdate(); // Fails on this line.
con.commit();


I have used similar code for select statements and they work fine. I only get the problem for insert/update/delete.

Can someone please help?
Dharamvir (DV) Punia
Ranch Hand

Joined: Oct 04, 2004
Posts: 34
Hi,
You might want to check the datatype of your columns in DB2, make sute its "varchar", you will get this error if there is conflict with data types meaning your setting in your string in your prepared statemnent as shown below:
ps.setString(1,"A");
ps.setString(2,"B");
But one of them might not be a proper data type in db2, that's why it will go in an invalid state, if you still have problems, post you Table ddl for you Libaray.table1, i will take a look at it. What version of db2 you are using?

You might also want to specify in your insert statements:
INSERT INTO LIBRARY.TABLE1(DB2_FIELD1, DB2_FIELD2) VALUES(?,?)

If still doesn't work then try the following statement , just to see if you can insert it:
INSERT INTO LIBRARY.TABLE1 (DB2_FIELD1, DB2_FIELD2) VALUES('A','B')
At least this will give you an idea that there is nothing wrong with the datatypes in db2.

Let me know if it helps.
-DV
Shane Parker
Greenhorn

Joined: Dec 14, 2004
Posts: 2
Thanks for your reply Dharamvir.

I have checked that the datatypes are all the correct type (ie. CHARACTER). I am actually trying to update a table in DB2 running on an iSeries (V5R2). When I try the insert from the interactive SQL prompt I have no problems.

ie. INSERT INTO LIBRARY.TABLE1 (FIELDA,FIELDB) VALUES('A',B')

But when I try exactly the same statement from my Java program it fails (with the error mentioned previously).

I'm almost certain this is do with Journaling on the iSeries. If I associate the physical file I'm trying to insert into with a journal, it all works fine. But I need to somehow do inserts into tables (ie. physical files) that are not journaled.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61064
    
  66

"Shane P",

We're pleased to have you here with us on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
Don't get me started about those stupid light bulbs.
 
subject: DB2 Insert Problem - Invalid Table State