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
– Axel, Commented Jun 5, 2012 at 7: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
– Awel, Commented Jun 5, 2012 at 7: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
– Axel, Commented Jun 5, 2012 at 9: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
– Awel, Commented Jun 6, 2012 at 6:30

Community · Accepted Answer · 2017-05-23 12:06:33Z

1

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

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jun 7, 2012 at 18:01

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

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

Collectives™ on Stack Overflow

JDBC Java ALTER TABLE Syntax error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related