• 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
  • Liutauras Vilda
  • Bear Bibeault
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Paul Clapham
  • Devaka Cooray
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Frits Walraven
Bartenders:
  • Carey Brown
  • salvin francis
  • Claude Moore

MySQL syntax error on prepared statement  RSS feed

 
Ranch Hand
Posts: 242
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



The error is by where all the question marks are.  I cannot find what I did wrong.  I put proper spaces in, and I used the exact same number of columns that are in the table.
 
Marshal
Posts: 64107
215
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
. . . and what was the error message? If it has an error number, what does the handbook/website say that error number means?
 
Campbell Ritchie
Marshal
Posts: 64107
215
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do the nulls mean? Are they permitted by the database?
 
Nathan Milota
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the error was at the bottom line.  I needed to change to ps and take out the SQL.


However, I wanted to start the number at 100 for auto increment of the ID.  I just set it to null so the database would assign the ID for me on a not null column.  

It started it at 1 though.
 
Saloon Keeper
Posts: 2504
321
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the ID column is AUTO_INCREMENT then you should not be trying to insert anything in to that column (null or otherwise).

If you want the ID column to start at 100, include a statement in your DDL to alter the AUTO_INCREMENT value to 100:
 
Saloon Keeper
Posts: 20639
122
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ron McLeod wrote:If the ID column is AUTO_INCREMENT then you should not be trying to insert anything in to that column (null or otherwise).



And if you do insert a value for an auto-increment column, the common database behavior is that it will set that value instead of using the next available auto-increment value.

And if that value conflicts with an existing value and the column is defined as unique (as, for example a primary key), then the insert will fail.

And if a later auto-increment operation would set a unique column value to that existing value, then if you are lucky then the auto-increment manager in the DBMS will detect the collision and skip over it, selecting a new auto-increment value. But if you are not, the insert for the auto-incremented value will fail. As far as I know, there's not defined standard on which will happen, so depending on the DBMS vendor and version, consider the results as unpredictable.
 
Greenhorn
Posts: 17
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I just want to suggest a way to debug this type of errors in the future.

Print the SQL statement string using: System.out.println( SQL Statement );

Connect to the database using SQL Developer or any other client and paste the printed statement and run it.

This way you can see what is the problem with the statement.

Let me know if you need more specific information about how to do this.

Best Regards
Alin Bistrian
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!