Why is my sql call working in MySQL but not in my Java class

Question

I have a SQL Call in my MySQL workbench to test so that I could ensure that the syntax was correct, I double checked and it works, however when I use the same syntax in my java class to pass it to the database and get information I get a SQLException like this

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 'JOIN customers on customers.ANum = leads.ANum JOIN automobiles 
on automobiles.AN' at line 1

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 'JOIN customers on customers.ANum = leads.ANum JOIN automobiles on 
automobiles.AN' at line 1

Here is me Syntax in MySQL:

SELECT leads.Anum, leads.recDate, leads.status, leads.salesman, leads.apptDt, 
leads.appTime, leads.notes,customers.fName, customers.mName, customers.lName, 
customers.phone, customers.cell, customers.work, customers.bestTime, customers.email, 
customers.addr1, customers.addr2, customers.city, customers.state, customers.zip, 
customers.rentOwn, customers.pmtMo, customers.livedMo, customers.livedYr, 
customers.ssn, customers.birthday, customers.employer, customers.incomeMo, 
customers.empMo, customers.empYr, customers.creditApp, automobiles.newUsed, 
automobiles.extColorWant, automobiles.intColorWant, automobiles.desiredInfo,   
automobiles.extColor, automobiles.intColot, automobiles.yearHave, automobiles.make, 
automobiles.model, automobiles.mileage, automobiles.goodPoor FROM leads JOIN customers 
on customers.ANum = leads.ANum JOIN automobiles on automobiles.ANum = customers.ANum 
where leads.dealer = 'SKA' and leads.recDate between '2013-01-01' and '2014-12-31' 
order by leads.recDate DESC;

Syntax for My Java SQL Call:

String sql = "SELECT leads.Anum, leads.recDate, leads.status, leads.salesman, leads.apptDt, leads.appTime, leads.notes,"
            + "customers.fName, customers.mName, customers.lName, customers.phone, customers.cell, customers.work," 
            + "customers.bestTime, customers.email, customers.addr1, customers.addr2, customers.city, customers.state," 
            + "customers.zip, customers.rentOwn, customers.pmtMo, customers.livedMo, customers.livedYr, customers.ssn," 
            + "customers.birthday, customers.employer, customers.incomeMo, customers.empMo, customers.empYr, customers.creditApp,"
            + "automobiles.newUsed, automobiles.extColorWant, automobiles.intColorWant, automobiles.desiredInfo, automobiles.extColor, automobiles.intColot,"
            + "automobiles.yearHave, automobiles.make, automobiles.model, automobiles.mileage, automobiles.goodPoor"
            + "FROM leads JOIN customers on customers.ANum = leads.ANum JOIN automobiles on automobiles.ANum = customers.ANum Where leads.dealer = "
            + "? and leads.recDate between ? and ? order by leads.recDate DESC";

As I said Before The SQL Call in MySQL works just fine, returns exactly what I want it to return, But I get the SQL Exception in my java web application, Any Ideas what could be going wrong here. My first impression is that for whatever reason the Java sql library doesn't like the syntax, and if that is the case where would I find documentation on how to resolve the issue.

You might need to enclose the strings in (single) quotes in the Java. Just a guess, though. — Reinstate Monica -- notmaynard
– Reinstate Monica -- notmaynard, Commented Jan 2, 2015 at 18:51

Todd · Accepted Answer · 2015-01-01 18:35:16Z

5

You need a space at the end of your line...

+ "automobiles.yearHave, automobiles.make, automobiles.model, automobiles.mileage, automobiles.goodPoor" <-- Space here
+ "FROM leads JOIN customers on customers.ANum = leads.ANum JOIN automobiles on automobiles.ANum = customers.ANum Where leads.dealer = "

This is causing the FROM clause and the fields in the SELECT clause to run together...

...automobiles.mileage, automobiles.goodPoorFROM leads...

answered Jan 1, 2015 at 18:35

Todd

31.9k11 gold badges91 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2707813 Over a year ago

Thanks man, I guess I just needed an extra set of eyes because I must have overlooked that at least 100 times. I really appreciate the answer.

Todd Over a year ago

No problem! I've been there too.

Collectives™ on Stack Overflow

Why is my sql call working in MySQL but not in my Java class

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related