Topic
1 reply Latest Post - ‏2007-04-30T16:18:08Z by Jean_Anderson
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

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 (
clientNo int DEFAULT AUTOINCREMENT,
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
try
{
return metaData.getColumnCount();
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
} // 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 - lameck@webmail.co.za
Updated on 2007-04-30T16:18:08Z at 2007-04-30T16:18:08Z by Jean_Anderson
  • Jean_Anderson
    Jean_Anderson
    179 Posts
    ACCEPTED ANSWER

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

    ‏2007-04-30T16:18:08Z  in response to SystemAdmin
    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:

    http://publib.boulder.ibm.com/epubs/html/c1894713.html

    For example, it mentions that AUTOINCREMENT columns were replaced by "GENERATED BY DEFAULT AS IDENTITY" in 10.2. Syntax for 10.2 is here:
    http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html

    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!)");
    return;
    }
    ...[/code]
    Here's another code fragment from the function tests [2]:
    code private void crossCheckGetColumnsAndResultSetMetaData(ResultSet rs,
    boolean partial)
    throws SQLException
    {
    Statement s = createStatement();
    while (rs.next())
    {
    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++)
    {
    ...[/code]
    Does either help you spot what the problem might be?

    regards,

    -jean

    [1]
    https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java
    [2] https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java