[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

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)
0
usa100
Asked:
usa100
  • 8
  • 8
  • 5
  • +2
1 Solution
 
CEHJCommented:
How is EVENT_ID defined in the table?
0
 
usa100Author Commented:
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
 
CEHJCommented:
What happens when you try to execute:

SELECT * FROM Doc_Log WHERE EVENT_ID = 4703996961324730781

at the SQL Plus prompt?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
usa100Author Commented:
It works fine from SQL Plus prompt.

Thanks Again
0
 
usa100Author Commented:
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
 
CEHJCommented:
I can't think what it could be. Have you tried setFloat()?
0
 
CEHJCommented:
0
 
CEHJCommented:
Having said that, there shouldn't be a problem as the table column is NUMBER not LONG
0
 
jamovilleCommented:
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
 
usa100Author Commented:
It doesn't work either.

I really appreciate your time and efforts.
Thanks a lot.
0
 
usa100Author Commented:
Hi Friends:

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

Best Regards.

0
 
jamovilleCommented:
What was the answer?
0
 
NeutronCommented:
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
 
usa100Author Commented:
The Correct Answer was:

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

Thanka All
0
 
jamovilleCommented:
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
 
NeutronCommented:
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
 
usa100Author Commented:
I have tried Jamoville's solution but it failed during run time.

Best regards
0
 
NeutronCommented:
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
 
jamovilleCommented:
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
 
LonAllenCommented:
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
 
NeutronCommented:
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
 
LonAllenCommented:
Neutron,

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


Lon
0
 
NeutronCommented:
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
 
usa100Author Commented:
Excellent......
0
 
LonAllenCommented:
</ntr>

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

Lon
0
 
NeutronCommented:
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
 
NeutronCommented:
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
 
NeutronCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now