usa100
asked on
Please help: Problem with setLong during SELECT
Hi All:
When I try to use the following code it fails during runtime. Long type is passed as Command Line Argument to the Java Client.
Any help will be greatly appreciated.
Thanks
-------------------------- -----
try // do query
{
String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = ?";
System.out.println("SELECT STATEMENT....:"+"\t"+query );
long PUBLISH_ID = EVENT_ID;
System.out.println("EVENT_ ID to be used is:"+"\t"+PUBLISH_ID);
pstmt1 = conn.prepareStatement(quer y);
pstmt1.setLong(1,PUBLISH_I D);
// pstmt1.setString(1,Long.to String(EVE NT_ID));
rs1 = pstmt1.executeQuery(query) ;
System.out.println(rs1);
RUNTIME ERROR..................... ........
SELECT STATEMENT....: SELECT * FROM Doc_Log WHERE EVENT_ID = ?
EVENT_ID to be used is: 4703996961324730781
java.sql.SQLException: ORA-01008: not all variables bound
at oracle.jdbc.dbaccess.DBErr or.throwSq lException (DBError.j ava:168)
at oracle.jdbc.oci8.OCIDBAcce ss.check_e rror(OCIDB Access.jav a:1597)
at oracle.jdbc.oci8.OCIDBAcce ss.parseEx ecuteDescr ibe(OCIDBA ccess.java :79
8)
at oracle.jdbc.driver.OracleS tatement.d oExecuteQu ery(Oracle Statement. jav
a:1674)
at oracle.jdbc.driver.OracleS tatement.d oExecuteWi thTimeout( OracleStat eme
nt.java:1870)
at oracle.jdbc.driver.OracleS tatement.e xecuteQuer y(OracleSt atement.ja va:
538)
at Doc_Pub.FailedEventReader( Doc_Pub.ja va:334)
at Doc_Pub.main(Doc_Pub.java: 241)
BinaryReader: Error coming out of MyReader!
Exception in thread "main" java.lang.NullPointerExcep tion
at Doc_Pub.FailedEventReader( Doc_Pub.ja va:461)
at Doc_Pub.main(Doc_Pub.java: 241)
When I try to use the following code it fails during runtime. Long type is passed as Command Line Argument to the Java Client.
Any help will be greatly appreciated.
Thanks
--------------------------
try // do query
{
String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = ?";
System.out.println("SELECT
long PUBLISH_ID = EVENT_ID;
System.out.println("EVENT_
pstmt1 = conn.prepareStatement(quer
pstmt1.setLong(1,PUBLISH_I
// pstmt1.setString(1,Long.to
rs1 = pstmt1.executeQuery(query)
System.out.println(rs1);
RUNTIME ERROR.....................
SELECT STATEMENT....: SELECT * FROM Doc_Log WHERE EVENT_ID = ?
EVENT_ID to be used is: 4703996961324730781
java.sql.SQLException: ORA-01008: not all variables bound
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.oci8.OCIDBAcce
at oracle.jdbc.oci8.OCIDBAcce
8)
at oracle.jdbc.driver.OracleS
a:1674)
at oracle.jdbc.driver.OracleS
nt.java:1870)
at oracle.jdbc.driver.OracleS
538)
at Doc_Pub.FailedEventReader(
at Doc_Pub.main(Doc_Pub.java:
BinaryReader: Error coming out of MyReader!
Exception in thread "main" java.lang.NullPointerExcep
at Doc_Pub.FailedEventReader(
at Doc_Pub.main(Doc_Pub.java:
How is EVENT_ID defined in the table?
ASKER
Hi CEHJ:
Thanks for your quick response.
EVENT_ID was captured as type Long through one of my Java Clients from a Broker. However EVENT_ID is defined as type NUMBER in the Oracle table. Because I did need to join couple of Oracle tables using the Long type as key.
Therefore I decided to insert this Long type in the Oracle as a Number.
EVENT_ID NOT NULL NUMBER(38)
I parse the command line argument as below before I setLong in the SELECT query.
EVENT_ID = Long.valueOf(ID).longValue ();
Thanks
Thanks for your quick response.
EVENT_ID was captured as type Long through one of my Java Clients from a Broker. However EVENT_ID is defined as type NUMBER in the Oracle table. Because I did need to join couple of Oracle tables using the Long type as key.
Therefore I decided to insert this Long type in the Oracle as a Number.
EVENT_ID NOT NULL NUMBER(38)
I parse the command line argument as below before I setLong in the SELECT query.
EVENT_ID = Long.valueOf(ID).longValue
Thanks
What happens when you try to execute:
SELECT * FROM Doc_Log WHERE EVENT_ID = 4703996961324730781
at the SQL Plus prompt?
SELECT * FROM Doc_Log WHERE EVENT_ID = 4703996961324730781
at the SQL Plus prompt?
ASKER
It works fine from SQL Plus prompt.
Thanks Again
Thanks Again
ASKER
Is it true that WHERE clause doesn't work with Long Type in Oracle.
(I am using oracle.jdbc.driver.OracleD river in my Java Client.)
String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = ?";
System.out.println("SELECT STATEMENT....:"+"\t"+query );
long PUBLISH_ID = EVENT_ID;
System.out.println("EVENT_ ID to be used is:"+"\t"+PUBLISH_ID);
pstmt1 = conn.prepareStatement(quer y);
pstmt1.setLong(1,EVENT_ID) ;
// pstmt1.setString(1,Long.to String(EVE NT_ID));
rs1 = pstmt1.executeQuery(query) ;
System.out.println(rs1);
(I am using oracle.jdbc.driver.OracleD
String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = ?";
System.out.println("SELECT
long PUBLISH_ID = EVENT_ID;
System.out.println("EVENT_
pstmt1 = conn.prepareStatement(quer
pstmt1.setLong(1,EVENT_ID)
// pstmt1.setString(1,Long.to
rs1 = pstmt1.executeQuery(query)
System.out.println(rs1);
I can't think what it could be. Have you tried setFloat()?
You could be right:
http://www.arrowsent.com/oratip/tip23.htm
http://www.arrowsent.com/oratip/tip23.htm
Having said that, there shouldn't be a problem as the table column is NUMBER not LONG
Try calling executeQuery() without passing the statement in. You already set the prepareStatement with this line:
pstmt1 = conn.prepareStatement(quer y);
pstmt1.setLong(1,PUBLISH_I D);
So try this:
pstmt1 = conn.prepareStatement(quer y);
pstmt1.setLong(1,PUBLISH_I D);
rs1 = pstmt1.executeQuery();
pstmt1 = conn.prepareStatement(quer
pstmt1.setLong(1,PUBLISH_I
So try this:
pstmt1 = conn.prepareStatement(quer
pstmt1.setLong(1,PUBLISH_I
rs1 = pstmt1.executeQuery();
ASKER
It doesn't work either.
I really appreciate your time and efforts.
Thanks a lot.
I really appreciate your time and efforts.
Thanks a lot.
ASKER
Hi Friends:
Have a great weekend. I could solve this problem.
Thanks a lot for your valuable time and efforts.
Best Regards.
Have a great weekend. I could solve this problem.
Thanks a lot for your valuable time and efforts.
Best Regards.
What was the answer?
Hi jamoville,
the answer is what you offered as the solution.
usa100 was calling method
ResultSet executeQuery( String query )
declared by the superinterface Statement, instead of calling (as you suggested) method
ResultSet PreparedStatement#executeQ uery()
by pre-setting the long-type parameter of the PreparedStatement.
IMO, your answer should have been accepted.
Greetings
</ntr> :)
the answer is what you offered as the solution.
usa100 was calling method
ResultSet executeQuery( String query )
declared by the superinterface Statement, instead of calling (as you suggested) method
ResultSet PreparedStatement#executeQ
by pre-setting the long-type parameter of the PreparedStatement.
IMO, your answer should have been accepted.
Greetings
</ntr> :)
ASKER
The Correct Answer was:
String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = EVENT_ID";
Thanka All
String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = EVENT_ID";
Thanka All
Not sure how this would work. EVENT_ID is now a string without a numerical value. Appending the EVENT_ID variable to the string would work also but is less efficient.
Hi usa100, :-)
The answer to the problem with business logic might have been
String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = EVENT_ID";
...but the answer to your question for exception:
(Please help: Problem with setLong during SELECT)
java.sql.SQLException: ORA-01008: not all variables bound
...is what I quoted that jamoville said.
If you applied that then your
PreparedStatement#setLong( index, longValue )
method would work.
Best wishes,
</ntr> :)
The answer to the problem with business logic might have been
String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = EVENT_ID";
...but the answer to your question for exception:
(Please help: Problem with setLong during SELECT)
java.sql.SQLException: ORA-01008: not all variables bound
...is what I quoted that jamoville said.
If you applied that then your
PreparedStatement#setLong(
method would work.
Best wishes,
</ntr> :)
ASKER
I have tried Jamoville's solution but it failed during run time.
Best regards
Best regards
Hi usa100, :-)
You tried this:
j> So try this:
j>
j>
j> pstmt1 = conn.prepareStatement(quer y);
j> pstmt1.setLong(1,PUBLISH_I D);
j>
j> rs1 = pstmt1.executeQuery();
instead of this:
u> pstmt1 = conn.prepareStatement(quer y);
u> pstmt1.setLong(1,PUBLISH_I D);
u> // pstmt1.setString(1,Long.to String(EVE NT_ID));
u> rs1 = pstmt1.executeQuery(query) ;
...and it failed?
Life is full of surprises :-)
Best wishes,
</ntr> :)
You tried this:
j> So try this:
j>
j>
j> pstmt1 = conn.prepareStatement(quer
j> pstmt1.setLong(1,PUBLISH_I
j>
j> rs1 = pstmt1.executeQuery();
instead of this:
u> pstmt1 = conn.prepareStatement(quer
u> pstmt1.setLong(1,PUBLISH_I
u> // pstmt1.setString(1,Long.to
u> rs1 = pstmt1.executeQuery(query)
...and it failed?
Life is full of surprises :-)
Best wishes,
</ntr> :)
Not sure how this would work. EVENT_ID is now a string without a numerical value. Appending the EVENT_ID variable to the string would work also but is less efficient.
What I am seeing here is that usa100 was querying the database with the following query to begin with:
String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = ?";
Unless the field EVENT_ID in the database table Doc_Log is of type char and contains the value "?" he is going to get an SQL exception of some type based on the fact that he is querying the wrong datatype in the field.
When he changed the query to:
String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = EVENT_ID";
and he has obviously set his variable EVENT_ID to some value his query will now execute and the EVENT_ID field in the database table contains this value (EVENT_ID). Use of either hungarian notation or a different name for the variable EVENT_ID would have made the problem easier to see.
What would have made more sense would have been to call his variable EVENT_ID something different (say lEvent_ID or lEVENT_ID (if it is truely a constant)) so that it is more easily distinguishable from the database field EVENT_ID. Also prepending the database field with the table name would have at least made the readability of the query statement less confusing:
String query = "SELECT * FROM Doc_Log WHERE Doc_Log.EVENT_ID = lEvent_ID";
Lon
String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = ?";
Unless the field EVENT_ID in the database table Doc_Log is of type char and contains the value "?" he is going to get an SQL exception of some type based on the fact that he is querying the wrong datatype in the field.
When he changed the query to:
String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = EVENT_ID";
and he has obviously set his variable EVENT_ID to some value his query will now execute and the EVENT_ID field in the database table contains this value (EVENT_ID). Use of either hungarian notation or a different name for the variable EVENT_ID would have made the problem easier to see.
What would have made more sense would have been to call his variable EVENT_ID something different (say lEvent_ID or lEVENT_ID (if it is truely a constant)) so that it is more easily distinguishable from the database field EVENT_ID. Also prepending the database field with the table name would have at least made the readability of the query statement less confusing:
String query = "SELECT * FROM Doc_Log WHERE Doc_Log.EVENT_ID = lEvent_ID";
Lon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Neutron,
Thats new to me. Thanks for the explanation. I will look into it.
Lon
Thats new to me. Thanks for the explanation. I will look into it.
Lon
Hi Lon, :)
Of course that is what we all do and that is the Purpose of EE - to exchange :-)
Btw, I think I sense that Delphi force is strong within your family. ;-)
Greetings,
</ntr> :)
Of course that is what we all do and that is the Purpose of EE - to exchange :-)
Btw, I think I sense that Delphi force is strong within your family. ;-)
Greetings,
</ntr> :)
ASKER
Excellent......
</ntr>
You'll need to ellaborate on the Delphi response. Never touch the stuff...
Lon
You'll need to ellaborate on the Delphi response. Never touch the stuff...
Lon
Hi Lon, :-)
L> You'll need to ellaborate on the Delphi response. Never touch the stuff...
My mistake then.
The technique you used for variable binding in SQL statement is exactly like in Delphi :-)
Greetings,
</ntr> :)
L> You'll need to ellaborate on the Delphi response. Never touch the stuff...
My mistake then.
The technique you used for variable binding in SQL statement is exactly like in Delphi :-)
Greetings,
</ntr> :)
Hi usa100, :-)
Sorry but you rewarded the points to the wrong guy :-)
I have no objections to rewarded points - when I earn them.
jamoville answered your question, and all I have done was asking why his answer was rejected, giving the reasons which made me believe that his answer was correct.
Can some of the EE Admins transfer rewarded points to jamoville?
If not I will post the 20point question with title 'Points for jamoville' and reward it with an A. :-)
Best wishes,
</ntr> :)
Sorry but you rewarded the points to the wrong guy :-)
I have no objections to rewarded points - when I earn them.
jamoville answered your question, and all I have done was asking why his answer was rejected, giving the reasons which made me believe that his answer was correct.
Can some of the EE Admins transfer rewarded points to jamoville?
If not I will post the 20point question with title 'Points for jamoville' and reward it with an A. :-)
Best wishes,
</ntr> :)
Hi jamoville, :-)
Ok, I will post now a 20point question with title 'Points for jamoville' and reward it with an A when you make any posting to that question (Java programming Topic). :-)
Greetings,
</ntr> :)
Ok, I will post now a 20point question with title 'Points for jamoville' and reward it with an A when you make any posting to that question (Java programming Topic). :-)
Greetings,
</ntr> :)