It appears that the parser for text tables misses the possibility that
varchar and longvarchar columns can contain LF, CRLF, or CR line breaks.
Since out app allows the user to fill in comments in a multi-line
textarea on a web form, we seem to have quite a few of these. No
problem with normal hsqldb tables in 1.7.0.
Test case:
/*********************** CUT HERE ***************************/
create text table t1 (
id numeric(12) not null,
txt varchar(4000) null,
instime datetime not null
);
set table t1 source "t1.csv";
alter table t1 add constraint pk_t1 unique (id);
insert into t1 (id,txt,instime) values (1,
'This is a short line', '2002-01-01 00:00:00');
insert into t1 (id,txt,instime) values (2,
'This is a long txt value
line2
line3
line4
line5', '2002-02-02 02:20:00');
insert into t1 (id, txt,instime) values (3,
'Second short line', '2002-03-03 03:30:00');
commit;
checkpoint;
/************************************************************/
At this point, shut down the Database Manager, restart the Database
Manager, and query for all rows:
select * from t1;
You will get an exception like:
File input/output error: S1000 File input/output error: reading:
java.lang.NumberFormatException: For input string: " lines" in statement
[select * from t1] / Error Code: -29 / State: S1000
The exception thrown is:
java.lang.NumberFormatException: For input string: " lines"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:426)
at java.lang.Integer.parseInt(Integer.java:476)
at java.math.BigDecimal.<init>(BigDecimal.java:207)
at
org.hsqldb.TextDatabaseRowInput.readDecimal(TextDatabaseRowInput.java:282)
at org.hsqldb.DatabaseRowInput.readData(DatabaseRowInput.java:237)
at org.hsqldb.Row.<init>(Row.java:373)
at org.hsqldb.TextCache.makeRow(TextCache.java:509)
at org.hsqldb.Cache.getRow(Cache.java:469)
at org.hsqldb.Table.getRow(Table.java:1521)
at org.hsqldb.Node.findNode(Node.java:206)
at org.hsqldb.Node.getRight(Node.java:286)
at org.hsqldb.Index.next(Index.java:750)
at org.hsqldb.TableFilter.next(TableFilter.java:334)
at org.hsqldb.Select.buildResult(Select.java:459)
at org.hsqldb.Select.getResult(Select.java:284)
at org.hsqldb.Parser.processSelect(Parser.java:137)
at org.hsqldb.Database.execute(Database.java:426)
at
org.hsqldb.jdbcConnection.executeStandalone(jdbcConnection.java:2951) at
org.hsqldb.jdbcConnection.execute(jdbcConnection.java:2540)
at org.hsqldb.jdbcStatement.fetchResult(jdbcStatement.java:1804)
at org.hsqldb.jdbcStatement.execute(jdbcStatement.java:728)
at
org.hsqldb.util.DatabaseManagerSwing.execute(DatabaseManagerSwing.java:626)
It appears that the problem is in TextCache.makeRow where it assumes
that each linebreak (LF, CRLF, CR) delimits a row. Unfortunately, life
is more subtle than that when varchars can contain random crap like
user-entered linefeeds or XML data with linebreaks.
So I think this means 2 changes need to be made to Text table support:
1) If a varchar or longvarchar column contains a linebreak sequence,
then it needs to get quoted on output. This duplicates Excel's
behavior, I believe.
2) While the parser is looking for the end of the current row, it must
ignore linebreak sequences inside a quoted column.
Would anyone know how to fix this quickly? Or should I try to figure
out how to fix this & submit a patch for (hopeful) inclusion in 1.7.1?
-=- D. J.
-------------------------------------------------------
This sf.net email is sponsored by: OSDN - Tired of that same old
cell phone? Get a new here for FREE!
https://www.inphonic.com/r.asp?r=sourceforge1&refcode1=vs3390
_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
