Choosing a DataBlade API Function

Table 53 shows the functions that the DataBlade API provides to send SQL statements to the database server for execution.

As the preceding table shows, you need to consider the following factors when deciding which DataBlade API statement-execution function to use:

• What type of SQL statement do you need to send?
• Does your SQL statement contain input parameters?
• If the SQL statement is a query, can you use an implicit cursor to access the retrieved rows?

Choose the DataBlade API statement-execution function that is appropriate for the needs of your DataBlade API application.

Type of Statement

The DataBlade API statement-execution functions can execute the following types of SQL statements:

• An SQL statement that does not return rows of data (is not a SELECT statement and not an EXECUTE FUNCTION statement that executes an iterator function)

  Most SQL statements do not return rows. For example, all data definition (DDL) statements and most data manipulation (DML) statements return only a status to indicate the statement's success.

• An SQL statement that does return one or more rows of data

  The following SQL statements return rows:

  • SELECT statement
  • EXECUTE FUNCTION statement, when the user-defined function returns more than one row of data

  An SQL statement that returns rows is often called a query because it asks the database server to answer a question: which rows match?

The following table shows how to choose a DataBlade API statement-execution function based on the type of SQL statement.

Prepared Statements and Input Parameters

A prepared SQL statement is the parsed version of an SQL statement. The database server prepares an SQL statement for execution at a later time. Preparing a statement enables you to separate the parsing and execution phases of the statement execution. When you prepare a statement, you send the statement to the database server to be parsed. The database server checks the statement for syntax errors and creates an optimized version of the statement for execution.

You need to prepare an SQL statement only once. You can then execute the statement multiple times. Each time you execute the statement, you avoid the parsing phase. Prepared statements are useful for SQL statements that execute often in your DataBlade API module.

SQL statements that have missing column or expression values are called parameterized statements because you use input parameters as placeholders for missing column or expression values. An input parameter is a placeholder in an SQL statement that indicates that the actual column value is provided at runtime. You can specify input parameters in the statement text representation of an SQL statement for either of the following reasons:

• A column or expression value is unknown at the time you prepare the SQL statement.
• A column or expression value changes for each execution of the SQL statement.

For a parameterized SQL statement, your DataBlade API module must provide the following information to the database server for each of its input parameters.

You can also obtain information about the input parameters after the parameterized statement is prepared. For more information, see Obtaining Input-Parameter Information.

A DataBlade API module can prepare an SQL statement for the following reasons:

• To increase performance by reducing the number of times that the database server parses and optimizes the statement
• To execute a parameterized SQL statement and provide different input-parameter values each time the statement executes

The following table shows how to choose a DataBlade API statement-execution function based on whether the SQL statement needs to be prepared.

The mi_exec_prepared_statement( ) or mi_open_prepared_statement( ) function provides argument values for specifying the input-parameter values when the function executes the statement. You can also use these functions to execute prepared statements that do not have input parameters.

Queries and Implicit Cursors

When a DataBlade API statement-execution function executes a query, the function must create a place to hold the resulting rows. Each of these functions (mi_exec( ), mi_exec_prepared_statement( ), or mi_open_prepared_statement( )) automatically creates a row cursor (often called simply a cursor). The row cursor is an area of memory that serves as a holding place for rows that the database server has retrieved.

The simplest way to hold the rows of a query is to use an implicit cursor, which is defined with the following characteristics.

Cursor Characteristic

Restriction

Read-only

You can only examine the contents of the row cursor. You cannot modify these contents.

Sequential

A sequential cursor allows movement through the rows of the cursor in the forward direction only. You cannot go backward through the cursor. To reaccess a row that you have already accessed, you must close the cursor, reopen it, and move to the desired row.

Most DataBlade API modules can use an implicit cursor for accessing rows. However, if the cursor characteristics of the implicit cursor are not adequate for the needs of your DataBlade API module, you can define an explicit cursor with any of the following cursor characteristics.

Cursor Characteristic

Description

Cursor type

In which direction does the cursor enable you to access rows? You can choose a sequential cursor or a scroll cursor.

Cursor mode

Which operations are valid on the rows in the cursor? You can choose read-only or update mode.

Cursor lifespan

How long does the cursor remain open? You can choose whether to use a hold cursor.

For more information on these cursor characteristics, see Defining an Explicit Cursor.

The following table shows how to choose a DataBlade API statement-execution function based on the type of cursor that the query requires.

With the mi_open_prepared_statement( ) function, you can specify an explicit cursor to hold the query rows. In addition, you can assign a name to the cursor that you can use in other SQL statements.