Check if a column is auto incremented in Sybase with JDBC

Question

To check if a column is auto incremented i can do the following

Connection con = ...    
DatabaseMetaData meta = con.getMetaData();
ResultSet metaCols = meta.getColumns(catalog, schema, table, "%");
while ( metaCols.next() )   
     String value = rs.getString("IS_AUTOINCREMENT")    
  ...

works fine except with Sybase databases. I've tried it with the jTDS and JConnect drivers, but with both drivers I get the this exception:

java.sql.SQLException: Invalid column name IS_AUTOINCREMENT.

Is there another the get find out, whether a column in Sybase is auto incremented or not? I thought "IS_AUTOINCREMENT" is a feature with JDBC4 and jTDS is a JDBC4 compatible driver.

Answer 1

Sybase uses 'identity' columns rather than 'default autoincrement' which is why I believe you are getting this message.

Try checking if TYPE_NAME column contains keyword "identity".

The behaviour of identity columns is a little different also, but that is an aside.

Answer 2

What version of JConnect are you using? Try using 6. It should work using:

DatabaseMetaData.getTypeInfo()

PS. Sorry new to the site, not enough points to comment on your post :(

Answer 3

sp_help delivers all the information I need. This SP returns several ResultSets. The third ResultSet contains the information I need.

Statement stmt = con.createStatement();
stmt.executeQuery("sp_help " + table);
stmt.getMoreResults();
stmt.getMoreResults();
ResultSet rs = stmt.getResultSet();
//...
while( rs.next() )
   boolean identity = rs.getBoolean("Identity");

Answer 4

Sorry, I misunderstood as you have found below using sp_help if the identity column contains a 1 then the column is an identity.

There are also other methods available. I was concentrating on Java methods when I could have given you the answer in seconds had I known you would be happy with SQL commands such as sp_help, sp_columns and selecting from systemtables.

Best of luck.

Answer 5

This is the easiest way to get the identity information

ResultSet tableInfo = tableInfoQuery.executeQuery("SELECT * FROM " + tableName + " WHERE 1=2");
ResultSetMetaData rsMetaData = tableInfo.getMetaData();
for (int i = 1; i < = rsMetaData.getColumnCount(); i++)
{rsMetaData.isAutoIncrement(i);}