Error when inserting in functional index

Hi guys, about 2 weeks ago i made a new post about an error 206 when using a functional index in our 4GL application. We are using IDS 10.FC9 on AIX 5.3 4GL 7.32 We have a 4GL which was returning the error 206 when was trying to do this select: select * from edc_det where cod_emp = v_emp and cod_pto = v_cod_pto and num_edc = v_num_edc order by cla_ent desc end function This is functional index which we created like this: create table "informix".edcve ( cod1 decimal(8,0) not null , cod2 decimal(4,0) not null ); CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0) WITH (NOT VARIANT);RETURN cod1 ; END FUNCTION; create index ix_edcve on edcve(f_test1(cod1)); The reason for this error is because before this select the 4GL do this insert through this function: function ins_edcve(vr_edcve ,v_swc) define vr_edcve record like edcve .*, v_swc,v_sal smallint let nom_tab = "edcve" if v_swc = TRUE then whenever error call det_err_lock_gen else whenever error call det_err_lock_tra end if insert into edcve values (vr_edcve .*) This inserts causes the application switch to database where this table exists, so when application was going to do select on the table edcdet said error 206. Because the application switched to other database. The table edcve exists in the database db_inter and the table edcdet exists in the database db1. Also i found this error only in application, i made a similiar 4GL to replicate this behavior and this is not happen. This only happens with this 4GL and with the functional index. Im struggling to find out what it could case this?? I dont have many ideas what could cause this. I open a case with error, but it have not been not much progress. I hope anyone knows what could be happening. Thanks

That potentially troublesome insert looks like an insert into a local table to me. And where does the stored procedure live? Does it live in the other database and it's being executed remotely? I'm missing something. Is it possible that somewhere in this one 4GL application there is an explicit database switch that you are missing? Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Dec 17, 2009 at 3:45 PM, LYNKZ MIKE <yellr@telecom.com.co> wrote: > Hi guys, about 2 weeks ago i made a new post about an error 206 when using > a > functional index in our 4GL application. > > We are using IDS 10.FC9 on AIX 5.3 4GL 7.32 > > We have a 4GL which was returning the error 206 when was trying to do this > select: > > select * from edc_det> > where cod_emp = v_emp > > and cod_pto = v_cod_pto > > and num_edc = v_num_edc > > order by cla_ent desc > end function > > This is functional index which we created like this: > > create table "informix".edcve > ( > cod1 decimal(8,0) not null , > cod2 decimal(4,0) not null > ); > > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0) > WITH (NOT VARIANT);> RETURN cod1 ; > END FUNCTION; > > create index ix_edcve on edcve(f_test1(cod1));> > The reason for this error is because before this select the 4GL do this > insert > through this function: > > function ins_edcve(vr_edcve ,v_swc) > define > > vr_edcve record like edcve .*, > > v_swc,v_sal smallint > > let nom_tab = "edcve" > if v_swc = TRUE > > then whenever error call det_err_lock_gen > > else whenever error call det_err_lock_tra > end if > > insert into edcve values (vr_edcve .*)> > This inserts causes the application switch to database where this table > exists, so when application was going to do select on the table edcdet said > error 206. Because the application switched to other database. > > The table edcve exists in the database db_inter and the table edcdet exists > in > the database db1. > > Also i found this error only in application, i made a similiar 4GL to > replicate this behavior and this is not happen. > > This only happens with this 4GL and with the functional index. > > Im struggling to find out what it could case this?? > > I dont have many ideas what could cause this. > > I open a case with error, but it have not been not much progress. > > I hope anyone knows what could be happening. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b6900db938047af2efc9

Hi Art, thanks for response, yeah, the function for the functional index exists in the same database as index which is db_inter which is the remote database. The appl 4GL dinamically changes of database, but that is far before the insert in the table. The strange is why the 4GL switchs database when it performs insert?? Any ideas?? Thanks. That potentially troublesome insert looks like an insert into a local table to me. And where does the stored procedure live? Does it live in the other database and it's being executed remotely? I'm missing something. Is it possible that somewhere in this one 4GL application there is an explicit database switch that you are missing? Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Dec 17, 2009 at 3:45 PM, LYNKZ MIKE <yellr@telecom.com.co> wrote: > Hi guys, about 2 weeks ago i made a new post about an error 206 when using > a > functional index in our 4GL application. > > We are using IDS 10.FC9 on AIX 5.3 4GL 7.32 > > We have a 4GL which was returning the error 206 when was trying to do this > select: > > select * from edc_det> > where cod_emp = v_emp > > and cod_pto = v_cod_pto > > and num_edc = v_num_edc > > order by cla_ent desc > end function > > This is functional index which we created like this: > > create table "informix".edcve > ( > cod1 decimal(8,0) not null , > cod2 decimal(4,0) not null > ); > > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0) > WITH (NOT VARIANT);> RETURN cod1 ; > END FUNCTION; > > create index ix_edcve on edcve(f_test1(cod1));> > The reason for this error is because before this select the 4GL do this > insert > through this function: > > function ins_edcve(vr_edcve ,v_swc) > define > > vr_edcve record like edcve .*, > > v_swc,v_sal smallint > > let nom_tab = "edcve" > if v_swc = TRUE > > then whenever error call det_err_lock_gen > > else whenever error call det_err_lock_tra > end if > > insert into edcve values (vr_edcve .*)> > This inserts causes the application switch to database where this table > exists, so when application was going to do select on the table edcdet said > error 206. Because the application switched to other database. > > The table edcve exists in the database db_inter and the table edcdet exists > in > the database db1. > > Also i found this error only in application, i made a similiar 4GL to > replicate this behavior and this is not happen. > > This only happens with this 4GL and with the functional index. > > Im struggling to find out what it could case this?? > > I dont have many ideas what could cause this. > > I open a case with error, but it have not been not much progress. > > I hope anyone knows what could be happening. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b6900db938047af2efc9