?
Solved

Hon can I do with the big data?

Posted on 2001-07-18
13
Medium Priority
?
1,156 Views
Last Modified: 2016-03-23
Hi,
When I inserted some datas into a column which is varchar2 I always got error ora-01704 String literal too long. Must the length of the data is bigger than 4000.

I want to change the column type to LOB,but I don't know how to write the data into it and how to read
them out! I use Oracle jdbc.

Any reply will be appreciated.

Vivian Qi
0
Comment
Question by:vivianQQ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 3

Expert Comment

by:rjackman
ID: 6292854
use BLOB or CLOB  oracle data type
then use the prepared statement to access these datat types
e.g
String sql="insert into table name values(?)where id=?";
try
{
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setBlob(1,blobobject);
//or u can set the clob or u can directly read data using stream and set it to BLOB field in Databasein that case use the setBinaryStream method of the prepared statement
}
catch(Exception ee)
{
  ee.printStackTrace();
}
cheers
RJ
0
 

Author Comment

by:vivianQQ
ID: 6293789
But how can I transfer the String that I got from the client into the CLOB.I have try java.io.writer.
java.io.Writer writer

// read data into a character array
char[] data = {'0','1','2','3','4','5','6','7','8','9'};

// write the array of character data to a CLOB
writer = ((CLOB)my_clob).getCharacterOutputStream();
writer.write(data);
writer.flush();
writer.close();
This is the example that given by oralce 8 document.I got the error :"Error #: 300 : method write(oracle.sql.CLOB) not found in class java.io.Writer at line 195, column 16"
when I compile the code.

what's wrong?
0
 
LVL 3

Expert Comment

by:rjackman
ID: 6294636
hi
which Writer class u are using
and where r u trying to write?
is it in the file?
well if u are getting the string then u can try following
String client ="0,1,2,3,4,5,6,7,8,9";
 
ByteArrayInputStream barray = new ByteArrayInputStream(client.getBytes());
then use this stream and passit to preparedstatement.setInputStream()to set it in DB
Cheers
RJ
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:vivianQQ
ID: 6296513
I want to know how can I declare a clob and write a string into it before insert it into the table?

I built the code in this way:

        CharArrayInputStream barray = new CharArrayInputStream(intro.getBytes());
        String sql="insert into singers values('"+singer_ID+"','"+singer_Category+"','"+cn_Name+"','"+en_Name+"','"+gender+"','?')";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInputStream(1,barray);
        pstmt.execute();

I got the compiler errors:
"sseiBean.java": Error #: 300 : class CharArrayInputStream not found in class ssei.sseiBean at line 198, column 9
"sseiBean.java": Error #: 300 : class CharArrayInputStream not found in class ssei.sseiBean at line 198, column 43
"sseiBean.java": Error #: 300 : method setInputStream(int, <any>) not found in interface java.sql.PreparedStatement at line 201, column 15

Thank U!
I am not familiar with stream and clob.
0
 

Author Comment

by:vivianQQ
ID: 6296563
I want to know how can I declare a clob and write a string into it before insert it into the table?

I built the code in this way:

        CharArrayInputStream barray = new CharArrayInputStream(intro.getBytes());
        String sql="insert into singers values('"+singer_ID+"','"+singer_Category+"','"+cn_Name+"','"+en_Name+"','"+gender+"','?')";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInputStream(1,barray);
        pstmt.execute();

I got the compiler errors:
"sseiBean.java": Error #: 300 : class CharArrayInputStream not found in class ssei.sseiBean at line 198, column 9
"sseiBean.java": Error #: 300 : class CharArrayInputStream not found in class ssei.sseiBean at line 198, column 43
"sseiBean.java": Error #: 300 : method setInputStream(int, <any>) not found in interface java.sql.PreparedStatement at line 201, column 15

Thank U!
I am not familiar with stream and clob.
0
 
LVL 9

Expert Comment

by:yongsing
ID: 6296642
I believe you can treat the CLOB column as though it is a varchar. Suppose that you have two columns, and one of them is a CLOB to store HTML code, then you can have the following:

Statement statement = connection.createStatement();
statement.executeUpdate("insert into mytable (id, html) values('00','" + html + "')"); // where html is a CLOB data
statement.close();
connection.close();
0
 

Author Comment

by:vivianQQ
ID: 6296735
But how can I change the String variable to a CLOB variable?
0
 
LVL 9

Expert Comment

by:yongsing
ID: 6296847
You don't have to covert it to a CLOB variable. Just insert you String variable into the database as though the column is of type varchar.

// Open the html file
FileInputStream fis = new FileInputStream("somefile.html");
BufferedReader reader = new BufferedReader( new InputStreamReader(fis));

// Read each line of the file and append to a StringBuffer
String line = null;
StringBuffer stringBuffer = new StringBuffer();
while ( (line = reader.readLine()) != null) {
     stringBuffer.append(line);
}

// Convert to a string
String content  = stringBuffer.toString();

// Insert the string into the database
Statement statement = connection.createStatement();

// html is the CLOB column, but content is String
statement.executeUpdate("insert into mytable (id, html) values('00','" + content + "')");

statement.close();
connection.close();

I am not sure about Oracle, but it works perfectly in DB2.
0
 

Author Comment

by:vivianQQ
ID: 6297271
ORA-01704 string literal too long

Cause: The string literal is longer than 4000 characters.

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

I still got the error like above when I enter the String that longer than 4000 characters even used the clob.

What should I do with long String?How can I insert it into the table?

I have really been confused.

ThX
0
 
LVL 3

Expert Comment

by:rjackman
ID: 6297332
hi there
use following
String clientString = "balabalabalabaaba";
char[] chararray = clientString.toCharArray();

String sql="insert into table name values(?)where id=?";
try
{
CharArrayReader charReader = new CharArrayReader(chararray);

PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setCharacterStream(1,charReader);//pls refer the documentation
if(pstmt.executeUpdate()!=1)
{
  System.out.println("Error in the Code...");
}
}
catch(Exception ee)
{
 ee.printStackTrace();
}
cheers
RJ
0
 
LVL 3

Accepted Solution

by:
black earned 100 total points
ID: 6325350
I currently have Clobs and Blobs in my Oracle 8.1.6 database I did some stuff to test inserting, updating, reading and deleting. Here's the code I used. I just ran it from a JSP.
The basic steps I follow are:
 1. Register the driver via a Class.forName
 2. Get a connection
 3. Set the auto commit false
 4. Create an empty clob
 5. Retrieve the Clob (use the "for update" in your sql)
 6. Get the output stream from the clob and write to it.
 7. Close all streams
 8. Run another SQL to select the clob
 9. Get the Clob from the result set, get the input stream and read all it's data

Hope it's of some use:

// Start code
<%@ page import = "java.io.*" %>
<%@ page import = "javax.naming.*" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "javax.sql.*" %>
<%@ page import = "oracle.sql.*" %>
<%@ page import = "oracle.jdbc.*" %>
<html>
<head>
<title>Test clob</title>
</head>
<body>
<%
try {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@sdevdb1:1521:sdevdb1", "dragon", "smaug");
  conn.setAutoCommit (false);
  System.out.println("got connection");
  Statement dbStatement = conn.createStatement();
  System.out.println("got statement");
  String sqlStatement = "delete from report where report_id='charles'";
  dbStatement.execute(sqlStatement);
  sqlStatement = "insert into report(report_id, description, presentation_xml) " +
                          "values ('charles', 'testing clob', empty_clob())";
  dbStatement.execute(sqlStatement);
  System.out.println("inserted clob");

  sqlStatement = "select * from report where report_id='charles' for update";
  ResultSet dbResultSet = dbStatement.executeQuery(sqlStatement);
  dbResultSet.next();
  CLOB clob = ((OracleResultSet)dbResultSet).getCLOB(3);
  System.out.println("got clob");
  Writer writer = clob.getCharacterOutputStream();
  System.out.println("got clob output stream");

  BufferedReader br = new BufferedReader(new FileReader("./blizzard/myclob.txt"));
  System.out.println("got file");
  String data = "";
  StringBuffer strbuf = new StringBuffer();
 
  for(data = br.readLine(); data != null; data = br.readLine()) {
    strbuf.append(data);
  }

  writer.write(strbuf.toString());
  writer.close();
 
  System.out.println("wrote file to clob output stream");
  dbResultSet.close();
  dbStatement.close();
  conn.close();
  System.out.println("closed connections");
  conn = DriverManager.getConnection("jdbc:oracle:thin:@sdevdb1:1521:sdevdb1", "dragon", "smaug");
  dbStatement = conn.createStatement();
  sqlStatement = "select * from report where report_id='charles'";
  dbResultSet = dbStatement.executeQuery(sqlStatement);
  dbResultSet.next();
  clob = ((OracleResultSet)dbResultSet).getCLOB(3);
  System.out.println("got clob again");
  br = new BufferedReader(clob.getCharacterStream());
  strbuf = new StringBuffer();
  for(data = br.readLine(); data != null; data = br.readLine()) {
    strbuf.append(data);
  }
  out.println(strbuf);
} catch(Exception e) {
  out.println(e.getMessage());
  System.out.println("Exception thrown:" + e);
}
%>

</body>
</html>

// End Code
0
 

Author Comment

by:vivianQQ
ID: 6332706
I have solved the problem!
Thank all of U ,Black especially!




Vivian Qi
0
 

Author Comment

by:vivianQQ
ID: 6332712
I found the "for update" in the select statement string is very important.But nobody told before U .Thank U very much!

Vivian Qi
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month8 days, 18 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question