File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes PreparedStatement Error in DB2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement Error in DB2" Watch "PreparedStatement Error in DB2" New topic
Author

PreparedStatement Error in DB2

shyamala vydyam
Greenhorn

Joined: Sep 20, 2010
Posts: 4
Hi ,

I am using PreparedStatement in db2 where i am passing many parameters around 32 , but as soon as i execute i get the following exception ,

Java code :

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;



public class db2connect {

public static void main(String[] args) throws Exception {

Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection con = DriverManager.getConnection(
"jdbc:db2j:net://url/db",
"db", "pwd");

// Statement stmt = con.createStatement();
// String query="SELECT * FROM SM_NEWS where (lower(SUBJECT) like
// '%lorem%') OR (lower(SUBJECT) like '%ipsum%') OR (lower(SUBJECT) like
// '%dolor%') OR (lower(SUBJECT) like '%sit%') OR (lower(SUBJECT) like
// '%amet,%') OR (lower(SUBJECT) like '%consectetur%')OR (lower(SUBJECT)
// like '%adipisicing%')OR (lower(SUBJECT) like '%elit,%')OR
// (lower(SUBJECT) like '%sed,%') OR (lower(SUBJECT) like '%do%')OR
// (lower(SUBJECT) like '%eiusmod%') OR (lower(SUBJECT) like '%tempor%')
// OR (lower(SUBJECT) like '%incididunt%')OR (lower(SUBJECT) like
// '%ut%')OR (lower(SUBJECT) like '%labore%') AND END_DATE >=
// '2011-01-10 13:33:04.047'";

String query = "SELECT NEWS_ID FROM SM_NEWS WHERE (" +
"(" +
"(lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(SUBJECT) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
/*"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?) " +
"OR (lower(LONG_DESC) LIKE ?)" +*/
") " +
"AND (END_DATE >= ?))";
PreparedStatement pstmt = con.prepareStatement(query);

for (int i = 1; i <= 26; i++) {
pstmt.setString(i, "Lorem");
}
java.util.Date date = new java.util.Date();
pstmt.setTimestamp(27, new Timestamp(date.getTime()));

ResultSet rs = pstmt.executeQuery();
if (rs.next()) {

System.out.println(rs.getString(1) + " \t" + rs.getString(2) + "\t"
+ rs.getString(3));
}
else{
System.out.println("No");
}

}

}


Exception in thread "main" com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -954, SQLSTATE: 57011, SQLERRMC: null
at com.ibm.db2.jcc.c.qh.c(qh.java:1671)
at com.ibm.db2.jcc.c.qh.a(qh.java:1235)
at com.ibm.db2.jcc.a.db.n(db.java:748)
at com.ibm.db2.jcc.a.db.i(db.java:257)
at com.ibm.db2.jcc.a.db.c(db.java:53)
at com.ibm.db2.jcc.a.t.c(t.java:46)
at com.ibm.db2.jcc.a.sb.g(sb.java:154)
at com.ibm.db2.jcc.c.qh.o(qh.java:1230)
at com.ibm.db2.jcc.c.rh.d(rh.java:2434)
at com.ibm.db2.jcc.c.rh.d(rh.java:2510)
at com.ibm.db2.jcc.c.rh.T(rh.java:426)
at com.ibm.db2.jcc.c.rh.executeQuery(rh.java:409)
at db2connect.main(db2connect.java:77)


if i give 25 it works fine.


Can any one tell me how to increase the parameters for DB2 PreparedStatement?

Thanks,
Shyamala
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
Are you allowed to put comments in a prepared statement? The one time I did that connecting to an Oracle database through .NET code, I had to strip the comments out. I did a quick Google search jdbc adding comments to preparedstatement and did not find anything (for or against).
shyamala vydyam
Greenhorn

Joined: Sep 20, 2010
Posts: 4
After removing the comments also doesnt makes any difference....still getting the same exception....

any idea plzzzzzzzz reply me
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

It looks like you ran out of resources. Why not just split up the query into two separate queries/ Then each one is smaller.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
jQuery in Action, 2nd edition
 
subject: PreparedStatement Error in DB2
 
Similar Threads
jdbc program written in Eclipse3.2.1 to connect DB2
connecting to db2 using net driver
java.sql.SQLException: No suitable driver
java DB2 program
db2 express-c 9.7 failure in loading t2 native library db2jcct2