Hi,

maybe a stupid question, but I can't find an answer to it:
we have a web-based application system, where the texts of
the reply mails are held in a DB2 database (in varchar fields).
To input the texts via the db2 prompt is a pain, because of
the numerous escape characters.
I was thinking about importing the varchar content from a file,
but I don't find how to do it. I'm thinking about something
like 'update ... set field=[content of file] where ...'
Is there an easy solution for this?

Thanks in advance

Michael

Hi,
Use Import Command.. That is so simple..

Regards,
Thiru.
WantedToBeDBA.
Certified DB2 DBA.

Thiru wrote:
[color=blue]
> Hi,
> Use Import Command..[/color]

I did. However, it seems to be impossible to change a single field.
I can of course import an entire set of vaues into a table, but
that's not necessarily what I want to do. Moreover, the import didn't
work to well with .del-files. And I'm reluctant to modify the
..ixf-files ...
I was hoping for a shorter solution. Isn't there any?

Michael

Michael Bader wrote:
[color=blue]
> Hi,
>
> maybe a stupid question, but I can't find an answer to it:
> we have a web-based application system, where the texts of
> the reply mails are held in a DB2 database (in varchar fields).
> To input the texts via the db2 prompt is a pain, because of
> the numerous escape characters.
> I was thinking about importing the varchar content from a file,
> but I don't find how to do it. I'm thinking about something
> like 'update ... set field=[content of file] where ...'
> Is there an easy solution for this?[/color]

You can use a UDF that opens the file (passed as parameter to the UDF) and
returns the file's content as VARCHAR. Then a simple INSERT or UPDATE will
do the trick:

INSERT INTO tab
VALUES ( ..., readMailFromFile(<file-name>), ... )


Using IMPORT would be more complicated as you'd have to build a proper CSV
file or so, and that might be a pain as you have to take care of escaping
again.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze wrote:[color=blue]
> Michael Bader wrote:
>[color=green]
>> Hi,
>>
>> maybe a stupid question, but I can't find an answer to it:
>> we have a web-based application system, where the texts of
>> the reply mails are held in a DB2 database (in varchar fields).
>> To input the texts via the db2 prompt is a pain, because of
>> the numerous escape characters.
>> I was thinking about importing the varchar content from a file,
>> but I don't find how to do it. I'm thinking about something
>> like 'update ... set field=[content of file] where ...'
>> Is there an easy solution for this?[/color]
>
> You can use a UDF that opens the file (passed as parameter to the UDF) and
> returns the file's content as VARCHAR. Then a simple INSERT or UPDATE will
> do the trick:
>
> INSERT INTO tab
> VALUES ( ..., readMailFromFile(<file-name>), ... )
>
>
> Using IMPORT would be more complicated as you'd have to build a proper CSV
> file or so, and that might be a pain as you have to take care of escaping
> again.
>[/color]

This works fine for me for a very trivial table:

db2 create table mail(id int not null primary key, text varchar(3000))
db2 load from loadit of del lobs from `pwd` modified by coldel, insert
into mail

where 'loadit' is a CSV file in the current directory containing id and
filename, separated by comma:

1,mail01
2,mail02
3,mail03
..
..
..

Cheers,

-- stefan