Skip to main content

%Library.SQLGatewayConnection

Class %Library.SQLGatewayConnection Extends %RegisteredObject [ Not ProcedureBlock, System = 3 ]

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

The sample class mentioned here (Sample.Person) is part of https://github.com/intersystems/Samples-Data. See Downloading Samples.

Properties​

ConnectionName​

Property ConnectionName As %String;

DSN​

Property DSN As %String;

User​

Property User As %String;

Password​

Property Password As %String;

DLLName​

Property DLLName As %String;

DLLHandle​

Property DLLHandle As %Binary;

sqlcode​

Property sqlcode As %Integer;

ConnectionHandle​

Property ConnectionHandle As %Binary;

LegacyMode​

Property LegacyMode As %Integer;

GatewayStatus​

Property GatewayStatus As %Integer [ Calculated ];

DisconnectOnClose​

Property DisconnectOnClose As %Boolean;

Methods​

Connect​

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.

GetConnection​

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.

GatewayStatusGet​

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

GetGTWVersion​

Method GetGTWVersion() As %Integer

method GetGTWVersion returns the current version of odbcgateway.dll(so)

GetLastSQLCode​

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).

GetErrorList​

Method GetErrorList(hstmt, Output errorlist As %List) As %Status [ ProcedureBlock = 1 ]

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 platform specific 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;

Prepare​

Method Prepare(hstmt, sql) As %Status

Method Prepare(hstmt, sql) invokes SQLPrepare for a given handle.

PrepareW​

Method PrepareW(hstmt, sql) As %Status

Method PrepareW(hstmt, sql) invokes SQLPrepareW for a given handle.

getResultSet​

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.

ParamData​

Method ParamData(hstmt, ByRef ind As %Integer) As %Status [ ProcedureBlock = 1 ]

Method ParamData(hstmt) invokes SQLParamData for a given handle.

Execute​

Method Execute(hstmt) As %Status

Method Execute(hstmt) invokes SQLExecute for a given handle.

AllocateStatement​

Method AllocateStatement(ByRef hstmt) As %Status

Method AllocateStatement(hstmt) invokes SQLAllocHandle and creates the corresponding structures in the SQL Gateway.

Disconnect​

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

Fetch​

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.

RowCount​

Method RowCount(hstmt) As %Integer

Method RowCount(hstmt) returns SQLRowCount for the corresponding hstmt.

MoreResults​

Method MoreResults(hstmt) As %Integer

Method MoreResults(hstmt) returns SQLMoreResults for the corresponding hstmt.

getNextResultSet​

Method getNextResultSet(hstmt, ByRef qrc As %Integer = 0) As %Library.ObjectHandle

BindParameters​

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 $list format
  • dtype - SQL datatypes of parameters in the $list format
  • precision - $list of the correponding precision values - used for reserving 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);

BindParameter​

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.

SetParameter​

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.

SetParameterBinary​

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.

DescribeColumns​

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

FetchRows​

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.

Columns​

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.

ColumnPrivileges​

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.

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.

TablePrivileges​

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.

ColumnsW​

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

ColumnPrivilegesW​

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

SpecialColumnsW​

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

SpecialColumns​

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.

TablesW​

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

TablePrivilegesW​

Method TablePrivilegesW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) As %Status

The wide version of TablePrivileges. See documentation for Tables

PrimaryKeysW​

Method PrimaryKeysW(hstmt, CatalogName As %String, SchemaName As %String, TableName As %String) As %Status

The wide version of PrimaryKeys. See documentation for SQLPrimaryKeys

PrimaryKeys​

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.

DropStatement​

Method DropStatement(hstmt) As %Status

Method DropStatement(hstmt) invokes SQLFreeHandle with SQL_HANDLE_STMT.

CloseCursor​

Method CloseCursor(hstmt) As %Status

Method CloseCursor(hstmt) invokes SQLCloseCursor .

UnbindParameters​

Method UnbindParameters(hstmt) As %Status

Method UnbindParameters(hstmt) invokes SQLFreeStmt with SQL_UNBIND. Unbinding is done automatically when BindParameters is called.

SetConnectOption​

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.

SetStmtOption​

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.

Transact​

Method Transact(type) As %Status

Method Transact( type) invokes SQLEndTran. Pass 0 for SQL_COMMIT and 1 for SQL_ROLLBACK.

GetParameter​

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.

DescribeProcedureColumns​

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.

DescribeProcedureColumnsW​

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.

DescribeProcedures​

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.

DescribeProceduresW​

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.

GetData​

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.

GetDataL​

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.

GetDataLW​

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.

PutData​

Method PutData(hstmt, ByRef val) As %Status

Method PutData calls SQLPutData> for the column icol and sets the sqlcode property.

PutDataW​

Method PutDataW(hstmt, ByRef val) As %Status

Method PutDataW calls SQLPutData> for the column icol and sets the sqlcode property.

GetDataW​

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.

GetOneRow​

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.

DescribeParameters​

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 $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

GetInfo​

Method GetInfo(infotype, ByRef infoval) As %Status

Method GetInfo calls SQLGetInfo for the current connection handle.

GetTypeInfo​

Method GetTypeInfo(hstmt, infotype As %Integer) As %Status

Method GetInfo calls SQLGetTypeInfo for the current connection handle.

Statistics​

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.

StatisticsW​

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.

ForeignKeys​

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.

ForeignKeysW​

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.

GetUV​

Method GetUV(ByRef infoval) As %Status

Method GetUV returns whether the dll was built as Unicode.

UnloadDLL​

Method UnloadDLL() As %Status

This method unloads odbcgateway.dll from the process memory.

%OnClose​

Method %OnClose() As %Status

IsTextNetworkError​

ClassMethod IsTextNetworkError(ErrorText As %String) As %Boolean [ ProcedureBlock = 1 ]

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.