Inserting rows into two joined tables using 'sqlca.sqlerrd1'
Hi folks,
I am trying to insert rows into two joined tables using
dbinfo('sqlca.sqlerrd1') in a loop.
Here is the SQL I'm using:
CREATE PROCEDURE insert_offers
DEFINE deptcd, upsellcd CHAR(3);
FOREACH SELECT department_cd, upsell_cd INTO deptcd, upsellcd FROM
mkt_backup
insert into marketing_text (upsell_cd)
values (upsellcd)
insert into department_text (department_cd, marketing_text_id)
values (deptcd, dbinfo('sqlca.sqlerrd1')
END FOREACH
END PROCEDURE
Is this the right way to do it? Is there an easier way to do this in
informix - perhaps without a stored procedure?
(I am a java programmer and never used Informix until recently, so bear
with me.)
Thanks a bunch.
Eugene
That looks fine. However, if you want to avoid creating a stored procedure,
you could prepare and use these statements directly in Java:
insert into marketing_text (upsell_cd)
values (?)
select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
insert into department_text (department_cd, marketing_text_id)
values (?, ?)
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"citynomad" <eugene.lubman@gmail.com> wrote in message
news:1119394927.324348.298810@o13g2000cwo.googlegroups.com...
> Hi folks,
>
> I am trying to insert rows into two joined tables using
> dbinfo('sqlca.sqlerrd1') in a loop.>
> Here is the SQL I'm using:
>
> CREATE PROCEDURE insert_offers
> DEFINE deptcd, upsellcd CHAR(3);>
> FOREACH SELECT department_cd, upsell_cd INTO deptcd, upsellcd FROM
> mkt_backup
> insert into marketing_text (upsell_cd)
> values (upsellcd)
> insert into department_text (department_cd, marketing_text_id)
> values (deptcd, dbinfo('sqlca.sqlerrd1')
> END FOREACH
> END PROCEDURE>
> Is this the right way to do it? Is there an easier way to do this in
> informix - perhaps without a stored procedure?
> (I am a java programmer and never used Informix until recently, so bear
> with me.)
>
> Thanks a bunch.
>
> Eugene
Thanks Doug,
I don't really want to do this from Java, since it's only a one time
import that I need to do, not part of the application. I just wanted
to run the code by someone knowledgeable before I go ahead and load a
whole bunch of junk into production database. :)
Eugene
Doug Lawry wrote:
> That looks fine. However, if you want to avoid creating a stored procedure,
> you could prepare and use these statements directly in Java:
>
> insert into marketing_text (upsell_cd)
> values (?)>
> select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
>
> insert into department_text (department_cd, marketing_text_id)
> values (?, ?)>
> --
> Regards,
> Doug Lawry
> www.douglawry.webhop.org
>
>
> "citynomad" <eugene.lubman@gmail.com> wrote in message
> news:1119394927.324348.298810@o13g2000cwo.googlegroups.com...
> > Hi folks,
> >
> > I am trying to insert rows into two joined tables using
> > dbinfo('sqlca.sqlerrd1') in a loop.> >
> > Here is the SQL I'm using:
> >
> > CREATE PROCEDURE insert_offers
> > DEFINE deptcd, upsellcd CHAR(3);> >
> > FOREACH SELECT department_cd, upsell_cd INTO deptcd, upsellcd FROM
> > mkt_backup
> > insert into marketing_text (upsell_cd)
> > values (upsellcd)
> > insert into department_text (department_cd, marketing_text_id)
> > values (deptcd, dbinfo('sqlca.sqlerrd1')
> > END FOREACH
> > END PROCEDURE> >
> > Is this the right way to do it? Is there an easier way to do this in
> > informix - perhaps without a stored procedure?
> > (I am a java programmer and never used Informix until recently, so bear
> > with me.)
> >
> > Thanks a bunch.
> >
> > Eugene