Re: Problem with create table in Derby 10.2.2.0
Leo, You need to use BIGINT rather than long for KEY_ID column. Mamta On 2/9/07, Leo Alberto [EMAIL PROTECTED] wrote: Hy I'm try to using Derby 10.2.2.0http://db.apache.org/derby/releases/release-10.2.2.0.cgi 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-a2db3c51 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
Re: Problem with create table in Derby 10.2.2.0
BTW, the 10.2 Reference manual talks more about BIGINT at http://db.apache.org/derby/docs/10.2/ref/rrefbuiltbigint.html Mamta On 2/9/07, Mamta Satoor [EMAIL PROTECTED] wrote: Leo, You need to use BIGINT rather than long for KEY_ID column. Mamta On 2/9/07, Leo Alberto [EMAIL PROTECTED] wrote: Hy I'm try to using Derby 10.2.2.0http://db.apache.org/derby/releases/release-10.2.2.0.cgi 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-a2db3c51 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
Re: Problem with create table in Derby 10.2.2.0
Hi Leo, Also I think following will be a better createString, I wonder why you didn''t get any error for generate'. Also please don't use 'Table' as table name. I got error for that also. And KEY_ID can not have any null values, since you are making it unique it should be declared as not null. My suggestion for the createString is as follows create table Table1 (SAMPLE_ID int not null generated always as identity,KEY _ID bigint not null,KEY_INST_COUNT int, DATAOBJ blob (102400), unique(KEY_ID)); On 2/9/07, Mamta Satoor [EMAIL PROTECTED] wrote: Leo, You need to use BIGINT rather than long for KEY_ID column. Mamta On 2/9/07, Leo Alberto [EMAIL PROTECTED] wrote: Hy I'm try to using Derby 10.2.2.0http://db.apache.org/derby/releases/release-10.2.2.0.cgi 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-a2db3c51 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