OpenInsight V10.0.1

I have the SQL connection working as long as the SQL statement has no issues when completing. If there is an error in the execution the documentation says that a -1 return code should be received. My system always returns a 0 when there is an error. Assuming this is a documentation\minor change in V10 is no problem. However, I am unable to determine how to get the actual error code or error message from SQL. All the provided examples simply display a generic message. That may be ok way to return the result to an end user, but the real error must be returned and documented so that the root cause can be identified and corrected.

Any guidance will be appreciated.

Thanks

DSBFS_EXEC_ERRORS is supposed to return the SQL errors using GET_STATUS

You should be able to use

errors = ""

if get_Status(errors) then

 ...

end

DBFS_EXEC_SQL uses the Ole based ADO libraries. It uses GOSUB TestOleStat after each SQL ADO command. If OleStatus is non zero then it retrieves the details from the SQL connection. Below is the section of code which does this.

- Bob

TestOleStat:

* Any OLE Error?

* May come from the OLE or ADO or SQL server layer

this_desc = ''

err = ''

ecnt = 0



* Non Ole Errors?

Gosub GetSqlErrors

if OleStatus() then

   err_detail<1,-1> = 'Ole Error ':OleStatus()

End

If ecnt then

   status = false$

   Goto ErrorHandler

end



return







GetSqlErrors:

* Server can return error conditions that do not trigger an ole errror

oErrs = Conn->Errors

ecnt = oErrs->Count

for e = 1 to ecnt

   status = false$

   oErr = OleGetProperty(oErrs, 'Item', e-1)

   *oErr = ''

   *is_ok = DSBFS_RecordsetHelper('getItem', oErrs, e-1, oErr)

   this_desc = "Error Number: " :  oErr->Number

   this_desc :=" " :  Quote(oErr->Description)

   this_desc :="  (Source: " : oErr->Source : ")"

   this_desc :="  (SQL State: " : oErr->SQLState : ")"

   this_desc :="  (NativeError: " : oErr->NativeError : ")"

   err_detail<1,-1> = this_desc

Next

x = oErrs->Clear()

Return





ErrorHandler:



* rjc  02-01-13  Clean up

unused = OleCallMethod(rs, 'Close')

unused = OleCallMethod(oErrs, 'Clear')

unused = OleCallMethod(conn, 'Close')

oparams = ''

cmd = ''

rs = ''

conn = ''



if get_Status(unused) else

   call set_status(1, err_detail)

end

status = false$



Return

As way to verify that I am not getting the report codes I setup a test in my V7.3 system which uses the XO_ interface. The same statement using the XO_Calls returns an error and error message in V7.3 returns an empty return code and no errors in V10. In both cases if I remove the trailing 3 on Rev_File_no3 the command processes without error.

This is the V10 logic:

sqlScript = " UPDATE OI_Table SET Rev_File_No3 = N'REV48028' WHERE OI_Table_Name = 'EQUIP_MASTER';"

resultCode = DSBFS_exec_SQL(sourceName, sqlScript, demote_levels, result, test, param_vals, param_types)

errors = if Get_Status(errors) then end This is the V7.3 logic sqlScript = " UPDATE OI_Table SET Rev_File_No3 = N'REV48028' WHERE OI_Table_Name = 'EQUIP_MASTER';" retVal = QryMethod(hQry,QRY_EXECUTE$,sqlScript) retVal = QryMethod(hQry, QRY_GETERROR$, "", "", "","",errTxt) If retVal = 0 Then * Cannot execute command script. errTxt =

        retVal  = QryMethod(hQry, QRY_GETERROR$, "", "", "","",errTxt)

End

This is the error that should be returned:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Rev_File_No3'.

Thanks for the info. I'll if I can reproduce it.

- Bob

Any progress on this issue ?

bb

Checking again to see of there is a resolution or a different method of getting SQL errors. This is becoming critical as I need to release one of my systems in V10 and it relies on quite a few SQL interfaces.

View this thread on the Works forum...