How to use Unicode \uFFFF in REGEXP_REPLACE built-in function?

[t603]

Hello!

How can I use REGEXP_REPLACE built-in function to do following thing:

update self set text = regexp_replace(text, '@NL@', '\u00E0') where id
= 113;

I have a problem, that I do not know, how to specify Unicode code for
some Unicode character. In Java

newString = oldString.replaceAll("@NL@", "\u00E0");

should work, but I do not know, how to do it in H2. Double quotes I
can not use because of error of wrong column name, I also tried
several ways of escaping, but with no success.

Thank You for answer in advance. Stepan

[t603]

I found it by myself. Use CHAR() built-in function.

update self set html = regexp_replace(html, '@NL@', char(10)) where id
= 5839

Regading this my question, I was thinking about the most simple way,
how to insert content of the (external) file into one column of one
record.

Anyway, thank You for great H2. Stepan

[Thomas Mueller]

Hi,

Your solution (using CHAR()) is the most platform independent
solution. SQL doesn't support escape characters. H2 supports it
however, see http://www.h2database.com/html/functions.html#sql239

> how to insert content of the (external) file into one column of one record.

I'm not sure if you mean this:

CREATE TABLE TEST(NAME VARCHAR);
INSERT INTO TEST VALUES(STRINGDECODE('Hello\nHallo\nBonjour'));

Regards,
Thomas

[t603]

Hi,

thank You for Your answer.

> > how to insert content of the (external) file into one column of one record.
>

> CREATE TABLE TEST(NAME VARCHAR);
> INSERT INTO TEST VALUES(STRINGDECODE('Hello\nHallo\nBonjour'));

...No, I think something like:

create table test (name varchar(50), data varchar);
insert into table test values ('Stepan', fileRead('c:\temp
\stepan.xml', 'UTF-8'));
insert into table test values ('Thomas', fileRead('c:\temp
\thomas.xml', 'UTF-8'));

So something like CSVREAD, but this function, let say FILEREAD will
return String to be inserted as varchar or clob or blob according to
the data type, into which will be loaded or explicitly set as third
argument. Also reverse function FILEWRITE used as

select fileWrite(data, 'c:\temp\stepan.xml', 'UTF-8') from test where
name = 'stepan';
or
select fileWrite(data, 'c:\temp\'+name+'.xml', 'UTF-8') from test
where name is not null and data is not null;

Thank You, Stepan

[Thomas Mueller]

Hi,

> create table test (name varchar(50), data varchar);
> insert into table test values ('Stepan', fileRead('c:\temp
> \stepan.xml', 'UTF-8'));
> insert into table test values ('Thomas', fileRead('c:\temp
> \thomas.xml', 'UTF-8'));

There is a feature request:
Built-in methods to read/write large objects (BLOB and CLOB):
FILE_STORE('test.sql', 'select data from test where id=1'),
FILE_READ('test.sql')

I have moved this up in the list, but it is still priority 2 at the moment.

> select fileWrite(data, 'c:\temp\stepan.xml', 'UTF-8') from test where
> name = 'stepan';

I'm not sure if a 'select with side effect' is a good idea. I'm also
not sure if a method that takes a query is a good idea (CSVWRITE does
that as well, but usually no parameters are required for CSVWRITE).
Another problem is: are two methods required for each (one for CLOB
and another for BLOB), or is one method for read and one for write
enough? What do you think?

Regards,
Thomas

[t603]

Hi,

On Jan 8, 6:04 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> > select fileWrite(data, 'c:\temp\stepan.xml', 'UTF-8') from test where
> > name = 'stepan';
>
> I'm not sure if a 'select with side effect' is a good idea. I'm also

...Well, but I think, that it is needed to write each column from each
row into different files. Let suppose, that I have table with people
and theirs photos. How to store Stepan's photo into stepan.jpg and
Thoma's photo into thomas.jpg? I think, that there are two ways:

1) SQL select with "side effect":

select name, fileWrite(photo, 'c:\temp\'+name+'.jpg', '') from people;

2) inside while or for loop of procedural languague

while (recordSet.next() <> false) {
fileWrite(recordSet.getBlob("photo"), 'c:\\temp\
\'+resultSet.getString('name')+'.jpg', '');
}

For me the second approach - procedural languague - is better,
cleaner, more welcome. But today H2 does not have internal procedural
languague. Although I heard, that You are going to implement
JavaScript as internal procedural languague of H2 via Rhino ;-)

> Another problem is: are two methods required for each (one for CLOB
> and another for BLOB), or is one method for read and one for write
> enough? What do you think?

...There should be difference between string and nonstring Read (and
Write) functions, at least because of character encoding. Or how to
read text file, which is encoded in "windows-1250". I hate non Unicode
files - different character encoding, but this is reality.

Stepan

[t603]

> languague. Although I heard, that You are going to implement
> JavaScript as internal procedural languague of H2 via Rhino ;-)

...Or JavaFX? I do not want to spread false rumours. Stepan

[Thomas Mueller]

Hi,

> > languague. Although I heard, that You are going to implement
> > JavaScript as internal procedural languague of H2 via Rhino ;-)
> ...Or JavaFX? I do not want to spread false rumours.

My current idea is to support Java, using javac (starting a process).
Other compilers could be supported as well (the Eclipse compiler, any
script compiler). Basically, the compiler should be pluggable. A first
try has been implemented in the H2 Console. If you start the H2
Console with the command line options "-webScript true" you can
execute this 'script':

@JAVA
return "Hello " + "World";

It is not very flexible (the code block needs to return an Object).
Also, it is very slow currently because a new process is created.
Anyway it is quite useful (for me). There is one problem: security.
Does anybody know how to run code in a 'sandbox' (like Applets)?

Regards,
Thomas