Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    3

    Question Unanswered: Using Java JDBC with MS SQL to set IDENTITY_INSERT on BEFORE an insert.

    Hi,

    I'm getting an error upon executing my code. I have a table which has an identity column in it. However the table is currently empty and I want to transfer the data from an Oracle database into the SQL Server database. This is all very easy. However...

    I need to run the following command before I can add the data.

    "set identity_insert tablename on"

    However, upon execution I get the following error - how can I use the prepared statement AND set the tables identity_insert value to on?

    java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid call Statement method: {0}

    The code that does this process is below... in this case you can assume setIdentOn = true.

    String insertSQL = "insert into " + currentTable + "(" + columns + ") values(" + values + ")";

    PreparedStatement insert = msconn.prepareStatement(insertSQL);
    try {
    if(setIdentOn) {
    mslog("Setting IDENTITY_INSERT \"" + currentTable + "\" ON");
    insert.execute("SET IDENTITY_INSERT " + currentTable + " ON");
    }
    insert.clearParameters();

    for(int i=0;i<cOrder.size();i++) {
    Object obj = data.get((String)cOrder.elementAt(i));

    if(obj instanceof String) insert.setString(i+1, (String) obj);
    else if(obj instanceof Integer) insert.setInt(i+1, ( (Integer) obj).intValue());
    else if(obj instanceof Float) insert.setFloat(i+1, ((Float)obj).floatValue());
    else if(obj instanceof Double) insert.setDouble(i+1, ((Double)obj).doubleValue());
    else if(obj instanceof Character) insert.setString(i+1, String.valueOf(((Character)obj).charValue()));
    else if(obj instanceof Timestamp) insert.setTimestamp(i+1, (Timestamp)obj);
    else if(obj instanceof java.sql.Date) insert.setDate(i+1, (java.sql.Date)obj);
    else if(obj instanceof NULL) {
    int DATA_TYPE = getSQLType(currentTable, (String)cOrder.elementAt(i));
    switch(DATA_TYPE) {
    case Types.BLOB: {
    insert.setBinaryStream(i+1, new ByteArrayInputStream(new byte[0], 0, 0), 0);
    break;
    }
    case Types.CLOB: {
    insert.setCharacterStream(i+1, new CharArrayReader(new char[0], 0, 0), 0);
    break;
    }
    default: {
    insert.setNull(i+1, DATA_TYPE);
    }
    }
    }
    else if(obj instanceof Blob) {
    Blob blob = (Blob)obj;
    insert.setBinaryStream(i+1, blob.getBinaryStream(), (int)blob.length());
    }
    else if(obj instanceof Clob) {
    Clob clob = (Clob)obj;
    insert.setCharacterStream(i+1, clob.getCharacterStream(), (int)clob.length());
    }
    else mslog("Object not bound at location " + (i+1));
    }
    return insert.executeUpdate();
    }
    catch(Exception e) {
    System.err.println("FAILED ON: " + insertSQL);
    throw e;
    }
    finally {
    // try { statement.close(); } catch(Exception e) { }
    try { insert.close(); } catch(Exception e) { }
    }

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Using Java JDBC with MS SQL to set IDENTITY_INSERT on BEFORE an insert.

    Microsoft has also a problem with the IDENTITY_INSERT, see Microsoft Knowledge Base Article - 253157, UG: Identity Field Remains Read-Only After Executing SET IDENTITY_INSERT ON Statement. It doesn't answer your question, but gives you an indication, that the SQL Server may have problems with that, if even Microsoft can't just fix that bug.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    May 2003
    Posts
    3

    Red face Re: Using Java JDBC with MS SQL to set IDENTITY_INSERT on BEFORE an insert.

    Ok... 1 day later... lots of stress. Fixed it, it was exceedingly simple. Aren't they always.

    Instead of using the insert.execute("set identity_insert " + currentTable + " on"); separately what I have done is incorporated it all in the same SQL statement. Like so...

    String insertSQL = (setIdentOn ? "set identity_insert " + currentTable + " on " : "") + "insert into " + currentTable + "(" + columns + ") values(" + values + ")" + (setIdentOn ? "set identity_insert " + currentTable + " off " : "");

    I probably don't need to turn off the identity_insert for the table after the insert has executed, but it works now. Hoorah!

    Thank heavens for that.

    Now on to simulating an oracle on before insert trigger... argh.

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Using Java JDBC with MS SQL to set IDENTITY_INSERT on BEFORE an insert.

    Thank you, Steve, to let us know.

    You are playing save to set IDENTITY_INSERT off, since if the table would stay in the ON mode, it does not generate IDs for other applications, and you could get an error, trying to turn the setting ON, which is already ON.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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