SQL Anywhere Error -116: Table must be empty when Calling SQL script from Java

[Former Member]

Hi.

Following this thread where some of you helped me solve a problem I had to run SQL scripts in Java I ended up using CallableStatement to do so.

The issue now is that sometimes I get errors in some SQL files that run sweet on Interactive SQL.

Namely, the greates issue for the moment comes with scripts which contain some ALTER TABLE xxxxx ADD COLUMN Other scripts containing procedures, ALTER TABLE statements which do not add any column and other stuff are not a problem. Scripts containing ADD COLUMN are.

But when the script contains an ADD COLUMN Statement the process throws a -116 Exception

java.sql.SQLException: SQL Anywhere Error -116: Table must be empty at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780)

It demands the table to be empty, which is not feasible for our updating processes. I am using Jconnect 7.0 driver to get the connection, by the way.

Any ideas of what's wrong and how to make it up? Thanks a lot.

[johnsmirnios]

Are you attempting to add a non-nullable column without a DEFAULT value? If so, try adding a DEFAULT.

Ah... I believe Java goes through TDS which has a different default nullability for columns than the SQLAnywhere protocol. Try explicitly adding NULL or NOT NULL to the column specification in your ALTER TABLE statement.

See http://dcx.sybase.com/index.html#sa160/en/dbadmin/allow-nulls-by-default-option.html*d5e34313

[chris_keating]

The SA JDBC driver is used by SQL Anywhere Interactive SQL. Consider using the sample interactive sql application included with the jConnect software to verify SQL logic against jConnect.

Alternatively, the SA JDBC driver could be used in the application.

[johnsmirnios]

And, more importantly, I think SQLAnywhere JDBC driver goes through ODBC which will not use TDS.

[t1950]

the only time you get that error is if you alter statement doesn't have null as the default and the table has rows in it.

ex: alter table foo add columnX char(50) NULL WILL NOT fail on table that has rows

alter table foo add columnX char(50) WILL FAIL if the the table has rows.

[johnsmirnios]

Yes, the second statement will fail on a TDS connection (ie Java using jConnect). It will not fail for non-TDS connections because columns will default to nullable if you don't specify the nullability explicitly as you did in the first statement.