Class Reference
IRIS for UNIX 2019.2
InterSystems: The power behind what matters   
Documentation  Search
Private  Storage   

class %Library.SQLGatewayConnection extends %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

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
10 63


Summary

Properties
ConnectionHandle ConnectionName DLLHandle DLLName
DSN GatewayStatus LegacyMode Password
User sqlcode

Methods
%AddToSaveSet %ClassIsLatestVersion %ClassName %ConstructClone
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Extends
%GetParameter %IsA %IsModified %New
%NormalizeObject %ObjectModified %OriginalNamespace %PackageName
%RemoveFromSaveSet %SerializeObject %SetModified %ValidateObject
AllocateStatement BindParameter BindParameters CloseCursor
ColumnPrivileges ColumnPrivilegesW Columns ColumnsW
Connect DescribeColumns DescribeParameters DescribeProcedureColumns
DescribeProcedureColumnsW DescribeProcedures DescribeProceduresW Disconnect
DropStatement Execute Fetch FetchRows
ForeignKeys ForeignKeysW GatewayStatusGet GetConnection
GetData GetDataL GetDataLW GetDataW
GetErrorList GetGTWVersion GetInfo GetLastSQLCode
GetOneRow GetParameter GetTypeInfo GetUV
IsTextNetworkError MoreResults ParamData Prepare
PrepareW PrimaryKeys PrimaryKeysW PutData
PutDataW RowCount SetConnectOption SetParameter
SetParameterBinary SetStmtOption SpecialColumns SpecialColumnsW
Statistics StatisticsW TablePrivileges TablePrivilegesW
Tables TablesW Transact UnbindParameters
UnloadDLL getNextResultSet getResultSet


Properties

• property ConnectionHandle as %Binary;
• property ConnectionName as %String;
• property DLLHandle as %Binary;
• property DLLName as %String;
• property DSN as %String;
• property GatewayStatus as %Integer [ Calculated ];
• property LegacyMode as %Integer;
• property Password as %String;
• property User as %String;
• property sqlcode as %Integer;

Methods

• method AllocateStatement(ByRef hstmt) as %Status
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)
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
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
Method CloseCursor(hstmt) invokes SQLCloseCursor .
• method ColumnPrivileges(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String, ColumnName As %String) as %Status
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
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
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
The wide version of Columns. See documentation for Columns
• method Connect(dsn, usr, pwd, timeout) as %Status
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
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
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
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
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
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
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
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
Method DropStatement(hstmt) invokes SQLFreeHandle with SQL_HANDLE_STMT.
• method Execute(hstmt) as %Status
Method Execute(hstmt) invokes SQLExecute for a given handle.
• method Fetch(hstmt) as %Status
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
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
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
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
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
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
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
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
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
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
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
method GetGTWVersion returns the current version of odbcgateway.dll(so)
• method GetInfo(infotype, ByRef infoval) as %Status
Method GetInfo calls SQLGetInfo for the current connection handle.
• method GetLastSQLCode() as %Integer
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
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
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
Method GetInfo calls SQLGetTypeInfo for the current connection handle.
• method GetUV(ByRef infoval) as %Status
Method GetUV returns whether the dll was built as Unicode.
• classmethod IsTextNetworkError(ErrorText As %String) as %Boolean
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
Method MoreResults(hstmt) returns SQLMoreResults for the corresponding hstmt.
• method ParamData(hstmt, ByRef ind As %Integer) as %Status
Method ParamData(hstmt) invokes SQLParamData for a given handle.
• method Prepare(hstmt, sql) as %Status
Method Prepare(hstmt, sql) invokes SQLPrepare for a given handle.
• method PrepareW(hstmt, sql) as %Status
Method PrepareW(hstmt, sql) invokes SQLPrepareW for a given handle.
• method PrimaryKeys(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) as %Status
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
The wide version of PrimaryKeys. See documentation for SQLPrimaryKeys
• method PutData(hstmt, ByRef val) as %Status
Method PutData calls SQLPutData> for the column icol and sets the sqlcode property.
• method PutDataW(hstmt, ByRef val) as %Status
Method PutDataW calls SQLPutData> for the column icol and sets the sqlcode property.
• method RowCount(hstmt) as %Integer
Method RowCount(hstmt) returns SQLRowCount for the corresponding hstmt.
• method SetConnectOption(opt, val) as %Status
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
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
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
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
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
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
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
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
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
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
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
The wide version of Tables. See documentation for Tables
• method Transact(type) as %Status
Method Transact( type) invokes SQLEndTran. Pass 0 for SQL_COMMIT and 1 for SQL_ROLLBACK.
• method UnbindParameters(hstmt) as %Status
Method UnbindParameters(hstmt) invokes SQLFreeStmt with SQL_UNBIND. Unbinding is done automatically when BindParameters is called.
• method UnloadDLL() as %Status
This method unloads odbcgateway.dll from the process memory.
• method getNextResultSet(hstmt, ByRef qrc As %Integer = 0) as %Library.ObjectHandle
getNextResult
• method getResultSet(hstmt) as %SQL.IResultSet
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.


Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.