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

Syntax error between dates

 
Isaac Ferguson
Ranch Hand
Posts: 1034
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

when I run this query:



From var is an String and to var too

i get the next error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN1981-01-01AND1982-01-01' at line 1

Any idea?

Regards,
Isaac
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, it's exactly like the error message says: "You have an error in your SQL syntax".

So the first thing to do is to look at the SQL which the driver claims has an error; why don't you print out the string which contains the SQL?
 
Isaac Ferguson
Ranch Hand
Posts: 1034
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not sure if I m getting what you mean I have debugged and printed out the content of preparedStatement :


preparedStatement = (com.mysql.jdbc.JDBC4PreparedStatement) com.mysql.jdbc.JDBC4PreparedStatement@4fc96776: select * from formdinero_usuarios WHERE created_at BETWEEN1981-01-01AND1982-01-01

The query is :




And the error I get in the console is:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN1981-01-01AND1981-01-01' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at com.bari.dao.UserDao.getFilteredUsers(UserDao.java:140)
at com.bari.controller.UserController.doGet(UserController.java:116)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)


Any idea?
 
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
Seems you should definitely add a few spaces in your query.
And it would even better if you used a prepared statement as well:And then you can supply the parameter values and execute the query.

Hope it helps!
Kind regards,
Roel
 
Henry Wong
author
Marshal
Pie
Posts: 22114
88
C++ Chrome Eclipse IDE Firefox Browser Java jQuery Linux VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

It looks like the JDBC code is complaining that it doesn't know what "BETWEEN1981-01-01AND1981-01-01" part of your SQL select statement means. Perhaps you forgot to put spaces in your SQL?

Henry
 
Isaac Ferguson
Ranch Hand
Posts: 1034
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok now the syntax issue is solved.

My code is like:

PreparedStatement preparedStatement = connection.prepareStatement("select * from formdinero_usuarios WHERE created_at BETWEEN "+ from+ " AND " + to);

It looks like a problem with spaces after the between clause.
 
Isaac Ferguson
Ranch Hand
Posts: 1034
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a code like this:


It works well when I run it from the console on MySql, and show a couple of records

No messages or errors are in the console

Any idea?

Regards,
Isaac
 
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
So you use a prepared statement, but you are actually not using a prepared statement.

The query of a prepared statement would look like: And then you can set the parameter values using the setParameter methods. Could you try this? As it's probably some kind of data type conversion issue.
 
Isaac Ferguson
Ranch Hand
Posts: 1034
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried this:
 
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
You are close, but not almost there.

First have a look at the Using Prepared Statements section of Oracle's official JDBC tutorial and then change your code accordingly.
 
Isaac Ferguson
Ranch Hand
Posts: 1034
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I read the article and I got the concept, now I changed the code.

Now it looks like this where from and date are two variables with Date types:



When debugging I got the next, when selecting over the



When I tried to execute that line of code, I got the next error:



Any more idea?
 
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
You use the executeQuery() method to execute SQL queries (a SELECT statement) and the executeUpdate() method to execute DML (INSERT, UPDATE, DELETE) or DDL (e.g. CREATE TABLE) statements.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic