%SQL.Util.Procedures

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


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.