• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL result to be used in subsequent query in Java

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
 I am trying to run a query over one table and extract a single field (there will only ever be one record!).
 I then want to use the extracted field to run another query inside the same Java program.  
 I am passing in the parameter called Lot  (hence why I put the single quotes around it - not sure if I need them though but LOT_ID is a character field)

 When the first query runs, I want to use LOT_ID  to get the field LOT_CODE from the second query.
 I cannot compile this at the moment as it does not like MyLot.

Any advice?

Dave



 
Rancher
Posts: 89
13
Scala Eclipse IDE MySQL Database Tomcat Server Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Dave,

Can you post the error it is giving you? Include the full stack trace with all the method calls. That would be useful in helping to solve your issue.

-Zach
 
Dave Price
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
 I get 1 compilation error.  I was hoping that MyLot would have been defined in the previous query.


                                                                                                         
                               
                                                                                   
 
Zach Rode
Rancher
Posts: 89
13
Scala Eclipse IDE MySQL Database Tomcat Server Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah I see. So When we are executing JDBC it actually runs the JPQL as SQL inside the database. Therefore, when you are selecting LOT_ID into MyLot, you are only creating the variable on the Oracle Database and not in the JVM. As a solution, you can create the variable MyLot in java and then place it in your JPQL Statement by using the result set from the first statement and assigning it to that variable you created. Alternatively, you might be able to call the variable you create in the Oracle DB just like you would as a regular SQL statement, although I have not tried this with JDBC so I'm not 100% sure it will work.

as a note, JPQL stands for Java Persistence Query Language and is just the how you have written the SQL in java.

Hopefully that helps.

-Zach
 
Dave Price
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
 So I managed to get it to compile, but when it ran, it fell over form the following error.  



 I guess it doesn't like putting the result into my fields and I don't know of any other way of saving the LOT_ID result.

Dave
 
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@ Dave Price



About the above code from the initial message of this post: the way a prepared statement is to be executed is as follows:



And, what is the database the application is accessing (is it an Oracle or MySQL)?
 
Dave Price
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oracle
 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dave, have you used JDBC before with Java programming?
 
Dave Price
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nope.  
 I kinda botching this together from threads I have found as this is a one off task.
I'm an iSeries programmer by trade and never gone near the Java language.

An SQL admin gave me the tables & fields I need - the rest I am putting together the best I can.

Dave
 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is my suggestion. Try to get some familiarity with JDBC Java programming. Here is a link to Oracle's Java JDBC tutorials. You will find some examples and how the JDBC API's (Statement, PreparedStatement, ResultSet, etc..) are used.

https://docs.oracle.com/javase/tutorial/jdbc/index.html

Then you will be able answer some of the questions you have now. JDBC and databses are fun and challenging to learn (I learnt them too)
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Zach Rode wrote:
as a note, JPQL stands for Java Persistence Query Language and is just the how you have written the SQL in java.
-Zach



JPQL is not the same thing as SQL. JPQL is a platform-independent query language for the Java Persistence API (JPA). It looks a lot like SQL, but there are some serious differences. You can't use JPQL with raw JDBC, only SQL.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic