Solved

JDBC query is getting executed!

Posted on 2004-04-08
15
1,036 Views
Last Modified: 2012-05-04
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
Comment
Question by:mvkraju
  • 7
  • 6
15 Comments
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10784648
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
 

Author Comment

by:mvkraju
ID: 10784755
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
 

Author Comment

by:mvkraju
ID: 10784785
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
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.

 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10784831
Ah, yeas indeed.

Change to:
String sqlDate = "2004-04-01-00.00.00";
pstmt.setBytes(1, sqlDate.getBytes());
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10784837
Oops, don't forget to add the single quotes...to quick with the submit...

String sqlDate = "'2004-04-01-00.00.00'";
0
 

Author Comment

by:mvkraju
ID: 10785483
sorry, it did not work for me.
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10785693
What doesn't work? Do you still get an error, or does the result set not contain any rows?
0
 

Author Comment

by:mvkraju
ID: 10785729
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
 

Author Comment

by:mvkraju
ID: 10785755
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
 
LVL 14

Accepted Solution

by:
Tommy Braas earned 40 total points
ID: 10786089
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
 

Author Comment

by:mvkraju
ID: 10786956
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
 

Author Comment

by:mvkraju
ID: 10787331
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
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10787369
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
session migration servlets 2 42
web services creation SOAP vs REST 5 57
eclipse apache tomcat admin console 52 124
Notify sent to other threads in Java 9 34
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The viewer will learn how to implement Singleton Design Pattern in Java.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

808 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