Many tables joining in java access SQL

Question

I'm trying to do a SELECT query in SQL in Java (Netbeans) using Access as SGBD but when it seems that the comma (",") that you can put between tables in Access doesn't work on Java ?

What am I doing wrong?

Thanks to @teppic for his answer but doesn't work much`

However, I still have the problem: it says syntax error in FROM. What is really strange is that the query works well in Access, and other (simpler) queries were correctly implemented in the Java script. I did not found doc on that in the books I bought or even on the Net because it's often with less tables ! Thank you in advance –

PreparedStatement ps = cnx.prepareStatement("SELECT Client.Nom , Facture.Date_Achat , Client.Prénom , Facture.N°Fac , SUM(Contient1.Quantité1*PrixAchatMonture)AS Somme"+ 
"FROM Facture , Client , Contient1 , Monture"+
"ON Facture.N°Client=Client.N°Client AND Facture.N°Fac=Contient1.N°Fac AND Contient1.IDM=Monture.IDM "+
"WHERE Client.Nom =? AND Client.Prénom=?"+
"GROUP BY Client.Nom, Facture.Date_Achat, Client.Prénom, Facture.N°Fac",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
        // ! au sens de la jointure pour l'affichage

[Microsoft][Pilote ODBC Microsoft Access] In SELECT instruction,reserved word or missing argument or incorrect punctuation

Update

I tried adding the spaces to the end of each string fragment but I still get an error

PreparedStatement ps = cnx.prepareStatement("SELECT Client.Nom , Facture.Date_Achat , Client.Prénom , Facture.N°Fac , SUM(Contient1.Quantité1 * Monture.PrixAchatMonture) AS Somme "+ "FROM Facture "+ "INNER JOIN Client ON Facture.N°Client=Client.N°Client "+ "INNER JOIN Contient1 ON Facture.N°Fac=Contient1.N°Fac "+ "INNER JOIN Monture ON Contient1.IDM=Monture.IDM "+"WHERE Client.Nom =? AND Client.Prénom=? "+ "GROUP BY Client.Nom, Facture.Date_Achat, Client.Prénom, Facture.N°Fac ",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

You forgot spaces at the end of the strings you're joining. – teppic Nov 22 '16 at 18:00 — teppic, Nov 22 '16 at 18:00
PierreDH, take a look at my answer – Bruno Nov 23 '16 at 18:35 — Bruno, Nov 23 '16 at 18:35

score 0 · Accepted Answer · answered Nov 23 '16 at 18:31

There's no space between the:

last column and the 'FROM' word
last table in 'FROM' and 'ON'
Client.Prénom=? and 'GROUP BY'

It should look like this:

PreparedStatement ps = cnx.prepareStatement("SELECT Client.Nom , 
Facture.Date_Achat , Client.Prénom , Facture.N°Fac , SUM(Contient1.Quantité1*PrixAchatMonture)AS Somme "+ 
"FROM Facture , Client , Contient1 , Monture "+
"ON Facture.N°Client=Client.N°Client AND Facture.N°Fac=Contient1.N°Fac AND Contient1.IDM=Monture.IDM "+
"WHERE Client.Nom =? AND Client.Prénom=? "+
"GROUP BY Client.Nom, Facture.Date_Achat, Client.Prénom, Facture.N°Fac",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
        // ! au sens de la jointure pour l'affichage

Despite that, you should change the following column name: N°Client. maybe it will give you cause problem too.

However, I still have the problem: it says syntax error in FROM. What is really strange is that the query works well in Access, and other (simpler) queries were correctly implemented in the Java script. I did not found doc on that in the books I bought or even on the Net because it's often with less tables ! Thank you in advance — , Nov 24 '16 at 10:27

score 0 · Answer 2 · answered Nov 23 '16 at 18:31

@teppic did have a point, the way the strings are concatenated would lead to invalid Access SQL since you don't have proper spaces at the end of your strings. I added some spaces for you.

PreparedStatement ps = cnx.prepareStatement (
    "SELECT Client.Nom , Facture.Date_Achat , Client.Prénom , Facture.N°Fac , SUM(Contient1.Quantité1*PrixAchatMonture)AS Somme "+ 
    "FROM Facture , Client , Contient1 , Monture "+
    "ON Facture.N°Client=Client.N°Client AND Facture.N°Fac=Contient1.N°Fac AND Contient1.IDM=Monture.IDM "+
    "WHERE Client.Nom =? AND Client.Prénom=? "+
    "GROUP BY Client.Nom, Facture.Date_Achat, Client.Prénom, Facture.N°Fac",
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_READ_ONLY
);

Also, it's better to use proper joins instead of relying on old-school join syntax. Read up on the internet what that is about. This link came up first when I looked up joins on the internet.

score 0 · Answer 3 · answered Nov 23 '16 at 18:31

0

@teppic meant you're missing spaces in your strings, so words are going to run together in the result.

For example, you have

... Somme"+ "FROM Facture , Client , Contient1 , Monture"+

which will result in

... SommeFROM Facture , Client , Contient1 , Monture...

Try

...Somme "+ "FROM Facture , Client , Contient1 , Monture "+

instead.

answered Nov 23 '16 at 18:31

Hod

2,157
1
12
20

Thank you really much for your advice, it is really instructive ! – Nov 23 '16 at 20:01

score 0 · Answer 4 · edited May 23 '17 at 11:54

When it comes to Unicode character support, Java's JDBC-ODBC Bridge and Microsoft's Access ODBC driver simply do not play well together.

For example, in an Eclipse project with a Java source file (.java file) saved as UTF-8, this will work ...

sql = "SELECT [Date_Achat] FROM [Facture]";

... but this will fail with "Too few parameters. Expected 1." ...

sql = "SELECT [N°Fac], [Date_Achat] FROM [Facture]";

... indicating that the Access ODBC driver does not recognize [N°Fac] as a field in the table [Facture].

However, if I change the Java source file encoding to Cp1252 in Eclipse then ...

sql = "SELECT [N°Fac], [Date_Achat] FROM [Facture]";

... works as expected.

So, you have a couple of options:

(1) If your application can get by with only supporting the Windows-1252 character set then you can use that encoding for your Java source files.

(2) You could create a saved query (i.e., a "View") named [qryFacture] in Access as ...

SELECT [N°Fac] AS [No_Fac], [Date_Achat] FROM [Facture]

... and then query against the view instead of the table.

sql = "SELECT [No_Fac], [Date_Achat] FROM [qryFacture]";

(3) You could use the UCanAccess JDBC driver instead of the JDBC-ODBC Bridge (which as been removed from Java 8 anyway).

Many tables joining in java access SQL

4 Answers4