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(query);
pstmt1.setLong(1,PUBLISH_ID);
// pstmt1.setString(1,Long.toString(EVENT_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.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:1597)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteDescribe(OCIDBAccess.java:79
8)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.jav
a:1674)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1870)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:
538)
at Doc_Pub.FailedEventReader(Doc_Pub.java:334)
at Doc_Pub.main(Doc_Pub.java:241)
BinaryReader: Error coming out of MyReader!

Exception in thread "main" java.lang.NullPointerException
at Doc_Pub.FailedEventReader(Doc_Pub.java:461)
at Doc_Pub.main(Doc_Pub.java:241)

CEHJ

How is EVENT_ID defined in the table?

usa100

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

CEHJ

What happens when you try to execute:

SELECT * FROM Doc_Log WHERE EVENT_ID = 4703996961324730781

at the SQL Plus prompt?

usa100

ASKER

It works fine from SQL Plus prompt.

Thanks Again

usa100

ASKER

Is it true that WHERE clause doesn't work with Long Type in Oracle.

(I am using oracle.jdbc.driver.OracleDriver 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(query);
pstmt1.setLong(1,EVENT_ID);
// pstmt1.setString(1,Long.toString(EVENT_ID));
rs1 = pstmt1.executeQuery(query);
System.out.println(rs1);

CEHJ

I can't think what it could be. Have you tried setFloat()?

CEHJ

You could be right:
http://www.arrowsent.com/oratip/tip23.htm

CEHJ

Having said that, there shouldn't be a problem as the table column is NUMBER not LONG

jamoville

Try calling executeQuery() without passing the statement in. You already set the prepareStatement with this line:

pstmt1 = conn.prepareStatement(query);
pstmt1.setLong(1,PUBLISH_ID);

So try this:

pstmt1 = conn.prepareStatement(query);
pstmt1.setLong(1,PUBLISH_ID);

rs1 = pstmt1.executeQuery();

usa100

ASKER

It doesn't work either.

I really appreciate your time and efforts.
Thanks a lot.

usa100

ASKER

Hi Friends:

Have a great weekend. I could solve this problem.
Thanks a lot for your valuable time and efforts.

Best Regards.

jamoville

What was the answer?

Neutron

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#executeQuery()
by pre-setting the long-type parameter of the PreparedStatement.

IMO, your answer should have been accepted.

Greetings
</ntr> :)

usa100

ASKER

The Correct Answer was:

String query = "SELECT * FROM Doc_Log "+"WHERE EVENT_ID = EVENT_ID";

Thanka All

jamoville

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.

Neutron

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> :)

usa100

ASKER

I have tried Jamoville's solution but it failed during run time.

Best regards

Neutron

Hi usa100, :-)

You tried this:

j> So try this:
j>
j>
j> pstmt1 = conn.prepareStatement(query);
j> pstmt1.setLong(1,PUBLISH_ID);
j>
j> rs1 = pstmt1.executeQuery();

instead of this:

u> pstmt1 = conn.prepareStatement(query);
u> pstmt1.setLong(1,PUBLISH_ID);
u> // pstmt1.setString(1,Long.toString(EVENT_ID));
u> rs1 = pstmt1.executeQuery(query);

...and it failed?
Life is full of surprises :-)

Best wishes,
</ntr> :)

jamoville

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.

LonAllen

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

ASKER CERTIFIED SOLUTION

Neutron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LonAllen

Neutron,

Thats new to me. Thanks for the explanation. I will look into it.

Lon

Neutron

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> :)

usa100

ASKER

Excellent......

LonAllen

</ntr>

You'll need to ellaborate on the Delphi response. Never touch the stuff...

Lon

Neutron

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> :)

Neutron

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> :)

Neutron

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> :)