Using Snapshots (SQL Adapters)
The EnsLib.SQL.SnapshotOpens in a new tab class represents a static object that you create and populate in various ways. This object is meant for use by an SQL adapter in a production. The class provides methods for examining the data; more methods are available for this object than for the result set. This topic describes how to use the EnsLib.SQL.SnapshotOpens in a new tab class.
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.
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.InboundAdapterOpens in a new tab or EnsLib.SQL.OutboundAdapterOpens in a new tab). Within the adapter, connect to a DSN. Then you can do any of the following:
-
Use the ExecuteProcedure() or ExecuteProcedureParmArray() method of the adapter. Each of these returns the results as a snapshot.
These methods are discussed in Using the Result Sets.
-
Create a result set (see Using the Result Sets) and then use the GetSnapshot() method of the result set. This method has the following signature.
method GetSnapshot(ByRef pSnap As EnsLib.SQL.Snapshot, pFetchAll As %Boolean = 0) returns %Status
Returns a snapshot object by reference in the first argument. If you pass an existing snapshot object to the method, the method uses the FirstRow and MaxRowsToGet properties of that object to determine which rows to place in the snapshot. Otherwise, the method uses the default values.
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:
-
Use the CreateFromFile(), CreateFromStream(), or CreateFromResultSet class method.
-
Create a new instance of a snapshot (via the %New() class method), and then use the ImportFile(), ImportFromStream(), or ImportFromResultSet() method.
The following list provides the details for these methods, all of which are in the EnsLib.SQL.Shapshot class:
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:
-
pFilename specifies the name of the file to import. This is the only required argument.
-
pRowSeparator is one of the following:
-
The character that separates one row from the next row. The default is a line feed character.
-
A number, preceded by a minus sign, that indicates the line length in characters.
-
-
pColumnSeparator is one of the following:
-
The character that separates one column from the next column. There is no default character.
-
The number 0, which means that the columns are determined by the pColumnWidths argument; see the next argument.
-
A number, preceded by a minus sign, that indicates the number of initial characters to skip in each row. In this case, the columns are determined by the pColumnWidths argument; see the next argument.
-
-
pColumnWidths is one of the following:
-
A comma-separated list of column widths (number of characters), if the fields in the file are positional.
-
The number of columns, if the file uses column separators.
-
-
pLineComment specifies a string after which the rest of a row should be ignored. Within a given row, after this string is found, the snapshot does not parse the rest of the row into columns.
-
pStripPadChars means characters to strip from the beginning and end of a field. The default is the space character.
-
pColNamesRow specifies the index of the row that contains column names, if any.
-
pFirstRow specifies the index of the first row (from the file) to include in the snapshot.
-
pMaxRowsToGet specifies the maximum number of rows to include in the snapshot.
-
pStatus is the status that the method returns when it attempts to create the snapshot.
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().
classmethod CreateFromResultSet(pRS,
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.
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().
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().
method ImportFromResultSet(pRS,
pLegacyMode As %Integer = 1,
pODBCColumnType As %Boolean = 0) as %Status
Imports a result set into a snapshot instance. The arguments are as follows:
-
pRS is an instance of EnsLib.SQL.GatewayResultSetOpens in a new tab, or a result set in the %SQL package such as %SQL.StatementResultOpens in a new tab or %SQL.ISelectResult (%SQL.IResult).
-
pLegacyMode specifies how to search for meta data. If this argument is 0, then InterSystems IRIS® first tries to use %GetMetadata. This leads to different source of metadata for legacy result set classes. The default is 1, which maintains previous behavior while still supporting %SQL.* and older classes.
-
pODBCColumnType controls how the ColumnType is set. If pODBCColumnType is 1, then ColumnType text is set to the ODBC type column type text and not the clientType.
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:
-
Create a new instance of a snapshot (via the %New() class method).
-
Use the SetColNames(), SetColSizes(), and SetColTypes() methods to specify the names, sizes, and types of the columns.
-
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:
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")
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")
method SetColSizes(pColSize...) returns %Status
Sets the sizes of the columns (the width in number of characters), in the order given by the arguments.
method SetColTypes(pColType...) returns %Status
Sets the types of the columns, in the order given by the arguments.
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:
-
The %CurrentRow property is an integer that indicates the current row.
-
The AtEnd property is true if the current row is the last row; otherwise it is false.
-
The ColCount properties indicates the number of columns in the snapshot.
-
The RowCount properties indicates the number of columns in the snapshot. This property counts only the rows that do not start with the comment string, if any. To create a snapshot that includes comments, use the CreateFromFile() and related methods, and specify a value for the pLineComment argument. A row is counted if it begins without the comment string but includes the comment string in a later position.
Examining the Current Row of the Snapshot
Use the following methods to examine the current row of the snapshot:
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).
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).
method GetColumnName(pColumn As %Integer = 0)
Returns the name of the column whose position is specified by pColumn.
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.
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.
method GetColumnType(pColumn As %Integer = 0)
Returns the type of the column whose position is specified by pColumn.
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().