Skip to main content

Using the XDBC Gateway

The following section gives an overview of the basic usage of the various SQL APIs provided by the %XDBC package. In particular, this page covers:

  • Statements

  • Prepared statements

  • Stored procedures

  • Result sets

The examples in this page build on the example table created in the Statements section and assume that you already know how to configure a connection between InterSystems IRIS and your external database with the XDBC Gateway.

Note:

Some functionality provided by the %XDBC package and its methods might be limited or unavailable depending on the features of the external database. For example, Microsoft SQL Server does not support setSchema()Opens in a new tab and InterSystems IRIS only supports two transaction isolation levels. For details, refer to your external database's documentation.

Statements

To create a statement, use CreateStatement():

set conn = ##class(%XDBC.Gateway.Connection).GetConnection("ExampleJDBC")
set statement = conn.CreateStatement()

To Execute a statement, use one of the following depending on the statement type:

The following example uses the various statement execution methods to create and insert values into a table:

  1. CREATE a table:

    set res = statement.ExecuteUpdate("CREATE TABLE Contacts (first_name VARCHAR(50), last_name VARCHAR(50), phone VARCHAR(20))")

  2. INSERT data into the table. For demonstration purposes, this example uses both individual and batch statements:

    do statement.ExecuteUpdate("INSERT INTO Contacts VALUES ('John', 'Doe', '892-555-3819')")
do statement.AddBatch("INSERT INTO Contacts VALUES ('Jan', 'Kowalski', '563-555-0662')")
do statement.AddBatch("INSERT INTO Contacts VALUES ('Anna', 'Kowalska', '779-555-9984')")
do statement.ExecuteBatch()

  3. Query the table:

    set res = statement.ExecuteQuery("SELECT * FROM Contacts")
do res.%Display()

    Output:

    first_name   last_name   phone
John         Doe         892-555-3819
Jan          Kowalski    563-555-0662
Anna         Kowalska    779-555-9984

3 Rows(s) Affected

Prepared Statements

You can parameterize statements with prepared statements. To create a prepared statement, use XDBC.Gateway.Connection:PrepareStatement() and use the question mark (?) character for each parameter:

set conn = ##class(%XDBC.Gateway.Connection).GetConnection("ExampleJDBC")
set prepared = conn.PrepareStatement("INSERT INTO Contacts(first_name, last_name, phone) VALUES(?,?,?)")

Parameters are 1-indexed in left-to-right ascending order. To set the parameter, use the Set() method that corresponds to its type (these vary between JDBCOpens in a new tab and ODBCOpens in a new tab). The above column types are all VARCHARs, so set them with SetString():

do prepared.SetString(1, "Jane")
do prepared.SetString(2, "Doe")
do prepared.SetString(3, "391-555-1883")

You can then execute the prepared statement with an Execute method, depending on the statement type:

  • ExecuteUpdate(): Executes a prepared DDL statement (such as INSERT, UPDATE, and DELETE) and returns the number of rows affected.

  • ExecuteQuery(): Executes a prepared SELECT statement and returns the results as an instance of %XDBC.Gateway.ResultSet.

  • ExecuteBatch(): Executes a batch of prepared statements and, if all statements succeed, returns the number of rows affected as an array. You can add statements with AddBatch().

  • Execute(): Executes any prepared statement and returns a boolean if results were returned by the statement. A result in this context is either a ResultSet (retrieved with getResultSet()) or the number of rows updated (retrieved with getUpdateCount()). If Execute() returns multiple results, you can retrieve them with getMoreResults() (after retrieving the first result with either getResultSet() or getUpdateCount()). In general, you should only use this for statements that can return multiple types of results.

The above example is an INSERT statement, so you can execute it with ExecuteUpdate():

do prepared.ExecuteUpdate()

Stored Procedures

Support for stored procedures differs slightly between JDBC and ODBC.

In JDBC, you can use an existing stored procedure to create an instance of CallableStatementOpens in a new tab and then call it with Execute():

  1. Connect to the external database with JDBC:

    set conn = ##class(%XDBC.Gateway.Connection).GetConnection("ExampleJDBC")

  2. Create the stored procedure:

    do statement.ExecuteUpdate("CREATE PROCEDURE addCountryCode(IN prefix VARCHAR(20)) BEGIN UPDATE Contacts SET phone = {fn CONCAT(:prefix, phone)} WHERE phone NOT LIKE {fn CONCAT(:prefix, '+%')}; SELECT * FROM Contacts; END")

  3. Create an instance of CallableStatementOpens in a new tab with PrepareCall(), specifying a CALL to the stored procedure and using ? (question marks) as placeholders for each argument:

    set callable = conn.PrepareCall("CALL addCountryCode(?)")

  4. Set the arguments for the procedure call with the Set() method that matches the type of your arguments. In this example, the argument is a VARCHAR, so use SetString():

    do callable.SetString(1, "+1")

    For a full list of Set() methods, see CallableStatementOpens in a new tab.

  5. Call the stored procedure with Execute():

    do callable.Execute()

    Results:

    set statement = conn.CreateStatement()
set res = statement.ExecuteQuery("SELECT * FROM Contacts")
do res.%Display()

first_name   last_name   phone
John         Doe         +1892-555-3819
Jan          Kowalski    +1563-555-0662
Anna         Kowalska    +1779-555-9984

3 Rows(s) Affected

In ODBC, you can specify the CALL to the stored procedure directly with a StatementOpens in a new tab or parameterize it with PreparedStatementOpens in a new tab.

To use a Statement:

  1. Connect to the external database with ODBC:

    set conn = ##class(%XDBC.Gateway.Connection).GetConnection("ExampleODBC")

  2. Create a Statement:

    set statement = conn.CreateStatement()

  3. Create the stored procedure:

    do statement.ExecuteUpdate("CREATE PROCEDURE addCountryCode(IN prefix VARCHAR(20)) BEGIN UPDATE Contacts SET phone = {fn CONCAT(:prefix, phone)} WHERE phone NOT LIKE {fn CONCAT(:prefix, '+%')}; SELECT * FROM Contacts; END")

  4. Call the stored procedure with the appropriate Execute() method and CALL. In this case, the stored procedure contains an INSERT statement, so use ExecuteUpdate():

    do statement.ExecuteUpdate("CALL addCountryCode('+1')")

    Results:

    set statement = conn.CreateStatement()
set res = statement.ExecuteQuery("SELECT * FROM Contacts")
do res.%Display()

first_name   last_name   phone
John         Doe         +1892-555-3819
Jan          Kowalski    +1563-555-0662
Anna         Kowalska    +1779-555-9984

3 Rows(s) Affected

To use a PreparedStatement:

  1. Connect to the external database with ODBC:

    set conn = ##class(%XDBC.Gateway.Connection).GetConnection("ExampleODBC")

  2. Create the stored procedure:

    do statement.ExecuteUpdate("CREATE PROCEDURE addCountryCode(IN prefix VARCHAR(20)) BEGIN UPDATE Contacts SET phone = {fn CONCAT(:prefix, phone)} WHERE phone NOT LIKE {fn CONCAT(:prefix, '+%')}; SELECT * FROM Contacts; END")

  3. Create a PreparedStatement:

    set prepared = conn.PrepareStatement("CALL addCountryCode(?)")

  4. Set the arguments for the procedure call with the Set() method that matches the type of your arguments. In this example, the argument is a VARCHAR, so use SetString():

    do prepared.SetString(1, "+1")

    For a full list of Set() methods, see PreparedStatementOpens in a new tab.

  5. Call the stored procedure with the appropriate Execute() method. In this case, the stored procedure contains an UPDATE statement, so use ExecuteUpdate():

    do prepared.ExecuteUpdate()

    Results:

    set statement = conn.CreateStatement()
set res = statement.ExecuteQuery("SELECT * FROM Contacts")
do res.%Display()

first_name   last_name   phone
John         Doe         +1892-555-3819
Jan          Kowalski    +1563-555-0662
Anna         Kowalska    +1779-555-9984

3 Rows(s) Affected

Result Sets

Query results are returned in subclasses of the %XDBC.Gateway.ResultSetOpens in a new tab interface, the particular implementation of which depends on your connection method (JDBCOpens in a new tab or ODBCOpens in a new tab). Result sets can be conceptualized as a set of rows and a cursor, which points to a row after a call to Next()Opens in a new tab.

To access a result set row-by-row, use Next() to verify that a next row exists (returns 1 if the next row exists and 0 otherwise) and advance the cursor to the next row, and use Get()Opens in a new tab to retrieve the data in the column specified by either a column index or name.

The following example queries the Contacts table which contains the data:

| first_name | last_name |    phone     |
| ---------- | --------- | ------------ |
| John       | Doe       | 892-555-3819 |
| Jan        | Kowalski  | 563-555-0662 |
| Anna       | Kowalska  | 779-555-9984 |

  1. Query the table to get a ResultSet:

    set res = statement.ExecuteQuery("SELECT * FROM Contacts")

  2. Use Next() to advance the cursor and verify that a valid row exists, then get the data from each column with Get(). This example passes both column indices and names to Get() for demonstration purposes:

    while result.Next() {
    write !, "Name: ", res.Get(1), " ", res.Get("last_name"), !,  "Phone: ", res.Get(3)
}

    Output:

    Name: John Doe
Phone: 892-555-3819

Name: Jan Kowalski
Phone: 1563-555-0662

Name: Anna Kowalska
Phone: 779-555-9984

To retrieve an entire row as a list, use GetRow():

do res.Next()
zwrite res.GetRow()
$lb("John", "Doe", "892-555-3819")

To display all rows at once, use %Display(). This advances the cursor to the end of the result set:

do res.%Display()

first_name   last_name   phone
John         Doe         892-555-3819
Jan          Kowalski    563-555-0662
Anna         Kowalska    779-555-9984

3 Rows(s) Affected

Previous sectionConnecting with the XDBC Gateway
FeedbackOpens in a new tab