Using the SQL Gateway Programmatically
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:
-
FetchSamples Example — lists a simple program that opens a connection, runs a query, and accesses the result set.
-
Creating and Using an External Data Set — demonstrates using %SQL.StatementOpens in a new tab methods to run queries and access data sets.
-
Calling ODBC Functions Directly — demonstrates how to call ODBC query functions directly, rather than through %SQL.StatementOpens in a new tab.
-
Quick Reference for %SQLGatewayConnection — provides details about the supported methods and properties.
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()
{
#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:
-
Create an instance of %SQLGatewayConnectionOpens in a new tab via the %New() method.
-
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 %StatusHere 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.
-
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").
-
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.
-
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:
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:
-
Create an instance of %SQLGatewayConnectionOpens in a new tab via the %New() method.
-
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.
-
Call the AllocateStatement() method and receive (by reference) a statement handle.
-
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:
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()
}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):
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.
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.
%SQLGatewayConnection Methods and Properties
This is an alphabetical listing of selected methods and properties. See “Supported ODBC Function Calls” for methods not listed here.
Invokes ODBC function SQLAllocHandle() and creates the corresponding structures in the SQL Gateway.
method AllocateStatement(ByRef hstmt) as %Status
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.
%BinaryOpens in a new tab property that provides the current connection handle to the ODBC-compliant data source.
Closes the connection.
method Disconnect() as %Status
%BinaryOpens in a new tab property that provides the handle for the shared library, as currently in use. This is set when you connect.
%StringOpens in a new tab property that provides the name of the shared library currently in use. This is set when you connect.
%StringOpens in a new tab property that provides the data source name of the ODBC-compliant data source to which you want to connect.
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.
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.
%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
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.
Establishes a connection, using configuration file entries to determine the DSN, user name, and password.
method GetConnection(conn, timeout) as %Status
Returns the current version of the shared library.
method GetGTWVersion() as %Integer
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
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.
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.
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.
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.
%StringOpens in a new tab property that provides the associated password.
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.
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.
%IntegerOpens in a new tab property that provides the SQL code returned by the last call (if any).
Unloads the shared library for the ODBC SQL Gateway from the process memory.
method UnloadDLL() as %Status
%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.
