HyperSQL Database Engine (HSQLDB)

On Wed, 1 Oct 2003 10:36, Dimitri Maziuk wrote:
> I'm trying to use text tables and I ran into a problem with values
> that contain newlines: HSQLDB will happily write multi-line value out
> to CSV file, and then it'd treat newlines as record separators.

I have no experience with writing text files using HSQLDB, but

> Am I missing something obvious? I don't see a way to specify record
> separator for text tables -- can I store multi-line strings in text
> table?

HSQLDB seems to recognise java escape codes, so \n in a string is a=20
newline eg in the following line from one of my tables the (single line=20
in the file):
"TR",,-39.88087,149.01993,2000-04-29 02:25:00,259,"Transect=3DFlinders=20
(1500m);","One lump of coal - 200g.\nConsiderable amounts of fish etc "

the last column is:
   "One lump of coal - 200g.\nConsiderable amounts of fish etc "
Which is treated as two lines:=20
One lump of coal - 200g.
Considerable amounts of fish etc=20

Regards
Gordon
--=20

Gordon Keith
Programmer/Data Analyst
Marine Acoustics
CSIRO Marine Research
http://www.marine.csiro.au

During the past 30 years people from all the civilised
countries of the earth have consulted me.
Among all of my patients in the second half of life,=20
that is to say, over 35, there has not been one whose
problem in the last resort, was not that of finding a=20
religious outlook on life.
  - Carl Justav Jung, one of the great founders of psychology

> HSQLDB seems to recognise java escape codes, so \n in a string is a
> newline eg in the following line from one of my tables the (single line
> in the file):
> "TR",,-39.88087,149.01993,2000-04-29 02:25:00,259,"Transect=Flinders
> (1500m);","One lump of coal - 200g.\nConsiderable amounts of fish etc "

Uhmmm. So where did that "\n" come from? 'Cause I read values from file
and insert them into text table. If value is multi-line string, it's
written as multi-line string into CSV. That breaks CVS format, of
course,
as newlines are record separators there.

Dima

You can insert \n into any table apart from TEXT tables.

The new version of the software prevents you from inserting the new line
characters.

You can replace your carriage returns and linefeeds with another control
character before inserting into a TEXT table.

Fred
----- Original Message -----
From: "Dmitri Maziuk" <dmaziuk@...>
To: <hsqldb-user@...>
Sent: 01 October 2003 16:06
Subject: Re: [Hsqldb-user] text tables


> HSQLDB seems to recognise java escape codes, so \n in a string is a
> newline eg in the following line from one of my tables the (single line
> in the file):
> "TR",,-39.88087,149.01993,2000-04-29 02:25:00,259,"Transect=Flinders
> (1500m);","One lump of coal - 200g.\nConsiderable amounts of fish etc "

Uhmmm. So where did that "\n" come from? 'Cause I read values from file
and insert them into text table. If value is multi-line string, it's
written as multi-line string into CSV. That breaks CVS format, of
course,
as newlines are record separators there.

Dima


-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

On Wednesday 01 October 2003 10:24 am, fredt wrote:
> You can insert \n into any table apart from TEXT tables.
>
> The new version of the software prevents you from inserting the new lin=
e
> characters.
>
> You can replace your carriage returns and linefeeds with another contro=
l
> character before inserting into a TEXT table.

Thanks. Any reason you won't do this in the engine? -- if you're already=20
checking for newlines, adding code to quote them shouldn't be too bad (& =
let=20
user specify escape sequence the same way we can specify fs and vs).=20

That way a text table can be used as any other SQL table -- other SQL tab=
les=20
don't require special massaging of multi-line strings on user side.

Dima
--=20
I have not been able to think of any way of describing Perl to [person]
"Hello, blind man?  This is color."                                     -=
- DPM

On Thu, 2 Oct 2003 01:06, Dmitri Maziuk wrote:

> > "TR",,-39.88087,149.01993,2000-04-29
> > 02:25:00,259,"Transect=3DFlinders (1500m);","One lump of coal -
> > 200g.\nConsiderable amounts of fish etc "
>
> Uhmmm. So where did that "\n" come from? 'Cause I read values from
> file and insert them into text table.=20

I generate all my text tables outside HSQLDB.=20

In my applications I basically just use HSQLDB as a JDBC->CSV bridge=20
where the CSV files are generated from other sources.

This means my application can be written to use JDBC and work either=20
with a database backend (normally oracle here) or with CVS file written=20
on the CD I distribute the application and data on.=20

I could distribute using cached tables, but having the data in human=20
readable format is very attractive.

Regards
Gordon


--=20

Gordon Keith
Programmer/Data Analyst
Marine Acoustics
CSIRO Marine Research
http://www.marine.csiro.au

The main thing is keep the main thing the main thing.