Solved

Hon can I do with the big data?

Posted on 2001-07-18
13
1,137 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now