Results 1 to 15 of 15
  1. #1
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default jdbc: invalild column index from statement.execute():

    Hi,
    I have been unable to insert a row of data into the oracle db. The problem seems to be with stmt.setObject(int index, Object o). Here is the code:


    Connection conn= new Connection(myDB);
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO AG_GPS_CURRENT VALUES (402128, 0, 0, 0, TO_DATE(?,'DD-MM-YYYY') , 'test'," +
    " 0, 0 , 0, 0, 0, TO_DATE(?,'DD-MM-YYYY') , 0, 0, 0, 0, 0," +
    " 'kin', 'wilm', 'de', '19801', TO_DATE(?,'DD-MM-YYYY'),0, TO_DATE(?,'DD-MM-YYYY')," +
    "'a','a','a','a','a','a','a','a','a','a','a','a',? ) ");

    JGeometry j3= new JGeometry(3,3,0,3416);
    STRUCT obj = JGeometry.store(j3, conn);

    String date= "10-06-2011";
    stmt.setObject(5, date);
    stmt.setObject(12, date);
    stmt.setObject(22, date);
    stmt.setObject(24, date);
    stmt.setObject(37, obj);
    stmt.execute(); //excuteUpdate():

    Thanks
    Last edited by ernestcronin; 06-23-2011 at 08:47 PM.

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

    Default

    Word of advice, a table with that many columns is suggestive that you should learn a bit about database normalization. Next, read about PreparedStatements, as you set the value incrementally.
    Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

  3. #3
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default

    Thanks,
    I didn't create the tables- they are the parameters of my employer's db- I just have to learn to use them.
    Here is the solution for any other possible viewers:
    Connection conn= new Connection(myDB);
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO AG_GPS_CURRENT VALUES (402128, 0, 0, 0, TO_DATE(?,'DD-MM-YYYY') , 'test'," +
    " 0, 0 , 0, 0, 0, TO_DATE(?,'DD-MM-YYYY') , 0, 0, 0, 0, 0," +
    " 'kin', 'wilm', 'de', '19801', TO_DATE(?,'DD-MM-YYYY'),0, TO_DATE(?,'DD-MM-YYYY')," +
    "'a','a','a','a','a','a','a','a','a','a','a',' a',? ) ");

    JGeometry j3= new JGeometry(3,3,0,3416);
    STRUCT obj = JGeometry.store(j3, conn);

    String date= "10-06-2011";
    stmt.setObject(1, date);
    stmt.setObject(2, date);
    stmt.setObject(3, date);
    stmt.setObject(4, date);
    stmt.setObject(5, obj);
    stmt.execute(); //excuteUpdate():

  4. #4
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    read doWhile's link, it will answer your question

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    I would suggest converting the date String to a java.sql.Date before assigning to the PreparedStatement, and remove the TO_DATE calls. Far less work for the db, since it has no conversions to do then, and Java will only have one (rather than none for Java and 4 for the db).

  6. #6
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default

    Tolls, I am now in testing and have discovered that your suggestion is worth considering. First, my application does 50 insertions/s with 56% of the time in the Oracle database. If it will speed up the process to convert to a java.sql.Date object, then i shall do this. However, I have a more pressing issue. My application will do approximately 81000-83000 conversions/insertions until 'invalid index column' occurs here:

    else if(otc.getROW().get(j).getKey().equals("CENTROID_G EOM")){
    STRUCT obj = (STRUCT)aRow.get(j);
    pstmt.setObject(++count, obj); //HERE

    Here is the other confusing thing- I rerun the program and it does the insertion as if an error was never thrown! The column index is correct. The STRUCT is correct. Memory usage is constant. Are there any suggestions or ideas as to what could be the culprit?

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Could be your count is getting out of synch somehow?
    Are you batching these or simply executing one at a time?
    There's not a great deal to go on here, to be honest.

  8. #8
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default

    I understand. The count is correct- a simple print-to-screen indicates this. The same problem occurs whether batched or not.

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Stick a try/catch around that statement so you can catch the exception straight away and log everything.
    The value of count, the text of the prepared statement, the exception and stack trace, and anything else you can think of and then rethrow the exception so the code will continue as though you hadn't caught it. Get everything in there that you can, seeing as it's such a rare occurrence. Never know what minor variable is the one causing the trouble.

  10. #10
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default

    Okay, I'll try that. I'm going to trouble you with one more thing in regards to your first suggestion about converting to sql.date.

    else if(type.equals("TIMESTAMP")){
    System.out.println(data);
    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH24:mm:ss.SSS");
    java.util.Date parsedUtilDate = formater.parse((String) data);
    java.sql.Timestamp sqlDate= new java.sql.Timestamp(parsedUtilDate.getTime());
    query+= key + " = " + "'" +sqlDate + "', ";

    I get this error:
    2011-07-25 15:27:45.000
    Exception in thread "main" java.text.ParseException: Unparseable date: "2011-07-25 15:27:45.000"

    Is it irrelevant that the string is a representation of microsoft sequel date and the new date is going into Oracle?

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    HH is hours in the day, not HH24.
    Java Code:
    query+= key + " = " + "'" +sqlDate + "', ";
    I hope you're not concatenating your query variables in like that.
    You have a prepared statement so should be using setDate().

  12. #12
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default

    I am trying to implement your suggestion- 'I would suggest converting the date String to a java.sql.Date before assigning to the PreparedStatement, and remove the TO_DATE calls'.

    Databasing is not my area of expertise, so please forgive my ignorance. I'm quite sure what you mean. I have to build one long query.

    I didn't want to inundate you with code, but here is my insert method (non-batched):

    public void insertNewRowInCurrent(ArrayList<Object> aRow) throws SQLException{

    otc.addToTable(aRow);

    String query="INSERT INTO " + odc.getOutput_CurrentTable() + " (ID, ";
    String query2 = " VALUES( " + ++currentRow + ", ";
    PreparedStatement pstmt;
    String key="";
    Object data;
    String type="";
    int count=0;



    for(int i= 0; i< otc.getROW().size(); i++){ //getRow = a mapping of the outgoing db
    key = otc.getROW().get(i).getKey(); //key = the col name of outgoing db
    type = otc.getROW().get(i).getType(); //the type of value in a given col
    data= aRow.get(i); //aRow = a row of data to be inserted

    if(key.equals("CENTROID_GEOM")){

    query+= key +")";
    query2+= " ?)";

    }

    else if(type.equals("NUMBER")){
    query+= key + ", ";
    query2+= data.toString() + ", ";
    }

    else if(type.equals("VARCHAR2")){
    query+= key + ", ";
    query2+= "'"+data.toString()+"'" + ", ";
    }
    else if(type.equals("TIMESTAMP")){
    query+= key + ", ";
    query2+= " TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS.ff3'), ";
    }
    }//end i loop
    query+=query2;
    pstmt = odc.getConn().prepareStatement(query);
    for(int j= 0; j<otc.getROW().size(); j++){
    if(otc.getROW().get(j).getType().equals("TIMESTAMP ")){
    String date = (String)aRow.get(j);
    pstmt.setObject(++count, date);

    }

    else if(otc.getROW().get(j).getKey().equals("CENTROID_G EOM")){
    STRUCT obj = (STRUCT)aRow.get(j);
    pstmt.setObject(++count, obj);
    }
    }
    pstmt.execute();
    pstmt.close();
    }

    if i can speed up the application by doing the timestamp conversion 'on the java side', then i'll happily make those changes.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Can you use code tags?
    That's difficult to read without them.

    Anyway, all those variables should be bound to the statement. That is, they should be done as you are doing for CENTROID_GEOM, placing a ? and then later setting them using set<whatever appropriate setter> on the prepared statement. So that first loop shouldn't care what the type of the object is, it should simply build the insert up using:
    Java Code:
    for(int i= 0; i< otc.getROW().size(); i++){ //getRow = a mapping of the outgoing db
        key = otc.getROW().get(i).getKey(); //key = the col name of outgoing db
        query+= ", " + key +"?";
    }

  14. #14
    ernestcronin is offline Member
    Join Date
    Jun 2011
    Posts
    7
    Rep Power
    0

    Default

    Thank you very much for your time and help. Its much appreciated.

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    You might want to print out the query as well, just to make sure it looks correct.
    Can't guarantee I've got the above exactly right...commas and brackets are the usual suspects.

Similar Threads

  1. Replies: 2
    Last Post: 01-07-2011, 11:50 AM
  2. Jdbc Prepared Statement execute()
    By nitishjtm in forum JDBC
    Replies: 37
    Last Post: 12-16-2010, 08:43 AM
  3. Replies: 0
    Last Post: 07-24-2009, 08:58 AM
  4. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-20-2009, 11:53 PM
  5. Replies: 0
    Last Post: 07-11-2008, 04:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •