• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1187
  • Last Modified:

Hon can I do with the big data?

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
vivianQQ
Asked:
vivianQQ
  • 7
  • 3
  • 2
  • +1
1 Solution
 
rjackmanCommented:
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
 
vivianQQAuthor Commented:
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
 
rjackmanCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
vivianQQAuthor Commented:
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
 
vivianQQAuthor Commented:
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
 
yongsingCommented:
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
 
vivianQQAuthor Commented:
But how can I change the String variable to a CLOB variable?
0
 
yongsingCommented:
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
 
vivianQQAuthor Commented:
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
 
rjackmanCommented:
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
 
blackCommented:
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
 
vivianQQAuthor Commented:
I have solved the problem!
Thank all of U ,Black especially!




Vivian Qi
0
 
vivianQQAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now