I'm having a problem working with files that contain special
characters.
Background:
Create table statement:
"CREATE TABLE Titles (TitleID INT GENERATED ALWAYS AS IDENTITY
CONSTRAINT TITLE_ID_PK PRIMARY KEY, FullTitle VARCHAR(512) UNIQUE NOT
NULL, TitleDate VARCHAR(64), TitleNotes VARCHAR(512), TitleTypeID
SMALLINT NOT NULL, FOREIGN KEY (TitleTypeID) REFERENCES TitleTypes
(TitleTypeID));"
Prepared statement (for inserting into Titles table):
"INSERT INTO Titles (FullTitle, TitleDate, TitleNotes, TitleTypeID)
VALUES (?, ?, ?, ?)"
This works successfully for most data, but when the FullTitle data
contains special characters (e.g. accented characters like á and Á)
the FullTitle String seems to be being truncated and causes a
violation of the UNIQUE condition for the FullTitle column of the
Titles table.
Here is some sample output from the trace file (trace level 3,
converted with the ConvertTraceFile tool:
"INSERT INTO Titles (FullTitle, TitleDate, TitleNotes, TitleTypeID)
VALUES (?, ?, ?, ?) {1: STRINGDECODE('\"\u00c0ngel Casas show
\" (1984)'), 2: '1984-1988', 3: NULL, 4: 1};
INSERT INTO Titles (FullTitle, TitleDate, TitleNotes, TitleTypeID)
VALUES (?, ?, ?, ?) {1: STRINGDECODE('\"\u00c0ngel Casas show\" (1984)
{(#1.1)}'), 2: '1984', 3: NULL, 4: 1};
INSERT INTO Titles (FullTitle, TitleDate, TitleNotes, TitleTypeID)
VALUES (?, ?, ?, ?) {1: STRINGDECODE('\"\u00c0ngel Casas show\" (1984)
{(#1.10)}'), 2: '1984', 3: NULL, 4: 1};"
Here is an example error message and stack trace from the resultant
org.h2.jdbc.JdbcSQLException:
"org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
"CONSTRAINT_INDEX_9 ON PUBLIC.TITLES(FULLTITLE)"; SQL statement:
INSERT INTO Titles (FullTitle, TitleDate, TitleNotes, TitleTypeID)
VALUES (?, ?, ?, ?) [23001-138]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:
157)
at org.h2.index.PageBtree.find(PageBtree.java:115)
at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:137)
at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:92)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:87)
at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:78)
at org.h2.table.RegularTable.addRow(RegularTable.java:116)
at org.h2.command.dml.Insert.insertRows(Insert.java:120)
at org.h2.command.dml.Insert.update(Insert.java:82)
at org.h2.command.CommandContainer.update(CommandContainer.java:70)
at org.h2.command.Command.executeUpdate(Command.java:199)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
141)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
127)
at
imdbtitlemanagerdb.datahandlers.TitlesHandler.handleDataFile(TitlesHandler.java:
266)
at imdbtitlemanagerdb.DBHandler.importIMDBDataFiles(DBHandler.java:
569)
at
imdbtitlemanagerdb.IMDBTitleManagerDBMain.<init>(IMDBTitleManagerDBMain.java:
108)
at
imdbtitlemanagerdb.IMDBTitleManagerDBMain.main(IMDBTitleManagerDBMain.java:
50)"
I'd appreciate any pointers in the right direction here. Thanks in
advance for any help.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
