Are SQL concatenations possible using the JDBC to ODBC bridge?

Question

When I run a query using concatenation of fields in MS Access I can write the following and it works fine:

SELECT e.FirstName + " " + e.LastName + " " + e.MiddleName AS Attendant FROM (EMPLOYEES e INNER JOIN PAY pay ON e.SocialSecurityNumber = pay.Pay_Emp_ID) INNER JOIN Patients pat ON pay.Pay_Pat_ID = pat.PatientID;

But if I try to run the same query using the JDBC / ODBC Bridge in SQuirreL SQL I get an error message instead:

Error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SQLState:  07001
ErrorCode: -3010

Does ODBC require some other syntax that's different from MS-Access?

Answer 1

The Access ODBC driver allows concatenation but seems to have an issue with those double quote characters ... at least in this context. Based on testing from VBScript, I think your query may work if you substitute single for double quotes:

SELECT e.FirstName + ' ' + e.LastName + ' ' + e.MiddleName AS Attendant
FROM
    (EMPLOYEES e INNER JOIN PAY pay
    ON e.SocialSecurityNumber = pay.Pay_Emp_ID)
    INNER JOIN Patients pat
    ON pay.Pay_Pat_ID = pat.PatientID;

However, I don't know Java so have no idea whether that bridge feature may garble the ODBC communication somehow. But submitting that query directly through the Access ODBC driver did work for me.