Create Table:: unexpected token: INTEGER

[Artur]

Hey, i have another problem with manipulating a created Access database.
First i get the connection to a database:

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
cn = DriverManager.getConnection("jdbc:ucanaccess://"
+ database.getAbsolutePath()
+ ";newdatabaseversion=V2010");

With this, the database is created and existing and now i have to create some tables and so on:
The first CREATE TABLE String looks like this:

cn.createStatement().executeUpdate(cn.nativeSQL(s));

and

s = "CREATE TABLE Ursprung ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Dateiname TEXT NOT NULL, Einlesezeitpunkt TEXT NOT NULL );"

But executeUpdate() throws:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: INTEGER
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:211)
at com.krauth.Logbook.DB.createDB(DB.java:1469)
at com.krauth.Logbook.DB.openDB(DB.java:83)
at com.krauth.Logbook.Jlb2DBMain.main(Jlb2DBMain.java:137)
Caused by: java.sql.SQLException: unexpected token: INTEGER
at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:108)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:131)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:209)
... 3 more

Whats wrong with the CREATE TABLE Statement?

The code was written to create a sqllite database and is getting an update to support also MS-Access databases.

So there are a lot of default DDL Calls (CREATE TABLE and INSERT INTO) to be used.

Thank you :)

[Marco Amadei]

You can use either

CREATE TABLE Ursprung ( ID AUTOINCREMENT PRIMARY KEY , Dateiname TEXT NOT NULL, Einlesezeitpunkt TEXT NOT NULL )

or

CREATE TABLE Ursprung ( ID COUNTER PRIMARY KEY , Dateiname TEXT NOT NULL, Einlesezeitpunkt TEXT NOT NULL )

but the first one(with AUTOINCREMENT instead of COUNTER ) is allowed only since the latest ucanaccess version.
Your original DDL was syntactically wrong.

Cheers marco

[Artur]

So the next problem with my sql queries :)

            String s = "SELECT MAX(ID) AS ID FROM " + tablename + ";";
            lock.lock();
            ResultSet rs = cn.createStatement().executeQuery(s);
            lock.unlock();
            if (rs != null)
                num = rs.getInt("ID");

example for s:
"SELECT MAX(ID) AS ID FROM Ursprung;"

rs.getInt() throws:

net.ucanaccess.jdbc.UcanaccessSQLException: invalid cursor state: identifier cursor not positioned on row in UPDATE, DELETE, SET, or GET statement: ; ResultSet is positioned before first row
at net.ucanaccess.jdbc.UcanaccessResultSet.getInt(UcanaccessResultSet.java:458)
at com.krauth.Logbook.DB.getMaxID(DB.java:1959)
at com.krauth.Logbook.LbFileReader.readLogbook(LbFileReader.java:473)
at com.krauth.Logbook.Jlb2DBMain.main(Jlb2DBMain.java:159)
Caused by: java.sql.SQLException: invalid cursor state: identifier cursor not positioned on row in UPDATE, DELETE, SET, or GET statement: ; ResultSet is positioned before first row
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getCurrent(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getInt(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getInt(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessResultSet.getInt(UcanaccessResultSet.java:456)
... 3 more
Caused by: org.hsqldb.HsqlException: invalid cursor state: identifier cursor not positioned on row in UPDATE, DELETE, SET, or GET statement: ; ResultSet is positioned before first row
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
... 9 more

Thank you :)

[Marco Amadei]

You have to call if(rs.next()) before num = rs.getInt("ID");
Also
if (rs != null)
doesn't make sense because rs can't be null.
Cheers Marco

[Artur]

ok thank you.
Another question, What Timestamps are supported by ucanacces for inserting dates?
For sqlite im using a format like 26.09.2014-08:15:09.
I tried the specified format #03/08/2014 03:15:57 AM# and its working (just for ms-access).

But are there other supported formats for dates? I dont want 2 Insert Operations with different date formats for sqlite or ms-access.

OK the Date Problem is solved :) but the next one is waiting :(

I have an Insert with an XML String, i just convert all " to ' and put the whole String in a Text Field.

Now there's this Exception:

net.ucanaccess.jdbc.UcanaccessSQLException: data exception: string data, right truncation
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:211)
at com.krauth.Logbook.DBInsert.run(DBInsert.java:44)
Caused by: java.sql.SQLDataException: data exception: string data, right truncation
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:73)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:134)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:209)
... 1 more
Caused by: org.hsqldb.HsqlException: data exception: string data, right truncation
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.CharacterType.castOrConvertToType(Unknown Source)
at org.hsqldb.types.CharacterType.convertToType(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 7 more

for the Insert Command:
INSERT INTO KATransaktionsdaten( UrsprungID, EintragNr, KA_EP_ID, SchichtID, KA_EP_Nr, Quelletyp, QuelleID, Zeit, EP_Ref, EP_Name, EP_XML) VALUES (4,440,862131,861692,1,100,861692,"2014-03-08 13:44:53","00000000393500000375","EP_Ausgabe_Berechtigung","<txaber>...</txaber>")

any idea?

[Marco Amadei]

The most common option, supported by Access and UCanAccess as well as by most of dbms', is yyyy-MM-dd HH:mm:ss.
With Access and UCanAccess you have to use the # date delimeters.Or use the PreparedStatement if it makes sense.
Also you can use the dateValue function, whose behavior is related to your locale settings, to parse a string and convert it into a date.