New post
Article
· Jul 18, 2017 2m read

Old/New Dynamic SQL Cheat Sheet

#Best Practices #Code Snippet #SQL #Caché

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet:

1
%ResultSet::%New()
%SQL.Statement::%New()

2

   Call the Prepare() instance method

   Call the %Prepare() instance method

3

   Previous step returns status; check that

   Previous step returns status; check that

4

   Call the Execute() instance method

   Call the %Execute() instance method

5

   Previous step returns status; check that

   Previous step returns instance of %SQL.StatementResult; use that in next steps

6

   Call the Next() instance method (e.g., iteratively, in while loop)

   Call the %Next() instance method (e.g., iteratively, in while loop)

7

   Call the GetData() instance method to get a column by column number

   Call the %GetData() instance method to get a column by column number

   Call the Get() or Data() instance method to get a column by column name

   Call the %Get() instance method to get a column by column name

 

Then here is the terser cheat sheet that I actually use:

1
%ResultSet::%New()
%SQL.Statement::%New()

2

   Prepare()

   %Prepare()

3

   Check status

   Check status

4

   Execute()

   %Execute()

5

   Check status

   Use value returned by %Execute in next steps

6

   Next()

   %Next()

7

   GetData()

   %GetData()

   Get() or Data()

   %Get()

Discussion (35)4
Log in or sign up to continue
Mark Hanson · Jul 18, 2017

A few comments:

  • %ResultSet does have %Execute, %Next, %GetData, %Get, %Prepare methods also
  • In %ResultSet use 'Data' multidimentional property to get columns by name as this is more efficient than the 'Get' method.
  • In %SQL.StatementResult, do not use %Get to get columns by name, instead just reference the properties directly, e.g. 'Write resultOref.Name' instead of 'Write resultOref.%Get("Name")'
1 0
Warlin Garcia · Jul 19, 2017

The only "caution" here is that referencing the property by name only works with properties where the SqlFieldName corresponds to a valid Cache property name:

  1. Property myproperty As %String;
  2. Property myproperty As %String [SqlFieldName = "mysqlname"];
  3. Property myproperty As %String [SqlFieldName="my_property"];

You can access #1 & #2 by property name, however #3 you can only access by using %Get("my_property");

0 0
Rubens Silva · Jul 19, 2017

Not exactly, this following syntax is weird, but works:

try typing instance."my_property", you can even create properties and methods with "".
Extremely useful if you are working with snake_case.

Here's the proof:

USER>zn "samples"
 
SAMPLES>set r = ##class(%SQL.Statement).%ExecDirect(,"SELECT TOP 1 ID AS ""my_id"" FROM SAMPLE.PERSON")
 
SAMPLES>w r.%Next()
1
SAMPLES>w r."my_id"
1

4 0
Dan Pasco · Aug 1, 2017

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.

1 0
Amir Samary · Aug 2, 2017

I understand the power of %SQL.Statement but as most of my queries are simple I continue using %ResultSet since the error treatment with %Status is more consistent.

It is bad enough that we have to deal with a mix of %Status and Exception handling. I don't like to have to check for %SQLCODE being negative after %Execute() and, if it is, having to transform it to a %Status to keep error handling consistent.

%ResultSet's Execute() method will return me a %Status while %SQL.Statement interface makes me have to deal with yet another type of error (%SQLCODE) making error handling code yet uglier... 

I like consistency, so I continue using %ResultSet. But when I need more functionality or more speed, I use %SQL.Statement instead.

Respectfully,

AS

2 0
Amir Samary · Aug 4, 2017

Hi Dan!

I have been using %ResultSet forever and my coding style is as follows:

/// Always return a %Status
ClassMethod SomeMethod() As %Status
{
     Set tSC = $System.Status.OK()
     Try
     {
          Set oRS = ##class(%ResultSet).%New()
          Set tSC = oRS.Prepare("Select ......")
          Quit:$System.Status.IsError(tSC)        

          Set tSC = oRS.Execute()
          Quit:$System.Status.IsError(tSC)

          While oRS.Next()
          {
              //Do something...
          }     
     }
     Catch (oException)
     {
          Set tSC = oException.AsStatus()
     }
     Quit tSC
}

As you can see, it is painful enough to have to deal with both Try/Catch and %Status ways of handling errors. I have used Try/Catch in the same way I used to use $ZT back in the days. We must protect the code from unpredictable errors such as <FILEFULL>, <STORE>, etc. On the other hand, most of our API return %Status. So, there is no choice but to use a similar structure for handling both ways of reporting errors.

With the new %SQL.Statement interface I am required to check yet another way of reporting errors (SQLCODE) and translate those errors to either %Status or an Exception. That makes my code look ugly and no so much object oriented as I would like. You see, when I am doing demos and coding in front of people I tend to code the same way I code when I am building something for real and vice-versa. Caché/Ensemble is really a formidable technology and one can build things with our technology that would take anyone else months on other technologies. But the first impression is key and when I am doing demos I want to show beautiful code that is easy to read and understand. That is why I keep using %ResultSet. It's true %Prepare() will return a %Status but %Execute won't and I would have to inspect %SQL.StatementResult for it's SQLCODE and transform it into a %Status/Exception.

I opened a prodlog for this some time ago (118943), requesting an enhancement for this class to support a %Status property as well as a SQLCODE. 

Kind regards,

AS

2 0
Amir Samary · Aug 5, 2017

I started using $System.Status.* methods about 10 years ago when I wanted to demo how we could take code from Visual Basic 6, VBA or ASP and copy most of its logic into a Caché class method and use Language = basic.

If you need to call one of our API methods from this VBScript code you would probably receive a %Status. As VBScript doesn't use the $$$ macros, the only way to parse the error was by using $System.Status methods. I believe supporting other languages as VBScript was one of the reasons we put this code in there... But I may be wrong.

So, for consistency, I started using only $System.Status methods everywhere. I could write some code in COS that would parse an error with $System.Status.IsError() and I could rewrite the same method in VBScript using the same $System.Status methods without having to explain to people why, on the same product, we would make you deal with errors in different ways. We couldn't avoid "On Error" x "Try/Catch" though.

This also helps people notice $System and %SYSTEM package of classes and see what else is in there. Very useful.

I understand using macros will result in faster code. I also believe our compile could optimize $System.Status.IsError() and $System.Status.OK() method calls to produce the same byte code as the macros. We probably don't do this, but as a Sales Engineer, that is trying to show people how simple and powerful our technology can be, I prefer consistency and clarity over speed. I would also prefer consistency and clarity over some additional speed in any professional code that must be maintained by someone else in the future... 

I have strong feelings about &SQL() too. I would avoid it at all costs whilst I know that it will be the faster way to run a query in Caché. I prefer using %SQL.Statement or %ResultSet because I hate to make my code uglier just to accommodate SQLCODE error handling. Beside this, &SQL can't be used on other supported languages such as VBScript (that is not important anymore) and will force you to compile your classes if you decide to add a new index to your class or make more dramatic changes such as changing your class storage definition. You can change your storage definition, add indices, etc. without having to recompile your classes when using %SQL.Statement or %ResultSet because those cached routines will be automatically deleted for you... That is what most people would expect. I like when things look clear, simple and natural... So I also avoid using &SQL.

Finally, people tend to not even check for errors . If you make things complex, most people will produce bad code and blame you for having a complex programming language. Consistency makes people safer.

Kind regards,

AS

1 0
Dan Pasco · Aug 7, 2017

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

1 0
Dan Pasco · Aug 7, 2017

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

1 0
Evgeny Shvarov · Aug 7, 2017

Thanks, Dan! Cool stuff!

Never knew about .prepare() and .execute() and my +1 vote for .next() to introduce.

One more general question: is it a good practice to embrace everything in a method in try/catch?

Consider I have some method which interacts with UI and it has try/catch in it and it catches everything it calls. Let's name it UpperUIMethod. But should I introduce try/catch in every methods I'm calling from this UpperUIMethod? 

Sometimes I want to avoid it to give more sense to the logic itself.

1 0
Dan Pasco · Aug 7, 2017

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)
4 0
Amir Samary · Aug 7, 2017

Hi Dan!

I don't really like macros. :) But I love exceptions. It would be awesome if %SQL.Statement simply threw the Exception when an error occurs instead of returning a SQLCODE that must be checked and transformed to either an exception or a %Status... In this way, we could keep the number of ways we deal with errors reduced to two, instead of three.

Your explanation is indeed very compelling and I will start using %SQL.Statement from now on. I was thinking about building a macro named $$$THROWONSQLERROR(result) that will receive the resultset returned by %Prepare and check it's SQLCODE and, if there is an error, throw it using result.%SQLCODE and %Message just like CreateFromSQLCODE does. This would allow me to hide SQLCODE.

Kind regards,

AS

0 0
Dan Pasco · Aug 7, 2017

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.

2 0