Skip Headers

Oracle TopLink Developer's Guide 10g Release 3 (10.1.3) B13593-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# Using SQL Calls

The TopLink expression framework enables you to define complex queries at the object level. If your application requires a more complex query or one that accesses data or stored procedures directly, you can specify a custom SQL string in an SQL `Call` object and provide that `Call` object to any query.

You can also specify an SQL string directly on `DatabaseQuery`. For more information, see "Specifying a Custom SQL String in a DatabaseQuery".

When using SQL calls, you can use a `ReturningPolicy` to control whether or not TopLink writes a parameter out or retrieves a value generated by the database. For more information, see "Configuring Returning Policy"

This section describes the following:

- Using an SQLCall

- Using a StoredProcedureCall

- Using a StoredFunctionCall

## Using an SQLCall

You can provide an `SQLCall` object to any query instead of an expression, but the SQL string contained in the `SQLCall` must return all data required to build an instance of the queried class.

The SQL string can be a complex SQL query, a stored procedure call, or a stored function call. You can specify input, output, and input/output parameters.

You can invoke an `SQLCall` through a session query method (as Example 98-39 illustrates) or through a `DatabaseQuery`.

Example 98-39 Session Read Query With Custom SQL

    Employee employee = (Employee) session.executeSelectingCall(
        new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44")
    );

WARNING: Allowing an unverified SQL string to be passed into methods makes your application vulnerable to SQL injection attacks.

TopLink assumes that a token in the custom SQL string of an `SQLCall` is a parameter if it is prefixed with one or more number signs (`#` ). You can bind values to these parameters using query API, as the following sections describe:

- Specifying a SQLCall Input Parameter

- Specifying a SQLCall Output Parameter

- Specifying a SQLCall Input / Output Parameter

- Specifying a SQLCall Parameter Type

### Specifying a SQLCall Input Parameter

In Example 98-40, you specify `last_name` as an input parameter by prefixing its name with one number sign (#). Example 98-41 shows how to bind a value to this input parameter when you execute the query.

Example 98-40 Specifying an SQLCall with an Input Parameter Using the # Prefix

    SQLCall sqlCall = new SQLCall(
        "INSERT INTO EMPLOYEE (L_NAME) VALUES (#last_name)"
    );

Example 98-41 Executing an SQLCall with an Input Parameter

    UpdateObjectQuery query = new UpdateObjectQuery(myEmployee);
    query.setCall(sqlCall);
    query.addArgument("last_name");   // input
    query.bindAllParameters();

    Vector arguments = new Vector();
    arguments.add("MacDonald");
    session.executeQuery(query, arguments);

### Specifying a SQLCall Output Parameter

In Example 98-42, you specify `employee_id` as an output parameter by prefixing its name with three number signs (`###`). You specify the type of the output parameter with `SQLCall` method `setCustomSQLArgumentType` (see "Specifying a SQLCall Parameter Type"). You continue to specify `last_name` as an input parameter by prefixing its name with `#`.

Example 98-42 Specifying a SQLCall with an Output Parameter Using the ### Prefix

    SQLCall sqlCall = new SQLCall(
        "INSERT INTO EMPLOYEE (L_NAME) VALUES (#L_NAME) RETURNING EMP_ID INTO ###employee_id"
    );
    sqlCall.setCustomSQLArgumentType("employee_id", Integer.class);

Example 98-43 Executing a SQLCall with an Output Parameter

    UpdateObjectQuery query = new UpdateObjectQuery(myEmployee);
    query.setCall(sqlCall);
    query.addArgument("last_name");   // input
    query.addArgument("employee_id"); // output
    query.bindAllParameters();

    Vector args = new Vector();
    args.add("MacDonald");

    DatabaseRecord record = (DatabaseRecord)((Vector)getSession().executeQuery(
        query, args)).firstElement();
    Integer employeeID = new Integer(((Number)record.get("employee_id")).intValue());

You can also obtain results for an output parameter declared to be of type `CURSOR` (see "Cursors and SQLCall").

### Specifying a SQLCall Input / Output Parameter

In Example 98-44, you specify `in_out` as an input and output parameter by prefixing its name with four number signs (`####`). The type of the input value determines the type of the output value. In this example, a `String` ("MacDonald") is passed in and the output value (for `EMP_ID`) is returned as a `String`.

Example 98-44 Specifying an Input and Output Parameter Using the #### Prefix

    SQLCall sqlCall = new SQLCall(
        "INSERT INTO EMPLOYEE (L_NAME) VALUES (####in_out) RETURNING EMP_ID INTO ####in_out"
    );

Example 98-45 Executing a SQLCall with an Input and Output Parameter

    UpdateObjectQuery query = new UpdateObjectQuery(myEmployee);
    query.setCall(sqlCall);
    query.addArgument("in_out");   // input and outpu
    query.bindAllParameters();

    Vector args = new Vector();
    args.add("MacDonald");

    DatabaseRecord record = (DatabaseRecord)((Vector)getSession().executeQuery(
        query, args)).firstElement();
    Integer employeeID = new Integer(record.get("in_out")); // out value has same type as input

### Specifying a SQLCall Parameter Type

If you map a parameter or identify it in a `ReturningPolicy` (see "Configuring Returning Policy"), you do not need to explicitly specify the parameter's type.

Otherwise, you must explicitly specify the parameter's type using `SQLCall` method `setCustomSQLArgumentType`.

If TopLink cannot determine the type of the parameter, it throws a `ValidationException`.

In Example 98-46, output parameter `EMP_ID` is mapped or identified in a `ReturningPolicy` but output parameter `AGE` is not: you must specify its type (`Integer`) explicitly.

Example 98-46 Specifying Parameter Types in a SQLCall

    SQLCall sqlCall = new SQLCall(
        "INSERT INTO EMPLOYEE (L_NAME) VALUES (#L_NAME) RETURNING EMP_ID, AGE INTO ###EMP_ID, ###AGE"
    );
    sqlCall.setCustomSQLArgumentType("AGE", Integer.class);

## Using a StoredProcedureCall

You can provide a `StoredProcedureCall` object to any query instead of an expression or a SQL string, but the procedure must return all data required to build an instance of the class you query.

Example 98-47 A Read-All Query with a Stored Procedure

    ReadAllQuery readAllQuery = new ReadAllQuery();
    call = new StoredProcedureCall();
    call.setProcedureName("Read_All_Employees");
    readAllQuery.setCall(call);
    Vector employees = (Vector) session.executeQuery(readAllQuery);

Using a `StoredProcedureCall`, you can access the following:

- Specifying an Input Parameter

- Specifying an Output Parameter

- Specifying an Input / Output Parameter

- Using an Output Parameter Event

Note: You no longer need to use `DatabaseQuery` method `bindAllParameters` when using a `StoredProcedureCall` with `OUT` or `INOUT` parameters. However, you should always specify the Java type for all `OUT` and `INOUT` parameters. If you do not, be aware of the fact that they default to type `String`.

### Specifying an Input Parameter

In Example 98-48, you specify the parameter `POSTAL_CODE` as an input parameter using the `StoredProcedureCall` method `addNamedArgument`, and you can specify the value of the argument using method `addNamedArgumentValue`.

Example 98-48 Stored Procedure Call with an Input Parameter

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("CHECK_VALID_POSTAL_CODE");
    call.addNamedArgument("POSTAL_CODE");
    call.addNamedArgumentValue("L5J1H5");
    call.addNamedOutputArgument(
        "IS_VALID",    // procedure parameter name
        "IS_VALID",    // out argument field name
        Integer.class  // Java type corresponding to type returned by procedure
    );
    ValueReadQuery query = new ValueReadQuery();
    query.setCall(call);
    Number isValid = (Number) session.executeQuery(query);

The order in which you add arguments must correspond to the order in which you add argument values. In Example 98-49, the argument `NAME` is bound to the value `Juliet` and the argument `SALARY` is bound to the value `80000`.

Example 98-49 Matching Arguments and Values in a Stored Procedure Call

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("CHECK_VALID_POSTAL_CODE");
    call.addNamedArgument("NAME");
    call.addNamedArgument("SALARY");
    call.addNamedArgumentValue("Juliet");
    call.addNamedArgumentValue(80000);

### Specifying an Output Parameter

Output parameters enable the stored procedure to return additional information. You can use output parameters to define a `readObjectQuery` if they return all the fields required to build the object.

In Example 98-50, you specify the parameter IS_VALID as an output parameter using the `StoredProcedureCall` method `addNamedOutputArgument`.

Example 98-50 Stored Procedure Call with an Output Parameter

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("CHECK_VALID_POSTAL_CODE");
    call.addNamedArgument("POSTAL_CODE");
    call.addNamedOutputArgument(
        "IS_VALID",    // procedure parameter name
        "IS_VALID",    // out argument field name
        Integer.class  // Java type corresponding to type returned by procedure
    );
    ValueReadQuery query = new ValueReadQuery();
    query.setCall(call);
    query.addArgument("POSTAL_CODE");
    Vector parameters = new Vector();
    parameters.addElement("L5J1H5");
    Number isValid = (Number) session.executeQuery(query,parameters);

Note: Not all databases support the use of output parameters to return data. However, because these databases generally support returning result sets from stored procedures, they do not require output parameters.

If you are using an Oracle database, you can make use of TopLink cursor and stream query results. For more information, see "Stored Procedure Cursor Output Parameters".

### Specifying an Input / Output Parameter

In Example 98-51, you specify the parameter `LENGTH` as an input/output parameter and specify the value of the argument when it is passed to the stored procedure using the `StoredProcedureCall` method `addNamedInOutputArgumentValue`. If you do not want to specify a value for the argument, use method `addNamedInOutputArgument`.

Example 98-51 Stored Procedure Call with an Input/Output Parameter

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("CONVERT_FEET_TO_METERs");
    call.addNamedInOutputArgumentValue(
        "LENGTH",          // procedure parameter name
        new Integer(100),  // in argument value
        "LENGTH",          // out argument field name
        Integer.class      // Java type corresponding to type returned by procedure
    )
    ValueReadQuery query = new ValueReadQuery();
    query.setCall(call);
    Integer metricLength = (Integer) session.executeQuery(query);

### Using an Output Parameter Event

TopLink manages output parameter events for databases that support them. For example, if a stored procedure returns an error code that indicates that the application wants to check for an error condition, TopLink raises the session event `OutputParametersDetected` to allow the application to process the output parameters.

Example 98-52 Stored Procedure with Reset Set and Output Parameter Error Code

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("READ_EMPLOYEE");
    call.addNamedArgument("EMP_ID");
    call.addNamedOutputArgument(
        "ERROR_CODE",    // procedure parameter name
        "ERROR_CODE",    // out argument field name
        Integer.class  // Java type corresponding to type returned by procedure
    );
    ReadObjectQuery query = new ReadObjectQuery();
    query.setCall(call);
    query.addArgument("EMP_ID");
    ErrorCodeListener listener = new ErrorCodeListener();
    session.getEventManager().addListener(listener);
    Vector args = new Vector();
    args.addElement(new Integer(44));
    Employee employee = (Employee) session.executeQuery(query, args);

## Using a StoredFunctionCall

You use a `StoredProcedureCall` to invoke stored procedures defined on databases that support them. You can also use a `StoredFunctionCall` to invoke stored functions defined on databases that support them, that is, on databases for which the `DatabasePlatform` method `supportsStoredFunctions` returns `true`.

In general, both stored procedures and stored functions let you specify input parameters, output parameters, and input and output parameters. For more information, see "Using a StoredProcedureCall". However, stored procedures need not return values, while stored functions always return a single value.

The `StoredFunctionCall` class extends `StoredProcedureCall` to add one new method: `setResult`. Use this method to specify the name (and alternatively both the name and type) under which TopLink stores the return value of the stored function.

When TopLink prepares a `StoredFunctionCall`, it validates its SQL and throws a `ValidationException` under the following circumstances:

- If your current platform does not support stored functions. Stored functions are supported only for Oracle.

- If you fail to specify the return type

In Example 98-53, note that the name of the stored function is set using `StoredFunctionCall` method `setProcedureName`.

Example 98-53 Creating a StoredFunctionCall

    StoredFunctionCall functionCall = new StoredFunctionCall();
    functionCall.setProcedureName("CHECK_VALID_EMPLOYEE");
    functionCall.addNamedArgument("EMP_ID");
    functionCall.setResult("FUNCTION_RESULT", String);
    ValueReadQuery query = new ValueReadQuery();
    query.setCall(functionCall);
    query.addArgument("EMP_ID");
    Vector args = new Vector();
    args.addElement(new Integer(44));
    String valid = (String) session.executeQuery(query, args);