error in java about SQLsyntax while query works correctly in MySQL

Question

while creating a program for a store that lists his assortment in a txt-file getting his information from a database from MySQL(phpMyAdmin), I always get the same error in netbeans while running the file. Netbeans show this error: "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 '.....' at line 1". my Query is correct, i've checked over a hundred times in MySQL and it gives always the right tables. also in java i've checked my code plenty of times.

i've googled it and find some other people with some similar issues but none of the proposed solutions work for my project. i've entered extra quotation marks (because I forget them). here's my code:

String Lijst = "";

    Connection con = null;
    try
    {
        con = getConnection();
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);

        String sql = "SELECT  ArtikelWinkel.Artikelnummer, Artikel.Naam, Artikel.Prijs, Artikel.GekregenPunten, ArtikelMetBonus.MinimumAantalGekocht, ArtikelMetBonus.BonuspuntenVerdiend, PuntenInwisselbaarArtikel.PrijsInPunten, PuntenInwisselbaarArtikel.MinimumGeldbedrag "
                + "FROM ArtikelWinkel LEFT OUTER JOIN Artikel ON Artikel.Artikelnummer=ArtikelWinkel.Artikelnummer  "
                + "LEFT OUTER JOIN ArtikelMetBonus ON ArtikelWinkel.Artikelnummer=ArtikelMetBonus.Artikelnummer "
                + "LEFT OUTER JOIN PuntenInwisselbaarArtikel ON ArtikelWinkel.Artikelnummer=PuntenInwisselbaarArtikel.Artikelnummer"
                + "WHERE ArtikelWinkel.Naam='" + naamWinkel + "'";
        ResultSet srs = stmt.executeQuery(sql); 
        Lijst = "Naam Winkel   -   Artikel  -  Artikelnummer     -   Prijs  -   Gekregen punten per artikel -   "
                + "min. # kopen om bonus te verdienen   -   # bonuspunten verdiend  -   min. bedrag kopen om prijs in punten  \n";

        while(srs.next())
        {
            Lijst +="\t\t"+ naamWinkel +"   -   "+srs.getString("Artikel.Naam")+"   -   "
                    +srs.getString("ArtikelWinkel.Artikelnummer") +"   -   "+srs.getString("Artikel.Prijs") 
                    + "   -   "+srs.getString("Artikel.GekregenPunten") +"  -   " +srs.getString("ArtikelMetBonus.MinimumAantalGekocht") +
                    "   -   "+srs.getString("ArtikelMetBonus.BonuspuntenVerdiend") + "  -   " +srs.getString("PuntenInwisselbaarArtikel.MinimumGeldbedrag")+ "\n";
        }
        con.close();

they were also people that suggested te let out the sql between the brackets in the statement: ResultSet srs = stmt.executeQuery(sql); I found that very unlogical and it gave an error too.

Some people thought I may had a connection problem but i have other methods in my class that work perfectly with my connectionMethod.

Any suggestions? thank you very much

Masudul · Accepted Answer · 2013-12-03 10:58:46Z

1

You didn't put space before WHERE at SQL query.

String sql = "SELECT..  .."
            ...PuntenInwisselbaarArtikel.Artikelnummer"
 + "WHERE ArtikelWinkel.Naam='" + naamWinkel + "'";   |_ put space here.

answered Dec 3, 2013 at 10:58

Masudul

22k5 gold badges45 silver badges60 bronze badges

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

Comments

Pradeep Simha · Accepted Answer · 2013-12-03 10:59:10Z

0

There is no space between Artikelnummer and your WHERE clause, change your query like this and try again:

+ "LEFT OUTER JOIN PuntenInwisselbaarArtikel ON ArtikelWinkel.Artikelnummer=PuntenInwisselbaarArtikel.Artikelnummer"
+ " WHERE ArtikelWinkel.Naam='" + naamWinkel + "'";

answered Dec 3, 2013 at 10:59

Pradeep Simha

18.2k19 gold badges60 silver badges112 bronze badges

Collectives™ on Stack Overflow

error in java about SQLsyntax while query works correctly in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related