Reading large large string from database

Avatar

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
yodadobe
Level 5

16-02-2009

I am reading is a large string value. It's data type in Oracle is CLOB. I was originally casting it to vARCHAR2(4000) in the select statement, but just realized the data can be larger than 4000. I took out the cast and have tried changing to several data types, but they all error out. How can I read in string data greater than 4000 characters?

Replies

Avatar

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
yodadobe
Level 5

16-02-2009

I used JDBC in a script object.

Avatar

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile
HowardTreisman
Level 9

20-02-2009

Use a String variable with unlimited length (there's a checkbox to control it when you define the string variable). In older versions, you can set the length to -1.

Howard

http://www.avoka.com

Avatar

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 5
yodadobe
Level 5

Likes

12 likes

Total Posts

298 posts

Correct reply

10 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
yodadobe
Level 5

20-02-2009

It gave an error that it could not put a CLOB into a String.

Avatar

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile
HowardTreisman
Level 9

20-02-2009

What's the exact error?

Avatar

02-03-2009

You must use Java to do this, the Execute Script operation makes it simple. I have put together a little process to demonstrate, and I can paste the Java from the Execute Script step below.



It's all in the data-typing. In the sample I built, the BigString and NewBigString process variables are of LiveCycle type "string" with unlimited size. These become CLOBs in the database. When read as a CLOB object using JDBC, converted to Java Strings, they then can be loaded into the LiveCycle Process data and the API handles the rest.



Let me know if I'm not clear enough with my explanation.

Jeff A Yates

http://www.avoka.com

------

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.Statement;

import java.sql.ResultSet;

import javax.sql.DataSource;

import javax.naming.InitialContext;



int processId = patExecContext.getProcessDataIntValue("/process_data/@id");



InitialContext context = new InitialContext();

Connection connection = ((DataSource)context.lookup("java:/IDP_DS")).getConnection();



String queryQuery = "select bigdocument, bigstring from tb_pt_workwithxlobs where process_instance_id = ?";

PreparedStatement queryStatement = connection.prepareStatement(queryQuery);



try {

queryStatement.setInt(1, processId);

ResultSet results = queryStatement.executeQuery();

results.next();



java.sql.Blob documentBlob = results.getBlob(1);

com.adobe.idp.Document document = new com.adobe.idp.Document(documentBlob.getBinaryStream());

patExecContext.setProcessDataValue("/process_data/@NewBigDocument", document);



java.sql.Clob stringClob = results.getClob(2);

patExecContext.setProcessDataValue("/process_data/@NewBigString", stringClob.getSubString(1L, (int)stringClob.length()));

} catch(Exception ex) {

ex.printStackTrace();

}



queryStatement.close();

connection.close();

Avatar

Avatar
Validate 1
Level 2
vu_tuan_anh
Level 2

Likes

4 likes

Total Posts

75 posts

Correct reply

1 solution
Top badges earned
Validate 1
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 1
Level 2
vu_tuan_anh
Level 2

Likes

4 likes

Total Posts

75 posts

Correct reply

1 solution
Top badges earned
Validate 1
Boost 3
Boost 1
Affirm 1
View profile
vu_tuan_anh
Level 2

07-10-2009

Just happen to refer to this thread.

You can use DBMS_LOB.SUBSTR to query repeatedly 4000 characters out of the field and concat them after finish.


E.g

SELECT
DBMS_LOB.SUBSTR(FieldName, 2000, ?)
FROM TableName

? is replace with a counter, increase by 2000 each time.

Regards,

Tuan Anh