Solved

Hon can I do with the big data?

Posted on 2001-07-18
13
1,140 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
map related example 6 55
pagenation logic how it is working in my code 1 55
Bot application - advice 3 38
java mysql insert application 14 28
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
A list of useful business intelligence software.
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 learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…

829 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