Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Apr 2007
    Posts
    23

    Unanswered: JDBC problem for INSERT INTO...SELECT statement

    When i tried to execute the exact statement in the DB2 Control Center GUI
    it works perfectly, but when i use it from JAVA, it prompts error.
    com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null

    below is the similar statement:
    Insert into
    table1(uid,username,createby,register_date)
    Select
    a.uid,
    b.username,
    'admin',
    current_timestamp
    from
    table2 as a,
    table3 as b
    where
    a.uid= 102
    and
    a.uid=b.uid
    Last edited by desmondtanck; 04-24-07 at 05:17.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    are you sure this is working in DB2 Control Center?

    Insert into
    table1(uid,username,createby,register_date)
    Select
    a.uid,
    b.username,
    'admin',
    current_timestamp
    from
    table2 as a,
    table3 as b, <-- use without comma!
    where
    a.uid= 102
    and
    a.uid=b.uid

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Apr 2007
    Posts
    23
    actually the statement is simplified from my original statement,
    so you can avoid the comma,
    it really works in the control center.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by desmondtanck
    actually the statement is simplified from my original statement, so you can avoid the comma, it really works in the control center.
    Then try avoiding the comma in Java, too.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Apr 2007
    Posts
    23
    actually that is just a spelling mistake when i post this title
    there is nothing to do with my original statement,
    i mean it really can be executed at control center

    but it somehow throw exception when i use it at Java.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    have you checked the error message documentation?
    Code:
    SQL0418N A statement contains a use of a parameter marker that
              is not valid.
    
    Explanation:
    
    Untyped parameter markers cannot be used:
    
    o   in a SELECT list
    
    o   as the sole argument of a datetime arithmetic operation
    
    o   in some cases as the sole argument of a scalar function
    
    o   as a sort key in an ORDER BY clause
    
    
    
     Parameter markers can never be used:
    
    o   in a statement that is not a prepared statement
    
    o   in the fullselect of a CREATE VIEW statement
    
    o   in the triggered action of a CREATE TRIGGER statement
    
    o   in a query captured by DB2 Query Patroller
    
    
    
     The statement cannot be processed.
    
    User Response:
    
    Correct the syntax of the statement.  If untyped parameter
    markers are not allowed, use the CAST specification to give the
    parameter marker a data type.
    
     sqlcode :  -418
    
     sqlstate :  42610

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did I get this right that you post a statement here that has nothing to do with your actual query? And the actual query fails in your application but supposedly works in CC?

    Does the statement you posted here work in CC and fail in your application? If the answer is no (same behavior in both cases), then it is completely useless here...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2007
    Posts
    23
    Thanks for your replies,

    yes, it has nothing to do with my actual query as i cant post the real query here, just to tell that the real query is similar to the one i've posted.

    What i want to make sure is that, is it possible that some query can be executed in CC and will fail when i execute using my application?

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    it shouldn't be working like that. Query is query. Are you sure your JDBC connection is working properly? What is your operating system name and version? What is you db2 edition and version (commands: db2level , db2licm -l)?

    Try using db2ca from command shell and test your JDBC connection. Is it working properly?

    Are you 100% sure the same query is working on CC and JDBC?

    Hope this helps,
    Grofaty

  10. #10
    Join Date
    Apr 2007
    Posts
    23
    yes, my jdbc can work perfectly with other statement,
    i can even successfully login to my system using db2.
    until i'm trying to do some INSERT INTO...SELECT..statement, it fails.

    i'm using DB2 Express Edition 9.1, the db2 is stored at Linux, and i
    have my application at Window 2000.

    the jdbc works with other query at my application, so that means that
    it works correctly right?

  11. #11
    Join Date
    Apr 2007
    Posts
    23
    I've found out a weird thing here,
    i tried to remove the WHERE clause from my actual query,
    it works fine.
    For example,
    insert into table1(username,createdate) select username, current_timestamp
    from table2

    The example above works fine in my application, but when i add the WHERE clause, it fails....
    insert into table1(username,createdate) select username, current_timestamp
    from table2 where username='tanck'

    it is the WHERE clause make the query fails, any suggestion?

    Thanks,
    Desmond

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by desmondtanck
    What i want to make sure is that, is it possible that some query can be executed in CC and will fail when i execute using my application?
    No, that (usually) not possible.

    CC uses JDBC to talk to DB2 - as does your application. So you have the same environment. Therefore, I believe you some other differences between your app and JDBC.

    The real problem is that you didn't show us a query that works in one case and fails in the other. You should find such a query (which you can post here), first.

    What I recommend right now is that you collect a JDBC trace to see what is really going on in the communication with DB2. There you may see something...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Apr 2007
    Posts
    23
    Statement 1:
    insert into table1(username,access_time) select username, current_timestamp from table2

    Statement 1 works fine in my application and CC.

    Statement 2:
    insert into table1(username,access_time) select username, current_timestamp from table2 where uid=102

    Statement 2 fails at my application but works nicely at CC. where uid is the auto increment primary key of table2.

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What sort of JDBC construct (java.sql.Statement, java.sql.PreparedStatement, etc) are you using? Could you post the jdbc code that is failing?

    Andy

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    ...and what's the JDBC trace saying?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •