• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL syntax error on prepared statement

 
Ranch Hand
Posts: 462
3
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What do the nulls mean? Are they permitted by the database?
 
Nathan Milota
Ranch Hand
Posts: 462
3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 4501
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
That which doesn't kill us makes us stronger. I think a piece of pie wouldn't kill me. Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic