Using SQL Adapters with Ensemble
Using Snapshots
[Home] [Back] 
InterSystems: The power behind what matters   
Class Reference   
Search:    

The EnsLib.SQL.Snapshot class represents a static object that you create and populate in various ways. The class provides methods for examining the data; more methods are available for this object than for the result set.

This chapter describes how to use the EnsLib.SQL.Shapshot class. It discusses the following:
Creating a Snapshot
When you use the SQL inbound adapter, by default, you automatically receive snapshot objects within your business service. For each row in your query, the adapter creates a snapshot object and sends it as an argument when it calls the ProcessInput() method of the business service. As noted previously, by default, this snapshot contains only a single row.
You can create a snapshot and populate it in other ways. Some of these techniques were discussed in previous chapters, but some are new to this chapter.
Creating a Snapshot from a Live Connection
In most cases, you will probably have a live connection to the data source. Specifically, you start with an SQL adapter (either EnsLib.SQL.InboundAdapter or EnsLib.SQL.OutboundAdapter). Within the adapter, connect to a DSN. Then you can do any of the following:
Creating a Snapshot from Static Data
You can also create a snapshot from static data, without having a connection to a DSN. To do so, use any of the following techniques:
The following list provides the details for these methods, all of which are in the EnsLib.SQL.Shapshot class:
CreateFromFile()
classmethod CreateFromFile(pFilename As %String,
   pRowSeparator As %String,
   pColumnSeparator As %String,
   pColumnWidths As %String,
   pLineComment As %String,
   pStripPadChars As %String,
   pColNamesRow As %Integer,
   pFirstRow As %Integer,
   pMaxRowsToGet As %Integer,
   Output pStatus As %Status) as Snapshot 
Creates a new snapshot object and loads it with data from a table-formatted text file. The arguments are as follows:
CreateFromStream()
classmethod CreateFromStream(pIOStream As %IO.I.CharacterStream,
   pRowSeparator As %String,
   pColumnSeparator As %String,
   pColumnWidths As %String,
   pLineComment As %String,
   pStripPadChars As %String,
   pColNamesRow As %Integer,
   pFirstRow As %Integer,
   pMaxRowsToGet As %Integer,
   Output pStatus As %Status) as Snapshot
Creates a new snapshot object and loads it with data from a table-formatted stream. See the comments for CreateFromFile().
CreateFromResultSet
classmethod CreateFromResultSet(pRS As %ResultSet, 
  pLegacyMode As %Integer = 1, 
  pODBCColumnType As %Boolean = 0, 
  pFirstRow As %Integer, 
  pMaxRowsToGet As %Integer, 
  Output pStatus As %Status) as Snapshot
Creates a new snapshot object and loads it with data from a result set. See the comments for CreateFromFile() and for ImportFromResultSet.
ImportFile()
 method ImportFile(pFilename As %String,
   pRowSeparator As %String = $C(10),
   pColumnSeparator As %String = $C(9),
   pColumnWidths As %String = "",
   pLineComment As %String = "",
   pStripPadChars As %String = " "_$C(9),
   pColNamesRow As %Integer = 0) as %Status 
Imports data from a table-formatted text file. See the comments for CreateFromFile().
ImportFromStream()
method ImportFromStream(pIOStream As %IO.I.CharacterStream,
   pRowSeparator As %String = $C(10),
   pColumnSeparator As %String = $C(9),
   pColumnWidths As %String = "",
   pLineComment As %String = "",
   pStripPadChars As %String = " "_$C(9),
   pColNamesRow As %Integer = 0) as %Status 
Here pIOStream is the stream to import. See the comments for CreateFromFile().
ImportFromResultSet()
method ImportFromResultSet(pRS As %ResultSet, 
   pLegacyMode As %Integer = 1, 
   pODBCColumnType As %Boolean = 0) as %Status 
Imports a result set into a snapshot instance. The arguments are as follows:
Example
Consider a file that has the following contents:
col1,col2,col3
value A1,value A2,value A3
value B1,         value B2           ,value B3
The following code reads this file, uses it to create a snapshot, and writes simple comments to the Terminal. Notice that the only arguments used are the filename and the column separator:
 set filename="c:/demo.txt"
 set snap=##class(EnsLib.SQL.Snapshot).%New()
 do snap.ImportFile(filename,,",")
 d show
 quit


show
 w "number of rows in snapshot=",snap.RowCount,!
 while snap.Next()
  {
      w "current row=",snap.%CurrentRow,!
      w "data in first column=",snap.GetData(1),!
      w "data in second column=",snap.GetData(2),!
      w "data in third column=",snap.GetData(3),!
      }
 quit
The output from this routine is as follows:
number of rows in snapshot=3
current row=1
data in first column=col1
data in second column=col2
data in third column=col3
current row=2
data in first column=value A1
data in second column=value A2
data in third column=value A3
current row=3
data in first column=value B1
data in second column=value B2
data in third column=value B3
Notice that line feeds are used by default as row separators. Also notice that by default, leading and trailing spaces are removed from each field.
Creating a Snapshot Manually
You can also create a snapshot manually, as follows:
  1. Create a new instance of a snapshot (via the %New() class method).
  2. Use the SetColNames(), SetColSizes(), and SetColTypes() methods to specify the names, sizes, and types of the columns.
  3. Use the AddRow() method to add a row of data.
The following list provides the details for these methods, all of which are in the EnsLib.SQL.Shapshot class:
AddRow()
method AddRow(pCol...) returns %Status
Adds a row that contains the given data. The argument list is the row data, field by field. For example, the following adds a row to a snapshot. In this case, the column names are ID, Name, and DOB, respectively:
 set sc=snapshot.SetColNames("1023","Smith,Bob","06-06-1986")
SetColNames()
 method SetColNames(pColName...) returns %Status 
Sets the names of the columns, in the order given by the arguments. For example, the following sets the column names as ID, Name, and DOB, respectively:
 set sc=snapshot.SetColNames("ID","Name","DOB")
SetColSizes()
method SetColSizes(pColSize...) returns %Status 
Sets the sizes of the columns (the width in number of characters), in the order given by the arguments.
SetColTypes()
method SetColTypes(pColType...) returns %Status
Sets the types of the columns, in the order given by the arguments.
Note:
Remember that the SQL type names vary between different database vendors. Use the type names that are appropriate for the database with which you are working. The SetColTypes() method does not perform any checking of your type names.
Getting Basic Information about the Snapshot
The following properties of the snapshot provide basic information:
Navigating the Snapshot
A snapshot consists of rows of data. You can use the following methods to navigate through the rows:
Next()
method Next(ByRef pSC As %Status) returns %Integer 
Advances the cursor to the next row. Returns 0 if the cursor is at the end of the snapshot.
Rewind()
method Rewind() returns %Status 
Returns the cursor to the first row of the snapshot.
Examining the Current Row of the Snapshot
Use the following methods to examine the current row of the snapshot:
Get()
method Get(pName As %String, pRow=..%CurrentRow) returns %String 
Returns the value of the column that has the name pName, in the indicated row (by default, the current row).
GetData()
method GetData(pColumn As %Integer, pRow=..%CurrentRow) returns %String 
Returns the value of the column whose position is specified by pColumn in the indicated row (by default, the current row).
GetColumnName()
method GetColumnName(pColumn As %Integer = 0)
Returns the name of the column whose position is specified by pColumn.
GetColumnId()
method GetColumnId(pName As %String) returns %Integer
Returns the ordinal position of the column that has the name pName. This method is useful when you work with unfamiliar tables.
GetColumnSize()
method GetColumnSize(pColumn As %Integer = 0)
Returns the size (the width in number of characters) of the database field whose position is specified by pColumn.
GetColumnType()
method GetColumnType(pColumn As %Integer = 0)
Returns the type of the column whose position is specified by pColumn.
Note:
SQL type names vary between different database vendors.
Resetting a Snapshot
If you have an existing snapshot object, you can clear the data and definitions from it. To do so, use the Clean() method, which returns a status. This is slightly more efficient than destroying the snapshot and creating a new one via %New().