Populate data to Text field in Informix (cont.)
Hi all.
Thanks to Art Agel, Nadine and Jim Tranny for your suggestions.
Since I jumped to Informix 4GL in 1995 as a main frame Cobol programmer=
and
working with only R4GL since then,
I am not familiar with c or other tools like sqlldr (Oracle stuff?).
I tried to work around like Jim as follows:
Using dbaccess to work around:
1. unload to /tmp/ln90 select seq_no,msg_str from rtffmsg where seq_no =
=3D
205429.
(The field msg_str in rtffmsg is a TEXT field)
The contents in the text file /tmp/ln90 is:
205429|<?xml version=3D"1.0" encoding=3D"UTF-8"?>\\\\
<F4FDocumentHeader>\\\\
<SchemaVersion>4.0.2</SchemaVersion>\\\\
|
It should be noted that at the end of each line in the second field
(between 2 pipes) there is a \\\\ sign, not the carriage return characte=
r.
2. create table ln1 (col1 integer, col2 TEXT);
load from /tmp/ln90 insert into ln1; #work OKselect from ln1 where 1=3D1 will show:
col1 205429
col2
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<F4FDocumentHeader>
<SchemaVersion>4.0.2</SchemaVersion>
3. create table ln2 (col1 integer, col2 char(1000);
insert into ln2 values
(205430,"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa=aaaa");
insert into ln2 values
(205431,"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");=
4. unload 2 records from ln2 to a text file: unload to /tm/ln91 select =
*
from ln2;
5. The contents in /tmp/ln91 where the 2(superscript: nd) column is a
string:
205430|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa=
aa|
205431|bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb|
6. load from /tmp/ln91 insert into ln1; select * from ln1;
7. The 3 records in ln1 now are:
col1 205429 #Rec 1
col2 #from rtffmsg.msg_str (TEXT)
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<F4FDocumentHeader>
<SchemaVersion>4.0.2</SchemaVersion>
col1 205430 #Rec 2
col2 #from ln2.col2 (char 1000)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
col1 205431
col2 #from ln2.col2 (char 1000)
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
So from this experiment can I say that the only way to load data to a=
Text field on an Informix table is that we have to use the unload/loa=
d
command?
In an Informix 4Gl program the "insert into ... values (var1,var2....=
)"
or "insert into .... select var1,var2 from ....." statement is where =
the
data is populated
to a table. If the type TEXT or BYTE is allowed to use in a 4GL progr=
am
then there must be some syntax to use like in the above example, the
value in the TEXT field msg_str of the table rtffmsg is populated by =
a
Java programm and my Java colleague showed me how Java loads HTML
information into rttfmsg.msg_str as an Informix table.
So I believe there must be some rule in Informix 4GL to insert data
(whether from a TEXT variable (but how to get data to that TEXT var?)=
or
from such a simple string like in the second case of "unload to
/tmp/ln91" and "load from /tmp/ln91 insert into rtffmsg" where a TEXT=
fielld msg_str exists).
Cheers,
Long N.
Disclaimer:
This correspondence is for the named person's use only. It may contain=
confidential or legally privileged information or both. No confidentia=
lity
or privilege is waived or lost by any mistransmission. If you receive =
this
correspondence in error, please immediately delete it together with any=
attachments from your system and notify the sender. You must not discl=
ose,
copy or rely on any part of this correspondence if you are not the inte=
nded
recipient.
Any opinions expressed in this message are those of the individual send=
er,
except where the sender expressly, and with authority, states them to b=
e
the opinions of Ruralco Holdings Limited or any of its subsidiaries
(collectively "Ruralco").
Although all care has been taken to screen this communication for virus=
es,
neither the sender nor Ruralco warrants that any communication via the
Internet is free of errors, viruses, interception or interference.
Information is distributed without warranties of any kind.
=
On Fri, Jun 6, 2008 at 2:10 AM, Long Nguyen <lnguyen@ruralco.com.au> wrote:
> Hi all.
> Thanks to Art Agel, Nadine and Jim Tranny for your suggestions.
> Since I jumped to Informix 4GL in 1995 as a main frame Cobol programmer=
> and
> working with only R4GL since then,
> I am not familiar with c or other tools like sqlldr (Oracle stuff?).
> I tried to work around like Jim as follows:
>
> Using dbaccess to work around:
> 1. unload to /tmp/ln90 select seq_no,msg_str from rtffmsg where seq_no =
> =3D
> 205429.
> (The field msg_str in rtffmsg is a TEXT field)
> The contents in the text file /tmp/ln90 is:
> 205429|<?xml version=3D"1.0" encoding=3D"UTF-8"?>\\\\
> <F4FDocumentHeader>\\\\
> <SchemaVersion>4.0.2</SchemaVersion>\\\\
> |
> It should be noted that at the end of each line in the second field
> (between 2 pipes) there is a \\\\ sign, not the carriage return characte=
> r.
>
> 2. create table ln1 (col1 integer, col2 TEXT);
> load from /tmp/ln90 insert into ln1; #work OK> select from ln1 where 1=3D1 will show:
>
> col1 205429
>
> col2
>
> <?xml version=3D"1.0" encoding=3D"UTF-8"?>
>
> <F4FDocumentHeader>
>
> <SchemaVersion>4.0.2</SchemaVersion>
>
> 3. create table ln2 (col1 integer, col2 char(1000);
> insert into ln2 values
> (205430,"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa=> aaaa");
> insert into ln2 values
> (205431,"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");=>
> 4. unload 2 records from ln2 to a text file: unload to /tm/ln91 select =
> *
> from ln2;
> 5. The contents in /tmp/ln91 where the 2(superscript: nd) column is a
> string:
> 205430|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa=
> aa|
> 205431|bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb|
> 6. load from /tmp/ln91 insert into ln1; select * from ln1;
> 7. The 3 records in ln1 now are:
> col1 205429 #Rec 1
> col2 #from rtffmsg.msg_str (TEXT)
> <?xml version=3D"1.0" encoding=3D"UTF-8"?>
> <F4FDocumentHeader>
> <SchemaVersion>4.0.2</SchemaVersion>
>
> col1 205430 #Rec 2
> col2 #from ln2.col2 (char 1000)
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
>
> col1 205431
> col2 #from ln2.col2 (char 1000)
> bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
>
> So from this experiment can I say that the only way to load data to a=
>
> Text field on an Informix table is that we have to use the unload/loa=
> d
> command?
> In an Informix 4Gl program the "insert into ... values (var1,var2....=
> )"
> or "insert into .... select var1,var2 from ....." statement is where =
> the
> data is populated
> to a table. If the type TEXT or BYTE is allowed to use in a 4GL progr=
> am
> then there must be some syntax to use like in the above example, the
> value in the TEXT field msg_str of the table rtffmsg is populated by =
> a
> Java programm and my Java colleague showed me how Java loads HTML
> information into rttfmsg.msg_str as an Informix table.
> So I believe there must be some rule in Informix 4GL to insert data
> (whether from a TEXT variable (but how to get data to that TEXT var?)=
> or
> from such a simple string like in the second case of "unload to
> /tmp/ln91" and "load from /tmp/ln91 insert into rtffmsg" where a TEXT=
>
> fielld msg_str exists).
> Cheers,
> Long N.
>
> Disclaimer:
> This correspondence is for the named person's use only. It may contain=
>
> confidential or legally privileged information or both. No confidentia=
> lity
> or privilege is waived or lost by any mistransmission. If you receive =
> this
> correspondence in error, please immediately delete it together with any=
>
> attachments from your system and notify the sender. You must not discl=
> ose,
> copy or rely on any part of this correspondence if you are not the inte=
> nded
> recipient.
> Any opinions expressed in this message are those of the individual send=
> er,
> except where the sender expressly, and with authority, states them to b=
> e
> the opinions of Ruralco Holdings Limited or any of its subsidiaries
> (collectively "Ruralco").
> Although all care has been taken to screen this communication for virus=
> es,
> neither the sender nor Ruralco warrants that any communication via the
> Internet is free of errors, viruses, interception or interference.
> Information is distributed without warranties of any kind.
> =
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
I suspect that I perhaps my suggestions were not clear, since no where
did I mention to use dbaccess or unload/unload. I also suggested that
you take a look at the 'locate' 4GL command.
Jim
There is a nice User Defined HTML type that comes as part of the web
datablade. You can use that to do the following.
They provide casting routines from html to clob. From what I can tell this
does not work for blob which is a text field. But you could change your
text field to a clob and use this type of method.
The UDT for XML might do the same thing but I have not looked at those.
create procedure myloadtext(myid integer)
returning html;
define l_text html;
define my_clob clob;
let l_text = 'THis is a test string';
*** Or what we do here is select from a bunch of rows to form XML type data
and use concat to set l_text ***
let my_clob = LOCOPY(l_text::clob, 'my_text_table', 'my_text_field');
insert into my_text_table values (myid, my_clob);
return l_text;
-- trace off;
end procedure;
On 6/6/08 2:10 AM, "Long Nguyen" <lnguyen@ruralco.com.au> wrote:
> Hi all.
> Thanks to Art Agel, Nadine and Jim Tranny for your suggestions.
> Since I jumped to Informix 4GL in 1995 as a main frame Cobol programmer=
> and
> working with only R4GL since then,
> I am not familiar with c or other tools like sqlldr (Oracle stuff?).
> I tried to work around like Jim as follows:
>
> Using dbaccess to work around:
> 1. unload to /tmp/ln90 select seq_no,msg_str from rtffmsg where seq_no =
> =3D
> 205429.
> (The field msg_str in rtffmsg is a TEXT field)
> The contents in the text file /tmp/ln90 is:
> 205429|<?xml version=3D"1.0" encoding=3D"UTF-8"?>\\\\
> <F4FDocumentHeader>\\\\
> <SchemaVersion>4.0.2</SchemaVersion>\\\\
> |
> It should be noted that at the end of each line in the second field
> (between 2 pipes) there is a \\\\ sign, not the carriage return characte=
> r.
>
> 2. create table ln1 (col1 integer, col2 TEXT);
> load from /tmp/ln90 insert into ln1; #work OK> select from ln1 where 1=3D1 will show:
>
> col1 205429
>
> col2
>
> <?xml version=3D"1.0" encoding=3D"UTF-8"?>
>
> <F4FDocumentHeader>
>
> <SchemaVersion>4.0.2</SchemaVersion>
>
> 3. create table ln2 (col1 integer, col2 char(1000);
> insert into ln2 values
> (205430,"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa=> aaaa");
> insert into ln2 values
> (205431,"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");=>
> 4. unload 2 records from ln2 to a text file: unload to /tm/ln91 select =
> *
> from ln2;
> 5. The contents in /tmp/ln91 where the 2(superscript: nd) column is a
> string:
> 205430|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa=
> aa|
> 205431|bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb|
> 6. load from /tmp/ln91 insert into ln1; select * from ln1;
> 7. The 3 records in ln1 now are:
> col1 205429 #Rec 1
> col2 #from rtffmsg.msg_str (TEXT)
> <?xml version=3D"1.0" encoding=3D"UTF-8"?>
> <F4FDocumentHeader>
> <SchemaVersion>4.0.2</SchemaVersion>
>
> col1 205430 #Rec 2
> col2 #from ln2.col2 (char 1000)
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
>
> col1 205431
> col2 #from ln2.col2 (char 1000)
> bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
>
> So from this experiment can I say that the only way to load data to a=
>
> Text field on an Informix table is that we have to use the unload/loa=
> d
> command?
> In an Informix 4Gl program the "insert into ... values (var1,var2....=
> )"
> or "insert into .... select var1,var2 from ....." statement is where =
> the
> data is populated
> to a table. If the type TEXT or BYTE is allowed to use in a 4GL progr=
> am
> then there must be some syntax to use like in the above example, the
> value in the TEXT field msg_str of the table rtffmsg is populated by =
> a
> Java programm and my Java colleague showed me how Java loads HTML
> information into rttfmsg.msg_str as an Informix table.
> So I believe there must be some rule in Informix 4GL to insert data
> (whether from a TEXT variable (but how to get data to that TEXT var?)=
> or
> from such a simple string like in the second case of "unload to
> /tmp/ln91" and "load from /tmp/ln91 insert into rtffmsg" where a TEXT=
>
> fielld msg_str exists).
> Cheers,
> Long N.
>
> Disclaimer:
> This correspondence is for the named person's use only. It may contain=
>
> confidential or legally privileged information or both. No confidentia=
> lity
> or privilege is waived or lost by any mistransmission. If you receive =
> this
> correspondence in error, please immediately delete it together with any=
>
> attachments from your system and notify the sender. You must not discl=
> ose,
> copy or rely on any part of this correspondence if you are not the inte=
> nded
> recipient.
> Any opinions expressed in this message are those of the individual send=
> er,
> except where the sender expressly, and with authority, states them to b=
> e
> the opinions of Ruralco Holdings Limited or any of its subsidiaries
> (collectively "Ruralco").
> Although all care has been taken to screen this communication for virus=
> es,
> neither the sender nor Ruralco warrants that any communication via the
> Internet is free of errors, viruses, interception or interference.
> Information is distributed without warranties of any kind.
> =
>
>
>
******************************************************************************>
*
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>