Class Reference
IRIS for UNIX 2024.1.2
|
Class Reference
IRIS for UNIX 2024.1.2
|
|
|
Private
Storage
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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 | |||
|---|---|---|---|
| ConnectionHandle | ConnectionName | DLLHandle | DLLName |
| DSN | GatewayStatus | LegacyMode | Password |
| User | sqlcode | ||
|
|
|
|
Method AllocateStatement(hstmt) invokes SQLAllocHandle and creates the corresponding structures in the SQL Gateway.
BindParameter is calling SQLBindParameter with the corresponding values, the buffer length is guaranteed to be not less then StrLen.
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);
Method CloseCursor(hstmt) invokes SQLCloseCursor .
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.
The wide version of Columns. See documentation for ColumnPrivilegesW
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.
The wide version of Columns. See documentation for Columns
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:
- 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 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 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 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 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 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 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) invokes SQLFreeHandle with SQL_HANDLE_STMT.
Method Execute(hstmt) invokes SQLExecute for a given handle.
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 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 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 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 is used for establishing connection. It uses an entry from InterSystems IRIS configuration for determening the DSN, user name, and password.
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 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 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 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 returns the current version of odbcgateway.dll(so)
Method GetInfo calls SQLGetInfo for the current connection handle.
method GetLastSQLCode returns an SQL code for the last call if this call does not return an SQL code (e.g. SQLGetData).
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, 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 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) returns SQLMoreResults for the corresponding hstmt.
Method ParamData(hstmt) invokes SQLParamData for a given handle.
Method Prepare(hstmt, sql) invokes SQLPrepare for a given handle.
Method PrepareW(hstmt, sql) invokes SQLPrepareW for a given handle.
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.
The wide version of PrimaryKeys. See documentation for SQLPrimaryKeys
Method PutData calls SQLPutData> for the column icol and sets the sqlcode property.
Method PutDataW calls SQLPutData> for the column icol and sets the sqlcode property.
Method RowCount(hstmt) returns SQLRowCount for the corresponding hstmt.
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) 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) 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) 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 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.
The wide version of SpecialColumns. See documentation for SpecialColumns
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 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 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.
The wide version of TablePrivileges. See documentation for Tables
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.
The wide version of Tables. See documentation for Tables
Method Transact( type) invokes SQLEndTran. Pass 0 for SQL_COMMIT and 1 for SQL_ROLLBACK.
Method UnbindParameters(hstmt) invokes SQLFreeStmt with SQL_UNBIND. Unbinding is done automatically when BindParameters is called.
This method unloads odbcgateway.dll from the process memory.
getNextResult
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.