Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1082
  • Last Modified:

JDBC query is getting executed!

Hi,

 I have a query in UDB, it getting executed at the SQL command prompt.
db2 => SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2A.MQMG WHERE DATE(TSTAMP
)=DATE('2004-04-01-00.00.00') GROUP BY HOUR(TSTAMP)

It's giveing result very nicely.

But the same query i am trying to execute in Java using prepareStatement. It's giving error when the query is getting executed

here is my code..
String query = "ALLMSG_HOUR_CNT","SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2A.MQMG WHERE DATE(TSTAMP)=DATE(?) GROUP BY HOUR(TSTAMP)"
reparedStatement pstmt = conn.prepareStatement(query);
pstmt.clearParameters();
String sqlDate = "2004-04-01-00.00.00";
pstmt.setString(1, sqlDate);
ResultSet rs = pstmt.executeQuery();

It's giving error in the last line
I appreciate ur help!

thanks
0
mvkraju
Asked:
mvkraju
  • 7
  • 6
1 Solution
 
Tommy BraasCommented:
Change your date string!

String sqlDate = "2004-04-01-00.00.00";   ==>   String sqlDate = "2004-04-01 00:00:00";

P.s. always include the errors in your questions d.s.
0
 
mvkrajuAuthor Commented:
I changed it as you said, it's not working..

my TSTAMP format is yyyy-MM-dd-HH.mm.ss in the DB

sorry here is the exception

sgWarehouseRptAction.execute(474) - java.lang.NullPointerException
STDOUT=TRUEjava.lang.NullPointerException
      at com.cfg.ia.mwr.db.SqlHandler.processMyQuery(SqlHandler.java:103)
      at com.cfg.ia.mwr.db.SqlHandler.msgHourCountExec(SqlHandler.java:624)
      at com.cfg.ia.mwr.action.MsgWarehouseRptAction.evaluateTarget(MsgWarehouseRptAction.java:58)
      at com.cfg.ia.mwr.action.MsgWarehouseRptAction.execute(MsgWarehouseRptAction.java:450)
      at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
0
 
mvkrajuAuthor Commented:
By the by i observed one thing

this code is getting executed in the command prompt
db2 => SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2A.MQMG WHERE DATE(TSTAMP
)=DATE('2004-04-01-00.00.00') GROUP BY HOUR(TSTAMP)

but this is not

db2 => SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2A.MQMG WHERE DATE(TSTAMP
)=DATE("2004-04-01-00.00.00") GROUP BY HOUR(TSTAMP)

only the difference between the both queries is
second one date in enclosed in double quote.



0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Tommy BraasCommented:
Ah, yeas indeed.

Change to:
String sqlDate = "2004-04-01-00.00.00";
pstmt.setBytes(1, sqlDate.getBytes());
0
 
Tommy BraasCommented:
Oops, don't forget to add the single quotes...to quick with the submit...

String sqlDate = "'2004-04-01-00.00.00'";
0
 
mvkrajuAuthor Commented:
sorry, it did not work for me.
0
 
Tommy BraasCommented:
What doesn't work? Do you still get an error, or does the result set not contain any rows?
0
 
mvkrajuAuthor Commented:
When i try this

try
        {
            ConnHandler connHandler = new ConnHandler(false);
            logger.debug("got connHandler...");
            Connection conn = connHandler.getConn();
            logger.debug("got connection...");
            String query = "SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE "+
                    "DATE(TSTAMP)=DATE(?) GROUP BY HOUR(TSTAMP)";
            logger.debug("query |"+query);
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.clearParameters();
            String sqlDate = "2004-04-01-00.00.00";
            logger.debug("sqlDate |"+sqlDate);
            pstmt.setString(1, sqlDate);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
            {
                logger.debug("it has data..");
            }
            rs.close();
            pstmt.close();
            conn.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        catch(IllegalAccessException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        catch(InstantiationException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        catch(NamingException e)
        {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

i am getting the following error

STDOUT=TRUE2004-04-08 13:53:52,991-main (DEBUG) SqlExecutor.<init>(32) - got connHandler...
STDOUT=TRUE2004-04-08 13:53:53,001-main (DEBUG) ConnHandler.getDriverConn(101) - Entering...
STDOUT=TRUE2004-04-08 13:53:53,211-main (DEBUG) SqlExecutor.<init>(34) - got connection...
STDOUT=TRUE2004-04-08 13:53:53,211-main (DEBUG) SqlExecutor.<init>(37) - query |SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE DATE(TSTAMP)=DATE(?) GROUP BY HOUR(TSTAMP)
STDOUT=TRUE2004-04-08 13:53:53,221-main (DEBUG) SqlExecutor.<init>(41) - sqlDate |2004-04-01-00.00.00
java.lang.UnsatisfiedLinkError: SQLBindChar
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:1675)
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:1398)
      at com.cfg.ia.mwr.test.SqlExecutor.<init>(SqlExecutor.java:43)
      at com.cfg.ia.mwr.test.SqlExecutor.main(SqlExecutor.java:77)
      at java.lang.reflect.Method.invoke(Native Method)
      at com.intellij.rt.execution.application.AppMain.main(Unknown Source)
STDOUT=TRUEException in thread "main" Process terminated with exit code 1

0
 
mvkrajuAuthor Commented:
When i try this code as per ur suggestion

onnHandler connHandler = new ConnHandler(false);
            logger.debug("got connHandler...");
            Connection conn = connHandler.getConn();
            logger.debug("got connection...");
            String query = "SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE "+
                    "DATE(TSTAMP)=DATE(?) GROUP BY HOUR(TSTAMP)";
            logger.debug("query |"+query);
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.clearParameters();
            String sqlDate = "'2004-04-01-00.00.00'";
            logger.debug("sqlDate |"+sqlDate);
//            pstmt.setString(1, sqlDate);
            pstmt.setBytes(1, sqlDate.getBytes());
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
            {
                logger.debug("it has data..");
            }
            rs.close();
            pstmt.close();
            conn.close();

i am gettring the follosing error

STDOUT=TRUE2004-04-08 13:59:54,380-main (DEBUG) SqlExecutor.<init>(32) - got connHandler...
STDOUT=TRUE2004-04-08 13:59:54,390-main (DEBUG) ConnHandler.getDriverConn(101) - Entering...
STDOUT=TRUE2004-04-08 13:59:54,891-main (DEBUG) SqlExecutor.<init>(34) - got connection...
STDOUT=TRUE2004-04-08 13:59:54,891-main (DEBUG) SqlExecutor.<init>(37) - query |SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE DATE(TSTAMP)=DATE(?) GROUP BY HOUR(TSTAMP)
STDOUT=TRUE2004-04-08 13:59:54,901-main (DEBUG) SqlExecutor.<init>(41) - sqlDate |'2004-04-01-00.00.00'
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0418N  A statement contains a use of a parameter marker that is not valid.  SQLSTATE=42610

      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:261)
      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:209)
      at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:443)
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:1882)
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:1398)
      at com.cfg.ia.mwr.test.SqlExecutor.<init>(SqlExecutor.java:44)
      at com.cfg.ia.mwr.test.SqlExecutor.main(SqlExecutor.java:78)
      at java.lang.reflect.Method.invoke(Native Method)
      at com.intellij.rt.execution.application.AppMain.main(Unknown Source)
0
 
Tommy BraasCommented:
Try the following:

            String query = "SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE "+
                    "DATE(TSTAMP)=CAST(? AS DATE) GROUP BY HOUR(TSTAMP)";

Alternatively:

            String query = "SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE "+
                    "DATE(TSTAMP)=CAST(? AS DATE()) GROUP BY HOUR(TSTAMP)";

0
 
mvkrajuAuthor Commented:
here is the query response
STDOUT=TRUE2004-04-08 16:34:23,649-main (DEBUG) SqlExecutor.<init>(37) - query |SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2admin.MQMDLOG WHERE DATE(TSTAMP)= CAST(? AS DATE) GROUP BY HOUR(TSTAMP)
STDOUT=TRUE2004-04-08 16:34:23,659-main (DEBUG) SqlExecutor.<init>(41) - sqlDate |2004-04-01-00.00.00
java.lang.UnsatisfiedLinkError: SQLBindChar
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:1675)
      at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:1398)
0
 
mvkrajuAuthor Commented:
orangehead911 thanks for your support.

This query is not working with PreparedStatement.
But it works with Statement. I don't know what is the reason.
But i could keep moving on.

Thanks
0
 
Tommy BraasCommented:
I think you'll be better off just sticking with Statement for now. Another option of course is to have the statement as a stored procedure in the database and access it with the CallableStatement instead.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now