need a update-insert stored procedure
A user wanted a generic, atomic "update if exists, else insert" stored procedure in Informix SPL, passing variable column/value lists (he'd been experimenting with SET collection parameters). Art Kagel recommended using the MERGE statement instead — load the data into a temp table and MERGE into the target with WHEN MATCHED UPDATE / WHEN NOT MATCHED INSERT. Paul Watson noted MERGE can hit the 64K SQL statement limit, which Art clarified depends on the number of columns, not data width. For the user's 1800 tables, Art advised against a fully dynamic procedure and suggested generating per-table procedures from systables/syscolumns/sysindices (e.g. based on his dbstruct.ec utility). The poster accepted this approach.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I don't often ask the community here, but first some background information.
Compared to other DB's I've used in the past, I am a noob when it comes to
Informix, particular stored procedures and it's control structures.
However, I've come across the SET function that allows me to send a variable
list of parameters to a stored procedure, something I would like to take
advantage of. Here is a usage example of SET within an informix stored
procedure:
CREATE PROCEDURE test_3(c SET(CHAR(10) NOT NULL))
RETURNING CHAR(10) AS r;
DEFINE r CHAR(10);
FOREACH SELECT * INTO r FROM TABLE(c)
RETURN r WITH RESUME;
END FOREACH;
END PROCEDURE;
EXECUTE PROCEDURE test_3(SET{'string1','string2','hellworld'});
EXECUTE PROCEDURE test_3('SET{''string3'',''string4''}');
DROP PROCEDURE test_3;
The output from each procedure execution is each individual string sent it.
i.e.
string1
string2
hellworld
and
string3
string4
So, often when writing code that talks to a database, I tend to use a stock
routine in a library that will, with a data structure, update table record
data or insert that data into the table if the record does not exist.
Typically the data structure is in a hash format something along these lines:
Update_Insert( table => *tablename*,
set => {
*field1* => *value1*,
*field2* => *value2*,
*field3* => *value4*
},
where => {
*field_a* => *value_a*,
*field_a* => *value_a*
},
insert_values => {
*field1* => *value1*,
*field2* => *value2*
}
);
I have need to make this routine as atomic a function as possible. So, in the
DB it goes. Yes, I can use begin/commit/restore, and I will, but reducing the
time of execution and appropriate locking is what's driving this need.
So my question is - how do i go about porting a code-based routine like this
Update_Insert function, into an Informix stored procedure?
sry, for the bad formatting, the editor took away my indentation, it's not
clear how to restore it.
thanks in advance!!
Have you looked at using the MERGE statement instead of a stored
procedure? Just put the data you need to process into a temp table and
MERGE the temp table into the target table with WHEN MATCHED UPDATE ... and
WHEN NOT MATCHED INSERT .... An example using a temp table that has the
same column structure as the target:
MERGE INTO target_table AS t
USING temp_table AS s
ON s.key_col1 = t.key_col1
AND s.key_col2 = t.key_col2 ...
WHEN MATCHED UPDATE
SET t.attr_1 = s.attr_1, t.attr_2 = s.attr_2, ...
WHEN NOT MATCHED INSERT (t.key_col1, t.key_col2, ... t.attr_1, t.attr_2,
...)
VALUES (s.key_col1, s.key_col2, ..., s.attr_1, s.attr_2, ... )
;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Jan 7, 2015 at 10:25 AM, MATTHEW KAISER <mkaise@midwestern.edu>
wrote:
> I don't often ask the community here, but first some background
> information.
>
> Compared to other DB's I've used in the past, I am a noob when it comes to
> Informix, particular stored procedures and it's control structures.
>
> However, I've come across the SET function that allows me to send a
> variable
> list of parameters to a stored procedure, something I would like to take
> advantage of. Here is a usage example of SET within an informix stored
> procedure:
>
> CREATE PROCEDURE test_3(c SET(CHAR(10) NOT NULL))>
> RETURNING CHAR(10) AS r;
>
> DEFINE r CHAR(10);
>
> FOREACH SELECT * INTO r FROM TABLE(c)
>
> RETURN r WITH RESUME;
>
> END FOREACH;
>
> END PROCEDURE;
>
> EXECUTE PROCEDURE test_3(SET{'string1','string2','hellworld'});>
> EXECUTE PROCEDURE test_3('SET{''string3'',''string4''}');>
> DROP PROCEDURE test_3;>
> The output from each procedure execution is each individual string sent it.
> i.e.
>
> string1
> string2
> hellworld
>
> and
>
> string3
> string4
>
> So, often when writing code that talks to a database, I tend to use a stock
> routine in a library that will, with a data structure, update table record
> data or insert that data into the table if the record does not exist.
> Typically the data structure is in a hash format something along these
> lines:
>
> Update_Insert( table => *tablename*,
>
> set => {
>
> *field1* => *value1*,
>
> *field2* => *value2*,
>
> *field3* => *value4*
>
> },
>
> where => {
>
> *field_a* => *value_a*,
>
> *field_a* => *value_a*
>
> },
>
> insert_values => {
>
> *field1* => *value1*,
>
> *field2* => *value2*
>
> }
>
> );
>
> I have need to make this routine as atomic a function as possible. So, in
> the
> DB it goes. Yes, I can use begin/commit/restore, and I will, but reducing
> the
> time of execution and appropriate locking is what's driving this need.
>
> So my question is - how do i go about porting a code-based routine like
> this
> Update_Insert function, into an Informix stored procedure?
>
> sry, for the bad formatting, the editor took away my indentation, it's not
> clear how to restore it.
>
> thanks in advance!!
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b3a8192391bac050c1277ee
Thank you Art, I can use that.
Is there a way of using the systables and syscolumns system tables to make
this more generic, so I don't have to know the identity of the table ahead of
time?
Can I do something along the lines of using systables to grabbing a pointer to
a table or can I use a variable to hold the tablename within a sql statement?
Something along the lines of this pseudocode:
DEFINE r = table_name;
DEFINE cols = SET{'col1','col2',...};
DEFINE vals = SET{val1,val2,...};
INSERT INTO r (cols) values (vals);
Merge is very useful until you wide tables with char data then you will blow
through the max sql statement sizzle
Cheers
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
> On Jan 7, 2015, at 10:32, Art Kagel <art.kagel@gmail.com> wrote:
>
> Have you looked at using the MERGE statement instead of a stored
> procedure? Just put the data you need to process into a temp table and
> MERGE the temp table into the target table with WHEN MATCHED UPDATE ... and
> WHEN NOT MATCHED INSERT .... An example using a temp table that has the
> same column structure as the target:
>
> MERGE INTO target_table AS t
> USING temp_table AS s
> ON s.key_col1 = t.key_col1
>
> AND s.key_col2 = t.key_col2 ...
> WHEN MATCHED UPDATE
>
> SET t.attr_1 = s.attr_1, t.attr_2 = s.attr_2, ...
> WHEN NOT MATCHED INSERT (t.key_col1, t.key_col2, ... t.attr_1, t.attr_2,
> ....)
> VALUES (s.key_col1, s.key_col2, ..., s.attr_1, s.attr_2, ... )
> ;
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.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 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, Jan 7, 2015 at 10:25 AM, MATTHEW KAISER <mkaise@midwestern.edu>
> wrote:
>
>> I don't often ask the community here, but first some background
>> information.
>>
>> Compared to other DB's I've used in the past, I am a noob when it comes to
>> Informix, particular stored procedures and it's control structures.
>>
>> However, I've come across the SET function that allows me to send a
>> variable
>> list of parameters to a stored procedure, something I would like to take
>> advantage of. Here is a usage example of SET within an informix stored
>> procedure:
>>
>> CREATE PROCEDURE test_3(c SET(CHAR(10) NOT NULL))>>
>> RETURNING CHAR(10) AS r;
>>
>> DEFINE r CHAR(10);
>>
>> FOREACH SELECT * INTO r FROM TABLE(c)
>>
>> RETURN r WITH RESUME;
>>
>> END FOREACH;
>>
>> END PROCEDURE;
>>
>> EXECUTE PROCEDURE test_3(SET{'string1','string2','hellworld'});>>
>> EXECUTE PROCEDURE test_3('SET{''string3'',''string4''}');>>
>> DROP PROCEDURE test_3;>>
>> The output from each procedure execution is each individual string sent it.
>> i.e.
>>
>> string1
>> string2
>> hellworld
>>
>> and
>>
>> string3
>> string4
>>
>> So, often when writing code that talks to a database, I tend to use a stock
>> routine in a library that will, with a data structure, update table record
>> data or insert that data into the table if the record does not exist.
>> Typically the data structure is in a hash format something along these
>> lines:
>>
>> Update_Insert( table => *tablename*,
>>
>> set => {
>>
>> *field1* => *value1*,
>>
>> *field2* => *value2*,
>>
>> *field3* => *value4*
>>
>> },
>>
>> where => {
>>
>> *field_a* => *value_a*,
>>
>> *field_a* => *value_a*
>>
>> },
>>
>> insert_values => {
>>
>> *field1* => *value1*,
>>
>> *field2* => *value2*
>>
>> }
>>
>> );
>>
>> I have need to make this routine as atomic a function as possible. So, in
>> the
>> DB it goes. Yes, I can use begin/commit/restore, and I will, but reducing
>> the
>> time of execution and appropriate locking is what's driving this need.
>>
>> So my question is - how do i go about porting a code-based routine like
>> this
>> Update_Insert function, into an Informix stored procedure?
>>
>> sry, for the bad formatting, the editor took away my indentation, it's not
>> clear how to restore it.
>>
>> thanks in advance!!
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> --047d7b3a8192391bac050c1277ee
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, you COULD do build an INSERT and UPDATE or even a MERGE statement
dynamically in ESQL/C (or other host language) or even in and SPL procedure
to process data for any named table, but I don't know why you WOULD. You
know what tables you have to process and you know the structure of each.
So, just write a separate procedure for each table. These will be more
efficient than having to read the catalog and build and prepare a dynamic
statement for each call to the procedure. Is your database's schema so
dynamic that this is not practical? If so, then maybe those tables should
be JSON/BSON collections inside Informix instead! Of course, then noone
will know the current schema of any given document in the collection, but
you just have to worry about what data columns you have in the input data.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Jan 7, 2015 at 12:32 PM, MATTHEW KAISER <mkaise@midwestern.edu>
wrote:
> Thank you Art, I can use that.
>
> Is there a way of using the systables and syscolumns system tables to make
> this more generic, so I don't have to know the identity of the table ahead
> of
> time?
>
> Can I do something along the lines of using systables to grabbing a
> pointer to
> a table or can I use a variable to hold the tablename within a sql
> statement?
>
> Something along the lines of this pseudocode:
>
> DEFINE r = table_name;
> DEFINE cols = SET{'col1','col2',...};
> DEFINE vals = SET{val1,val2,...};
>
> INSERT INTO r (cols) values (vals);>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c33bda088fb0050c1371c9
Not if you merge against a temp table! Well, OK, a table with so many
columns that the lists of columns you are comparing in the ON clause and
the WHEN clauses exceed the 64K size limit will still be a problem. My
point is just that it's not the size of the data itself that causes a
limitation, but rather the number of columns to be compared and merged.
You can safely merge a table with a small number of very wide character
columns and a row size greater than 64K but not one with 30,000 smallint
columns.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Jan 7, 2015 at 12:35 PM, Paul Watson <paul@oninit.com> wrote:
> Merge is very useful until you wide tables with char data then you will
> blow
> through the max sql statement sizzle
>
> Cheers
> Paul
>
> Paul Watson
> Oninit www.oninit.com
> +1 913 387 7529
>
> > On Jan 7, 2015, at 10:32, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > Have you looked at using the MERGE statement instead of a stored
> > procedure? Just put the data you need to process into a temp table and
> > MERGE the temp table into the target table with WHEN MATCHED UPDATE ...
> and
> > WHEN NOT MATCHED INSERT .... An example using a temp table that has the
> > same column structure as the target:
> >
> > MERGE INTO target_table AS t
> > USING temp_table AS s
> > ON s.key_col1 = t.key_col1
> >
> > AND s.key_col2 = t.key_col2 ...
> > WHEN MATCHED UPDATE
> >
> > SET t.attr_1 = s.attr_1, t.attr_2 = s.attr_2, ...
> > WHEN NOT MATCHED INSERT (t.key_col1, t.key_col2, ... t.attr_1, t.attr_2,
> > ....)
> > VALUES (s.key_col1, s.key_col2, ..., s.attr_1, s.attr_2, ... )
> > ;
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.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 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, Jan 7, 2015 at 10:25 AM, MATTHEW KAISER <mkaise@midwestern.edu>
> > wrote:
> >
> >> I don't often ask the community here, but first some background
> >> information.
> >>
> >> Compared to other DB's I've used in the past, I am a noob when it comes
> to
> >> Informix, particular stored procedures and it's control structures.
> >>
> >> However, I've come across the SET function that allows me to send a
> >> variable
> >> list of parameters to a stored procedure, something I would like to take
> >> advantage of. Here is a usage example of SET within an informix stored
> >> procedure:
> >>
> >> CREATE PROCEDURE test_3(c SET(CHAR(10) NOT NULL))> >>
> >> RETURNING CHAR(10) AS r;
> >>
> >> DEFINE r CHAR(10);
> >>
> >> FOREACH SELECT * INTO r FROM TABLE(c)
> >>
> >> RETURN r WITH RESUME;
> >>
> >> END FOREACH;
> >>
> >> END PROCEDURE;
> >>
> >> EXECUTE PROCEDURE test_3(SET{'string1','string2','hellworld'});> >>
> >> EXECUTE PROCEDURE test_3('SET{''string3'',''string4''}');> >>
> >> DROP PROCEDURE test_3;> >>
> >> The output from each procedure execution is each individual string sent
> it.
> >> i.e.
> >>
> >> string1
> >> string2
> >> hellworld
> >>
> >> and
> >>
> >> string3
> >> string4
> >>
> >> So, often when writing code that talks to a database, I tend to use a
> stock
> >> routine in a library that will, with a data structure, update table
> record
> >> data or insert that data into the table if the record does not exist.
> >> Typically the data structure is in a hash format something along these
> >> lines:
> >>
> >> Update_Insert( table => *tablename*,
> >>
> >> set => {
> >>
> >> *field1* => *value1*,
> >>
> >> *field2* => *value2*,
> >>
> >> *field3* => *value4*
> >>
> >> },
> >>
> >> where => {
> >>
> >> *field_a* => *value_a*,
> >>
> >> *field_a* => *value_a*
> >>
> >> },
> >>
> >> insert_values => {
> >>
> >> *field1* => *value1*,
> >>
> >> *field2* => *value2*
> >>
> >> }
> >>
> >> );
> >>
> >> I have need to make this routine as atomic a function as possible. So,
> in
> >> the
> >> DB it goes. Yes, I can use begin/commit/restore, and I will, but
> reducing
> >> the
> >> time of execution and appropriate locking is what's driving this need.
> >>
> >> So my question is - how do i go about porting a code-based routine like
> >> this
> >> Update_Insert function, into an Informix stored procedure?
> >>
> >> sry, for the bad formatting, the editor took away my indentation, it's
> not
> >> clear how to restore it.
> >>
> >> thanks in advance!!
> >
>
>
*******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> > --047d7b3a8192391bac050c1277ee
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b3a8ff0ca8c5e050c137f69
Awesome, Thanks! That points me in the right direction. My DB has over 1800 tables inside and I don't always have control over how there built, so with my atomic constraint, hence the need for a generic but flexible routine.
I would write a utility or script to read a table's schema from the systable, syscolumns, and sysconstraints or sysindices (for keys) and generate a procedure for you. Then you can run it against all 1800 tables and rerun it any time the schema for one changes. Better that way. You don't have to code 1800 procedures by hand and you don't have to eat the overhead of a fully dynamic procedure! My dbstruct.ec utility source (from the utils2_ak package) would be a good starting point if you are comfortable with ESQL/C. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Jan 7, 2015 at 12:47 PM, MATTHEW KAISER <mkaise@midwestern.edu> wrote: > Awesome, Thanks! > > That points me in the right direction. > > My DB has over 1800 tables inside and I don't always have control over how > there built, so with my atomic constraint, hence the need for a generic but > flexible routine. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013c667251a5da050c13fa59