docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using the InterSystems SQL Gateway  /  Using the ODBC SQL Gateway Programmatically


Using the InterSystems SQL Gateway
Using the ODBC SQL Gateway Programmatically
[Back] 
InterSystems: The power behind what matters   
Search:  


If you require options that are not provided by the standard SQL Gateway wizards, you can use the %SQLGatewayConnection class to call ODBC functions from ObjectScript. You can either execute a dynamic query (obtaining a result set) or you can perform low-level ODBC programming. The following topics are discussed in this chapter:
To use this section, you should have some experience with ODBC — this book does not provide details on the ODBC functions. If you encounter any problems, you can monitor the gateway by enabling logging for both InterSystems IRIS and ODBC (see the Logging and Environment Variables chapter in Using the InterSystems ODBC Driver).
FetchSamples Example
The following example provides a simple demonstration of how to open a connection, prepare and execute a query, and access the resulting data set. See the entries in Quick Reference for %SQLGatewayConnection for information on Connect(), Disconnect(), ConnectionHandle, and sqlcode. See the Quick Reference section on Supported ODBC Function Calls for a list of supported ODBC functions and the %SQLGatewayConnection methods that call them.
ClassMethod FetchSamples
ClassMethod FetchSamples()
{
   #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
   set pDSN="Cache Samples"
   set usr="_system"
   set 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
   set rownum=1
   while((gc.sqlcode'=100) && (rownum<=200)) {
      for ii=1:1:numcols {
         set sc=gc.GetData(hstmt, ii, 1, .val)
         write " "_val
         if $$$ISERR(sc) break
      }
      set 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

   set sc=gc.Disconnect()
   quit sc
}
Creating and Using an External Data Set
To create and use a data set that queries an external database, do the following:
  1. Create an instance of %SQLGatewayConnection via the %New() method.
  2. Call the Connect() method of that instance, passing arguments that specify the ODBC data source name, as well as the username and password that are needed to log into that source, if necessary.
    The Connect() method has the following signature:
    method Connect(dsn, usr, pwd, timeout) returns %Status
    
    Here dsn is the DSN for the data source, usr is a user who can log into that data source, pwd is the corresponding password, and timeout specifies how long to wait for a connection.
  3. Create an instance of %SQL.Statement via the %New() method, providing the string argument "%DynamicQueryGW:SQLGW".
    Note:
    This is slightly different from the argument that you use with a typical dynamic query ("%DynamicQuery:SQL").
  4. Invoke the Prepare() method of the result set. The first argument should be a string that consists of a SQL query, the second argument should be omitted, and the third argument should be the instance of %SQLGatewayConnection.
  5. Call the Execute() method of the result set, optionally providing any arguments in the order expected by the query. This method returns a status, which should be checked.
To use the result set, you generally examine it one row at a time. You use methods of %SQL.Statement to retrieve information such as the value in a given column. Typically you iterate through all the rows using Next(), as demonstrated in the following example:
Example
ClassMethod SelectAndWrite() as %Status
{
    Set gateway=##class(%SQLGatewayConnection).%New()
    Set sc=gateway.Connect("AccessPlayground","","")
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set res=##class(%SQL.Statement).%New("%DynamicQueryGW:SQLGW")
    Set sc=res.Prepare("SELECT * FROM PEOPLE",,conn)
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set sc=res.Execute()
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    While res.Next()
    { Write !,res.GetData(1)," ",res.GetData(2)," ",res.GetData(3)
    }
    Set sc=gateway.Disconnect()
    Quit sc
}
For more information on %SQL.Statement, see the chapter Dynamic SQL in Using InterSystems SQL. Also see the class documentation for %SQL.Statement.
Calling ODBC Functions Directly
If %SQL.Statement does not provide enough control, you can use the %SQLGatewayConnection class to access ODBC directly. It provides a set of methods that correspond to ODBC functions (see Supported ODBC Function Calls), as well as other utility functions. You can connect to and use an ODBC-compliant database and then perform low-level ODBC programming. The overall procedure is as follows:
  1. Create an instance of %SQLGatewayConnection via the %New() method.
  2. Call the Connect() method of that instance, passing arguments that specify the ODBC data source name, as well as the username and password that are needed to log into that source, if necessary.
  3. Call the AllocateStatement() method and receive (by reference) a statement handle.
  4. Call other methods of the gateway instance, using that statement handle as an argument. Most of these methods call ODBC functions.
The following simple example demonstrates this procedure. It is similar to the example in the previous section, but it uses the %SQLGatewayConnection versions of Prepare() and Execute() to call ODBC query functions SQLPrepare() and SQLExecute() directly, rather than using the %SQL.Statement methods:
Executing a query using %SQLGatewayConnection methods
ClassMethod ExecuteQuery(mTable As %String)
{
   set mDSN="DSNtest"
   set mUsrName="SYSDBA"
   set mUsrPwd="masterkey"

   // Create an instance and connect
   set gateway=##class(%SQLGatewayConnection).%New()
   set status=gateway.Connect(mDSN,mUsrName,mUsrPwd)
   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
   set hstmt=""

   // Allocate a statement
  set status=gateway.AllocateStatement(.hstmt)
   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()

   // Use %SQLGatewayConnection to call ODBC query functions directly
   set status=gateway.Prepare(hstmt,"SELECT * FROM "_mTable)
   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
   set status=gateway.Execute(hstmt)

   if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
   quit gateway.Disconnect()
}
Note:
Null Values and Empty Strings
When you use the methods described in this chapter, remember that InterSystems IRIS and SQL have the following important differences:
Quick Reference for %SQLGatewayConnection
Overview of the %SQLGatewayConnection API
The %SQLGatewayConnection class provides properties and methods that you can use to manage the connection to the external data source, check status information, and get information about the ODBC shared library. The methods and properties covered in this reference are listed below, organized by usage (see Supported ODBC Function Calls for methods not listed here):
Managing the Connection
The %SQLGatewayConnection class provides properties and methods that you can use to manage the connection to the external data source.
Status and Query Methods
Most of the methods of %SQLGatewayConnection return a status, which you should check. Status information is also available via the following properties and methods:
The following methods get rows from the result set:
The following methods get and set the values of bound query parameters:
Using the Shared Library
The %SQLGatewayConnection class provides properties and methods that you can call to get information about the shared library used by the ODBC SQL Gateway.
Note:
The phrase shared library refers in general to the file or library that comprises the ODBC SQL Gateway. On Windows platforms, this is a file with the extension .dll. See Special Considerations for UNIX® and Related Platforms for more information on supported UNIX® shared objects. The properties and methods described here apply to all operating systems.
%SQLGatewayConnection Methods and Properties
This is an alphabetical listing of selected methods and properties. See Supported ODBC Function Calls for methods not listed here.
Connect()
Establishes a connection to a DSN.
method Connect(dsn, usr, pwd, timeout) returns %Status
If username and password are both empty, this method calls the ODBC function SQLDriverConnect(). If that call is unsuccessful or username/password are specified, the method calls the ODBC function SQLConnect().
If the timeout parameter is not 0, SQLSetConnectAttr() is first called to set SQL_ATTR_LOGIN_TIMEOUT.
ConnectionHandle (%Binary property)
The current connection handle to the ODBC-compliant data source.
Disconnect()
Closes the connection.
method Disconnect() returns %Status
DLLHandle (%Binary property)
Handle for the shared library, as currently in use. This is set when you connect.
DLLName (%String property)
Name of the shared library currently in use. This is set when you connect.
DSN (%String property)
Data source name of the ODBC-compliant data source to which you want to connect.
FetchRows()
Returns (by reference) a specified number of rows for the given connection handle.
method FetchRows(hstmt, Output rlist As %List, nrows As %Integer) returns %Status
Here hstmt is the connection handle, returned (by reference) from AllocateStatement(). Also, rlist is the returned list of rows; this is an InterSystems IRIS $list. Each item in the list contains a row. If there is no data (SQL_CODE = 100), fetching is assumed to be successful but the return list is empty.
Caution:
This method is primarily useful for testing, and it truncates character fields up to 120 characters so that more fields would fit in a row. Use GetData() instead when you need non-truncated data.
GatewayStatus (%Integer property)
Indicates the status of the last call. This is one of the following:
GatewayStatusGet()
Returns an error code for the last call.
method GatewayStatusGet() returns %Integer
It does not initialize the error code and can be called multiple times. See the previous notes for the GatewayStatus property.
GetGTWVersion()
Returns the current version of the shared library.
method GetGTWVersion() returns %Integer
GetLastSQLCode()
Returns an SQL code for the last call if this call does not return an SQL code (for example, if you used SQLGetData()).
method GetLastSQLCode() returns %Integer
GetOneRow()
Returns (by reference) the next row for the given connection handle.
method GetOneRow(hstmt, ByRef row) returns %Status
Here hstmt is the connection handle, returned (by reference) from AllocateStatement(). Also, row is the returned row, an InterSystems IRIS $list. Each item in the list contains a field. If there is no data (SQL_CODE = 100), fetching is assumed to be successful but the return list is empty.
Caution:
This method is primarily useful for testing, and it truncates character fields up to 120 characters so that more fields would fit in a row. Use GetData() instead when you need non-truncated data.
GetParameter()
Returns (by reference) the current value of the indicated parameter.
method GetParameter(hstmt, pnbr, ByRef value) returns %Status
Here hstmt is the connection handle returned (by reference) from AllocateStatement() and pnbr is the ordinal number of the parameter.
GetUV()
Returns (by reference) whether the shared library was built as Unicode.
method GetUV(ByRef infoval) returns %Status
Note that this method always returns a status of $$$OK.
Password (%String property)
Associated password.
SetConnectOption()
Invokes the ODBC function SQLSetConnectAttr().
method SetConnectOption(opt, val) returns %Status
Only integer values are supported. Integer values for the opt argument may be taken from the sql.h and sqlext.h header files.
SetParameter()
Sets the value of a previously bound parameter.
method SetParameter(hstmt, pvalue, pnbr) returns %Status
Here hstmt is the connection handle returned (by reference) from AllocateStatement(), pvalue is the value to use, and pnbr is the ordinal number of the parameter. The parameters are stored in $list format. If the allocated buffer is not sufficient, a new buffer will be allocated.
sqlcode (%Integer property)
Contains the SQL code returned by the last call (if any).
UnloadDLL()
Unloads the shared library for the ODBC SQL Gateway from the process memory.
method UnloadDLL() returns %Status
User (%String property)
Username to log into the data source.
Supported ODBC Function Calls
The following table lists the supported ODBC functions and indicates which %SQLGatewayConnection methods are used to call those functions.
These methods do not have a detailed listing in this chapter. For details on the method arguments, actions, and return values, see the InterSystems Class Library reference for %SQLGatewayConnection.
Calling ODBC Functions from %SQLGatewayConnection
ODBC Function Method That Calls This Function
SQLAllocHandle AllocateStatement()
SQLBindParameter BindParameter()
SQLCloseCursor CloseCursor()
SQLColAttribute DescribeCols()
SQLColumns Columns()
SQLColumnsW ColumnsW()
SQLDescribeCols DescribeCols()
SQLDescribeParam DescribeParam()
SQLDiagRec GetErrorList()
SQLEndTran Transact()
SQLExecute Execute()
SQLFetch Fetch()
SQLFreeHandle DropStatement()
SQLFreeStmt UnbindParameters()
SQLGetData GetData()
SQLGetDataW GetDataW()
SQLGetInfo GetInfo()
SQLMoreResults MoreResults()
SQLNumParams DescribeParameters()
SQLParamData ParamData()
SQLPrepare Prepare()
SQLPrepareW PrepareW()
SQLPrimaryKeys PrimaryKeys()
SQLPrimaryKeys PrimaryKeysW()
SQLProcedureColumns ProcedureColumns()
SQLProcedureColumnsW ProcedureColumnsW()
SQLProcedures Procedures()
SQLPutData PutData()
SQLPutDataW PutDataW()
SQLRowCount RowCount()
SQLSetConnectAttr SetConnectOption() (see special considerations)
SQLSetStmtAttr SetStmtOption()
SQLSpecialColumns SpecialColumns()
SQLSpecialColumnsW SpecialColumnsW()
SQLTables Tables()
SQLTablesW TablesW()