Conversion into SQL Result Clob from a String

[Azrael Noor]

I am not using any prepared statement

i am using Simple Query

Query can take 4000 Characters of String at one time

I have data over 10 thousand characters

I take an array of 4000's and put query process in loop

I feel i am going on wrong side.

I tried to used Oracle Resultset before but unable to get result through it, i am unable to get any other example over Google search


I am using JDK 6, Oracle Database

try
        {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                String ra = "YAAHUUUUUU";
	        Connection gConn = DriverManager.getConnection("jdbc:oracle:thin:@192.18.5.3:121:qa", "en", "en123");
	        String sqlStr="select response_data from service_request_log where fk_status_id = 101 and request_type = 'Inbound'";
                System.out.println(""+ra);
	        ResultSet rsSOAPLog= gConn.createStatement().executeQuery(sqlStr);
	        if(rsSOAPLog.next())
	        {
        		oracle.sql.CLOB inputClob = ((OracleResultSet)rsSOAPLog).getCLOB("response_data");
                        System.out.println(""+ra);
	        	inputClob.setString(1,ra);
	        	sqlStr = "UPDATE service_request_log set remarks = '' where pk_servicerequest_id = '4349'";
                        System.out.println(""+ra);
                        PreparedStatement pstmt= gConn.prepareStatement(sqlStr);
	        	pstmt.setClob(1,inputClob);
	        	pstmt.executeUpdate();
	        	System.out.println("Cheek 7");
	        	pstmt.close();
	        }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

[Wendy L Gibbons]

using the search
java sql insert clob oracle I found this:
http://stackoverflow.com/questions/5549450/java-how-to-insert-clob-into-oracle-database

[Azrael Noor]

For All Examples i am getting the same

also got same before when i previously tried the, but due to time lapse i inserted logic mentioned in first post.

java.sql.SQLException: Unsupported feature
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
	at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
	at oracle.sql.CLOB.setString(CLOB.java:1148)
	at javaapplication2.DBConnection.main(DBConnection.java:42)

Could you please tell how to resolve this?

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                Connection gConn = DriverManager.getConnection("jdbc:oracle:thin:@192.18.5.3:121:va", "en", "en123");
                PreparedStatement pstmt= gConn.prepareStatement("UPDATE service_request_log set remarks = ? where pk_servicerequest_id = '4349'");
                java.sql.Clob clob = oracle.sql.CLOB.createTemporary(gConn, false, oracle.sql.CLOB.DURATION_SESSION);
                clob.setString(1, "Yahoooooooooooooo");
                pstmt.setClob(1, clob);
                pstmt.execute();

[Martin Vashko]

I've described how I've implemented inserting BLOBs into Oracle database some time ago here:
https://coderanch.com/forums/posts/list/513175#2322695
https://coderanch.com/forums/posts/list/518509#2347822

It should be easy to modify it to store CLOBs instead of BLOBs, but if you still have problems, feel free to ask again. Note that this solution is Oracle specific. There should be a database-agnostic way to put LOBs into database, but I didn't need that yet so I've only used this (Oracle specific) one.

[Azrael Noor]

I am looking onto your example

i now picked OJDBC6 Jar file, i think i was using old one OBDBC14

Compiling Same code and error changed

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                String ra = "Yahuuuu";
                Connection gConn = DriverManager.getConnection("jdbc:oracle:thin:@192.18.5.3:121:va", "en", "en123");
	        String sqlStr="select response_data from service_request_log where fk_status_id = 101 and request_type = 'Inbound'";
                System.out.println(""+ra);
	        ResultSet rsSOAPLog= gConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sqlStr);
	        if(rsSOAPLog.next())
	        {
                    oracle.sql.CLOB inputClob = ((OracleResultSet)rsSOAPLog).getCLOB("response_data");
                    PreparedStatement pstmt= gConn.prepareStatement("UPDATE service_request_log set remarks = ? where pk_servicerequest_id = '4349'");

                    inputClob.setString(1, "ehehehehehehe"); //<=---- Error Here
                    System.out.println("eheyehey");
                    pstmt.setClob(1,inputClob);
                    pstmt.executeUpdate();
                    System.out.println("Cheek 7");
	        	
	        }

java.sql.SQLException: ORA-22920: row containing the LOB value is not locked

	at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
	at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:406)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8TTILob.receiveReply(T4C8TTILob.java:903)
	at oracle.jdbc.driver.T4C8TTIClob.write(T4C8TTIClob.java:436)
	at oracle.jdbc.driver.T4CConnection.putChars(T4CConnection.java:2708)
	at oracle.sql.CLOB.putChars(CLOB.java:364)
	at oracle.sql.CLOB.setString(CLOB.java:825)
	at javaapplication2.DBConnection.main(DBConnection.java:32)

If anyone get this please suggest

[Martin Vashko]

Change your sqlStr variable to this:

String sqlStr="select response_data from service_request_log where fk_status_id = 101 and request_type = 'eMaven-Inbound' for update";  

The "for update" clause will lock the row. It is necessary condition in Oracle to lock row before you modify the LOB; it is documented somewhere. It was not in my code, because there the row gets locked when it is updated.

[Azrael Noor]

Martin Martin Martin Martin





Thanks It work Successfully Now

JAR: OJDBC6
JDK: 1.6
Code:

try
        {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            Connection gConn = DriverManager.getConnection("jdbc:oracle:thin:@192.18.5.13:121:va", "en", "en123");
            String sqlString="SELECT response_data FROM service_request_log WHERE fk_status_id = 101 AND request_type = 'Inbound' FOR UPDATE";
            ResultSet rsSOAPLog= gConn.createStatement().executeQuery(sqlString);
            
            if(rsSOAPLog.next())
            {
                oracle.sql.CLOB inputClob = ((OracleResultSet)rsSOAPLog).getCLOB("response_data");
                inputClob.setString(1, "STRING DATA NEED TO CONVERTED TO CLOB DATA");
                
                PreparedStatement pstmt= gConn.prepareStatement("UPDATE service_request_log SET response_data = ? where pk_servicerequest_id = '4349'");
                pstmt.setClob(1,inputClob);
                pstmt.executeUpdate();
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

Is their any other approach to achieve this?

[Martin Vashko]

You're welcome!

The approaches I posted before are very similar to what you've got now. There might be slight performance saving with them if you're uploading large CLOBs, or lots and lots of small ones. Otherwise it is probably not worth the hassle, if you're satisfied with your current solution.

[Azrael Noor]

In Present the data is appending to data which i am retrieving in first query.
Just looking around to empty CLOB object.

I will also look on your examples, and make through them too and ask you again on them, if any problem still persists

[Azrael Noor]

The Driver is working fine is updating data for 30 Thousand characters.

I am now implementing the above like this

 
public CLOB stringtoClob(String stringData) 
{
    	logDebugInfo("In stringToClob() function");
        CLOB clob = null;
        try {
            clob = CLOB.createTemporary(gConn, false, CLOB.DURATION_SESSION);
            clob.setString(1, stringData);
        } catch (SQLException sqlException) {
            setExceptionMessage(sqlException.getMessage(), sqlException.toString());
            logErrorInfo("Exception thrown in stringtoClob function : SQL Exception : " + sqlException);
        }

        return clob;
}

public boolean insertServiceRequestLog(String requestData) 
    {
        logDebugInfo("In insertServiceRequestLog() function");
        PreparedStatement objPreparedStatementConnection = null;
        String currentExceptionTime = "";
        String errorExceptionMessage = "";

        String sqlQuery = "";

        CLOB objClob = stringtoClob(requestData);

        if (!getSequenceNextVal("SEQ_SERVICE_REQUEST_LOG")) {
            return false;
        }

        sqlQuery =
                "INSERT INTO "
                + "Service_Request_Log"
                + "(Pk_ServiceRequest_ID, "
                + "remarks) "
                + "VALUES("
                + "'" + gPrimaryIDSequenceNumber + "',"
                + " ?)";
        
        try 
        {
            objPreparedStatementConnection = gConn.prepareStatement(sqlQuery);
            objPreparedStatementConnection.setClob(1, objClob);
            objPreparedStatementConnection.executeUpdate();
        } 
        catch (SQLException errorException) 
        {
            setExceptionMessage(errorException.getMessage(), errorException.toString());
            exceptionProcessing(gLastExeceptionTime, gLastExceptionMessge, currentExceptionTime, errorExceptionMessage, errorException.toString(), convertStringToInt(gErrorTimeOut), true);
            return false;
        } 
        catch (Exception errorException) 
        {
            setExceptionMessage(errorException.getMessage(), errorException.toString());
            logErrorInfo("In executeUpdate function : Exception thrown in executeUpdate function: Unable to insert/update in database" + errorException);
            return false;
        }
        return true;
    }