Win a copy of Zero to AI - A non-technical, hype-free guide to prospering in the AI era this week in the Artificial Intelligence and Machine Learning 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
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

java.sql.SQLSyntaxErrorException:

 
Ranch Hand
Posts: 176
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello my fellow programmers (The ones who have been in the business longer than me)

So I am having an issue with the syntax of MySQL statement in eclipse however not on the workbench when I try to apply the exact same syntax which is quite confusing because what I usually do when i have a Syntax error is I try it in MySQL workbench and it always helped me find the error. So Here is my code:



Here is the statement I ran in MySQL workbench that I assume from multiple revisions is that it is the exact same one from Eclipse:


And last but not least, the error from my eclipse console:



Thank you in advance for any help
 
Marshal
Posts: 25930
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It would be useful to see the actual SQL query which causes the error message. You assume that it's the same one that worked correctly in the SQL workbench, but since it causes an error message then it can't be the same one. So, put in the debugging statement and print the actual query.
 
Yosuf Ibrahim
Ranch Hand
Posts: 176
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:It would be useful to see the actual SQL query which causes the error message. You assume that it's the same one that worked correctly in the SQL workbench, but since it causes an error message then it can't be the same one. So, put in the debugging statement and print the actual query.



The query I ran in MySQL wrokbench is a copy-paste from the query that appears in the error message. I changed nothing, that is why i am confused.

the error I recieve


The query i ran on MySQL workbench:


I literelly copy-pasted the syntax

 
Paul Clapham
Marshal
Posts: 25930
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Since the query doesn't appear anywhere in your code, it didn't seem likely to me that you could have copied it from anywhere. That's why I asked you to do some debugging. But since you've already done that, could you post your debugging code?
 
Saloon Keeper
Posts: 22634
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Couple of things:

Consider using braces. Naked "if" clauses can cause very painful results. So first:
Secondly, I think if you use String.format, you'll have code that's easier to read:
OK, Maybe not so much since "%" is a "magic" character there. But as a general rule.

Also, if you're going to build up a complex string from multiple clauses, consider using a StringBuilder rather than concatenation. It's generally more efficient, especially if you construct the StringBuilder with a capacity suitable for most of the SQL you're expecting to create. Since the default buffer length is only 16 characters and every time you overflow that, the StringBuilder has to stop and internally create a new, bigger buffer copied from its old buffer.

And finally, I couldn't see it, but I suspect a common error. The terminal semi-colun (";") on SQL when using a SQL command-line or GUI utility is not part of the SQL. It's an indicator for the utility that you have completed entering the SQL directive in question and that it should then execute that directive. Sending a ";"-terminated SQL directive to a JDBC driver will almost invariably fail.
 
Yosuf Ibrahim
Ranch Hand
Posts: 176
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:Since the query doesn't appear anywhere in your code, it didn't seem likely to me that you could have copied it from anywhere. That's why I asked you to do some debugging. But since you've already done that, could you post your debugging code?



I copied the query from the console when it showed me the error:

 
Yosuf Ibrahim
Ranch Hand
Posts: 176
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
AAAAAAAAAAAAAAAAAAAAARGH I am such an idiot I have realized what my issue is still looking though into what is causing it.

What is happening is as follows:

This is the query the method is sending the my first attempt in searching for a product:


This is the second query it sends:


What the application is basically doing is every time the method is called the variable srchStatement does not get reset like it supposed to
 
Paul Clapham
Marshal
Posts: 25930
69
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Indeed. Which is why I suggested looking at the whole query and not just the part in the error message. I suppose you did that in the end? Anyway it's good you got the problem solved.
 
Yosuf Ibrahim
Ranch Hand
Posts: 176
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:Indeed. Which is why I suggested looking at the whole query and not just the part in the error message. I suppose you did that in the end? Anyway it's good you got the problem solved.



I really need to learn how to debug. However, knowing the problem has sadly not solved it for me :(


"srchStatement += srchStatement" is basically the wrong way to do it as it does not just add the extra text, it also adds itself first and I have tried using concat which does not work because if my first string is empty it just returns the empty string and does not add the extra text. So I am stuck
 
Paul Clapham
Marshal
Posts: 25930
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, I see. Your code is confused as to whether "srchStatement" is meant to hold the entire query, or at least the where-clause part of it, or whether it is meant to hold the part of the where-clause for barcode and so on. Consider using different variables for those two different things.
 
Yosuf Ibrahim
Ranch Hand
Posts: 176
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:Oh, I see. Your code is confused as to whether "srchStatement" is meant to hold the entire query, or at least the where-clause part of it, or whether it is meant to hold the part of the where-clause for barcode and so on. Consider using different variables for those two different things.



FIXED IT WOOOOOOOOOOOOOOOOO



Even though I hate it when i get stuck on something for so long, I love it when I finally get it right. I am going to blame it on lack of proper sleep :P

Thank you Paul and Tim for your time. And I will try to learn debugging better from now on. I could have resolved the issue much earlier if I had figured out earlier what it was
 
I've been selected to go to the moon! All thanks to this tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic