Teradata CLOB and JDBC : The Locator is invalid because it has been changed

Teradata Applications
Enthusiast

Teradata CLOB and JDBC : The Locator is invalid because it has been changed

I'm trying to copy CLOB from one database to another in Java. However I get  "The Locator is invalid because it has been changed" error.
This article says this can happen because ResultSet, PreparedStamenent are closed. However all my objects are open. And the error is thrown when I try to executeBatch()

 

THIS IS JAVA CODE. You can copy paste it to eclipse to test it 

Statement select = connectionDB1.createStatement(); ResultSet rs = select.executeQuery("SELECT TOP 1 myClob FROM myTab"); rs.next(); PreparedStatement ps = connectionDB2.prepareStatement("INSERT INTO myTab2 SELECT ? "); ps.setClob(1, rs.getClob(1)); System.out.println("CLOB inserted"); ps.addBatch(); ps.executeBatch();   // ERROR is thrown here before I close any objects WHY?                                  // java.sql.BatchUpdateException

Why does this happen? How to prevent this?


Accepted Solutions
Teradata Employee

Re: Teradata CLOB and JDBC : The Locator is invalid because it has been changed

 Based on your code and what Tom said, here is a sample using Reader.

    Statement select = connectionDB1.createStatement(); 
    try
    {
        ResultSet rs = select.executeQuery("SELECT TOP 1 myClob FROM myTab"); 
        try
        {
            rs.next(); 
            PreparedStatement ps = connectionDB2.prepareStatement("INSERT INTO myTab2 (myClob) VALUES(?)");
            try
            {
                Clob clob = rs.getClob(1);
                try
                {
                    ps.setCharacterStream (1, clob.getCharacterStream (), clob.length ()); 
                    System.out.println("CLOB inserted");
                    ps.addBatch();
                    ps.executeBatch();   // ERROR is thrown here before I close any objects WHY? 
                                         // java.sql.BatchUpdateException
                }
                finally
                {
                    clob.free () ;
                }
            }
            finally
            {
                ps.close () ;
            }
        }
        finally
        {
            rs.close () ;
        }
    }
    finally
    {
        select.close () ;
    }
1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Teradata CLOB and JDBC : The Locator is invalid because it has been changed

Your code excerpt shows that you are using two different JDBC Connection objects.

 

java.sql.Blob and java.sql.Clob objects are only usable with a single Connection. You cannot obtain a Blob or Clob object from one Connection and use it with a different Connection.

 

If you want to copy Blob or Clob data from one Connection to another, you will need to obtain an InputStream (from Blob.getBinaryStream) or a Reader (from Clob.getCharacterStream) and use that object to insert the data into the destination Connection via PreparedStatement.setBinaryStream (to insert data to a BLOB column) or PreparedStatement.setCharacterStream (to insert data to a CLOB column).

 

Unlike Blob and Clob objects, an InputStream or a Reader can carry data from one JDBC Connection to another.

Enthusiast

Re: Teradata CLOB and JDBC : The Locator is invalid because it has been changed

Thank you. I couldn't make it work with a reader. Could you please post a sample code
Teradata Employee

Re: Teradata CLOB and JDBC : The Locator is invalid because it has been changed

 Based on your code and what Tom said, here is a sample using Reader.

    Statement select = connectionDB1.createStatement(); 
    try
    {
        ResultSet rs = select.executeQuery("SELECT TOP 1 myClob FROM myTab"); 
        try
        {
            rs.next(); 
            PreparedStatement ps = connectionDB2.prepareStatement("INSERT INTO myTab2 (myClob) VALUES(?)");
            try
            {
                Clob clob = rs.getClob(1);
                try
                {
                    ps.setCharacterStream (1, clob.getCharacterStream (), clob.length ()); 
                    System.out.println("CLOB inserted");
                    ps.addBatch();
                    ps.executeBatch();   // ERROR is thrown here before I close any objects WHY? 
                                         // java.sql.BatchUpdateException
                }
                finally
                {
                    clob.free () ;
                }
            }
            finally
            {
                ps.close () ;
            }
        }
        finally
        {
            rs.close () ;
        }
    }
    finally
    {
        select.close () ;
    }
Enthusiast

Re: Teradata CLOB and JDBC : The Locator is invalid because it has been changed

Thank you. This solution works