Ucanaccess error - unexpected token

Question

//golfer and CourseName are strings initialised earlier 
String query = "INSERT INTO Temp (GolferID,CourseID,DatePlayed,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th,11th,"
            + "12th,13th,14th,15th,16th,17th,18th)VALUES('" 
            + golfer + "','" + CourseName + "',#1/1/2011#";

    for(int j = 0; j <=17; j++)
    {
        query = query + "," + Scores[j];
    }
    query = query + ")";
    System.out.println(query);
//INSERT INTO  Temp(GolferID,CourseID,DatePlayed,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th,11th,12th,13th,14th,15th,16th,17th,18th)VALUES('test','Blue Valley CC',#1/1/2011#,4,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)*/
\\this is what the string eventually looks like.

    DBConnect db = new DBConnect();
    db.InsertGame(query);

this is the relevant code in DBConnect - InsertGame...

connection = DriverManager.getConnection("jdbc:ucanaccess://Golf.accdb");
statement = connection.createStatement();//declared earlier
statement.execute(query);

This keeps giving me the following errors: SEVERE: null net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: 2 net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:109)"

If i copy and paste the query straight into access it executes perfectly. I am using the same method of inserting into another table in the database, that works perfectly fine however all those fields are text fields. Im not too sure if that makes a difference.

score 2 · Accepted Answer · answered Aug 26 '15 at 02:16

2

I was able to recreate your issue under UCanAccess 3.0.0 using the following code:

sql = 
        "INSERT INTO Temp (GolferID,CourseID,DatePlayed,1st,2nd,3rd) " +
        "VALUES ('test','Blue Valley CC',#1/1/2011#,4,3,5)";

UCanAccess seems to be getting confused by the column name 2nd. I was able to successfully execute the statement when I enclosed the column names in square brackets:

sql = 
        "INSERT INTO Temp (GolferID,CourseID,DatePlayed,[1st],[2nd],[3rd]) " +
        "VALUES ('test','Blue Valley CC',#1/1/2011#,4,3,5)";

answered Aug 26 '15 at 02:16

Gord Thompson

116,920
32
215
418

1

Yes, the specific sequence is misunderstood by the preparser. Even if I think the use of square brackets, as Gord suggested, is always a good practice with the "special names"(e.g. names starting with a digit), I will fix this bug in the next version. – jamadei Aug 26 '15 at 07:22
Perfection. Thank You – Sirshen Munsamy Aug 26 '15 at 14:56

Ucanaccess error - unexpected token

1 Answers1