You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Leo Alberto <Al...@ssi.it> on 2007/02/09 19:07:58 UTC
Problem with create table in Derby 10.2.2.0
Hy
I'm try to using Derby 10.2.2.0
<http://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(Un
known Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unkno
wn 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.wrapArgsForTransportAcr
ossDRDA(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>
123456789012345678901234567890123456789012345678901234567890123456789012
3456789012345678
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.prepareI
nternalStatement(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
Posted by Mamta Satoor <ms...@gmail.com>.
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 <ms...@gmail.com> wrote:
>
> Leo,
>
> You need to use BIGINT rather than long for KEY_ID column.
>
> Mamta
>
>
> On 2/9/07, Leo Alberto <Al...@ssi.it> wrote:
> >
> > Hy
> > I'm try to using Derby 10.2.2.0<http://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-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
> >
> >
>
>
Re: Problem with create table in Derby 10.2.2.0
Posted by Manjula Kutty <ma...@gmail.com>.
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 <ms...@gmail.com> wrote:
>
> Leo,
>
> You need to use BIGINT rather than long for KEY_ID column.
>
> Mamta
>
>
> On 2/9/07, Leo Alberto <Al...@ssi.it> wrote:
> >
> > Hy
> > I'm try to using Derby 10.2.2.0<http://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-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
> >
> >
>
>
Re: Problem with create table in Derby 10.2.2.0
Posted by Mamta Satoor <ms...@gmail.com>.
Leo,
You need to use BIGINT rather than long for KEY_ID column.
Mamta
On 2/9/07, Leo Alberto <Al...@ssi.it> wrote:
>
> Hy
> I'm try to using Derby 10.2.2.0<http://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-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
>
>