Please keep in mind that you are not supposed to be checking the local variable, SQLCODE. You should be checking the %SQLCODE property of either the prepared statement or the %SQL.StatementResult instance returned by %Execute().

And, the undocumented but fully supported methods of prepare() and execute() do throw exceptions now - no status code unless you want a status code. Just catch any thrown exception and process it as you wish.

I see two primary advantages of using exceptions over other error handling mechanisms. First is that exceptions can integrate all of the other error reporting mechanisms, allowing for consolidation of error handling code as well as the ability to report errors reported by one mechanism as an error using a different mechanism - %Status reported as SQLCODE and so on. The second is performance. Exceptions, using try/catch, are basically zero-cost for success, overhead encountered only when an exception is actually thrown. 

An additional advantage that is more subjective is code "cleanliness". Code written using try/catch/throw doesn't have to continually check for errors unless there functions not using some other error protocol are referenced.

To your question, I do not always add try/catch to a method if the catch {} block simply re-throws the exception. I only catch an exception if I need to do something to the exception before re-throwing it (or not throwing it at all) to the caller.

-Dan

PS: 

There is a macro, $$$THROWONERROR, that helps clean up calls to functions that return %Status. This macro is a convenient way to replace this pattern:

    set status = ..StatusReturningMethod()
    ​if $$$ISERR(status) { throw ##class(%Exception.StatusException).CreateFromStatus(status) }

with

    $$$THROWONERROR(status,..StatusReturningMethod())

If the status is not returned by the code, perhaps it is returned as a by-reference parameter value, then there is another macro that can help with the throw:

     $$$ThrowStatus(status)

Yes, that was placed there for consistency with %Library.ResultSet.

Thanks for pointing that out!

Here are two snippets. The first is as close as I can come to Amir's original example and the second is a more radical version that embraces try/catch and exceptions.

 /// Always return a %Status
ClassMethod SomeMethod2() As %Status
{
    Set tSC = $System.Status.OK()
    Try
    {
        Set oRS = ##class(%SQL.Statement).%New()
        Set tSC = oRS.%Prepare("Select Name,DOB,Home_City from Sample.Person where Name %STARTSWITH ?")
        Quit:$System.Status.IsError(tSC)
        Set result = oRS.%Execute("A")
        if result.%SQLCODE '< 0 {
            While result.%Next()
            {
                //Do something...
            }
        } else {
            throw ##class(%Exception.SQL).CreateFromSQLCODE(result.%SQLCODE,result.%Message)
        }
    }
    Catch (oException)
    {
        Set tSC = oException.AsStatus()
    }
    Quit tSC
}

Snippet #2 - embrace exceptions

 /// Always throw an exception, return something useful to the caller
ClassMethod SomeMethod3() as %String
{
    try {
        set oRS = ##class(%SQL.Statement).%New()
        do oRS.prepare("Select Name,DOB,Home_City from Sample.Person where Name %STARTSWITH ?")
        set result = oRS.execute("A")
        while result.%Next() {
            //Do something...
        }
         if result.%SQLCODE < 0 {
            throw ##class(%Exception.SQL).CreateFromSQLCODE(result.%SQLCODE,result.%Message)
        }
        set response = "something useful"
    } catch (oException) {
            // process the exception - perhaps eat it and return or re-throw
            // often, we can place error/exception logging code here
        throw oException
    }
    return response
}

Of course, it would be nice if there were a "next()" method that throws an exception. Even the prepare() and execute() are not "officially" documented.

HTH,

Dan

%Library.ResultSet combines the statement (prepared) and the result into a single instance. You must consume the entire result - or discard it - before you can execute the prepared statement again. %Library.ResultSet also copies the data from the underlying embedded SQL memory into the result set object - perhaps more than once. %SQL.Statement is the prepared statement and it can be executed as many times as you wish, each producing an independent result. The SQL statement result shares memory with SQL SELECT statements so data does not have to be copied in most cases.

%SQL.Statement and %SQL.StatementResult do provide status values where appropriate. However, the convention of "always return a %Status value" destroys our ability to implement true functions that return a usable return value. With the convention of always returning a %Status value, the error case is the primary check. With exceptions, errors become, well, the exception and code can be written in a more direct manner. Most modern programming languages use try/catch.

Since you like macros, you might investigate $$$THROWONERROR. This macro allows the COS programmer to combine %Status values and exceptions using try/catch, writing the error handling only once - in a CATCH block.

Can you elaborate on your comment regarding status and exception mixtures with %SQL.Statement? I think the interface is completely normal - and consistent with the SQL/CLI Standard. We do report %Status from some calls but the primary error handling is through the very normal SQLCODE/%Message properties.

For many simple statements and common operations, they will perform nearly the same. Both employ a generated code container with an embedded SQL statement that is compiled using the same SQL query processor. It is the interface to the compiled embedded SQL query where we see differences. The new(er - implemented several years ago) dynamic SQL uses objects to scope versions, you can have multiple instances of the same query open at the same time, you can return multiple result sets from a stored procedure using CALL, you can retrieve output-directed parameters from a CALL-ed procedure, you can execute DDL statements, and so on. With %ObjectSelectMode active, you can retrieve columns that are directly swizzled, enabling access to in-memory versions of objects that may not have been saved to disk. There are many reasons to use %SQL.Statement.

Are you saying that %Library.ResultSet performs better than %SQL.Statement when executing an equivalent query and retrieving the result? If so, please provide timing tests showing the difference.

It really depends on what you are doing. For retrieving all rows from a result set the increase we measured when we did initial testing showed 4-7x improvement. Dynamic SQL is a more fully featured implementation. It was designed to be consistent with the SQL-CLI standard. It can do so much more than our other dynamic mechanisms.

Yes, of course. The SQLCOMPUTECODE can invoke a classmethod but not an instance method unless you provide the instance. If you wish to pass arguments that correspond to other properties then just use curly-brace syntax to refer to them:

sqlcomputecode = { set {*} = ..MyComputeMethod({property1},{property2}) }

and the compiler will work out the details.

Also, we have proposed syntactical changes that would make this much nicer. The idea is that a class definition is composed of some set of members, members being things like properties, methods, indices, and so on. Each member type, excepting methods, can also have methods. The idea for the syntax is to embed the method definition with the member. This was just an idea and is not an active development project. But - something like this is what we envision:

property foo as %String [ sqlcomputed ] {

    method Compute(someParameter as %String) as %String {

        your code goes here, set the return value and return it

    }

}

Just an idea...

Triggers and computed properties (compute triggers) are certainly better than callbacks. Now that we have unified triggers callbacks should be avoided - IMO - in favor of using triggers. However, initial expression when there is some outside influence such as a clock or other value source involved, when the property is not read-only protected, etc. makes using initialexpression a bad idea in some cases. In cases where there is no opportunity for the user to interact with a new instance prior to it being saved then initialexpression is acceptable. If there is opportunity for interaction between instantiation and save (serialization) then initialexpression can be unreliable. The %%INSERT/%%UPDATE compute triggers avoid that. 

I don't understand why it is not recommended. Can you elaborate?

As a follow-up, I should have provided an example!

Class User.TriggerCompute Extends (%Persistent, %Populate)
{

Property foo As %String;

Property CreationDate As %TimeStamp [ SqlComputeCode = { set {*}=$zdt($h,3) }, SqlComputed, SqlComputeOnChange = %%INSERT ];
}

and I populated this class at random intervals. Here are five sample rows:

select top 5 * from triggercompute order by creationdate desc

5 row(s) affected

Very interesting question. Rubens Silva's answer is accurate - %OnAddToSaveSet provides more flexibility when you need to modify the object or the graph of objects to be serialized. 

The callbacks we provide as part of Caché Objects are limited to Objects and are often quite useful. However, Caché Persistent classes also project to SQL as tables. Users can perform CRUD operations using either Objects or SQL. Callbacks are not processed by SQL so any work you do in a callback must either be reproduced in some form in SQL or the user must recognize that accessing persistent data through SQL might produce different results from performing an equivalent access using Caché Objects. 

But we have better choices these days. Caché Objects provides two mechanisms for triggering behavior. SQL recognizes both of these mechanisms. The first is a compute trigger. You can define a property as SQLCOMPUTED. The value of the property can be defined to be computed whenever the value of a property defined in the SQLCOMPUTEONCHANGE list of properties is changed. We support two meta-properties to trigger a compute on insert and update - %%INSERT and %%UPDATE. If SQLCOMPUTEONCHANGE includes %%INSERT then the value of the property is computed when the object is about to be inserted into the database. This computation will override the current value of the property. Similarly, if %%UPDATE is specified in SQLCOMPUTEONCHANGE then the property's value is computed just prior to saving an updated version of the object. 

Compute triggers have been present in Caché for a very long time and %%INSERT/%%UPDATE were available in 2008.1.

The second mechanism is a TRIGGER. TRIGGERs were one the exclusive domain of SQL but we since added a new FOREACH keyword value of ROW/OBJECT. When FOREACH = ROW/OBJECT the trigger will be pulled at the defined time for each filing event using SQL or Objects. FOREACH = ROW indicates that the trigger is invoked only when SQL is used. There is no FOREACH value for invoking a trigger only when Objects is used.

We refer to this feature as "unified triggers". This feature first appears in Caché 2014.1.

-Dan

Very interesting question. The callbacks we provide as part of Caché Objects are limited to Objects and are often quite useful. However, Caché Persistent classes also project to SQL as tables. Users can perform CRUD operations using either Objects or SQL. Callbacks are not processed by SQL so any work you do in a callback must either be reproduced in some form in SQL or the user must recognize that accessing persistent data through SQL might produce different results from performing an equivalent access using Caché Objects. 

Rubens Silva's answer is accurate - %OnAddToSaveSet provides more flexibility when you need to modify the object or the graph of objects to be serialized. 

But we have better choices these days. Caché Objects provides two mechanism for triggering behavior. SQL recognizes both of these mechanisms. The first is a compute trigger. You can define a property as SQLCOMPUTED. The value of the property can be defined to be computed whenever the value of a property defined in the SQLCOMPUTEONCHANGE list of properties is changed. We support two meta-properties to trigger a compute on insert and update - %%INSERT and %%UPDATE.

The second mechanism is a TRIGGER. TRIGGERs were one the exclusive domain of SQL but we since added a new FOREACH keyword value of ROW/OBJECT. When FOREACH = ROW/OBJECT the trigger will be pulled at the defined time for each filing event using SQL or Objects. FOREACH = ROW indicates that the trigger is invoked only when SQL is used. There is no FOREACH value for invoking a trigger only when Objects is used.

We refer to this feature as "unified triggers". This feature first appears in Caché 2014.1.

-Dan