Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • 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
I have this exception and I try to put the query in the Oracle SQL Developer and it is work
her in the method I have this exception:

java.sql.SQLException: ORA-00933: SQL command not properly ended

 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the actual query being run? That is, what is the final statement?

It should be noted that building a statement is called dynamic SQL, and is generally considered a bad idea. You might want to consider using placeholders instead.
 
wadha alketbi
Ranch Hand
Posts: 53
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to select (task_id, task_name,end_date, status_name, employee_name FROM TASK,STATUS,EMPLOYEE) and all of these will be displayed in the interface but i have 4 fields that the user can enter the id or the task name or completed date from or completed date to
so the general statement is:



then i want to add to this query if any of the fields entered by the user
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Instead of i

Try something like:
No dynamic SQL, and a much simpler statement.
 
wadha alketbi
Ranch Hand
Posts: 53
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tank you for your help, i put it like that



now i have this exception:
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Get rid of the if() and only use SQL.


This gets rid of the complex logic, avoids dynamic SQL, and makes an easy to read statement. The value for each parameter will need to be passed when execute the statement.
 
wadha alketbi
Ranch Hand
Posts: 53
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is my way wrong? I want to check the date so I have condition
I did what you said but still I'm getting the same exception
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're way is not wrong, but, as dynamic SQL, it is open to SQL injection. Further, without looking at the statement before it is run, it is hard to tell what is being done.

Please post the actual statement being run (after everything is concatenated).
 
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:Is my way wrong? I want to check the date so I have condition

No, your query is not wrong. But when creating queries you should always use prepared statements. Using prepared statements has nothing but benefits:
  • no SQL injection attacks
  • you don't have to worry about character escaping (e.g. if your task name contains a quote, your current query will fail)
  • you might benefit from a performance advantage
  • More info about prepared statements can be found here.

    And using the NVL function is a very nice workaround to get rid of all if statements in your code. More info about this NVL function can be found here.

    wadha alketbi wrote:now i have this exception:
    java.sql.SQLException: Missing IN or OUT parameter at index:: 1

    That's happening because you are not setting a value for the placeholders in your query. If you have read the aforementioned article about prepared statements, you'll know what to do to change your code and get rid of this error.

    Hope it helps!
    Kind regards,
    Roel
     
    Brian Tkatch
    Bartender
    Posts: 567
    25
    Linux Notepad Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:using the NVL function is a very nice workaround to get rid of all if statements in your code.

    FWIW, it may only be helpful if the statement is not run repeatedly, or the query makes good use of indexes. Otherwise, it's can be very bad. I made the suggestion, just to get the query rolling. Alternatively, complex clauses can be used (WHERE ? IS NULL OR Id = ?). The former is cleaner (text-wise), the latter is clearer (logic-wise), and possibly easier for the query rewriter to remove the query. Not sure about that last point. Id = id can logically be removed, but i have a niggling doubt that it doesn't happen for some reason.

    Anyway, the real way to do it is to use separate queries. (Or one SP/FUNCTION that encloses separate queries.) That is, by far, the best method for simple queries, do to simplicity of code, and index usage. Though, a compromise can be made via CTEs and/or UNION ALLs, but i digress.
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you all,
    I do what you said but still I didn't getting the result ):
    what you mean by this "you are not setting a value for the placeholders in your query."?
    I read about NVL but I want it without using NVL
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    wadha alketbi wrote: what you mean by this "you are not setting a value for the placeholders in your query."?
    I read about NVL but I want it without using NVL

    It's not related with the NVL function. If you have a query which doesn't use the NVL function, but still uses the placeholders in the query, you need to set the appropriate values for this query before executing this query. That's explained very well (and even illustrated with a few code examples) in the link I have shared in my previous post about prepared statements.

    And here is another small code example

    Hope it helps!
    Kind regards,
    Roel
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you so much for your help, now the method become like that but there is new exception:
    ORA-01861: literal does not match format string




     
    Brian Tkatch
    Bartender
    Posts: 567
    25
    Linux Notepad Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    What is the results of:
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    in the console:
    SELECT TASK.task_id, TASK.task_name, TASK.end_date, STATUS.status_name, EMPLOYEE.employee_name FROM TASK,STATUS,EMPLOYEE WHERE TASK.STATUS_CODE = STATUS.STATUS_CODE AND TASK.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID AND TASK.TASK_ID ='1' AND TASK.TASK_NAME ='testing' AND TASK.end_date BETWEEN'2016-04-23' AND '2016-04-27'
    java.sql.SQLException: Invalid column index

    and i try it in the Oracle SQL Developer:
    ORA-01861: literal does not match format string
    01861. 00000 - "literal does not match format string"
    *Cause: Literals in the input must be the same length as literals in
    the format string (with the exception of leading whitespace). If the
    "FX" modifier has been toggled on, the literal must match exactly,
    with no extra whitespace.
    *Action: Correct the format string to match the literal.
     
    Brian Tkatch
    Bartender
    Posts: 567
    25
    Linux Notepad Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The format string is the date format string. Dates should always be converted (unless you explicitly set NLS_DATE_FORMAT for that session). For simplicity:
    Also, assuming TASK_ID is numeric, the single quotes around 1 are redundant, and cause a needless conversion.
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    wadha alketbi wrote:Thank you so much for your help, now the method become like that but there is new exception:
    ORA-01861: literal does not match format string

    Why are you setting parameters on stmt Your query doesn't have any placeholders at all! You are still concatenating the values into the query itself (which is a bad practice and has a bunch of drawbacks).

    You should definitely use prepared statements and placeholders in your query. If you know have a task name which contains a quote, your query will fail. I'll give you a few pointers about how you can improve your code

    Hope it helps!
    Kind regards,
    Roel
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you, I follow your way but nothing happen with me ): I do not know what is the reason I have same exception
    literal does not match format string
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I think the problem is in the date may be I have to use To_Date function
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    wadha alketbi wrote:Thank you, I follow your way but nothing happen with me ): I do not know what is the reason I have same exception
    literal does not match format string

    Can you provide the actual code you are currently using? Please provide only the appropriate part of the code.
     
    Brian Tkatch
    Bartender
    Posts: 567
    25
    Linux Notepad Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    wadha alketbi wrote:I think the problem is in the date may be I have to use To_Date function

    That is correct. However, because you happen to be using the ANSI format, you can use DATE instead.

    To use TO_DATE(), you should supply the format, which would be TO_DATE(?, 'YYYY-MM-DD')
     
    Dave Tolls
    Ranch Hand
    Posts: 2100
    15
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    If it's a DATE then you should be passing in a Date object into the PreparedStatement.
    Using TO_DATE in JDBC tends to indicate you're not using the correct datatypes.
     
    Brian Tkatch
    Bartender
    Posts: 567
    25
    Linux Notepad Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Dave Tolls wrote:TO_DATE in JDBC tends to indicate you're not using the correct datatypes.

    Or strict typing. But, considering the above coded uses dynamic SQL, there doesn't seem to be any typing.
     
    Dave Tolls
    Ranch Hand
    Posts: 2100
    15
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I can't think of ever needing to use it in a query in JDBC.
    You've got Java objects, and if one of them has a Date then it really ought to be a Date.

    If this is input from somewhere (anywhere) then it should already have been validated (and turned into a Date) long before it gets to this point.
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you all, I'm trying to use different ways but all same result so I will try To_Date

    This is the first method:





    The second method:



    In the second method, when I print the query the value of the task id and task name are showing like that 'true', I don't know why. also, in the For loop it says invalid index (i+1).
    The value of task id , task name , completeDateFrom and completDateTo are from the interface so the backBean will call method from the business class and then the method from business will call searchForTask method which is in DAO class. now I will try To_Date and i hope will work with me.
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I'm starting to wonder if you really have read (and understood) the links and explanations which was given by me (and other ranchers). We all have advised you to use prepared statements and use placeholders in your query instead of concatenating constant values into your query. Because that's not only a very bad practice, it has also many drawbacks! I even provided you with a code snippet to illustrate how you should refactor your existing code. You even have used this code snippet in your second method, but you have removed the most important parts of the code (placeholders and temporarily storing the parameters) and you are still concatenating your values directly into your query. So it's quite normal that your code is't working, because the code which has to be changed is still the same as in your original post...
     
    wadha alketbi
    Ranch Hand
    Posts: 53
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Good morning, it is working now thank you all for your help, the problem was in the date
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic