Livelink: Get SQL Error Message Example

/*

	Example showing how to get a reasonable error message from an OScript
	CAPI.Exec call ending in error.

	Below is sample output from running Test().

	Output:
	---------------------------
	SQL: select * from i_am_a_bogus_table
	Error Encountered: ORA-00942: table or view does not exist
	---------------------------
	SQL: select to_number('x') from dual
	Error Encountered: ORA-01722: invalid number
	---------------------------
	SQL: select non_existent_field from (select 'x' from dual)
	Error Encountered: ORA-00904: "I_AM_A_NON_EXISTING_FIELD": invalid identifier
	---------------------------

*/

function void Test()
	Dynamic prgCtx  = $copediscovery.Utils.GetPrgCtx('Admin')
	List    sqlList = {                                                    \
		"select * from i_am_a_bogus_table",                            \
		"select to_number('x') from dual",                             \
		"select i_am_a_non_existing_field from (select 'x' from dual)" }
	String  sql
	Assoc   retVal

	echo("---------------------------");
	for sql in sqlList
		retVal = ExecuteSQL(prgCtx, sql)
		echo("SQL: ", sql)
		if (!retVal.OK)
			echo("Error Encountered: ", retVal.ErrMsg)
		end
		echo("---------------------------");
	end
end


function Assoc ExecuteSQL(Dynamic prgCtx, String sql)
    Object dbConnect = prgCtx.fDbConnect
    CAPICONNECT conn = dbConnect.fConnection
    Dynamic results
    Dynamic dbErrList
    Integer ERR_TO_DISPLAY = 1
    Assoc retVal
    retVal.OK        = false
    retVal.ErrMsg    = Undefined
    retVal.dbResults = Undefined
    results = CAPI.Exec(conn, sql)
    if (IsError(results))
        dbErrList = dbConnect.CheckError(results)
        retVal.ErrMsg = Str.Format('%1', dbErrList.ErrorList[ ERR_TO_DISPLAY ].Detail)
    else
        retVal.OK = true
        retVal.dbResults = results
    end
    return retVal
end