• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jeff Foflygen
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jeff Foflygen
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic