Need guidance using DSBFS_exec_SQL (OpenInsight 64-bit) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight 64-bit" author:"Brad Bishop" author:"bob carten"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== Need guidance using DSBFS_exec_SQL (OpenInsight 64-bit) ====
=== At 17 NOV 2021 03:33:23PM Brad Bishop wrote: ===
 
 
<QUOTE>
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






</QUOTE>
----

=== At 17 NOV 2021 04:52PM bob carten wrote:  ===

<QUOTE>



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






<code>
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
</code>






</QUOTE>

----

=== At 18 NOV 2021 11:24AM Brad Bishop wrote:  ===

<QUOTE>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'.



</QUOTE>

----

=== At 18 NOV 2021 01:16PM bob carten wrote:  ===

<QUOTE>

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



- Bob

</QUOTE>

----

=== At 09 DEC 2021 09:40AM Brad Bishop wrote:  ===

<QUOTE>Any progress on this issue ?



bb





</QUOTE>

----

=== At 13 JAN 2022 05:11PM Brad Bishop wrote:  ===

<QUOTE>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. 

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=68EA9B31EF3B5641B5C99053738969E1|View this thread on the Works forum...]]