Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1065
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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