sql()

Connects to a Rules Engine and executes a SQL statement.

This function may be used in the Implementation field of methods of custom REST and SOAP services (that is, services defined in the USoft Service Definer that have Functional Type = USoft REST Default Provider or USoft SOAP Default Provider).

All constructs listed in this help topic will open a connection to the database, execute a statement, and then automatically close the connection. To keep the connection open and execute multiple statements using that same connection, see the openConnection() functions.

For services where the client does not need to provide credentials, you must pass a username and password on behalf of the client. Use the sql( outputClass, connection, userName, password, statementName, inputParameters ) construct at the end of this help topic.

This function must be used if the client does not need to provide credentials. You provide a username and password on behalf of the client.

Opens a connection to the Rules Engine using a specified username/password combination, executes a SQL statement, then closes the connection.

To keep the connection open and execute multiple statements using the same username/password combination, see the openConnection() function.

Returns the result set retrieved by the statement, serialized to the specified output class, except when the null value was passed as the first parameter.

Syntax

public <T> T sql( Class<T> outputClass, String connection, String userName, String password, String statementname, Object[] inputParameters )

throws InstantiationException, IllegalArgumentException, SQLException

The parameter outputClass may be used to identify the type of object that the caller requires the function to return. This is typically, but not necessarily, the name of a structure defined in the Services Definer. For details on allowed output classes, see " Java output classes returned by sql() ". The outputClass parameter is required, but if you do not wish to pass a value, you can pass the null value for it.

The parameter connection identifies the name of a specific connection defined in USoft Definer. The connection parameter is required, but if you do not wish to identify a specific connection, you can pass the wildcard string value "*" for it. In that case, the connection is randomly chosen from the list of Connections that Service Definer associates with the Application for which the statement is defined.

The required userName and password are the username and password used to log on to the Rules Engine.

The required statementName is the name of the SQL statement as defined in USoft Service Definer.

inputParameters may be used to identify one or more objects that match host variables used in the statement (notated using the ':' prefix). The inputParameters parameter is required, but if the statement does not use any host variables, you can call the function passing the parameter value new Object[0] instead of (a) specific value(s).

Example 1

Reservation result = (Reservation)sql( Reservation.class, "MyConnection", "JONATHAND", "Frt98WqA", "GET_CURRENT_BOOKING" , new Object[]{ "TOUR_BOOKINGS", booking_id } );

Example 2

This call passes only the name of the SQL statement, and a username/password combination. The "GET_CURRENT_BOOKINGS" statement does not take any host variables:

result = sql( null, "*", "JONATHAND", "Frt98WqA", "GET_CURRENT_BOOKINGS" , new Object[0] );

Exceptions

	Thrown if
InstantiationException	No connection with the specified name is found among the connections associated with the same application as the statement, or no connections are found associated with that application.
IllegalArgumentException	No statement with the specified name is defined for the service, or the input parameters do not match the host variables defined in the statement.
SQLException	An error is raised by the Rules Engine.