Home > Class Reference > %SYS namespace > %Library.SQLGatewayConnection

%Library.SQLGatewayConnection

class %Library.SQLGatewayConnection extends %Library.RegisteredObject

The SQLGatewayConnection class provides low level access to the SQLGateway The user should understand how to make ODBC calls in order to use this class Most of the calls which return %Status also set the ..sqlcode property. The user should check ..sqlcode if return status is not $$$OK. The user can use a %SQLGatewayConnection class as follows:
  #include %occInclude
  	//Create new Gateway connection object
  	set gc=##class(%SQLGatewayConnection).%New()
  	If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
  		  
  	//Make connection to target DSN
  	s pDSN="Samples"
  	s usr="_system"
  	s pwd="SYS"
  	set sc=gc.Connect(pDSN,usr,pwd,0) 
  	If $$$ISERR(sc) quit sc
  	if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")
  		  
  	set sc=gc.AllocateStatement(.hstmt) 
  	if $$$ISERR(sc) quit sc
  		  
  	//Prepare statement for execution
  	set pQuery= "select * from Sample.Person"
  	set sc=gc.Prepare(hstmt,pQuery) 
  	if $$$ISERR(sc) quit sc
  	//Execute statement
  	set sc=gc.Execute(hstmt)
  	if $$$ISERR(sc) quit sc
  	//Get list of columns returned by query
  	set sc=gc.DescribeColumns(hstmt, .columnlist) 
  	if $$$ISERR(sc) quit sc
  	 
  	//display column headers delimited by ":"
  	set numcols=$listlength(columnlist)-1  //get number of columns
  	for colnum=2:1:numcols+1 {
  		    Write $listget($listget(columnlist,colnum),1),":"
  	  		}
  	write !
  	 
  	//Return first 200 rows	  
  	set sc=gc.Fetch(hstmt)
  	if $$$ISERR(sc) quit sc
  	s rownum=1
  	while((gc.sqlcode'=100) && (rownum<=200)) {
  	      	for ii=1:1:numcols {
  		      	s sc=gc.GetData(hstmt, ii, 1, .val)
  		      	w " "_val
  		      	if $$$ISERR(sc) break
  	      	}
  	      	s rownum=rownum+1
  	 		write !
  	 		set sc=gc.Fetch(hstmt)
  			if $$$ISERR(sc) break
  
  	  		}
  	    
  	  //Close cursor and then disconnect
  	set sc=gc.CloseCursor(hstmt)
  	if $$$ISERR(sc) quit sc
  	 // Disconnect() should always be called when done with the connection.  
  	 // On some platforms, not calling Disconnect() can lead to a hang when the process halts
  	set sc=gc.Disconnect()
  	  
  	Quit sc
  

Property Inventory

Method Inventory

Properties

property ConnectionHandle as %Binary;
Property methods: ConnectionHandleGet(), ConnectionHandleIsValid(), ConnectionHandleSet()
property ConnectionName as %String;
Property methods: ConnectionNameDisplayToLogical(), ConnectionNameGet(), ConnectionNameIsValid(), ConnectionNameLogicalToDisplay(), ConnectionNameLogicalToOdbc(), ConnectionNameNormalize(), ConnectionNameSet()
property DLLHandle as %Binary;
Property methods: DLLHandleGet(), DLLHandleIsValid(), DLLHandleSet()
property DLLName as %String;
Property methods: DLLNameDisplayToLogical(), DLLNameGet(), DLLNameIsValid(), DLLNameLogicalToDisplay(), DLLNameLogicalToOdbc(), DLLNameNormalize(), DLLNameSet()
property DSN as %String;
Property methods: DSNDisplayToLogical(), DSNGet(), DSNIsValid(), DSNLogicalToDisplay(), DSNLogicalToOdbc(), DSNNormalize(), DSNSet()
property GatewayStatus as %Integer [ Calculated ];
Property methods: GatewayStatusDisplayToLogical(), GatewayStatusGet(), GatewayStatusIsValid(), GatewayStatusLogicalToDisplay(), GatewayStatusNormalize()
property LegacyMode as %Integer;
Property methods: LegacyModeDisplayToLogical(), LegacyModeGet(), LegacyModeIsValid(), LegacyModeLogicalToDisplay(), LegacyModeNormalize(), LegacyModeSet()
property Password as %String;
Property methods: PasswordDisplayToLogical(), PasswordGet(), PasswordIsValid(), PasswordLogicalToDisplay(), PasswordLogicalToOdbc(), PasswordNormalize(), PasswordSet()
property User as %String;
Property methods: UserDisplayToLogical(), UserGet(), UserIsValid(), UserLogicalToDisplay(), UserLogicalToOdbc(), UserNormalize(), UserSet()
property sqlcode as %Integer;
Property methods: sqlcodeDisplayToLogical(), sqlcodeGet(), sqlcodeIsValid(), sqlcodeLogicalToDisplay(), sqlcodeNormalize(), sqlcodeSet()

Methods

method AllocateStatement(ByRef hstmt) as %Status [ Language = objectscript ]
Method AllocateStatement(hstmt) invokes SQLAllocHandle and creates the corresponding structures in the SQL Gateway.
method BindParameter(hstmt, ParameterNumber As %Integer, IOType As %Integer, ValueType As %Integer, ParameterType As %Integer, ColumnSize As %Integer, DecimalDigits As %Integer, StrLen As %Integer) [ Language = objectscript ]
BindParameter is calling SQLBindParameter with the corresponding values, the buffer length is guaranteed to be not less then StrLen.
method BindParameters(hstmt, ptype As %List, dtype As %List, precision As %List, scale As %List, cd As %List) as %Status [ Language = objectscript ]
BindParameters is used for binding all the parameters for a given statement handle.
Arguments :
  • hstmt - Statement handle
  • ptype - input/ouput types of the parameters in the $list format
  • dtype - SQL datatypes of parameters in the $list format
  • precision - $list of the correponding precision values - used for reseving buffers for parameter values
  • scale - $list of the correponding scale values
  • cd - $list of column sizes

They are used in the following places of the SQLBindParameter function

SQLRETURN SQLBindParameter(
    SQLHSTMT         StatementHandle,   // hstmt
    SQLUSMALLINT     ParameterNumber,
    SQLSMALLINT      InputOutputType,   // $list element from ptype
    SQLSMALLINT      ValueType,
    SQLSMALLINT      ParameterType,     // $list element from dtype
    SQLUINTEGER      ColumnSize,        // $list element from cd
    SQLSMALLINT      DecimalDigits,     // $list element from scale
    SQLPOINTER       ParameterValuePtr ,
    SQLINTEGER       BufferLength,
    SQLINTEGER *     StrLen_or_IndPtr);
method CloseCursor(hstmt) as %Status [ Language = objectscript ]
Method CloseCursor(hstmt) invokes SQLCloseCursor .
method ColumnPrivileges(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ColumnName As %String) as %Status [ Language = objectscript ]
Method ColumnPrivileges invokes SQLColumnPrivileges. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method ColumnPrivilegesW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ColumnName As %String) as %Status [ Language = objectscript ]
The wide version of Columns. See documentation for ColumnPrivilegesW
method Columns(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ColumnName As %String) as %Status [ Language = objectscript ]
Method Columns invokes SQLColumns. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method ColumnsW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ColumnName As %String) as %Status [ Language = objectscript ]
The wide version of Columns. See documentation for Columns
method Connect(dsn, usr, pwd, timeout) as %Status [ Language = objectscript ]
The method Connect is used for establishing connection to a DSN. If username and password are both empty it calls SQLDriverConnect. If that call is unsuccessful or username/password are specified it calls SQLConnect. SQLSetConnectAttr is called before connection to set SQL_ATTR_LOGIN_TIMEOUT if the timeout parameter is not 0.
method DescribeColumns(hstmt, Output clist As %List) as %Status [ Language = objectscript ]
Method DescribeColumns returns a list with column information in a second argument. It calls SQLDescribeCols and SQLColAttribute. Each list element of the returned list is a list itself. The first list element contains the header in the $list format containing 8 elements:
  1. the number of columns in the given statement
  2. the string "SqlType"
  3. the string "Precision"
  4. the string "Scale"
  5. the string "Nullable"
  6. the string "Datatype"
  7. the string "DisplaySize"
  8. the string "IsMoney?"

Next elements correspond to the statement column and also contains 8 elements:
  1. column name
  2. SQL type of the column
  3. column size
  4. decimal digits
  5. nullable
  6. column datatype
  7. column display size
  8. SQL_TRUE or SQL_FALSE depending whether this is a currency type
method DescribeParameters(hstmt, Output plist As %List) as %Status [ Language = objectscript ]
Method DescribeParameters returns a list with parameter information in a second argument. It calls SQLDescribeParam and SQLNumParams. Each list element of the returned list is a list itself. The first list element contains the header in the $list format containing 5 elements:
  1. the number of parameters in the given statement
  2. the string "SqlType"
  3. the string "Precision"
  4. the string "Scale"
  5. the string "Nullable"

The next elements correspond to the statement parameter and contains 4 elements:
  1. the SQL type of the parameter
  2. the parameter size
  3. the scale
  4. whether the value is nullable
method DescribeProcedureColumns(hstmt, CatalogName As %String, SchemaName As %String, ProcName As %String, ColumnName As %String) as %Status [ Language = objectscript ]
Method DescribeProcedureColumns invokes SQLProcedureColumns. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method DescribeProcedureColumnsW(hstmt, CatalogName As %String, SchemaName As %String, ProcName As %String, ColumnName As %String) as %Status [ Language = objectscript ]
Method DescribeProcedureColumns invokes SQLProcedureColumns. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method DescribeProcedures(hstmt, CatalogName As %String, SchemaName As %String, ProcName As %String) as %Status [ Language = objectscript ]
Method DescribeProcedures invokes SQLProcedures. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method DescribeProceduresW(hstmt, CatalogName As %String, SchemaName As %String, ProcName As %String) as %Status [ Language = objectscript ]
Method DescribeProcedures invokes SQLProcedures. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method Disconnect() as %Status [ Language = objectscript ]
Method Disconnect closes the connection.
Disconnect() should always be called when done with the connection. On some platforms, not calling Disconnect() can lead to a hang when the process halts
method DropStatement(hstmt) as %Status [ Language = objectscript ]
Method DropStatement(hstmt) invokes SQLFreeHandle with SQL_HANDLE_STMT.
method Execute(hstmt) as %Status [ Language = objectscript ]
Method Execute(hstmt) invokes SQLExecute for a given handle.
method Fetch(hstmt) as %Status [ Language = objectscript ]
Method Fetch(hstmt) invokes SQLFetch. It returns an error for all non zero SQL codes. The user should check the ..sqlcode property.
method FetchRows(hstmt, Output rlist As %List, nrows As %Integer) as %Status [ Language = objectscript ]
Method FetchRows retrieves nrows of rows for the given hstmt (it should be a statement which returns a result set) It returns rlist - a list of row.s If there is no data (SQL_CODE = 100) fetching is assumed to be successful This call truncates character fields up to 120 characters so that more fields would fit in a row. It is advised to use GetData call when non truncated data is needed.
method ForeignKeys(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ForeignCatalogName As %String, ForeignSchemaName As %String, ForeignTableName As %String) as %Status [ Language = objectscript ]
Method ForeignKeys invokes SQLForeignKeys. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method ForeignKeysW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ForeignCatalogName As %String, ForeignSchemaName As %String, ForeignTableName As %String) as %Status [ Language = objectscript ]
Method ForeignKeysW invokes SQLForeignKeysW. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method GatewayStatusGet() as %Integer [ Language = objectscript ]
method GatewayStatusGet returns an error code for the last call. It does not initialize the error code and can be called multiple times. The possible return values are:
  • 0 - success
  • -1 - SQL error
  • -1000 - critical error
method GetConnection(conn, timeout) as %Status [ Language = objectscript ]
Method GetConnection is used for establishing connection. It uses an entry from InterSystems IRIS configuration for determening the DSN, user name, and password.
method GetData(hstmt, icol, type, ByRef val) as %Status [ Language = objectscript ]
Method GetData calls SQLGetData> for the column icol and returns data in the last argument.. The supported types are 1 (SQL_CHAR),-2 (SQL_BINARY) and -8 (SQL_WCHAR). This method may be called repeatedly for the same column in the case when the data length exceeds 31K.
method GetDataL(hstmt, icol, type, length, ByRef val) as %Status [ Language = objectscript ]
Method GetDataL calls SQLGetData> for the column icol and returns data in the last argument. This method support long strings and requests the specified number of bytes. The supported types are 1 (SQL_CHAR),-2 (SQL_BINARY) and -8 (SQL_WCHAR). This method may be called repeatedly for the same column.
method GetDataLW(hstmt, icol, type, length, ByRef val) as %Status [ Language = objectscript ]
Method GetDataL calls SQLGetData> for the column icol and returns data in the last argument. This method support long strings and requests the specified number of bytes. The supported types are 1 (SQL_CHAR) and -8 (SQL_WCHAR). This method may be called repeatedly for the same column.
method GetDataW(hstmt, icol, type, ByRef val) as %Status [ Language = objectscript ]
Method GetDataW calls SQLGetData> for the column icol and returns data in the last argument.. The supported types are 1 (SQL_CHAR) and -8 (SQL_WCHAR). This method may be called repeatedly for the same column in the case when the data length exceeds 31K.
method GetErrorList(hstmt, Output errorlist As %List) as %Status [ Language = objectscript ]
Method GetErrorList sets a list of errors. It calls SQLDiagRec as long as it returns errors for the given hdbc, hstmt (hdbc is set implicitly to the handle of the active connection). errorlist is a delimited string (with carriage return/new line characters as delimiters)metalist; each element corresponds to a particular error, in the format: SQLState: (...) NativeError: [...] Message: [... GetErrorList(0, .errorlist) - returns the error status of the ODBC function most recently called with the current hdbc;
method GetGTWVersion() as %Integer [ Language = objectscript ]
method GetGTWVersion returns the current version of odbcgateway.dll(so)
method GetInfo(infotype, ByRef infoval) as %Status [ Language = objectscript ]
Method GetInfo calls SQLGetInfo for the current connection handle.
method GetLastSQLCode() as %Integer [ Language = objectscript ]
method GetLastSQLCode returns an SQL code for the last call if this call does not return an SQL code (e.g. SQLGetData).
method GetOneRow(hstmt, ByRef row) as %Status [ Language = objectscript ]
Method GetOneRow retrieves the next for the given hstmt (it should be a statement which returns a result set) It returns rlist - a list of fields If there is no data (SQL_CODE = 100) fetching is assumed to be successful but the return list is empty. This call truncates character fields up to 120 characters so that more fields would fit in a row. It is advised to use GetData call when non truncated data is needed.
method GetParameter(hstmt, pnbr, ByRef value) as %Status [ Language = objectscript ]
Method GetParameter(hstmt, pnbr, value) returns the current value of the parameter with the ordinal number pnbr in value.
method GetTypeInfo(hstmt, infotype As %Integer) as %Status [ Language = objectscript ]
Method GetInfo calls SQLGetTypeInfo for the current connection handle.
method GetUV(ByRef infoval) as %Status [ Language = objectscript ]
Method GetUV returns whether the dll was built as Unicode.
classmethod IsTextNetworkError(ErrorText As %String) as %Boolean [ Language = objectscript ]
Decide whether the text of an error returned from the driver represents a disconnection implying the need to re-connect. If you encounter other error signatures that ought to be in this list, please contact InterSystems so that we may update this method.
method MoreResults(hstmt) as %Integer [ Language = objectscript ]
Method MoreResults(hstmt) returns SQLMoreResults for the corresponding hstmt.
method ParamData(hstmt, ByRef ind As %Integer) as %Status [ Language = objectscript ]
Method ParamData(hstmt) invokes SQLParamData for a given handle.
method Prepare(hstmt, sql) as %Status [ Language = objectscript ]
Method Prepare(hstmt, sql) invokes SQLPrepare for a given handle.
method PrepareW(hstmt, sql) as %Status [ Language = objectscript ]
Method PrepareW(hstmt, sql) invokes SQLPrepareW for a given handle.
method PrimaryKeys(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) as %Status [ Language = objectscript ]
Method PrimaryKeys invokes SQLPrimaryKeys. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method PrimaryKeysW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) as %Status [ Language = objectscript ]
The wide version of PrimaryKeys. See documentation for SQLPrimaryKeys
method PutData(hstmt, ByRef val) as %Status [ Language = objectscript ]
Method PutData calls SQLPutData> for the column icol and sets the sqlcode property.
method PutDataW(hstmt, ByRef val) as %Status [ Language = objectscript ]
Method PutDataW calls SQLPutData> for the column icol and sets the sqlcode property.
method RowCount(hstmt) as %Integer [ Language = objectscript ]
Method RowCount(hstmt) returns SQLRowCount for the corresponding hstmt.
method SetConnectOption(opt, val) as %Status [ Language = objectscript ]
Method SetConnectOption(opt, val) invokes SQLSetConnectAttr. Only integer values are supported. Integer values for the opt may be taken from sql.h and sqlext.h header files.
method SetParameter(hstmt, pvalue, pnbr) as %Status [ Language = objectscript ]
Method SetParameter(hstmt, pvalue, pnbr) is used for setting the previously bound parameter with the ordinal number pnbr to the value which is contained in a $list format in pvalue. If the allocated buffer is not sufficient a new buffer will be allocated.
method SetParameterBinary(hstmt, pvalue, pnbr) as %Status [ Language = objectscript ]
Method SetParameterBinary(hstmt, pvalue, pnbr) is used for setting the previously bound binary parameter with the ordinal number pnbr to the value which is contained in a $list format in pvalue. If the allocated buffer is not sufficient a new buffer will be allocated.
method SetStmtOption(hstmt, opt, val) as %Status [ Language = objectscript ]
Method SetStmtOption(hstmt, opt, val) invokes SQLSetStmtAttr. Only integer values are supported. Integer values for the opt may be taken from sql.h and sqlext.h header files.
method SpecialColumns(hstmt, IdentifierType As %Integer, CatalogName As %String, SchemaName As %String, TableName As %String, Scope As %Integer, Nullable As %Integer) as %Status [ Language = objectscript ]
Method SpecialColumns invokes SQLSpecialColumns. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method SpecialColumnsW(hstmt, IdentifierType As %Integer, CatalogName As %String, SchemaName As %String, TableName As %String, Scope As %Integer, Nullable As %Integer) as %Status [ Language = objectscript ]
The wide version of SpecialColumns. See documentation for SpecialColumns
method Statistics(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, unique As %Integer) as %Status [ Language = objectscript ]
Method Statistics invokes SQLStatistics. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method StatisticsW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, unique As %Integer) as %Status [ Language = objectscript ]
Method StatisticsW invokes SQLStatisticsW. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method TablePrivileges(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) as %Status [ Language = objectscript ]
Method TablePrivileges invokes SQLTablePrivileges. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method TablePrivilegesW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) as %Status [ Language = objectscript ]
The wide version of TablePrivileges. See documentation for Tables
method Tables(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, TableType As %String) as %Status [ Language = objectscript ]
Method Tables invokes SQLTables. It should be followed by the usual retrieval of a result set. Users should pass empty strings instead of the corresponding null values and $c(0) instead of empty strings. All other ODBC conventions for arguments in catalog functions apply.
method TablesW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, TableType As %String) as %Status [ Language = objectscript ]
The wide version of Tables. See documentation for Tables
method Transact(type) as %Status [ Language = objectscript ]
Method Transact( type) invokes SQLEndTran. Pass 0 for SQL_COMMIT and 1 for SQL_ROLLBACK.
method UnbindParameters(hstmt) as %Status [ Language = objectscript ]
Method UnbindParameters(hstmt) invokes SQLFreeStmt with SQL_UNBIND. Unbinding is done automatically when BindParameters is called.
method UnloadDLL() as %Status [ Language = objectscript ]
This method unloads odbcgateway.dll from the process memory.
method getNextResultSet(hstmt, ByRef qrc As %Integer = 0) as %Library.ObjectHandle [ Language = objectscript ]
method getResultSet(hstmt) as %SQL.IResultSet [ Language = objectscript ]
The method getResultSet is used for encapsulating data retrival from already executed statement. It return a resultset and all the resultset methods for data and metadata access are internally translated to ODBC calls. It means that if this path is chosen the user should not call Fetch, GetData etc independently, otherwise the results may be not complete and/or correct.

Inherited Methods