Skip to main content

%SQL.Util.Procedures

abstract class %SQL.Util.Procedures

This class implements several utility methods that can be invoked either as class methods or as an SQL procedure call (SQL invoked routine).

Method Inventory

Methods

classmethod CSV(pSelectMode As %Integer = {$ZU(115, 5)}, ByRef pRowType As %String(MAXLEN="")="", pFileName As %String(MAXLEN=200), pDelimiter As %String = ",", pQuote As %String = """", pTranslateTable As %String = "") [ SQLProc = CSV ]
Projected as the stored procedure: CSV

Parameters
Name Direction Description
pSelectMode Input The select mode that defines the format of the data. Valid values are 0 - logical, 1 - ODBC, and 2 - Display.
pRowType Input-Output A comma-delimited list of comma specs as defined above. It is easier to access the data in the result set if the column names are also valid object identifiers. A row type descriptor can be embedded in the input file. If the pRowType argument is not passed or is passed as null then the row type is expected to be embedded in the input file. If pRowType is passed as a non-null value then any embedded row type value will be ignored.
pFileName Input The name of the file containing the data. This can also be a stream oref. If this argument value is a stream oref then the referenced stream is used as the input data source.
pDelimiter Input The csv delimiter character. The default is comma.
pQuote Input The quote character. The default is a double quote. This is the character used to delimit values that might contain a value delimiter character or other control characters.
pTranslateTable Input The translate table to use for the input file. If not specified then the default translate table will be used. If pFileName is a stream oref then this argument is ignored.

This utility procedure/method instantiates a result set bound to a comma separated value (CSV) data source. The result set is returned as a dynamic result set in the procedure context object. Any records in the CSV source that begin with "--" are considered to be comments and are skipped. The columns from the data source form a result row. The structure of result row is defined as a row type descriptor passed as the pRowType formal parameter. The format of a row type descriptor is a comma delimited list of field definitions where each field definition contains a field name and a datatype. Both the field name and datatype follow the rules used by field definitions in a CREATE TABLE statement. For example:

ROW(StateFips VARCHAR(2),Zip_Code VARCHAR(5),State_Abbr VARCHAR(2),Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6))

For the purposes of the CSV method/procedure, the ROW() is optional.

Row type descriptors do not normally allow missing (empty) field definitions but for the specific purpose of describing the records of a CSV data source empty fields are allowed and indicate a skipped column in the data source. In the example below, the first two columns and the fourth column are ignored:

ROW(,,Zip_Code VARCHAR(5),,Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6))

It is not necessary to add additional commas to indicate columns skipped at the end of the record.

The row type descriptor can be embedded in the input file. Simply add a line at the beginning of the file as a comment containing the row type. For example:

	--ROW(,,Zip_Code VARCHAR(5),,Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6))

It is important that this line be the first line in the input file. Otherwise, it will not be recognized as the row type. The embedded row type descriptor is not used if CSV is passed a row type by the caller.

This utility can be invoked either as a class method or as a stored procedure. The parameters are described in the parameters table above.

The result is returned in the procedure context object. This is normally %sqlcontext if the utility is invoked as a class method call. It is the result object if the procedure is invoked using dynamic SQL. For example:

set rowtype = "StateFips VARCHAR(2),Zip_Code VARCHAR(5),State_Abbr VARCHAR(2),Name VARCHAR(200),Longitude_West Numeric(10,6),Latitude_North Numeric(10,6)"
set filename = "/Users/test/Documents/zip.csv"
set result = ##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.filename)
set resultSet = result.%NextResult()
write resultSet.%Next()
write resultSet.StateFips

classmethod CSVTOCLASS(pSelectMode As %Integer = {$ZU(115, 5)}, ByRef pRowType As %String(MAXLEN="")="", pFileName As %String(MAXLEN=200), pDelimiter As %String = ",", pQuote As %String = """", pHeaderCount As %Integer = 0, pClassName As %String(MAXLEN=400), pTranslateTable As %String = "", pTableName As %String(MAXLEN="")="") [ SQLProc = CSV_TO_CLASS ]
Projected as the stored procedure: CSV_TO_CLASS

Parameters
Name Direction Description
pSelectMode Input The select mode that defines the format of the data. Valid values are 0 - logical, 1 - ODBC, and 2 - Display.
pRowType Input-Output A comma-delimited list of comma specs as defined above. It is easier to access the data in the result set if the column names are also valid object identifiers. A row type descriptor can be embedded in the input file. If the pRowType argument is not passed or is passed as null then the row type is expected to be embedded in the input file. If pRowType is passed as a non-null value then any embedded row type value will be ignored.
pFileName Input The name of the file containing the data. This can also be a stream oref. If this argument value is a stream oref then the referenced stream is used as the input data source.
pDelimiter Input The csv delimiter character. The default is comma.
pQuote Input The quote character. The default is a double quote. This is the character used to delimit values that might contain a value delimiter character or other control characters.
pHeaderCount Input The number of records that are to be skipped at the beginning of the file.
pClassName Input The name of the class where the data will be imported. If this class does not yet exist then it will be generated from the row type. If this class already exists then it is assumed that the class definition matches the row type and that the Import method is implemented.
pTranslateTable Input The translate table to use for the input file. If not specified then the default translate table will be used. If pFileName is a stream oref then this argument is ignored.
pTableName Input The SQLTABLENAME to be defined in the generated class. SQLTABLENAME defaults to a value based on the classname is no value is passed.

Import a CSV document into a persistent class. The row type defines the property names and types. Refer to CSV() for more information on row types and row type embedding. The class will be generated if it does not already exist. If the class does exist then it is expected to implement the Import method with an interface compatible with the Import method generated by the utility. The easiest way to see the Import method interface is to generate a class from a row type.

This utility can be invoked either as a class method or as a stored procedure. The parameters are described in the parameters table above.

FeedbackOpens in a new tab