Re-select row after insert in 4GL?
SE 7.25.UC6R1
C4GL 7.32.UC4
I writing a table maintenance program in Informix 4GL and I'm wondering if
after inserting a new row I should re-select the row for any reason? Take
the follow sample code for example:
function declare_cursor(lv_sql)
define lv_sql char(512)
prepare p_maint from lv_sql
declare c_maint scroll cursor for p_maint
open c_maint
if fetch_data(1) then
let mv_queried = 1
else
let mv_queried = 0
error "No rows found"
end if
end function
function fetch_data(lv_dir)
define lv_dir smallint
fetch relative lv_dir c_maint into mv_p_class.*
if sqlca.sqlcode=0 then
clear form
call display_data()
return true
end if
if sqlca.sqlcode<0 then
# Give the user a chance to see the error
error "SQL error:",sqlca.sqlcode
sleep 2
end if
return false
end function
function insert_new_data()
define lv_sql char(512)
insert into p_class values(mv_p_class.*)
# Having inserted the row - lets reselect it...
let lv_sql="select * from p_class where pcl_code =",mv_p_class.pcl_code
call declare_cursor(lv_sql)
if mv_queried = false then
error "Internal error - could not reselect inserted row ..."
end if
call display_data()
end function
So after insert_new_data() inserts the new row I am re-selecting the row
that was just inserted. My question is, should I bother doing that or
should I just clear the form and return to the ring menu where the user
can either exit the program or add another record? My ring menu
has "Change" and "Delete" options so by selecting the inserted row I can
immediately either change or delete it without doing a find, but I'm not
sure a user would do either right after an insert.
If you're reselecting to verify that the insert worked, try checking the
SQLCA.SQLCODE (less than 0 is an error) and SQLCA.SQLERRD[3] which
should contain the number of rows affected, in your case it should be 1.
If you need to collect a serial number created by the insert, it should
be contained in SQLCA.SQLERRD[2]. If the table has defaults that may
have been filled by the insert, reselecting the record is an option.
--EEM
> -----Original Message-----
> From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]
> On Behalf Of loc
> Sent: Thursday, September 03, 2009 9:06 AM
> To: informix-list@iiug.org
> Subject: Re-select row after insert in 4GL?
>
>
> SE 7.25.UC6R1
> C4GL 7.32.UC4
>
> I writing a table maintenance program in Informix 4GL and I'm
wondering if
> after inserting a new row I should re-select the row for any reason?
Take
> the follow sample code for example:
>
>
> function declare_cursor(lv_sql)
> define lv_sql char(512)
>
> prepare p_maint from lv_sql
> declare c_maint scroll cursor for p_maint
>
> open c_maint
> if fetch_data(1) then
> let mv_queried = 1
> else
> let mv_queried = 0
> error "No rows found"
> end if
> end function
>
>
> function fetch_data(lv_dir)
> define lv_dir smallint
>
> fetch relative lv_dir c_maint into mv_p_class.*
>
> if sqlca.sqlcode=0 then
> clear form
> call display_data()
> return true
> end if
>
> if sqlca.sqlcode<0 then
> # Give the user a chance to see the error
> error "SQL error:",sqlca.sqlcode
> sleep 2
> end if
>
> return false
> end function
>
>
> function insert_new_data()
> define lv_sql char(512)
>
> insert into p_class values(mv_p_class.*)>
> # Having inserted the row - lets reselect it...
> let lv_sql="select * from p_class where pcl_code
=",mv_p_class.pcl_code
>
> call declare_cursor(lv_sql)
> if mv_queried = false then
> error "Internal error - could not reselect inserted row ..."
> end if
> call display_data()
> end function
>
>
>
> So after insert_new_data() inserts the new row I am re-selecting the
row
> that was just inserted. My question is, should I bother doing that or
> should I just clear the form and return to the ring menu where the
user
> can either exit the program or add another record? My ring menu
> has "Change" and "Delete" options so by selecting the inserted row I
can
> immediately either change or delete it without doing a find, but I'm
not
> sure a user would do either right after an insert.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list