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.

asked 12 Sep '13, 08:51

David%20Lopez's gravatar image

David Lopez
46335
accept rate: 0%


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

permanent link

answered 12 Sep '13, 09:37

John%20Smirnios's gravatar image

John Smirnios
11.9k396164
accept rate: 38%

edited 12 Sep '13, 09:44

Comment Text Removed
2

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.

(12 Sep '13, 09:59) Chris Keating
Replies hidden
2

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

(12 Sep '13, 10:44) John Smirnios

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.

permanent link

answered 13 Sep '13, 14:03

Tom%20Mangano's gravatar image

Tom Mangano
672242738
accept rate: 8%

edited 13 Sep '13, 14:04

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.

(13 Sep '13, 14:10) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78
×39
×19

question asked: 12 Sep '13, 08:51

question was seen: 4,541 times

last updated: 13 Sep '13, 14:10