Skip to main content

Using the SQL Gateway Programmatically

Note:

This section assumes that you have significant experience using ODBC API calls — it is not intended to provide details on how to use ODBC functions. If you encounter any problems, you can monitor the SQL Gateway by enabling logging for both InterSystems IRIS and ODBC (see the “Logging and Environment Variables” chapter in Using the InterSystems ODBC Driver).

If you require options that are not provided by the standard SQL Gateway wizards, you can use the %Library.SQLGatewayConnectionOpens in a new tab 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:

In the rest of this chapter, %Library.SQLGatewayConnectionOpens in a new tab is referred to by its abbreviated name, %SQLGatewayConnectionOpens in a new tab.

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 %SQLGatewayConnectionOpens in a new tab methods that call them.

ClassMethod FetchSamples
ClassMethod FetchSamples()
{
   #include %occInclude
   //Create new SQL 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 %SQLGatewayConnectionOpens in a new tab 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 in to that source, if necessary.

    The Connect() method has the following signature:

    method Connect(dsn, usr, pwd, timeout) as %Status
    
    

    Here dsn is the DSN for the data source, usr is a user who can log in to that data source, pwd is the corresponding password, and timeout specifies how long to wait for a connection.

  3. Create an instance of %ResultSetOpens in a new tab 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 %SQLGatewayConnectionOpens in a new tab.

  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 %ResultSetOpens in a new tab 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 conn=##class(%SQLGatewayConnection).%New()
    Set sc=conn.Connect("AccessPlayground","","")
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set res=##class(%ResultSet).%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=conn.Disconnect()
    Quit sc
}

For more information on %ResultSetOpens in a new tab, see the chapter “Using Dynamic SQL” in Using InterSystems SQL. Also see the class documentation for %ResultSetOpens in a new tab.

Calling ODBC Functions Directly

If %SQL.StatementOpens in a new tab does not provide enough control, you can use the %SQLGatewayConnectionOpens in a new tab 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 %SQLGatewayConnectionOpens in a new tab 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 in to that source, if necessary.

  3. Call the AllocateStatement() method and receive (by reference) a statement handle.

  4. Call other methods of the SQL 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 %SQLGatewayConnectionOpens in a new tab versions of Prepare() and Execute() to call ODBC query functions SQLPrepare() and SQLExecute() directly, rather than using the %SQL.StatementOpens in a new tab methods:

Executing a query using %SQLGatewayConnectionOpens in a new tab 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:

  • 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 %SQLGatewayConnectionOpens in a new tab 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 %SQLGatewayConnectionOpens in a new tab 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 in to 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 %SQLGatewayConnectionOpens in a new tab 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 %SQLGatewayConnectionOpens in a new tab 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
Connect()

Establishes a connection to a DSN.

method Connect(dsn, usr, pwd, timeout) as %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 property

%BinaryOpens in a new tab property that provides the current connection handle to the ODBC-compliant data source.

Disconnect()

Closes the connection.

method Disconnect() as %Status
DLLHandle property

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

DLLName property

%StringOpens in a new tab property that provides the name of the shared library currently in use. This is set when you connect.

DSN property

%StringOpens in a new tab 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

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

%StringOpens in a new tab 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

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
GetGTWVersion()

Returns the current version of the shared library.

method GetGTWVersion() as %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() as %Integer

GetOneRow()

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

method GetOneRow(hstmt, ByRef row) as %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) as %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) as %Status

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

Password property

%StringOpens in a new tab property that provides the associated password.

SetConnectOption()

Invokes the ODBC function SQLSetConnectAttr().

method SetConnectOption(opt, val) as %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) as %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 property

%IntegerOpens in a new tab 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
User property

%StringOpens in a new tab property that provides the username to log in to the data source.

Supported ODBC Function Calls

The following table lists ODBC functions directly supported by corresponding %SQLGatewayConnectionOpens in a new tab methods, and links to the class documentation for those methods. See “Calling ODBC Functions Directly” for an example that calls methods to invoke ODBC functions SQLPrepare and SQLExecute.

This chapter is not intended as a detailed reference for these methods. For details on method arguments, actions, and return values, see the InterSystems Class Library reference for %SQLGatewayConnectionOpens in a new tab.

Calling ODBC Functions from %SQLGatewayConnection
ODBC Function ObjectScript methods that call the function
SQLAllocHandle AllocateStatement()Opens in a new tab
SQLBindParameter BindParameter()Opens in a new tab, BindParameters()Opens in a new tab
SQLCloseCursor CloseCursor()Opens in a new tab
SQLColAttribute DescribeColumns()Opens in a new tab
SQLColumnPrivileges ColumnPrivileges()Opens in a new tab, ColumnPrivilegesW()Opens in a new tab
SQLColumns Columns()Opens in a new tab, ColumnsW()Opens in a new tab
SQLDescribeCols DescribeColumns()Opens in a new tab
SQLDescribeParam DescribeParameters()Opens in a new tab
SQLDiagRec GetErrorList()Opens in a new tab
SQLEndTran Transact()Opens in a new tab
SQLExecute Execute()Opens in a new tab
SQLFetch Fetch()Opens in a new tab
SQLForeignKeys ForeignKeys()Opens in a new tab, ForeignKeysW()Opens in a new tab
SQLFreeHandle DropStatement()Opens in a new tab
SQLFreeStmt UnbindParameters()Opens in a new tab
SQLGetData GetData()Opens in a new tab, GetDataL()Opens in a new tab, GetDataLW()Opens in a new tab, GetDataW()Opens in a new tab
SQLGetInfo GetInfo()Opens in a new tab
SQLGetTypeInfo GetTypeInfo()Opens in a new tab
SQLMoreResults MoreResults()Opens in a new tab
SQLNumParams DescribeParameters()Opens in a new tab
SQLParamData ParamData()Opens in a new tab
SQLPrepare Prepare()Opens in a new tab, PrepareW()Opens in a new tab
SQLPrimaryKeys PrimaryKeys()Opens in a new tab, PrimaryKeysW()Opens in a new tab
SQLProcedureColumns DescribeProcedureColumns()Opens in a new tab, DescribeProcedureColumnsW()Opens in a new tab
SQLProcedures DescribeProcedures()Opens in a new tab, DescribeProceduresW()Opens in a new tab
SQLPutData PutData()Opens in a new tab, PutDataW()Opens in a new tab
SQLRowCount RowCount()Opens in a new tab
SQLSetConnectAttr SetConnectOption()Opens in a new tab
SQLSetStmtAttr SetStmtOption()Opens in a new tab
SQLSpecialColumns SpecialColumns()Opens in a new tab, SpecialColumnsW()Opens in a new tab
SQLStatistics Statistics()Opens in a new tab, StatisticsW()Opens in a new tab
SQLTablePrivileges TablePrivileges()Opens in a new tab, TablePrivilegesW()Opens in a new tab
SQLTables Tables()Opens in a new tab, TablesW()Opens in a new tab
FeedbackOpens in a new tab