Pushing content of a file into a varchar field?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael Bader - 18 Nov 2005 16:00 GMT

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

Reply to this Message

Thiru - 19 Nov 2005 09:29 GMT

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

Regards,
Thiru.
WantedToBeDBA.
Certified DB2 DBA.

Reply to this Message

Michael Bader - 21 Nov 2005 08:35 GMT

> Hi,
> Use Import Command..

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

Reply to this Message

Knut Stolze - 21 Nov 2005 08:56 GMT

> Hi,
>
[quoted text clipped - 7 lines]
> like 'update ... set field=[content of file] where ...'
> Is there an easy solution for this?

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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Stefan Momma - 23 Nov 2005 22:42 GMT

>> Hi,
>>
[quoted text clipped - 18 lines]
> file or so, and that might be a pain as you have to take care of escaping
> again.

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

Reply to this Message