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