| Author |
Message
|
| SDS |
 Posted: Wed Feb 29, 2012 2:19 am Post subject: SQL Query Error in WMB |
 |
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
I'm getting a SQL Exception while using variables in the executeQuery argument as below.
| Code: |
| ResultSet rs1 = stmt.executeQuery("INSERT INTO MYORDERS (ORDER_ID, ORDER_STATUS) VALUES (refOrderId, refOrderStatus)"); |
The exception is :
ora-00984 column not allowed here in WMB
But if I hard code the values like below, it works fine.
| Code: |
ResultSet rs1 = stmt.executeQuery("INSERT INTO MYORDERS (ORDER_ID, ORDER_STATUS) VALUES (1234, 'status')");
|
Can anyone say what should be done to resolve this problem since I can't use hard coded value here. |
|
| Back to top |
|
 |
| adubya |
| Posted: Wed Feb 29, 2012 2:26 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
The problem here is that your SQL string is passed literally to Oracle which then attempts to resolve refOrderId and refOrderStatus. As these are presumably variables in your Java code which Oracle has no visibility of then it throws an error. Use a prepared statement in Java like
| Quote: |
| "INSERT INTO MYORDERS (ORDER_ID, ORDER_STATUS) VALUES (?, ?)" |
and when you invoke it pass refOrderId and refOrderStatus as arguments to your Java prepared statement execution method.
I can't remember the Java methods to create/run a prepared statement so haven't included them above but some simple searching of the documentation will find them |
|
| Back to top |
|
|
| SDS |
| Posted: Wed Feb 29, 2012 2:37 am Post subject: |
|
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
Thanks adubya.
But i don't have any idea how to use Java Prepare Statement in WMB Java Compute Node. Do I have to include it as ResultSet argument?
Can you please give me any example of it? |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 29, 2012 2:43 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
Actually I can't see any mention of a prepared statement method in the documentation.
So an alternative (no so nice) method would be to build up your SQL string as follows:-
| Code: |
| ResultSet rs1 = stmt.executeQuery("INSERT INTO MYORDERS (ORDER_ID, ORDER_STATUS) VALUES ("+refOrderId+","+ refOrderStatus+")"); |
|
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 29, 2012 2:55 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
|
| Back to top |
|
|
| SDS |
| Posted: Wed Feb 29, 2012 3:17 am Post subject: |
|
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
Thanks a lot for the help!!
but now its throwing a Null Pointer Exception while executing the PreparedStatement line.  |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 29, 2012 3:25 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| SDS wrote: |
Thanks a lot for the help!!
but now its throwing a Null Pointer Exception while executing the PreparedStatement line. |
I have assumed that the conn variable is populated with a legit Connection object. Is this the case ? |
|
| Back to top |
|
|
| SDS |
| Posted: Wed Feb 29, 2012 4:14 am Post subject: |
|
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
yes.. there was silly mistake in Connection parameter..
the issue is resolved now.
thanks a lot abudya.. 
i have another problem in converting timestamp from string data type to timestamp data type. i am using the following code.
| Code: |
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
java.util.Date businessDayDate = dateFormat.parse(XbusinessDayDate); |
e.g. String XbusinessDayDate = "2012-02-01 10:20:30"
but here the businessDayDate variable takes the value as "Date" only.
do you have any idea how can we convert string "2012-02-01 10:20:30" to timestamp 2012-02-01 10:20:30 to insert it as timestamp in DB?
Also in Prepared Statement if I try to set the Date Variable as below
stmt.setDate(5, businessDayDate);
it gives error as :
| Code: |
| The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, Date) |
Any body having any idea on the above issues plz help out! 
Last edited by SDS on Wed Feb 29, 2012 4:51 am; edited 2 times in total |
|
| Back to top |
|
|
| McueMart |
| Posted: Wed Feb 29, 2012 4:42 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Just a shot in the dark:
Is the stmt.setDate(...) method expecting a java.sql.Date instead of the java.util.Date which you are passing in? |
|
| Back to top |
|
|
| SDS |
| Posted: Wed Feb 29, 2012 4:53 am Post subject: |
|
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
Hi
The problem is resolved!!
I have used below statement directly.
| Code: |
| stmt.setTimestamp(5, Timestamp.valueOf(XbusinessDayDate)) |
And it solved both the problems..
Cheers!!  |
|
| Back to top |
|
|
|
|
