%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
The sample class mentioned here (Sample.Person) is part of https://github.com/intersystems/Samples-Data.Opens in a new tab See Downloading SamplesOpens in a new tab.
Property Inventory
- ConnectionHandle
- ConnectionName
- DLLHandle
- DLLName
- DSN
- GatewayStatus
- LegacyMode
- Password
- User
- sqlcode
Method Inventory
- 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 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(), 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
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 :
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);
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 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:
Next elements correspond to the statement column and also contains 8 elements:
- the number of columns in the given statement
- the string "SqlType"
- the string "Precision"
- the string "Scale"
- the string "Nullable"
- the string "Datatype"
- the string "DisplaySize"
- the string "IsMoney?"
Next elements correspond to the statement column and also contains 8 elements:
- column name
- SQL type of the column
- column size
- decimal digits
- nullable
- column datatype
- column display size
- SQL_TRUE or SQL_FALSE depending whether this is a currency type
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:
The next elements correspond to the statement parameter and contains 4 elements:
- the number of parameters in the given statement
- the string "SqlType"
- the string "Precision"
- the string "Scale"
- the string "Nullable"
The next elements correspond to the statement parameter and contains 4 elements:
- the SQL type of the parameter
- the parameter size
- the scale
- 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
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 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 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;
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 GetInfo calls SQLGetTypeInfo for the current connection handle.
method GetUV(ByRef infoval) as %Status
Method GetUV returns whether the dll was built as Unicode.
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) 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
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.
Inherited Members
Inherited 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()