• 1 reply
  • Latest Post - ‏2007-04-30T16:18:08Z by Jean_Anderson
1525 Posts

Pinned topic what is the syntax for an sql script with autoincrement field in derby10.2??

‏2007-04-30T14:18:35Z |
What is the correct syntax for an sql script with an autoincrement field in
derby_10.2? In the previous versions like Cloudscape364 i could easily do it like:

create table clients (
clientID varchar (20) NOT NULL,
clientLastName varchar (20) NOT NULL,
clientFirstNames varchar (30) NOT NULL,
clientAddress1 varchar (50) NOT NULL,
clientAddress2 varchar (50),
clientCity varchar (20),
clientProvince varchar (20) NOT NULL,
clientPostalCode varchar (10) NOT NULL,
registrationDate varchar (10) NOT NULL,
clientPhoneNo varchar (40) NOT NULL,
clientFaxNo varchar (40) NOT NULL,
clientEmail varchar (40),
clientCancellationDate varchar (10),
constraint pk_clients primary key(clientNo)

when i run an ij to create a table it gives me a an sql syntax error on the autoincrement value clientNo!! What should i do to get it right?

I also have a problem if i connect the database with the NetworkServer. if i declare this function:

// get number of columns in ResultSet
public int getColumnCount() throws IllegalStateException
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

// determine number of columns
return metaData.getColumnCount();
} // end try
catch ( SQLException sqlException )
} // end catch

return 0; // if problems occur above, return 0 for number of columns
} // end method getColumnCount

-it gives me a NullPointerException on return metaData.getColumnCount!! Would u please assist me coz i can't figure out why it is like this since the same code works just well in the previous version!!

Kind regards

Lameck -
Updated on 2007-04-30T16:18:08Z at 2007-04-30T16:18:08Z by Jean_Anderson
  • Jean_Anderson
    179 Posts

    Re: what is the syntax for an sql script with autoincrement field in derby10.2??

    Hi, since you mention the previous Cloudscape version, it's worth it to point out that features and syntax changed between older releases of Cloudscape and version 10. The Cloudscape migration doc might help:

    For example, it mentions that AUTOINCREMENT columns were replaced by "GENERATED BY DEFAULT AS IDENTITY" in 10.2. Syntax for 10.2 is here:

    For the second problem with metaData.getColumnCount, that's a standard JDBC call. Where does metaData get set and is it possibly NULL? I pulled this code fragment from one of the function tests that shows how to call that method[1]:

    code protected void dumpRS(int procId, ResultSet s) throws SQLException {

    ResultSetMetaData rsmd = s.getMetaData ();

    // Get the number of columns in the result set
    int numCols = rsmd.getColumnCount ();
    String[] headers = new StringnumCols;
    if (numCols <= 0) {
    System.out.println("(no columns!)");
    Here's another code fragment from the function tests [2]:
    code private void crossCheckGetColumnsAndResultSetMetaData(ResultSet rs,
    boolean partial)
    throws SQLException
    Statement s = createStatement();
    while (
    String schema = rs.getString("TABLE_SCHEM");
    String table = rs.getString("TABLE_NAME");

    ResultSet rst = s.executeQuery(
    "SELECT * FROM " + JDBC.escape(schema, table));
    ResultSetMetaData rsmdt = rst.getMetaData();
    for (int col = 1; col <= rsmdt.getColumnCount() ; col++)
    Does either help you spot what the problem might be?