This week's book giveaway is in the JavaScript forum.
We're giving away four copies of Svelte and Sapper in Action and have Mark Volkmann on-line!
See this thread for details.
Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Syntax error between dates

 
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • 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
 
Marshal
Posts: 25823
69
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?
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • 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?
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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
 
author
Posts: 23883
142
jQuery Eclipse IDE Firefox Browser VI Editor C++ Chrome Java Linux 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
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • 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.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried this:
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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.
 
Angus Ferguson
Ranch Hand
Posts: 1402
3
Netbeans IDE Spring Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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.
 
    Bookmark Topic Watch Topic
  • New Topic