String to text column solution
After struggling with java for a while, jdbc doesn't seem to support text
fields, I finally came up with this spl solution. It may not be eloquent but
it works. I ran this in dbaccess on my 'testdb':
create table 'informix'.test_msgs (
m_msg_id SERIAL not null,
m_msg_text TEXT
)
extent size 16 next size 32
lock mode row;
CREATE PROCEDURE "informix".ins_socketmsg
(pMsg VARCHAR(255)) ;
DEFINE wFn VARCHAR(20);
LET wFn=TO_CHAR(CURRENT YEAR TO SECOND,'%Y%m%d%H%M%S');
SYSTEM
"echo '0|"||pMsg||"|'>/tmp/"||wFn||".unl";
SYSTEM
"echo 'load from /tmp/"||wFn||".unl insert into test_msgs'|dbaccess testdb >
/tmp/messages.log 2>&1";
SYSTEM
"rm /tmp/"||wFn||".unl";
END PROCEDURE;
-- Permissions for routine "ins_socketmsg"
grant execute on procedure 'informix'.ins_socketmsg(varchar) to 'public';
execute procedure ins_socketmsg('test msg');
select * from test_msgs;
{ This is the dbaccess result
m_msg_id 1
m_msg_text
test msg }
But that will only handle TEXT blobs of 255 characters or less. Why not
just create a small ESQL/C program that takes the insert statement (with a
replaceable parameter) on the command line to operate on the text and just
execute it directly from Java? You can even make it run as a filter so the
Java app can open it as a pipe and stream the text into it. It will work
more efficiently, handle blobs up to the maximum size and work for any
table.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Nov 23, 2011 at 1:44 PM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
> After struggling with java for a while, jdbc doesn't seem to support text
> fields, I finally came up with this spl solution. It may not be eloquent
> but
> it works. I ran this in dbaccess on my 'testdb':
>
> create table 'informix'.test_msgs (
>
> m_msg_id SERIAL not null,
>
> m_msg_text TEXT
> )
> extent size 16 next size 32
> lock mode row;
>
> CREATE PROCEDURE "informix".ins_socketmsg
> (pMsg VARCHAR(255)) ;
>
> DEFINE wFn VARCHAR(20);
> LET wFn=TO_CHAR(CURRENT YEAR TO SECOND,'%Y%m%d%H%M%S');
>
> SYSTEM
> "echo '0|"||pMsg||"|'>/tmp/"||wFn||".unl";
>
> SYSTEM
> "echo 'load from /tmp/"||wFn||".unl insert into test_msgs'|dbaccess testdb
> >
> /tmp/messages.log 2>&1";
>
> SYSTEM
> "rm /tmp/"||wFn||".unl";
>
> END PROCEDURE;
>
> -- Permissions for routine "ins_socketmsg"
> grant execute on procedure 'informix'.ins_socketmsg(varchar) to 'public';>
> execute procedure ins_socketmsg('test msg');
> select * from test_msgs;>
> { This is the dbaccess result
> m_msg_id 1
> m_msg_text
> test msg }
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8d2a3030f104b26b71b8