JDBC Java ALTER TABLE Syntax error

Question

I'm making a program in Java with a MS Access database. An adapted snipped of my program

Statement s;
String[] tabinfo = {"Albara", "TBala"};
s.execute("ALTER TABLE " + tabinfo[0] + " ALTER COLUMN " + 
    tabinfo[1] + " SET DEFAULT 0 ");

What I get is an SqlException: ALTER TABLE syntax error. What I'm doing wrong? I have full rights of the file. The table and columns names are right as the names are extracted from the database metadata and I'm not modifying system tables.

It's a program to modify a db of my own that is used by a program that I made several years ago so changing the db is out of the question. Also out of the question is the use of Java, my clients computer won't install .Net framework 3.5 and I'm not going there to do informatics maintenance.

First make sure it has to do with Java/JDBC or Access. Get the complete String for your alter table statement and run it directly in access. then report back here. — Axel, Jun 05 '12 at 07:20
This very program was first written in Visual Basic 2008. As my client couldn't install .NET the program was rewritten in Java. I could alter the tables then but not now. I'm using the same unmodified backup version of the db I used to test the vb.net application. — Awel, Jun 05 '12 at 07:40
So, if you place a breakpoint on the line starting with `s.execute`, and copy the Strings' content, and execute it directly in Acces, what do you get? — Axel, Jun 05 '12 at 09:12
I use JDBC:ODBC driver. `ALTER TABLE proveidors ALTER COLUMN Fax INTEGER DEFAULT 0` `java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at ReparaTexfiMain.main(ReparaTexfiMain.java:74)` — Awel, Jun 06 '12 at 06:30

score 1 · Answer 1 · edited May 23 '17 at 12:06

That query does not conform to the ALTER TABLE syntax of MS Access specified on MSDN ALTER TABLE Statement (Microsoft Access SQL)

Other sources seem to indicate that you need to include the data type when setting the default, eg

ALTER TABLE <tableName> ALTER COLUMN <columnName> <columnType> DEFAULT <defaultValue>

(also notice it does not include SET)

This SO question seems to indicate you need to enable SQL Server syntax compatibility to be able to use this: SQL to add column with default value - Access 2003

JDBC Java ALTER TABLE Syntax error

1 Answers1