File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes java.sql.SQLException: ORA-00923: FROM keyword not found where expected Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: ORA-00923: FROM keyword not found where expected" Watch "java.sql.SQLException: ORA-00923: FROM keyword not found where expected" New topic
Author

java.sql.SQLException: ORA-00923: FROM keyword not found where expected

Jeff Foflygen
Greenhorn

Joined: Mar 12, 2009
Posts: 12
I am trying to run an application that is talking to an oracle DB. when I run the application, enter the value into the JTextField, then hit the submit button, I get the error: java.sql.SQLException: ORA-00923: FROM keyword not found where expected
I have checked the SQL, it is correct before anybody ask. If I place single quotes around the ? for my prepared statement, I get the error "invalid column index" take the quotes out, I get the ORA-00923 error. I know it works because if I modify the code and run it with a value in place of the ? like a regular SQL statement it returns the values that are to fill out in my 15 JTextFields of my swing application. any ideas or help would be much appreciated! help!
Here is the code for the JDBC connection class:
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Ah. I love these Oracle Apps queries. Done loads of them.

have checked the SQL, it is correct before anybody ask.
No it's not.
Why don't you print the sql statement and have a look at what is wrong?
I know it works because if I modify the code and run it with a value in place of the ? like a regular SQL statement it returns the values
Can you retry that? Please print both sql statements from your java code, and show them in this post. We can compare them.

I think it fails at the first + on line 33.

Why do you put ); on line 88? Where are these brackets opened? Why the semicolon?


OCUP UML fundamental and ITIL foundation
youtube channel
Jeff Foflygen
Greenhorn

Joined: Mar 12, 2009
Posts: 12
I printed the SQL statement by itself, entered a value in place of the ? and used TOAD to run it, I had to put a single quotes around the value, once I did that it returned the data that I want.

next I went back to the code that worked before returning the same data. it is below. the one thing that I found different was that this code was before I put in java to call the JTextField to enter the value, this code has the value in place already, when I hit my submit button, it runs this class and returns the data.

Shahzad Masud
Greenhorn

Joined: Oct 18, 2007
Posts: 11
This problem is quite simple. If I may have faced this problem, then I shall print the query and paste it in DBVisualizer and use query formatting feature. After using it, the query will be formatted properly and I can inspect very easily.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

This code looks much better.
Try to replace
+ " pha.segment1 = '907878' AND "
by (indeed, without single quotes)
+ " pha.segment1 = ? AND "


Then try running it with this fixed parameter binding:
prest.setString(1, "907878");


If it now returns a value, you can start using the input from your textfield:
prest.setString(1, textfieldInput);
Jeff Foflygen
Greenhorn

Joined: Mar 12, 2009
Posts: 12
I did as you suggested; here is what it looks like


Then I added the prepared statement code:


when I click the submit button, I get the following error:
java.sql.SQLException: ORA-01008: not all variables bound

Not too sure what that means? If I place the value in with single quotes, it works and returns my data.

???
Thanks!
Jeff
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

I suggested:
prest.setString(1, "907878");

You tried:
prest.setLong(1, 907878);

Please try my suggestion.
From your test in TOAD, you already know that the query expects a string, because you had to provide single quotes in TOAD.
Jeff Foflygen
Greenhorn

Joined: Mar 12, 2009
Posts: 12
when I change it to setString I get this error message:
Exception in thread "Thread-2" java.lang.Error: Unresolved compilation problem:
The method setString(int, String) in the type PreparedStatement is not applicable for the arguments (int, int)

In the code that you didn't like to much I noticed that the beginning of the SQL statement looked like this:


In the code now that looks better the beginning of the SQL looks like this:


could that be the cause of the error? Eclipse and JDeveloper both want me to setLong.

Thanks!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

I just described the table po_headers_all. Segment1 is VARCHAR2(20).
This should be correct for binding a varchar2:
prest.setString(1, "907878");

....Distinct ....could that be the cause of the error?
no




Eclipse and JDeveloper both want me to setLong.
Eh? po.po_headers_all.SEGMENT1 is a varchar2.
Clueless at this point.
Jeff Foflygen
Greenhorn

Joined: Mar 12, 2009
Posts: 12
Jan,

Thanks for all of your help! I got it figured out. it is working nicely now, what the problem was, was that the String for number of columns to return the data to was also the same string that I was trying to pull from. (hope that makes sense)

I put in this code,


POData is the name of the JTextField in another class, the same one I wanted to pull the variable from to place into the sql statement. I just had to define the string, and the method of that string.

Thanks!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.SQLException: ORA-00923: FROM keyword not found where expected