Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB2 store CLOB from java bigger then 32KB

Posted on 2013-05-26
4
Medium Priority
?
1,434 Views
Last Modified: 2013-05-27
Dear experts,

We have a table with a CLOB:
LARGE_TEXT_FIELD CLOB(262144)
Now if we want to store a string bigger then 32KB from our java application via the db2 jdbc driver we get the error:

 sqlcode: -102

 sqlstate: 54002
*

The DB2 server runs on linux, is version DB2 LUW 9.5

The same occurs if I e.g. want issue an sql insert via WinSQL which connects via ODBC to the DB2 server
How can this issue be solved?
We want to be able to store strings greater then 32KB
Storing a string smaller then 32KB works fine

*One of the following has occurred:
*  The comment in the COMMENT ON statement is greater than 254 bytes.
*  The application server name specified in the SQL CONNECT statement is
   greater than 18 bytes.
*  The string constant beginning with "<string>" has a length greater
   than 32672 bytes. Character strings with lengths greater than 32672
   bytes or graphic strings with lengths greater than 16336 characters
   can be specified only through assignment from host variables. Note
   that other servers in the DB2 family of products may specify a
   different size limit for character strings. Consult the documentation
   for the appropriate DB2 product for details.
*  In an XQuery expression, a string literal beginning with "<string>"
   has a length greater than 32672 bytes. The limit is determined on the
   string literal encoded in UTF-8. A string with length greater than
   32672 bytes can only be specified in an XQuery expression using XML
   values from the database through the XQuery functions that access
   data or through an XQuery variable assigned a value using SQL/XML
   functions like XMLQUERY, XMLEXISTS, or XMLTABLE.
*  Federated system users: the data source-specific limits must not be
   exceeded in a pass-through session. For example, a character literal
   larger than 254 bytes included in a statement sent to DB2 for OS/390
   in a pass-through session would cause this error.

It may be that data conversion is occurring and the resultant string is
too long. For connections between applications and databases running
under different codepages, string constants are converted from the
application codepage to the database codepage. In certain situations,
graphic string constants may be further converted from the database
codepage to a UCS-2 (UNICODE) encoding, for example when the database
has been created with an EUC codepage. This means that it is possible to
have a resultant string which is longer than the input string.

The statement cannot be processed.

User response:

For Comment on Table or Comment on Column, reduce the size of the
comment. For the SQL CONNECT statement, reduce the length of the
application server name. For other string constants, the requested
function is not available interactively. For an error occurring in the
context of a non-CONNECT SQL statement embedded in an application
program, assign the long string to a host variable and substitute that
variable for the string literal in the SQL statement.

Federated system users: for a pass-through session, determine what data
source is causing the error (see the Troubleshooting Guide for the
failing data sources). Examine the SQL dialect for that data source to
determine which specific limit has been exceeded, and adjust the failing
statement as needed.
0
Comment
Question by:mruff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
mccarl earned 2000 total points
ID: 39198603
Are you using plain JDBC? Or some layer on top of that like Spring, Hibernate, JPA, etc?

If we assume just plain JDBC, then I am guessing that at some point you have a line something like...
preparedStatement.setString(3, myString);

Open in new window

Where the 3 is whatever index of the ? in your SQL statement and the myString is what you are trying to insert/update. If so, you should just be able to change that to something like this...
preparedStatement.setClob(3, new StringReader(myString), myString.length());

Open in new window


If any of the above is way off the mark, can you post the code that you are using so that we have some chance of knowing what you are trying to do?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39199139
Or, more simply, you might try

preparedStatement.setCharacterStream(3, new StringReader(myString));

Open in new window

0
 

Author Closing Comment

by:mruff
ID: 39199145
Great!
Thx yes using a prepared statement solved the problem
Many thanks
0
 
LVL 36

Expert Comment

by:mccarl
ID: 39199985
Not a problem, glad to help!
0

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

705 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