Foundation Class Library (Java):

IPreparedQuery interface (deprecated)

IPreparedQuery is deprecated and is provided for backward compatibility only. New applications should use the java.sql.PreparedStatement interface from the JDBC Core API.

The IPreparedQuery interface represents a prepared flat query. An IPreparedQuery object contains a SQL statement that has been compiled. This is what makes a statement "prepared." An AppLogic uses a prepared query when it needs to execute a SQL statement multiple time with different parameters.

For example, if an AppLogic runs an INSERT statement several times, each time with a different set of values to insert into the table, using a prepared query involves the following steps:

Prepare (compile) the INSERT statement with placeholder parameters whose values will be specified later.

Specify a set of parameter values.

Execute the prepared query.

Specify another set of parameter values.

Execute the prepared query.

By preparing the SQL statement, the database needs to compile the statement only once. Without prepared statements, the database must recompile each statement every time it is executed, which is less efficient.

To create an instance of the IPreparedQuery interface, use prepareQuery( ) in the IDataConn interface (deprecated), as shown in the following example:

IPreparedQuery pqry;

pqry = conn.prepareQuery(0, qry, null, null);

Package
com.kivasoft

Methods


Name	Description
execute( )	Executes a prepared query.
setParams( )	Specifies the parameters and flags for a prepared query.

Example

// Create the data connection

IDataConn conn;

conn = createDataConn(0,GX_DA_DAD_DRIVERS.GX_DA_DRIVER_DEFAULT, 
"Orders", "Orders", "user", "password");

// Create the flat query and prepared query objects

IQuery qry = createQuery();

IPreparedQuery pqry;

IResultSet rs1, rs2;

// Set up the INSERT statement

qry.setSQL("INSERT INTO TABLE Products (ProductName, QuantityPerUnit) 
VALUES (:name, :quant)");

// Prepare the flat query

pqry = conn.prepareQuery(0, qry, null, null);

// Specify a set of query parameters, then execute

IValList params = GX.CreateValList()

params.setValString(":name","Chicken Dumplings");

params.setValString(":quant", "48 packages");

rs1 = pqry.execute(0, params, null, null);

. . . process rs1 . . .

// Specify different set of query parameters, then execute

params.setValString(":name", "Rice Noodles");

params.setValString(":quant", "96 packages");

rs2 = pqry.execute(0, params, null, null);

. . . process rs2 . . .

execute( )
Executes a prepared query.

Syntax

public IResultSet execute(	
	int dwFlags,
	IValList pParams,
	ITrans pTrans,
	IValList pProps)

dwFlags. Specifies flags used to execute this prepared query. To activate result set buffering, specify GX_DA_EXECUTEQUERY_FLAGS.GX_DA_RS_BUFFERING. Otherwise, specify zero.

pParams. IValList object that contains parameters to pass to the prepared query. Parameters are used to execute the query.

pTrans. ITrans object that contains the transaction associated with this query, or null for no transaction.

pProps. IValList object that contains query properties, or null for no properties. After instantiating an object of the IValList interface, set any of the following properties:

RS_BUFFERING turns on result set buffering when set to "TRUE".

RS_INIT_ROWS specifies the initial size of the buffer, in number of rows. If the result set size exceeds this setting, a fetchNext( ) call will return the error GX_DA_FETCHNEXT_RESULTS.GX_DA_BUFFER_EXCEEDED and result set buffering will be turned off.

RS_MAX_ROWS specifies the maximum number of rows for the buffer. If the result set size exceeds this setting, a fetchNext( ) call will return the error GX_DA_FETCHNEXT_RESULTS.GX_DA_BUFFER_EXCEEDED and result set buffering will be turned off.

RS_MAX_SIZE specifies the maximum number of bytes for the buffer.

If RS_BUFFERING is enabled and if the optional parameters are not specified, the global values in the registry are used instead.

Usage
Use execute( ) to run a prepared query. If the command contains parameters, instantiate an IValList object and use setValString( ) or setValInt( ) in the IValList interface to specify the parameter values to pass to the command.

Return Value
IResultSet object, or null for failure (such as an invalid parameter).

Example 1

// Create the data connection

IDataConn conn;

conn = createDataConn(0,GX_DA_DAD_DRIVERS.GX_DA_DRIVER_DEFAULT, 
"Orders", "Orders", "user", "password");

// Create the flat query and prepared query objects

IQuery qry = createQuery();

IPreparedQuery pqry;

IResultSet rs;

// Set up the INSERT statement

qry.setSQL("INSERT INTO TABLE Products (ProductName, QuantityPerUnit) 
VALUES (:name, :quant)");

// Prepare the flat query

pqry = conn.prepareQuery(0, qry, null, null);

// Specify a set of query parameters, then execute

IValList params = GX.CreateValList();

params.setValString(":name","Chicken Dumplings");

params.setValString(":quant", "48 packages");

rs = pqry.execute(0, params, null, null);

. . . process result set . . .

// Specify different set of query parameters, then execute

params.setValString(":name", "Rice Noodles");

params.setValString(":quant", "96 packages");

rs = pqry.execute(0, params, null, null);

. . . process result set . . .

Example 2

// Set up result set buffering for prepared query

// . . . data connection and flat query already set up . . .

IPreparedQuery pqry;

IValList props;

props = GX.CreateValList();

// Turn on result set buffering

props.setValString("RS_BUFFERING", "TRUE");

// Specify the maximum number of rows to buffer

props.setValInt("RS_MAX_ROWS", 50);

pqry = conn.prepareQuery(0, qry, null, props);

params.setValString(":name","Chicken Dumplings");

params.setValString(":quant", "48 packages");

IResultSet rs

rs = pqry.execute(GX_DA_EXECUTEQUERY_FLAGS.GX_DA_RS_BUFFERING, params, 
null, null);

. . . process result set . . .

ITrans interface (deprecated)

prepareQuery( ) in the IDataConn interface (deprecated)

setParams( )
Specifies the parameters for a prepared query.

Syntax

public int setParams(
	int dwFlags
	IValList pParams)

dwFlags. Specify zero (0).

pParams. Pointer to an IValList object that contains parameters to pass to the prepared query.

Usage
To pass parameters to the prepared query using setParams( ), you must pass NULL for the pParams parameter in execute( ).

Return Value
GXE.SUCCESS if the method succeeds.

ITrans interface (deprecated)

prepareQuery( ) in the IDataConn interface (deprecated)