• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL command not properly ended

 
wadha alketbi
Ranch Hand
Posts: 53
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good morning,
I have an error in sql command:
java.sql.SQLException: ORA-00933: SQL command not properly ended

this is my select statement:


thank you.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
wadha alketbi wrote:I have an error in sql command:
java.sql.SQLException: ORA-00933: SQL command not properly ended

First of all, if you have multiple conditions in the WHERE clause, you should use AND or OR (not comma or plus sign). Secondly you should consider using prepared statements in order to prevent possible SQL injection attacks. More info about prepared statements can be found here.

Hope it helps!
Kind regards,
Roel
 
Ron McLeod
Bartender
Pie
Posts: 1032
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to use the logical operator AND to link the predicates together, not commas. For example:

SELECT first_name, last_name FROM staff
WHERE location_id='BTLZ' AND building_code='U12';
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you hit an issue like this with concatenated SQL statements I find the easiest thing to do is to log the statement used and then see exactly what it has produced.
It's usually not what you think.

If, after that, you still can't see the issue then take that logged statement and try and run it directly in the db, either using the db's own command line or workbench tool. That should then give you a chance to correct it quickly...far quicker than attempting to fix and rerun using Java.

In this case, though, the first step should be to turn it into a PreparedStatement and bind the variables in, as Roel suggests. Apart from the protection against SQL injection, it also means you don't have to worry about quote marks.

ETA: Actually make that the second step. Get a functioning query outside of Java first, then use that as the basis for the PreparedStatement.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
wadha alketbi wrote:

Please, please, please, do not use dynamic SQL. It is insecure, inefficient, often exploitable, and confusing. Instead, use placeholders and pass the values when executing the statement.

Also, although the SELECT and FROM clauses use commas to separate object names, the WHERE clause is one clause only, and thus must use AND or OR to use multiple conditions.

Finally, when comparing dates, do not pass them as literals. This causes an implicit conversion using defaults you may not want. Instead, be specific:
The 'YYYYMMDD' is just one of the many formats that can be used, change it to match the format passed.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:[Finally, when comparing dates, do not pass them as literals. This causes an implicit conversion using defaults you may not want. Instead, be specific:
The 'YYYYMMDD' is just one of the many formats that can be used, change it to match the format passed.


This would usually result in a review comment.
The date should, except in rare circumstances, be bound using a Date object. Saves all that converting malarky.
Since whatever 't' is appears to be some model object I would hope getStartDate and getEndDate would return Dates.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:The date should, except in rare circumstances, be bound using a Date object.

Doesn't that depend on how the statement is executed? If the placeholder is strictly typed, the host language uses the CLI to tell the RDBMS what the type is, and he host language will pass it in specific format behind the scenes. (IOW, it passes TO_DATE() or its equivalent for you.) If the placeholder is not strictly typed, however, it must be handled manually. Strict typing is better, assuming the language/framework supports it. That being said, i am not familiar with what is supported in Java and completely forgot about strict typing when answering.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic