This week's book giveaway is in the Jython/Python forum.
We're giving away four copies of Hands On Software Engineering with Python and have Brian Allbey on-line!
See this thread for details.
Win a copy of Hands On Software Engineering with Python this week in the Jython/Python 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
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

What's wrong with my Insert query ?  RSS feed

 
Ranch Hand
Posts: 468
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'd like to seek help for a insert query.




The error I'm getting is :



MySQL workbench 6.3

I have re-check with reference to several online tutorial including

http://www.edu4java.com/en/sql/sql6.html
https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

but to no avail.

 
Saloon Keeper
Posts: 5124
135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1) There seems to be a space missing between "project.tutor" and "tutorName".

2) There's a " instead of a ' before Sunrise.

3) Parentheses are missing around the list of actual values.
 
Marshal
Posts: 62172
193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Moores wrote:. . .  2) There's a " instead of a ' before Sunrise. . . .

And is there a comma missing after Sunrise'?

If you have that sort of problem, it is probably worthwhile trying the query at the DBMS' command line rather than via a JDBC connection.
 
tangara goh
Ranch Hand
Posts: 468
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Moores wrote:1) There seems to be a space missing between "project.tutor" and "tutorName".

2) There's a " instead of a ' before Sunrise.

3) Parentheses are missing around the list of actual values.



I have put the 'tutorName' on the 2nd line with a 'prefix' + to indicate a continuation.

May I know the Parentheses are referring to the ( right after VALUES ? and end with a ) ?

This is already put in.

After editing point to with the above remaining unchanged, I am still getting the same error message.

Please help.

Tks again.
 
Rancher
Posts: 3794
40
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Top Tip.
If you hit a problem with SQL syntax like this then log the SQL you are producing in your code so you can then execute it directly into the database.
Just logging it (without executing it) is usually enough to spot the inevitable typo.
 
Tim Moores
Saloon Keeper
Posts: 5124
135
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

tangara goh wrote:I have put the 'tutorName' on the 2nd line with a 'prefix' + to indicate a continuation.


That's not what the problem is. The problem is a missing space between two names.

May I know the Parentheses are referring to the ( right after VALUES ? and end with a ) ?


Those aren't the parentheses I was talking about. I specifically said "around the list of actual values", not "around the list pf parameter names". You should read up on the syntax of Insert statements to learn how they look like.

Dave Tolls wrote:log the SQL you are producing in your code so you can then execute it directly into the database.


+1
 
tangara goh
Ranch Hand
Posts: 468
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Moores wrote:

tangara goh wrote:I have put the 'tutorName' on the 2nd line with a 'prefix' + to indicate a continuation.


That's not what the problem is. The problem is a missing space between two names.

May I know the Parentheses are referring to the ( right after VALUES ? and end with a ) ?


Those aren't the parentheses I was talking about. I specifically said "around the list of actual values", not "around the list pf parameter names". You should read up on the syntax of Insert statements to learn how they look like.

Dave Tolls wrote:log the SQL you are producing in your code so you can then execute it directly into the database.


+1



Tks Dave.  Are you referring to something like this as per this thread at Stackoverflow -  https://stackoverflow.com/questions/303994/log-all-queries-in-mysql/14403905#14403905 ?

Hi Tim,

The names 'Zara Ng' there was a space for sure.


OK.  I check the below, I think I know what you are referring to now.

Like this :

INSERT INTO `sakila`.`actor`
 (`actor_id`,
 `first_name`,
 `last_name`,
 `last_update`)
VALUES
 (<{actor_id: }>,
 <{first_name: }>,
 <{last_name: }>,
 <{last_update: CURRENT_TIMESTAMP}>);

Tks so much.  Didn't see this when I google earlier ....



https://dev.mysql.com/doc/workbench/en/wb-generating-sql.html
 
Dave Tolls
Rancher
Posts: 3794
40
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not loggin the query in MySQl.
I mean log it in your Java code.
That is, log the value of sql1.
 
Tim Moores
Saloon Keeper
Posts: 5124
135
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

tangara goh wrote:The names 'Zara Ng' there was a space for sure.



That's irrelevant. You need to pay close attention to detail - that's a crucial aspect of programming. I said:

Tim Moores wrote:The problem is a missing space between two names.



and in particular:

Tim Moores wrote:There seems to be a space missing between "project.tutor" and "tutorName".



So the names in question are attribute names.
 
tangara goh
Ranch Hand
Posts: 468
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Moores wrote:

tangara goh wrote:The names 'Zara Ng' there was a space for sure.



That's irrelevant. You need to pay close attention to detail - that's a crucial aspect of programming. I said:

Tim Moores wrote:The problem is a missing space between two names.



and in particular:

Tim Moores wrote:There seems to be a space missing between "project.tutor" and "tutorName".



So the names in question are attribute names.



Thanks Tim.  This part is resolved now.

But, I am now getting another error :


java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`project`.`tutor_subject`, CONSTRAINT `tutor_subject_ibfk_1` FOREIGN KEY (`tutor_id`) REFERENCES `tutor` (`tutor_id`) ON DELETE CASCADE)
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:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1162)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
at testSubjectIdInserts.testWork.main(testWork.java:81)



Basically, after the query where I insert the tutors into Mysql, I then try to get the last_insert_id to insert to the tutor_subject table.



Please let me know what is wrong.

Tks.
 
Dave Tolls
Rancher
Posts: 3794
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show how you are executing the two statements.
Specifically, what connection they are each using.
 
tangara goh
Ranch Hand
Posts: 468
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Can you show how you are executing the two statements.
Specifically, what connection they are each using.



Hi Dave,

The "mini" test program is working ok now.
However, now I tried to replicate it on the actual web app code, I am facing some difficulties.

I'd like to seek advice in regards to return generated Keys.

So, now I have a tutor POJO class and a tutorImpl which has a insert(Tutor t) method and it will include the sql and the returned, generated keys on the prepare Statment.



The tutor data which consists of the details like tutor_id, name, etc, so there will be a generated_id return.


Now, how do I retrieve the generated key which was generated from this tutorImpl class in another class  ?

I mean I can't repeat


Basically, I need to make use of this Generated_Keys and pump into the database with another class's data.

Please help me out and give me some hints the right way to retrieve the Generated_Keys on the 2nd class which has another insert class.

I am using the DAO pattern but I am not sure if this is a good design to implement or if there should be another way of doing things.

Hope to have some advice.





 
Marshal
Posts: 6349
437
BSD Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@OP

I got an impression that you solved your initial problem, now you asking different question which is not in sync with topic's title.
Please create other topic with new question (which is in your latest post), so you could discuss that one there.

Don't forget to click button at the bottom of the browser's window, which's tool tip reads as: "Mark topic as resolved".
 
The fastest and most reliable components of any system are those that are not there. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!