"No more data to read from socket" error when updating a CLOB - Java - JDBC (Archived)

Hi, I've come a across a problem in a web application that has have me stumped for the past couple hours. A user submits data through a web form and it goes into a table column of type CLOB. But this doesn't work all the time - sometimes the error "No more data to read from socket" is given and the insert or update fails. There doesn't seem to be any problem with the character data that is being entered. The size is irrelevant, I've read and written up to 30 KB of data with no problem using the web form. Here is the stack trace of when the error occurs:java.sql.SQLException: No more data to read from socket         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)         at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)         at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)         at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)         at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369)         at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)         at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)         at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)         at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)         at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)The update is performed by writing the String data to a temporary CLOB using a Writer and binding that to an OraclePreparedStatement. Here is the fragment of code that is doing the update:try {     (code to get DB connection goes here)     conn.setAutoCommit(false);     String query = "update...";     OraclePreparedStatement pstmt = null;     pstmt = (OraclePreparedStatement)conn.prepareStatement(query);         oracle.sql.CLOB tempClob = oracle.sql.CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);     tempClob.open(CLOB.MODE_READWRITE);     Writer tempClobWriter = ((oracle.sql.CLOB)tempClob).getCharacterOutputStream();     tempClobWriter.write(data);     tempClobWriter.flush();     tempClobWriter.close();     tempClob.close();     pstmt.setCLOB(1, tempClob);         pstmt.executeUpdate();     conn.commit();     conn.close(); } catch (SQLException e) {     e.printStackTrace(); }Most of what I have above was taken from an example from OTN and has worked fine for the most part, but not today. I am using Oracle 9i and the JDBC drivers that came with it (ojdbc14.jar) and Java SDK 1.4.2. I'm using the thin driver, but using the oci driver hasn't made a difference. I've also tried using the putString method of the CLOB class instead of the method above, but the same exception and error gets thrown when I call it. Any ideas would be appreciated :) 

Upon further investigation, I found the root cause of this problem is a java.io.IOException thrown by the call to the flush() or the close() function of the tempClobWriter.
Otherwise, I'm still stumped. 

Ok, I found out this may have something to do with the data being inserted.
I used a third party Java library that has an HTML encoder (which I've used before and works great) to encode the data into its HTML equivalent when I write, and decode the data when I read. Everything works fine now.
I'll consider the issue solved but I still have no idea what the specific cause was. 

Hi, I got the same error. Dose anyone have new issue? 

Hi,
my name is Diana. I've the same problem. My code about CLOB insert is:
String oldContent = "";
Clob cContent = rs.getClob("CONTENT");
if (cContent != null && cContent.length() > 0) oldContent = cContent.
getSubString(1, (int) rs.getClob("CONTENT").length());
if (oldContent != null && oldContent.length() > 0) {
StringReader sr = new StringReader(oldContent);
ps.setCharacterStream(3, sr, oldContent.length());
}
else ps.setNull(3, Types.LONGVARCHAR);
but, when it executes update instruction, it doesn't work well. The exception is:
com.ibm.websphere.ce.cm.StaleConnectionException: No more data to read from socket
This instruction is in a transaction of an EJB Session. In an other application that uses EJB Entities the same instruction seems to work well.
Can you help me, please?
best regards,
Diana 

Hi,
I ahve the same problem - but, I am facing this problem when I am trying to establish the connection.
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/hr");
Connection conn = ds.getConnection();
Any help ?? I am using Tomcat and Oracle 10g. 

user603864,
You probably need an Oracle "Connection" and not a Tomcat "Connection".
I believe there is a way to extract the Oracle "Connection" which is wrapped in the Tomcat "Connection", but since I don't use Tomcat, I don't know how to do that.
Good Luck,
Avi.

Related

Missing IN or OUT parameter at index:: 1

I'll bet this is a simple question for the gurus I usually see on these Java forums, but here goes anyway: I am getting the following error using a PreparedStatement even though I am setting all the question marks. I am trying to insert a file. Here is the error:java.sql.SQLException: Missing IN or OUT parameter at index:: 1Here is the code:String query = "insert into ECLS_DOCUMENT (DOC_ID, RELEASE, VERSION," + " RESPONSE_ID, BLOCK_ID, DOC_NAME, DOCUMENT, CREATED_BY, CREATED_DATE)" + " values ('1',1,1,'12',1,?,?,'Jeff',sysdate)"; try { Connection Conn = JDBCConnection.getConnection(); PreparedStatement pst = Conn.prepareStatement(query); File file = new File("C:\\jss\\receipts\\AUSTRALIA v1.0.doc"); java.io.InputStream inStream = new java.io.FileInputStream(file); pst.setString(1, file.getName()); pst.setBinaryStream(2, inStream, (int) file.length()); pst.execute(); inStream.close(); pst.close(); } catch (Exception e) { e.printStackTrace(); }Many thanks in advance. -Jeff
My best guess is that isn't actually the code that is getting executed.
Maybe there is a cached instance that is being run instead of that. 
The problem seems to be something like you suggest.
It has something to do with my db driver and debugging the code. When we restarted the server, it worked correctly. We think the hangup was caused somehow while we were debugging the code (like maybe we interrupted it in the middle of acquiring a connection or something).
It was wierd, but it's gone now.
Thanks,
-Jeff 
You probably missed on of the binding parameters 
You probably missed on of the binding parametersAnd that statement is wrong about the code that was posted.
And the follow up post from the OP made it much more obvious that the code posted is not what ran. 
Thanks a lot for this post. I had same issue with hot redeploy.... (restarting the server made everything work fine)

Function sequence error

Hi Chris, we where using this Java code with TimesTen 5.1.34:
while (rs.next()) {
associatedMsbs.add(Integer.toString(rs.getInt(HGROUPID)));
// Prepare object to insert in database
SvcLog_VO svcLog = new SvcLog_VO();
svcLog.setLogId(svcLogDAO.getlogIdNextVal(conn));
svcLog.setService( CommonConstants.MobileSwitchboardSvcId );
svcLog.setOperType( CommonConstants.OPER_TYPE_UPDREL );
svcLog.setEntityType( CommonConstants.ENTITY_TYPE_MOBILESWITCHBOARD);
svcLog.setEntityId( rs.getInt(HGROUPID) );
svcLog.setRelEntityId( woUserId );
// Insert object in database
svcLogDAO.insertData(svcLog, conn);
}
This code no longer works in TimesTen 7.0.3. We receive the following error:
[TimesTen 7.0.3.0.0 ODBC Driver]Function sequence error.
We had to change the code this way:
while (rs.next()) {
// Prepare object to insert in database
SvcLog_VO svcLog = new SvcLog_VO();
svcLog.setLogId(svcLogDAO.getlogIdNextVal(conn));
svcLog.setService( CommonConstants.MobileSwitchboardSvcId );
svcLog.setOperType( CommonConstants.OPER_TYPE_UPDREL );
svcLog.setEntityType( CommonConstants.ENTITY_TYPE_MOBILESWITCHBOARD);
svcLog.setEntityId( rs.getInt(HGROUPID) );
svcLog.setRelEntityId( woUserId );
// Store object in ArrayList
svcLogs.add(svcLog);
}
// Insert the objects in the ArrayList in database
for (int i = 0; i < svcLogs.size(); i++) {
SvcLog_VO svcLog = (SvcLog_VO)svcLogs.get(i);
svcLogDAO.insertData(svcLog, conn);
}
Once we split the code in two, first iterating the ResultSet and then inserting the objects, it works properly again. Is there any known issue in TimesTen about this?
Thanks in advance, 
Hi,
We may have seen this issue before. To help be sure can you tell me:
1. How do you create the resultSet (rs) object? Please show the exact code used to generate this.
2. Do you see the invalid function sequence error every time or is it intermittent?
Another user saw a similar issue when moving to 7.0.3. It turned out to be a garbage collection issue that had been exposed due to some JDBC optimisations in TimesTen 7.0. However it is not a bug...
Chris 
Hi, Chris:
1.ResultSet rs = null;
PreparedStatement ps = null;
// Statement prepared
ps = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// Query execution
rs = ps.executeQuery();
2. Ocurrs every time.
Thanks. 
Hi,
Thanks for that, It seems this is not the same as the previous similar issue. Can you please confirm which statement exactly incurs the exception? Also, which (if any) of the method calls in your code snippet invoke database activity and what do they do?
Thanks,
Chris 
Hi, Chris:
The code where this occurs is the following:
public void insertParameters(String xxx1, String xxx2, String xxx3,
int xxx4, Connection conn) throws TTException, SQLException {
PreparedStatement ps = null;
String query = null;
try {
query = queryInsert;
ps = conn.prepareStatement(query);
ps.setInt(1, xxx1);
ps.setString(2, xxx2);
ps.setString(3, xxx3);
ps.setString(4, xxx4);
// If debug is activated, the query is printed
if (log.isDebugEnabled()) {
ReadableQuery rq = new ReadableQuery();
rq.addParam(new Integer(xxx1));
rq.addParam(new String(xxx2));
rq.addParam(new String(xxx3));
rq.addParam(new String(xxx4));
log.debug(" Query to execute .. [" + rq.get(query) + "]");
}
ps.executeUpdate();
}
// Exception handling
catch (Exception ex) {
log.error(ex);
throw new TTException(ex);
} // Resources are closed
finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
log.error("Error closing JDBC resources");
throw e;
}
}
}
This method accesses to DDBB and inserts data in a table with the following structure
Command> desc wo.bs_tb_bsvc_param_values;
Table XXXXXXX:
Columns:
*aaa                          TT_INTEGER NOT NULL
*bbb                       TT_CHAR (35) NOT NULL
ccc TT_CHAR (10) NOT NULL
ddd TT_CHAR (256) NOT NULL
1 table found.
(primary key columns are indicated with *)
Command>
Thanks. 
Thanks for posting that. I am a bit confused now since in your earlier posts you said that the problem was occurring in code executing SELECT statements and using result sets. is it in fact the case that the problem is localized to this INSERT statement or is it a more general problem of which this is just a specific instance?
The code sequence in your most recent post is a very simple sequence that is widely used in all releaes of TimesTen so there must be something specific to your usage that is causing this issue. I have some more questions if I may:
1. What JVM/JDK are you using?
2. Are you using a plain JVM or are you running this code in an AppServer? If so, which one?
3. Are you using any form of connection pooling?
4. Are you using the direct mode or client/server access to TimesTen?
5. Are you sharing database connections between different threads?
Thanks, Chris 
Hi Chris,
I want to rectify when we say you that this error occurs always. The error is intermittent and i couldn't see a logic sequence, for me appears like random.
I read that you say "Another user saw a similar issue when moving to 7.0.3. It turned out to be a garbage collection issue that had been exposed due to some JDBC optimisations in TimesTen 7.0. However it is not a bug..." Could you say me how solve it ? I remember you we work with 7.0.3.1.0.
Regards,
Babs 
Hi, I have the same problem and get the following Exception:
java.sql.SQLException: [TimesTen][TimesTen 6.0.8 ODBC Driver]Function squence error
I code is very simple like this:
PreparedStatement pstm = connection.prepareStatement(sql)
pstm.setInt(1, ID);
ResultSet rs = pstm.executeQuery();
while(rs.next){   //  ---------------------- this line get trouble.
.....
}
We are using:
JDK 1.5.0.06
TimesTen 6.0.8
TimesTen JDBC driver: com.timesten.jdbc.TimesTenDriver
and Client/Server connections. 
Hi,
Please can you provide the 5 pieces of information that I asked for in my previous post. We need this in order to evaluate what may be occurring here.
Thanks,
Chris 
Hi I am also getting the same error while executing the SELECT query from C++ application.
[TimesTen][TimesTen 6.0.4 ODBC Driver]Function sequence error
Can some body tell me what this error actually means?
It does not occur everytimg. 
ODBC is a 'stateful' API. Every handle and object has a state and transitions between the states (based on API calls made by the application) have to conform to allowed state transitions. For example, once you have executed (SQLExecute() or SQLExecDirect()) a statement that opens a cursor (any SELECT or other statement that returns results), you must then close the cursor (SQLFreeStmt(...,SQL_CLOSE) before you can re-execute it, or use the statement handle for something else. This is just one example.
The message you are getting means that your program has tried to perform an operation that violates the allowed state transitions defined by ODBC. Basically, there is a logic error in your program.
Chris

problem while inserting image

problem while inserting image I have a problem while am inserting image into database. coding: String url="jdbc:odbc:project"; File image=new File("C:/11.jpg"); FileInputStream fis; String mid; Class.forName("oracle.jdbc.driver.OracleDriver"); con=DriverManager.getConnection(url,"scott","tiger"); Statement st=con.createStatement(); ps=con.prepareStatement("insert into ima values(?,?)"); ps.setString(1, "1"); fis=new FileInputStream(image); ps.setBinaryStream(2, fis,(int)image.length()); ps.executeUpdate(); error: java.sql.SQLException: No data at execution values pending. pls give me the solution regarding this problem
Oracle and BLOB's have for long been a special treatment. If you want to use PreparedStatement#setBinaryStream(), then make sure that you use the JDBC driver version which supports it. Otherwise you need to write some Oracle specific JDBC code. Google gives lot of examples, also when you google on just the exception message you got.

writing to CLOB

hi<br> can someone tell me a way to write into Oracle CLOB field from a jsp page? i am working on iPlanet webserver on Solaris.<br><br> i am trying oracle.sql.CLOB class for this...the import statement works fine.<br> eg . import."oracle.sql.CLOB" but when i do CLOB.createTemporary(par1,par2,par3)..it fails..saying the method cannot be found..i am not sure whats causing the problem<br<br<br> any suggestions?<br> thanks<br> Gunjan
Your Oracle version ??? 
Your Oracle version ???I've heard that Oracle 9 works normally with Clobs, but I haven't been able to do this, and have had to carry on with the cludgy Oracle specific fix ( which if the OP searches this forum for Oracle and Clob, they will find ). Does it work OK with Oracle 9, or is there a more specific version it works with?
oracle version is 9i 
oracle version is 9i 
This is my code sampel, work fine on Oracle 9i...
You should heed other config values:
- DB Version.
- Java Version (j2se o j2ee).
- JDBC Driver for Oracle, and for the right version of Java (are different at some cases).
String valueToPutOnCLOBField= "xxx";
SQLTool sqlt= new SQLTool();  // my utility class
PreparedStatement pstm = conn.prepareStatement( "...your sql query...");
pstm.setClob(2, sqlt.getClob(valueToPutOnCLOBField);
/**
* Own Class (Utility)
*/
public class SQLTool {
     public Clob getClob(String clobValue) throws Exception{
          Clob newClob= null;
          if(clobValue!=null) {
               try{
                    newClob = oracle.sql.CLOB.createTemporary(con, true, oracle.sql.CLOB.DURATION_SESSION);
                    if(newClob!=null) {
                         ((oracle.sql.CLOB)newClob).putString(1, Chars.replaceBadChars(clobValue));
                    }
               } catch (SQLException e) {
               } catch (Exception e){
               }
          }
          return newClob;
     }
}Anyway, can try to use pstm.setObject(2, valueToPutOnCLOBField) instead pstm.setClob(2, sqlt.getClob(valueToPutOnCLOBField). 
Hi
oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_CALL);
gives a classcast exception in Class Method
where as it runs fine in a main method...
any idea on this?
Thanks in advance 
Hi All,
We are trying to create CLOB from string(x) and pass the same to a stored procedure(TestProc), through the following lines of code:
java.sql.CallableStatement ocs = (CallableStatement)conn.prepareCall("{call TestProc(?)}");
String v_clob_string = x.toString();
CLOB v_clob = CLOB.createTemporary(conn,false,CLOB.DURATION_CALL);
System.out.println(v_clob.toString());
((oracle.sql.CLOB)v_clob).putString(1, v_clob_string);
ocs.setClob(1,v_clob);
If we run that piece of code with the Java version "1.3.1_06" and oracle client version 9.0, it works fine.
However if we run the same code with the java version "1.3.1_06" and oracle client version 9.2.
The following exception is encountered:
java.sql.SQLException: No more data to read from socket
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
at oracle.jdbc.ttc7.v8TTILob.receiveReply(v8TTILob.java:955)
at oracle.jdbc.ttc7.v8TTIClob.write(v8TTIClob.java:329)
at oracle.jdbc.ttc7.TTC7Protocol.lobWrite(TTC7Protocol.java:2334)
at oracle.sql.LobDBAccessImpl.putChars(LobDBAccessImpl.java:658)
at oracle.sql.CLOB.putChars(CLOB.java:542)
at oracle.sql.CLOB.putString(CLOB.java:627)
at ClobPoc_revised.main(ClobPoc_revised.java:39)
SQLException error code is 17410
SQLException message is No more data to read from socket
Any help will be highly appreciated. 
Pranav, if you are using jboss or any other AS that perform jdbc connection pooling , the classcast exception is caused by the "conn" object. I had this problem using jboss4.0.1/oracle9.2 .
In jboss4 the Connection object retrieved by ConnectionFactory.makeconnection() is an instance of org.jboss.resource.adapter.jdbc.WrappedConnection class (or a DelegatingConnection in tomcat or products that use DBCP)
My solution for writing CLOB with jboss4/oracle9.2 :
oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(((WrappedConnection) con).getUnderlyingConnection() , false, oracle.sql.CLOB.DURATION_CALL);
newClob.putString(1,stringContent);
pstm.setClob(5,newClob);
(include jboss-common-jdbc-wrapper.jar with WrappedConnection class)
Reading:
Clob clob = rs.getClob(1);
String str, clobcontent = "";
BufferedReader re = new BufferedReader(clob.getCharacterStream());
while ((str = re.readLine()) != null) {
clobcontent += str;
} 
Winnfield, thanks for this, you provided the best, most concise answer to this problem.
Ben 
Hi:
I have been doing the exact same thing which is
oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(((WrappedConnection) conn).getUnderlyingConnection(), false, oracle.sql.CLOB.DURATION_CALL);
but I am still getting the ClassCastException()
java.lang.ClassCastException: oracle.jdbc.driver.OracleConnection
12:14:32,674 ERROR [STDERR]      at oracle.jdbc.driver.OracleConnection.physicalConnectionWithin(OracleConnection.java:5174)
12:14:32,674 ERROR [STDERR]      at oracle.sql.CLOB.createTemporary(CLOB.java:1009)
Please help.
I am using JBOSS4.0.4RC1 & Oracle. Do I need to make any changes to any JBOSS configuration file as well to make this work.
Thanks,
Jyoti 
Great post jpardon,
I had the same problem and your post saved a lot of time.
I tested it with Oracle 10g
Many thanks! 
Don't forget to call 'freeTemporary' on the CLOB.
Otherwise it keeps it around in Temp space as long as the session is alive. 
Using the code ,
newClob = oracle.sql.CLOB.createTemporary(con, true, oracle.sql.CLOB.DURATION_SESSION);
this returns null and there is no exception in server logs.
I am using this in JBoss 4.2.1 with Oracle 10g nad Spring DAO framework.
My requirement is to insert a string value (>4K) in DB table in column of CLOB type through Stored Procedure.
We cannot use prepared statement, only Stored Procedure.
Any help appreciated.
Thanks in advance.
Regards,
SKhoja 
Skhoja wrote:
Using the code ,
newClob = oracle.sql.CLOB.createTemporary(con, true, oracle.sql.CLOB.DURATION_SESSION);
this returns null and there is no exception in server logs.
I am using this in JBoss 4.2.1 with Oracle 10g nad Spring DAO framework.
My requirement is to insert a string value (>4K) in DB table in column of CLOB type through Stored Procedure.
We cannot use prepared statement, only Stored Procedure.
Any help appreciated.
Thanks in advance.
Regards,
SKhojaCreate a new thread if you have a question. Post your problem there and describe what you have tried in order to solve your problem. I'm locking this 5 year old thread.
Kaj

Select \ Insert Into

I have some code (see below) that I've ported from odbc\VB6 that reads a CSV file into an Access table. The code partial works in that it creates the table with the correct field names but no rows are added. I assume the problem is in the line "String strSQL =...." but not sure what it might be. Any thoughts or suggestions??? Thanks F private static void ImportFromCsvToAccessTable(String mdbFilePath,String accessTableName , String csvDirPath ,String csvFileName ) throws ClassNotFoundException, SQLException{ Connection msConn = getDestinationConnection(mdbFilePath); try{                      //is this correct? String strSQL = "SELECT * INTO " + accessTableName + " FROM [Text;HDR=YES;DATABASE=" + csvDirPath + ";].[" + csvFileName + "]"; PreparedStatement selectPrepSt = msConn.prepareStatement(strSQL ); boolean result = selectPrepSt.execute(); //returns false System.out.println( "result = " + result ); } catch(Exception e) { System.out.println(e); } finally { msConn.close(); } }
Sorry, not even close. Way off. I suggest you thow away the example code you found and completely forget that code and search the internet on how to obtain a database connection and use it, read data from a simple data and display it without worrying about working with a CVS file.
Next, create a project that reads a CVS file and puts the data in an ArrayList. Dont worry about reading/writing to a database.
Lastly, combine the code in the two projects so you can store the CVS in a database.
*******
From your code example, I suspect you are just starting out in Java. If so, its not something you can throw together by finding some code on-line. You should read books on it. I suggest your first book would be 'Head First in java. Its highly recommended. You can get it on amazon.com relatively cheaply. 
It should be noted that there is a ODBC 'text' driver which reads CSV files.
I am not sure what syntax works with it and it is rather finicky but if there is a working VB6 application that using something like that (ODBC driver) then it is certainly something that can be written in java.
However it is also possible that VB just has an API that does that using similar syntax. If so then one would need a CSV parsing API.
If it was me I would probably write my own CSV parser anyway or find one rather than relying on the ODBC driver though.

Categories

Resources