Solved

Please help: Problem with setLong during SELECT

Posted on 2002-07-19
28
456 Views
Last Modified: 2012-06-27
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)
0
Comment
Question by:usa100
  • 8
  • 8
  • 5
  • +2
28 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 7165450
How is EVENT_ID defined in the table?
0
 

Author Comment

by:usa100
ID: 7165495
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
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 7165540
What happens when you try to execute:

SELECT * FROM Doc_Log WHERE EVENT_ID = 4703996961324730781

at the SQL Plus prompt?
0
 

Author Comment

by:usa100
ID: 7165572
It works fine from SQL Plus prompt.

Thanks Again
0
 

Author Comment

by:usa100
ID: 7165588
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);
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 7165593
I can't think what it could be. Have you tried setFloat()?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 7165601
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 7165613
Having said that, there shouldn't be a problem as the table column is NUMBER not LONG
0
 
LVL 5

Expert Comment

by:jamoville
ID: 7165654
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();

0
 

Author Comment

by:usa100
ID: 7165672
It doesn't work either.

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

Author Comment

by:usa100
ID: 7165685
Hi Friends:

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

Best Regards.

0
 
LVL 5

Expert Comment

by:jamoville
ID: 7165688
What was the answer?
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7169200
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> :)
0
 

Author Comment

by:usa100
ID: 7169413
The Correct Answer was:

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

Thanka All
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 5

Expert Comment

by:jamoville
ID: 7169834
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.
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7169872
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> :)
0
 

Author Comment

by:usa100
ID: 7169884
I have tried Jamoville's solution but it failed during run time.

Best regards
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7169906
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> :)
0
 
LVL 5

Expert Comment

by:jamoville
ID: 7170054
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.
0
 

Expert Comment

by:LonAllen
ID: 7170158
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
0
 
LVL 4

Accepted Solution

by:
Neutron earned 20 total points
ID: 7171164
Hi LonAllen, :-)

L> String query = "SELECT * FROM Doc_Log WHERE EVENT_ID = ?";

This form (with '?' instead of actual value is a normal was to set variable values in java.sql.PreparedStatement implementations. Each '?' has its respective index, so you can use methods like
       PreparedStatement#setLong
       PreparedStatement#setString
       PreparedStatement#setTimestamp

Best regards,
    </ntr> :)

0
 

Expert Comment

by:LonAllen
ID: 7172571
Neutron,

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


Lon
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7173646
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> :)
0
 

Author Comment

by:usa100
ID: 7179906
Excellent......
0
 

Expert Comment

by:LonAllen
ID: 7179927
</ntr>

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

Lon
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7180010
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> :)
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7180020
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> :)
0
 
LVL 4

Expert Comment

by:Neutron
ID: 7185079
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> :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

758 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

22 Experts available now in Live!

Get 1:1 Help Now