Solved

Hon can I do with the big data?

Posted on 2001-07-18
13
1,135 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
changePi Challenge 15 76
mapShare challenge 13 68
groovy example issue 10 66
Java JRE greater than 1.6 5 21
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
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 one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

757 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

21 Experts available now in Live!

Get 1:1 Help Now