With out knowing the table name in your case it is a guess where column 88 is in your final SQL but I see a couple of problems with the SQL:

 

1) The data types specified by your columns “long” and “int” are not valid data types (list of data types can be found in the reference for 10.2 http://db.apache.org/derby/docs/10.2/ref/ref-single.html#crefsqlj31068)

 

2) Plus the table constraint “unique(KEY_ID)” can only by applied to the KEY_ID column if it has a column constraint NOT NULL (http://db.apache.org/derby/docs/10.2/ref/ref-single.html#rrefsqlj13590).

 

Ian

 


From: Leo Alberto [mailto:Alberto.Leo@ssi.it]
Sent: Friday, February 09, 2007 13:08
To: derby-user@db.apache.org
Subject: Problem with create table in Derby 10.2.2.0

 

Hy

I'm try to using Derby 10.2.2.0 in embedded mode with the integrated plug-in environment in Eclipse 3.2.0. Environment jre 1.6.0

I would create a table with this code :

public boolean tableCreation(Connection currentConn, String Table)

                     throws DatabaseDriverException {

                                                                       

boolean doCreateTable = true;

              Statement stmt = null;

              String createString = "create table " +

Table +

"(SAMPLE_ID int not null generate always as increment, " +

"KEY_ID long, " +

"KEY_INST_COUNT int, " +

"DATAOBJ blob (102400), " +

              "unique(KEY_ID))";

             

              try {

 

                     currentConn.setAutoCommit(false);

                     stmt = currentConn.createStatement();

                     DatabaseMetaData md = currentConn.getMetaData();

                     ResultSet rs = md.getTables(null, null, "%", null);

                     while (rs.next()) {

System.out.println("Elenco tabelle nel db " + rs.getString(3));

                           if (rs.getString(3).equals(tabella)) {

                                  doCreateTable = false;

                                  break;

                           }

                     }

                     if (doCreateTable) {

                           // execute

                           stmt.execute(createString); // I'm arriving here ang got the error

                           doCreateTable = true;

                     }

                     currentConn.commit();

                     stmt.close();

 

              } catch (SQLException e) {

                     throw new DatabaseDriverException("Error create table", e);

              }

              return doCreateTable;

       }/* End tableCreation */

the error is:

Syntax error: Encountered "," at line 1, column 88.

       at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)

       at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)

       at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)

       at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)

       at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)

       at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)

       at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)Error create table

 

       at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

       at dds.dbdrivers.DerbyDriver.tableCreation(DerbyDriver.java:257)

       at provaDB.main(provaDB.java:39)

Caused by: java.sql.SQLException: Syntax error: Encountered "," at line 1, column 88.

       at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

       at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)

       ... 10 more

from errorlog.txt generated:

 

----------------------------------------------------------------

2007-02-09 17:31:01.452 GMT:

 Booting Derby version The Apache Software Foundation - Apache Derby - 10.2.2.0 - (485682): instance c013800d-0110-a78e-d8b1-ffffa2db3c51

on database directory C:\DdsAppDb\DbDOMAIN_(7) 

 

Database Class Loader started - derby.database.classpath=''

2007-02-09 17:31:10.786 GMT Thread[main,5,main] (XID = 122), (SESSIONID = 0), (DATABASE = DbDOMAIN_(7)), (DRDAID = null), Cleanup action starting

2007-02-09 17:31:10.786 GMT Thread[main,5,main] (XID = 122), (SESSIONID = 0), (DATABASE = DbDOMAIN_(7)), (DRDAID = null), Failed Statement is:

 

<my add>

1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

create table POSITION (SAMPLE_ID int not null generated always as identity, KEY_ID long, KEY_INST_COUNT int, DATAOBJ blob (102400), unique(KEY_ID))

ERROR 42X01: Syntax error: Encountered "," at line 1, column 88.

       at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

       at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)

       at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)

       at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)

       at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)

       at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

       at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

       at dds.dbdrivers.DerbyDriver.tableCreation(DerbyDriver.java:257)

       at provaDB.main(provaDB.java:39)

Cleanup action completed

 

I would be very glad if someone can help me understand wath is wrong!

thanks in advance

Alberto Leo