Need some help on Insert fail
Hi all, im in trouble.... In 4GL i have this issue i PREPARE an insert, then EXECUTE the insert, but some cases no error is registered(as i see) and insert dont happen: Table has SERIAL8 as primary key and is in an external instance of where the 4GL program runs, field log_exc is LVARCHAR Almost always works fine, just a couple times, always the same places it just dont happen, no insert no error in log file or servers logs ... Im looking for any idea that can help me, i cant control the text received on parameter or g_* variables... #################################### FUNCTION f_error_it_logging(pexc) #################################### DEFINE pexc CHAR(500) DEFINE lsql CHAR(512) DEFINE llog_fec DATETIME YEAR TO SECOND DEFINE llog_usr VARCHAR(128) DEFINE llog_srv_usr VARCHAR(128) DEFINE llog_srv_nam VARCHAR(128) DEFINE llog_lvl VARCHAR(5) DEFINE llog_nom VARCHAR(128) DEFINE llog_app VARCHAR(128) DEFINE llog_dir VARCHAR(128) DEFINE llog_msg VARCHAR(255) DEFINE lerr VARCHAR(255) LET llog_fec = CURRENT LET llog_usr = g_usr CLIPPED LET llog_srv_usr = g_usr CLIPPED LET llog_srv_nam = fgl_getenv("HOSTNAME") LET llog_lvl = "ERROR" LET llog_nom = fgl_getenv("INFORMIXSERVER") LET llog_app = g_prg CLIPPED LET llog_dir = fgl_getenv("PWD") LET llog_msg = g_err_msg CLIPPED WHENEVER ERROR CONTINUE LET lsql = "INSERT INTO logging@logdb:log(log_fec, log_usr, log_srv_usr,", "log_srv_nam, log_lvl, log_nom, log_app, log_dir, log_msg,", "log_exc) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" PREPARE l_ins FROM lsql IF (SQLCA.SQLCODE <> 0) THEN LET lerr = "IT.LOGGING (PREPARE) SQLCODE: ", SQLCA.SQLCODE, " - SQLERRM: ", SQLCA.SQLERRM CALL ERRORLOG(lerr CLIPPED) END IF EXECUTE l_ins USING llog_fec, llog_usr, llog_srv_usr, llog_srv_nam, llog_lvl, llog_nom, llog_app, llog_dir, llog_msg, pexc IF (SQLCA.SQLCODE <> 0) THEN LET lerr = "IT.LOGGING (EXECUTE) SQLCODE: ", SQLCA.SQLCODE, " - SQLERRM: ", SQLCA.SQLERRM CALL ERRORLOG(lerr CLIPPED) END IF WHENEVER ERROR STOP END FUNCTION
Try using this check after your EXECUTE statement. It should catch a failure that doesn't generate an error code: IF ( SQLCA.SQLCODE <> 0 OR SQLCA.SQLERRD[3] <> 1 ) THEN ... Instead of: IF (SQLCA.SQLCODE <> 0) THEN If you can, add the actual values that the insert fail to process as well, so you can try them yourself or see if there's something odd about them. Also, you didn't mention which version of the database you're using or which version of 4GL. --EEM >-----Original Message----- >From: informix-list-bounces@iiug.org [mailto:informix-list- >bounces@iiug.org] On Behalf Of eferreyra >Sent: Monday, October 17, 2011 11:57 AM >To: informix-list@iiug.org >Subject: Need some help on Insert fail > >Hi all, im in trouble.... > >In 4GL i have this issue i PREPARE an insert, then EXECUTE the insert, >but some cases no error is registered(as i see) and insert dont >happen: > >Table has SERIAL8 as primary key and is in an external instance of where >the 4GL program runs, field log_exc is LVARCHAR > >Almost always works fine, just a couple times, always the same places it >just dont happen, no insert no error in log file or servers logs ... > >Im looking for any idea that can help me, i cant control the text >received on parameter or g_* variables... > >#################################### >FUNCTION f_error_it_logging(pexc) >#################################### >DEFINE pexc CHAR(500) > >DEFINE lsql CHAR(512) >DEFINE llog_fec DATETIME YEAR TO SECOND >DEFINE llog_usr VARCHAR(128) >DEFINE llog_srv_usr VARCHAR(128) >DEFINE llog_srv_nam VARCHAR(128) >DEFINE llog_lvl VARCHAR(5) >DEFINE llog_nom VARCHAR(128) >DEFINE llog_app VARCHAR(128) >DEFINE llog_dir VARCHAR(128) >DEFINE llog_msg VARCHAR(255) >DEFINE lerr VARCHAR(255) > >LET llog_fec = CURRENT >LET llog_usr = g_usr CLIPPED >LET llog_srv_usr = g_usr CLIPPED >LET llog_srv_nam = fgl_getenv("HOSTNAME") LET llog_lvl = "ERROR" >LET llog_nom = fgl_getenv("INFORMIXSERVER") LET llog_app = g_prg CLIPPED >LET llog_dir = fgl_getenv("PWD") LET llog_msg = g_err_msg CLIPPED > >WHENEVER ERROR CONTINUE > >LET lsql = "INSERT INTO logging@logdb:log(log_fec, log_usr, >log_srv_usr,", > "log_srv_nam, log_lvl, log_nom, log_app, log_dir, >log_msg,", > "log_exc) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" > >PREPARE l_ins FROM lsql > >IF (SQLCA.SQLCODE <> 0) >THEN > LET lerr = "IT.LOGGING (PREPARE) SQLCODE: ", SQLCA.SQLCODE, > " - SQLERRM: ", SQLCA.SQLERRM > CALL ERRORLOG(lerr CLIPPED) >END IF > >EXECUTE l_ins USING llog_fec, llog_usr, llog_srv_usr, llog_srv_nam, >llog_lvl, > llog_nom, llog_app, llog_dir, llog_msg, pexc > >IF (SQLCA.SQLCODE <> 0) >THEN > LET lerr = "IT.LOGGING (EXECUTE) SQLCODE: ", SQLCA.SQLCODE, > " - SQLERRM: ", SQLCA.SQLERRM > CALL ERRORLOG(lerr CLIPPED) >END IF > >WHENEVER ERROR STOP > >END FUNCTION >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list