SQL

Encapsulation

Embedded SQL is not well encapsulated. The following variables are created or used by embedded SQL and may overwrite your own variables of the same name:

• SQLCODE - indicates the return code for an SQL statement
• %ROWCOUNT - indicates the number of rows returned by a query
• %ROWID - id of row returned
• %MSG - user define message in trigger code
• %OK - trigger code abort flag
• %<cursorName>0* - a whole host of variables for each named cursor (undocumented)
• %msg - sometimes created, sometimes killed (undocumented)
• %objlasterror - status string containing last object error if there has been one (undocumented)
• %0CacheRowLimit - seems to control the number of rows returned by a query (undocumented)

See also: 2007.1 Documentation about variables used by embedded SQL.

Comparison of %ResultSet and Embedded SQL

Commentary by Mark Sires about the difference between using %ResultSet and embedded SQL:

I don't profess to fully understand what goes on under the covers, but I have generally found %ResultSet to give better (more reliable) results than &sql these days. In your particular situation, I think the reason is that &sql with a cursor is making a call over the ODBC driver each time you do a fetch, and the cursor based fetch wasn't working over ODBC. Using %ResultSet with a dynamic query in this case makes a single call over ODBC and retrieves all 100 rows.

There may be cases where %ResultSet doesn't retrieve the entire query in a single call, but that seems to be its 'preferred' behavior, so in cases like yours, where the return set is small, and the cost of each call is high, it works better.

&SQL with a cursor seems to 'prefer' to walk through the table directly, rather than pulling the matching row id's into a temporary storage area. In some cases this is good, in other cases, not so much. If it hasn't chosen a good index to use in traversing the table (do not use -> in your where clause, it will always choose poorly), each 'Fetch' can be slow. If the data changes while it is 'walking', it can cause unexpected behavior. And if you use another &SQL statement to update the same table while in the cursor loop, very unexpected behavior can sometimes occur. On a much older version, I tracked that problem down to the reuse of some variable names in the generated code if the same table was used in 2 &SQL - they may have fixed that in subsequent releases. I changed my techniques to always use the cursor to retrieve the rows to look/modify, and then do any updates after closing the cursor, so I haven't worried about whether it has been changed or not. This is also why %ResultSet frequently works better for me, the generated code basically does what I was doing to retrieve the matching rows into a temporary storage area.

Another technique I use to avoid some of the runtime penalties (which really aren't much) of %Resultset is to put frequently used Static (or simple variable substitution) queries in a class, then use that query instead of %DynamicQuery. Using your query as an example, this could be the query class:

Class MyStuff.queries [ Abstract ]
{ 
Query GPSData(StartValue As %BigInt = 0, StopValue As %BigInt = 100) As %SQLQuery(CONTAINID = 1)
 {
  select ID, Location from SatNav.GpsAddress where ID>:StartValue and ID<:StopValue
 }
}

Then to use it :

Set query=##class(%ResultSet).%New("MyStuff.queries")
s ok=query.Execute(0,100)

The rest is pretty much the same.

If you are using the same query over and over again, this is more efficient because the query is 'precompiled', and the code lives in the routines generated by the class compilation, not in every routine it is used in. Also, if you need to tweak the query, you have one place to fix it. Of course, the downside, if you tweak it, you can break all the places that use it.

This commentary was originally posted here.

Projection of Lists as Sub-tables

Projecting a list as sub-table has always been possible but was a bit tricky, with recent versions (at least from 5.2, maybe before) it's easier:

Property MyList As list Of %String(STORAGEDEFAULT = "array");

One caveat: if you modify a class where storage has already been generated for the list property, adding the parameter does not recreate the storage definition where this behavior is implemented.

This commentary was originally posted here.

DISPLAYLIST/VALUELIST

Use %External to display the Display List value instead of the Value List value in your SQL.

SELECT %External(FIELDNAME) FROM SAMPLES

SQL Shell/SQL from terminal

You can execute SQL statements from the terminal. This is useful when using a GUI SQL tool, because no database connection or BDE need be set up in windows.

D $System.SQL.Shell
>> SELECT * FROM MyTable
(1)>> go