Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement Error in DB2

 
shyamala vydyam
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After removing the comments also doesnt makes any difference....still getting the same exception....

any idea plzzzzzzzz reply me
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like you ran out of resources. Why not just split up the query into two separate queries/ Then each one is smaller.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic