Solved

JDBC query is getting executed!

Posted on 2004-04-08
15
1,022 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now