473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Pushing content of a file into a varchar field?

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


Nov 18 '05 #1
4 1314
Hi,
Use Import Command.. That is so simple..

Regards,
Thiru.
WantedToBeDBA.
Certified DB2 DBA.

Nov 19 '05 #2
Thiru wrote:
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

Nov 21 '05 #3
Michael Bader wrote:
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?


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
Nov 21 '05 #4
Knut Stolze wrote:
Michael Bader wrote:
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?


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.


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
Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: E Arredondo | last post by:
Is there a way to create a MYSQL table or database using a text file that countains the Field name and the lenght ? that is : fields.txt ----- start ---------- name,10 address,15
7
by: James o'konnor | last post by:
hello. i have the next for create one table into db2 CREATE TABLE "MYSQUEMA"."TABLADEMO" ( "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +0 INCREMENT BY +1 MINVALUE +0...
4
by: spwpreston | last post by:
Hello, I am new to MySQL. I am trying to load from a text file to a table, and am having problems with the fields in the text file being translated to the correct fields in the TABLE. Right...
0
by: whitemoss | last post by:
Hi All, I had written a code to read a file and insert it's contents to the database. Since I will receive 3 files every hour, so, this program should read those files and insert the contents...
0
by: Peter Nofelt | last post by:
Hi all, ISSUE: ==================== In SQL 2005 (sp2) I get the following error when preforming a bulk insert with an associated xml format file: "Could not bulk insert. Unknown version of...
4
by: M6C | last post by:
Hello, I'm trying to import a Xml file in a table. It's working find, but when the data are inserted, my trigger on this table doesn't work ? I put my code below. Best regards Thank's
4
by: vunet.us | last post by:
I want to know if this practice is effective and secure: I have been thinking about storing some data, which my users upload, in text files rather than database, since often I do not know how much...
0
by: wisaunders | last post by:
the file I'm importing has > 200,000 records I have one problem: One of the columns in the .txt file (customerID) has almost all Inetger values except for about 30 records. Those 30 records...
4
by: ziycon | last post by:
I have the below code to read in the XML file and to print the query to screen to see what its generating. I have the XML file below that. I'm having problems checking for the for attributes like...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.