String to text column
How do I insert a string into a text column using spl routine?
This is part of a message broadcast system; I'm suppose to put the message in
messaging.message and a different process will pickup and broadcast.
The only way this has worked in the past is to save to a file then do a system
call and use dbaccess to load the row. Yuk!
Here is what has been working:
SYSTEM
"echo
'0|BC|UTBCIOOOO|"||wCurrent||"|||"||wMsg||"|'>/IDS/unloads/prot_order_msg/"||wFn
||".unl";
SYSTEM
"echo 'load from /IDS/unloads/prot_order_msg/"||wFn||".unl insert into
messages'|dbaccess socket_logs > /IDS/unloads/messages.log 2>&1";
I would like to do something like:
insert into 'informix'.messages(
m_msg_type ,
m_orig_ori ,
m_start_datetime ,
m_short_msg,
m_msg_text )
values ( 'BC','UTBCIOOOO' ,CURRENT YEAR TO SECOND ,wMsg, wTextMsg );
Where:
DEFINE wMsg VARCHAR(255);
DEFINE wTextMsg REFERENCES TEXT;
So how do I put a string into wTextMsg?
Here's the table:
create table 'informix'.messages (
m_msg_id SERIAL not null,
m_msg_type CHAR(4) not null,
m_orig_ori CHAR(9) not null,
m_start_datetime DATETIME YEAR TO SECOND,
m_end_datetime DATETIME YEAR TO SECOND,
m_short_msg VARCHAR(255,1),
m_msg_text TEXT not null,
m_sent_datetime DATETIME YEAR TO SECOND
)
$uname -a
Linux psdb01-testrf.ps.utah.gov 2.6.32-504.el6.x86_64 #1 SMP Tue Sep 16
01:56:35 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux
$onstat -
IBM Informix Dynamic Server Version 12.10.FC5 -- On-Line -- Up 26 days
00:11:57 -- 7309428 Kbytes
Not sure how large your string needs to be,
but if you can live with a 32Kb limit then
change text to a lvarchar and you do the
insert directly as a quoted string.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 06/30/2015 03:33:22 PM:
> From: "BEVIS KENNEDY" <bkennedy@utah.gov>
> To: ids@iiug.org
> Date: 06/30/2015 03:34 PM
> Subject: String to text column [35358]
> Sent by: ids-bounces@iiug.org
>
> How do I insert a string into a text column using spl routine?
> This is part of a message broadcast system; I'm suppose to put the
message in
> messaging.message and a different process will pickup and broadcast.
>
> The only way this has worked in the past is to save to a file then
> do a system
> call and use dbaccess to load the row. Yuk!
>
> Here is what has been working:
> SYSTEM
> "echo
> '0|BC|UTBCIOOOO|"||wCurrent||"|||"||wMsg||"|'>/IDS/unloads/
> prot=5Forder=5Fmsg/"||wFn||".unl";
>
> SYSTEM
> "echo 'load from /IDS/unloads/prot=5Forder=5Fmsg/"||wFn||".unl insert into
> messages'|dbaccess socket=5Flogs > /IDS/unloads/messages.log 2>&1";
>
> I would like to do something like:
> insert into 'informix'.messages(
>
> m=5Fmsg=5Ftype ,
>
> m=5Forig=5Fori ,
>
> m=5Fstart=5Fdatetime ,
>
> m=5Fshort=5Fmsg,
>
> m=5Fmsg=5Ftext )
> values ( 'BC','UTBCIOOOO' ,CURRENT YEAR TO SECOND ,wMsg, wTextMsg );
>
> Where:
> DEFINE wMsg VARCHAR(255);
> DEFINE wTextMsg REFERENCES TEXT;
>
> So how do I put a string into wTextMsg?
>
> Here's the table:
> create table 'informix'.messages (
>
> m=5Fmsg=5Fid SERIAL not null,
>
> m=5Fmsg=5Ftype CHAR(4) not null,
>
> m=5Forig=5Fori CHAR(9) not null,
>
> m=5Fstart=5Fdatetime DATETIME YEAR TO SECOND,
>
> m=5Fend=5Fdatetime DATETIME YEAR TO SECOND,
>
> m=5Fshort=5Fmsg VARCHAR(255,1),
>
> m=5Fmsg=5Ftext TEXT not null,
>
> m=5Fsent=5Fdatetime DATETIME YEAR TO SECOND
> )
>
> $uname -a
> Linux psdb01-testrf.ps.utah.gov 2.6.32-504.el6.x86=5F64 #1 SMP Tue Sep 16
> 01:56:35 EDT 2014 x86=5F64 x86=5F64 x86=5F64 GNU/Linux
> $onstat -
> IBM Informix Dynamic Server Version 12.10.FC5 -- On-Line -- Up 26 days
> 00:11:57 -- 7309428 Kbytes>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
You can do this easily in esql/c.
Another option, if you are running v12.10 you can try the new long lvarchar
data type. It works just like lvarchar but can handle longer strings (needs
an sbspace though for strings longer than 32k bytes).
Art
On Jun 30, 2015 6:34 PM, "BEVIS KENNEDY" <bkennedy@utah.gov> wrote:
> How do I insert a string into a text column using spl routine?
> This is part of a message broadcast system; I'm suppose to put the message
> in
> messaging.message and a different process will pickup and broadcast.
>
> The only way this has worked in the past is to save to a file then do a
> system
> call and use dbaccess to load the row. Yuk!
>
> Here is what has been working:
> SYSTEM
> "echo
>
>
'0|BC|UTBCIOOOO|"||wCurrent||"|||"||wMsg||"|'>/IDS/unloads/prot_order_msg/"||wFn
||".unl";
>
> SYSTEM
> "echo 'load from /IDS/unloads/prot_order_msg/"||wFn||".unl insert into
> messages'|dbaccess socket_logs > /IDS/unloads/messages.log 2>&1";
>
> I would like to do something like:
> insert into 'informix'.messages(
>
> m_msg_type ,
>
> m_orig_ori ,
>
> m_start_datetime ,
>
> m_short_msg,
>
> m_msg_text )
> values ( 'BC','UTBCIOOOO' ,CURRENT YEAR TO SECOND ,wMsg, wTextMsg );
>
> Where:
> DEFINE wMsg VARCHAR(255);
> DEFINE wTextMsg REFERENCES TEXT;
>
> So how do I put a string into wTextMsg?
>
> Here's the table:
> create table 'informix'.messages (
>
> m_msg_id SERIAL not null,
>
> m_msg_type CHAR(4) not null,
>
> m_orig_ori CHAR(9) not null,
>
> m_start_datetime DATETIME YEAR TO SECOND,
>
> m_end_datetime DATETIME YEAR TO SECOND,
>
> m_short_msg VARCHAR(255,1),
>
> m_msg_text TEXT not null,
>
> m_sent_datetime DATETIME YEAR TO SECOND
> )
>
> $uname -a
> Linux psdb01-testrf.ps.utah.gov 2.6.32-504.el6.x86_64 #1 SMP Tue Sep 16
> 01:56:35 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux
> $onstat -
> IBM Informix Dynamic Server Version 12.10.FC5 -- On-Line -- Up 26 days
> 00:11:57 -- 7309428 Kbytes>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec516235ddf97c90519c494c2