Skip to main content
Previous section

Using the ODBC SQL Gateway Programmatically

If you require options that are not provided by the standard SQL Gateway wizards, you can use the %Library.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 ODBC with InterSystems Software).

Note:

In the rest of this chapter, %Library.SQLGatewayConnection is referred to by its abbreviated name, %SQLGatewayConnection.

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
}
Copy code to clipboard

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) as %Status
    
    Copy code to clipboard

    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
}
Copy code to clipboard

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()
}
Copy code to clipboard
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:

  • In SQL, "" represents an empty string.

  • In InterSystems IRIS, "" equals null.

  • In InterSystems IRIS, $char(0) equals an empty string.

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.

  • DSN — (%String property) Data source name of the ODBC-compliant data source to which you want to connect.

  • User — (%String property) Username to log into the data source.

  • Password — (%String property) Associated password

  • ConnectionHandle — (%Binary property) The current connection handle to the ODBC-compliant data source.

  • Connect() — Establishes a connection to a DSN.

  • GetConnection() — Establishes a connection using configuration settings to determine the DSN, username, and password.

  • SetConnectOption() — Invokes the ODBC function SQLSetConnectAttr.

  • Disconnect() — Closes the connection.

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:

  • sqlcode — (%Integer property) Contains the SQL code return by the last call (if any).

  • GatewayStatus — (%Integer property) Indicates the status of the last call.

  • GetLastSQLCode() — Returns an SQL code for the last call if this call does not return an SQL code.

  • GatewayStatusGet() — Returns an error code for the last call.

The following methods get rows from the result set:

  • FetchRows() — Returns (by reference) a specified number of rows for the given connection handle.

  • GetOneRow() — Returns (by reference) the next row for the given connection handle.

The following methods get and set the values of bound query parameters:

  • GetParameter() — Returns (by reference) the current value of the indicated parameter.

  • SetParameter() — Sets the value of a previously bound parameter.

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.

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

  • GetGTWVersion() — Returns the current version of the shared library.

  • GetUV() — Returns (by reference) whether the shared library was built as Unicode. Note that this method always returns a status of $$$OK.

  • UnloadDLL() — Unloads the shared library from the process memory.

%SQLGatewayConnection Methods and Properties

This is an alphabetical listing of selected methods and properties. See “Supported ODBC Function Calls” for methods not listed here.

AllocateStatement()

Invokes ODBC function SQLAllocHandle() and creates the corresponding structures in the SQL Gateway.

method AllocateStatement(ByRef hstmt) as %Status
Copy code to clipboard
Connect()

Establishes a connection to a DSN.

method Connect(dsn, usr, pwd, timeout) as %Status
Copy code to clipboard

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 property

%Binary property that provides the current connection handle to the ODBC-compliant data source.

Disconnect()

Closes the connection.

method Disconnect() as %Status
Copy code to clipboard
DLLHandle property

%Binary property that provides the handle for the shared library, as currently in use. This is set when you connect.

DLLName property

%String property that provides the name of the shared library currently in use. This is set when you connect.

DSN property

%String property that provides the 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) as %Status
Copy code to clipboard

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 property

%String property that provides the status of the last call. Status value will be one of the following:

  • 0 - success

  • -1 - SQL error

  • -1000 - critical error

GatewayStatusGet()

Returns an error code for the last call.

method GatewayStatusGet() as %Integer
Copy code to clipboard

It does not initialize the error code and can be called multiple times. See the previous notes for the GatewayStatus property.

GetConnection()

Establishes a connection, using configuration file entries to determine the DSN, user name, and password.

method GetConnection(conn, timeout) as %Status
Copy code to clipboard
GetGTWVersion()

Returns the current version of the shared library.

method GetGTWVersion() as %Integer
Copy code to clipboard
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() as %Integer
Copy code to clipboard
GetOneRow()

Returns (by reference) the next row for the given connection handle.

method GetOneRow(hstmt, ByRef row) as %Status
Copy code to clipboard

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) as %Status
Copy code to clipboard

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) as %Status
Copy code to clipboard

Note that this method always returns a status of $$$OK.

Password property

%String property that provides the associated password.

SetConnectOption()

Invokes the ODBC function SQLSetConnectAttr().

method SetConnectOption(opt, val) as %Status
Copy code to clipboard

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) as %Status
Copy code to clipboard

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 property

%Integer property that provides 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() as %Status
Copy code to clipboard
User property

%String property that provides the 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()