A Caché of Tips: ResultSets

[Michael Cohen]

Relational database systems store data in tables that organize the data in rows of columns.  Views and ResultSets also return rows of columns.  Historically, database systems have allowed programmers to execute an SQL query or stored procedure and process the returned ResultSet data.  MultiValue users are familiar with the similar process of programatically processing CMQL SELECT lists.  

This MV Tip will discuss Caché's object based approach to this useful functionality.  Two general areas will be addressed:  Generating the ResultSet, and Processing the ResultSet.

A. Example Data

---------------

For this Tip, I will use MV file PERSON with DICT:

LIST DICT PERSON BY F2

Field............ CODE KEY.CODE CONV. FORMAT DISPLAY NAME SM ASSOC.....

Name

 

@ID               D           0       10L    PERSON       S

NAME              D           1       20L                 S

DOB               D           2 D4/   10L                 S

STATE             D           3       2L                  S

 

4 Items listed.

MV:

And with data:

LIST PERSON NAME DOB STATE

PERSON.... NAME................ DOB....... STATE

 

1          John Smith           06/04/1984 MA

2          Steve Jones          02/24/1955 CA

3          Laurene Jones        11/06/1963 CA

 

3 Items listed.

MV:

And with corresponding class MVFILE.PERSON:

Class MVFILE.PERSON Extends (%Persistent, %MV.Adaptor, %XML.Adaptor) [ ClassType = persistent, Inheritance = right, ProcedureBlock, SqlRowIdPrivate ]

{

Parameter MVAUTOLOCK = 0;

Parameter MVCLEARDICT = 0;

Parameter MVCREATE As BOOLEAN = 0;

/// Do not modify MVFILENAME if the file already exists.

Parameter MVFILENAME As STRING = "PERSON";

Parameter MVREPOPULATE = 0;

Property Dob As %MV.Date(MVATTRIBUTE = 2, MVAUTO = "P", MVNAME = "DOB", MVPROJECTED = 0, MVTODISPLAY = "D4/", MVTYPE = "D");

Property ItemId As %String;

Property Name As %String(COLLATION = "Space", MVATTRIBUTE = 1, MVAUTO = "P", MVNAME = "NAME", MVPROJECTED = 0, MVTYPE = "D");

Property State As %String(COLLATION = "Space", MVATTRIBUTE = 3, MVAUTO = "P", MVNAME = "STATE", MVPROJECTED = 0, MVTYPE = "D");

Index indexItemId On ItemId [ IdKey, PrimaryKey ];

}

B. Generating the ResultSet

---------------------------

ResultSets can be generated based on an SQL query, query method (using SQL or custom code), or a stored procedure.  These first examples do a traditional lookup of NAME based on ID.  Note that to provide SQL access to our MV data, we must have a class defined that references it (typically created via PROTOCLASS).

B.1. SQL Query

--------------

Caché's programatic SQL provides two types of query:  Embedded and Dynamic.

Embedded SQL queries generate code at program compile time and use program variables.

For example, COS code,

 set NUM=2

 &sql(SELECT NAME INTO :NM FROM MVFILE.PERSON WHERE ID=:NUM)

 write "Name of PERSON "_NUM_" is: "_NM

will place the Name of PERSON 2 into variable NM, and display the result.

Note that MVBasic does not support &sql, but can call COS methods that do.

Dynamic SQL query code uses standard xDBC Prepare and Execute style syntax with parameters, and returns a ResultSet object that provides access to rows of columns as well as metadata.

This TIP uses Dynamic SQL.  The preferred interface for Dynamic SQL is the %SQL.Statement class. An %SQL.Statement result object for an SQL SELECT statement is a result set object, providing metadata such as %SQLCODE, %ROWCOUNT, %Message and columns properties, and is an instance of %SQL.StatementResult, supporting the expected result set access to the data.  Also available are classes %ResultSet.SQL and %Library.ResultSet.

This MVBasic program returns the same result as the above example:

stmt = "%SQL.Statement"->%New()

status = stmt->%Prepare("SELECT NAME FROM MVFILE.PERSON WHERE ID=?")

num=2

rset = stmt->%Execute(num)

rset->%Next()

CRT "Name of PERSON ":num:" is: ":rset->Name

B.2. Query Method

-----------------

a. The above SQL can be added to the class to avoid repeatedly entering it in code.

Query Q1(ID As %String) As %SQLQuery

{

SELECT Name FROM PERSON WHERE (%ID = :ID)

}

Processing this query is illustrated in C.3. below.

b. When you instruct Studio Query wizard to create a query (Q2 for this example) based on custom code, taking input ID and returning NAME, it creates the following methods, to which I manually added two lines to Q2Fetch.  This illustrates that you can manually code a query to return whatever data you wish (as long as you have access to it).

Query Q2(ID As %String) As %Query(ROWSPEC = "NAME:%String")

{

}

ClassMethod Q2Execute(ByRef qHandle As %Binary, ID As %String) As %Status

{

Quit $$$OK

}

ClassMethod Q2Close(ByRef qHandle As %Binary) As %Status [ PlaceAfter = Q2Execute ]

{

Quit $$$OK

}

ClassMethod Q2Fetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = Q2Execute ]

{

s Row=$lb("bogus")

s AtEnd=1

Quit $$$OK

}

Note that you can change the Language from the default COS to MVBasic:

ClassMethod Q2Fetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ Language = mvbasic, PlaceAfter = Q2Execute ]

{

Row=$lb("bogus")

AtEnd=1

RETURN 1

}

B.3. Stored Procedure

---------------------

SQL and custom class queries can be made visible to xDBC applications as SQL stored procedures simply by flagging them and giving them a name, for example:

Query Q1(ID As %String) As %SQLQuery [ SqlName = SP_Q1, SqlProc ]

{

    SELECT Name FROM PERSON WHERE (%ID = :ID)

}

Once you have specified how to populate an SQL ResultSet, there are a number of ways to access the data and metadata.  Since the ResultSet is an object, you can access its properties and methods.  In addition, there are numerous ODBC and JDBC tools that make it convenient to access SQL ResultSet data.

C. Processing the ResultSet

---------------------------

Now that we can create ResultSets, there are a number of ways to process them.

C.1. Object Access to an SQL ResultSet

--------------------------------------

This was illustrated by the MVBasic example in B.1. above, repeated here:

stmt = "%SQL.Statement"->%New()

status = stmt->%Prepare("SELECT NAME FROM MVFILE.PERSON WHERE ID=?")

num=2

rset = stmt->%Execute(num)

rset->%Next()

CRT "Name of PERSON ":num:" is: ":rset->Name

The ResultSet object offers a method %Next that puts the next row of selected data into the object's properties.

C.2. ODBC Access to a ResultSet

-------------------------------

a. Using our Management Portal, under SQL -> Browse SQL Schemas, for each Schema we generate a list of defined Stored Procedures, and provide a Run screen that prompts for parameters and displays the results.

Running the SP_Q1 we defined above prompts for ID.  If I supply 2 and click on Run Query, the result displayed is column "Name" with value "Steve Jones".

Various third-party tools (such as WinSQL and JaySql) provide similar access to the data, as does our native java interface.

C.3. Object access to a ResultSet Created by an SQL Class Query

---------------------------------------------------------------

This MVBasic program displays the result of Q1 above for ID=2.

rset = "%ResultSet"->%New()

rset->ClassName = "MVFILE.PERSON"

rset->QueryName = "Q1"

num=2

status = rset->%Execute(num)

rset->Next(status)

CRT "Name of PERSON ":num:" is: ":rset->Name

This is the result from TCL.

MV:TSTQ1

Name of PERSON 2 is: Steve Jones

MV:

C.4. Object access to a ResultSet Created by a Custom Class Query

-----------------------------------------------------------------

Same code as above (Q1 changed to Q2)

rset = "%ResultSet"->%New()

rset->ClassName = "MVFILE.PERSON"

rset->QueryName = "Q2"

num=2

status = rset->%Execute(num)

rset->Next(status)

CRT "Name of PERSON ":num:" is: ":rset->Name

MV:TSTQ2

Name of PERSON 2 is: bogus

MV:

 

----------------------------------------------------------

Up to this point, my examples only returned a single row with a single column.  As you might guess from the name, a ResultSet can return multiple rows and columns.  The following MVBasic program FINAL displays the ID and NAME for all Persons from CA.  It illustrates that metadata is available, rows are returned by %Next, and column data can be accessed by column number in the query (also available by column name).

DIM names(100)

stmt = "%SQL.Statement"->%New()

status = stmt->%Prepare("SELECT ID, NAME FROM MVFILE.PERSON WHERE STATE=?")

state="CA"

ncols=stmt->%Metadata->columns->Count()

* collect column names from metadata

FOR I = 1 TO ncols

    column=stmt->%Metadata->columns->GetAt(I)

    names(I)=column->colName

NEXT I

rset = stmt->%Execute(state)

rset->%Next(status)

IF status<>1 THEN STOP "%Execute failed: ":status

IF rset->%SQLCODE=100 THEN STOP "NO RECORDS FOUND"

LOOP WHILE rset->%SQLCODE=0

   FOR I = 1 TO ncols

      CRT names(I):": ":rset->%GetData(I)

   NEXT I

   rset->%Next(status)

   CRT

REPEAT

CRT rset->%ROWCOUNT:" Items listed."

Here is the sample data via TCL and the result from the FINAL program.

MV:LIST PERSON NAME DOB STATE

LIST PERSON NAME DOB STATE

PERSON.... NAME................ DOB....... STATE

 

1          John Smith           06/04/1984 MA

2          Steve Jones          02/24/1955 CA

3          Laurene Jones        11/06/1963 CA

 

3 Items listed.

MV:

MV:

MV:FINAL

ID: 2

Name: Steve Jones

 

ID: 3

Name: Laurene Jones

 

2 Items listed.

MV:

For simplicity, these examples included only a minimum of the normal error checking.

There is much doc available on Dynamic SQL, including:

Development Guides -> Using Caché SQL -> Using Dynamic SQL

The is an example of JDBC at:

Caché Tutorials -> MultiValue Quick Start Tutorial -> Using Java -> JDBC Query

There is additional doc available on the classes mentioned.

Advanced topics, such as Scrollable or Updateable ResultSets, or processing Procedures that that return multiple ResultSets are covered in the documentation.

[Dawn Wolthuis]

Good stuff, Michael. We have been using

sqlrs = "%ResultSet"->%New()

instead of %SQL.Statement. Should we be migrating our code to this,
are these similar, or is there some specific reason you used
%SQL.Statement and I used %Library.ResultSet? Thanks. --dawn

> --
> You received this message because you are subscribed to the Google Groups
> "InterSystems: MV Community" group.
> To post to this group, send email to Cac...@googlegroups.com
> To unsubscribe from this group, send email to
> CacheMV-u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/CacheMV?hl=en



--
Dawn M. Wolthuis

Take and give some delight today

[Emily Haggstrom]

Hi Dawn,

%SQL.Statement is the new (and preferred) Dynamic SQL API. At the moment, there's not much of a difference between that and %ResultSet, but going forward changes and updates will be mainly handled in %SQL.Statement.

So, to answer your question, new development should generally be done with %SQL.Statement, but there's no great need to update your existing code to use it.

Cheers,
Emily

[José Roberto Pereira Jr.]

Hi  Emily,

Some past posts in this list claimed that %SQL.Statement are faster than %ResultSet. Are you think that don't worth update previous code using %ResultSet?

Thanks,

José

José Roberto

[Emily Haggstrom]

Hi José,

 

%SQL.Statement is better equipped to handle certain situations that %ResultSet is, which means it is faster under certain circumstances. If you are having a problem with some of your queries using %ResultSet, by all means give %SQL.Statement a try. I just wanted to assure you that existing code using %ResultSet will continue to perform as it has in the past.

 

Cheers,

Emily

 

---

[Michael Cohen]

Emily is right (if it is not broken, no need to fix it)

 

A couple more points…

due to improvements in such things as SQL and routine management, things tend to get faster after Caché upgrades, even your old code that references %ResultSet

 

most of the cost of reporting is the SQL, reading it off the disk, and formatting; moving intermediate bytes around is minor; but it is true that accessing data columns as Properties (like: rset->colName) would be slightly faster than via a Method (like: rset->%Get(“colName”); for more info, see %SQL.IResultSet