Chapter 2 Working with the Component Builder

Creating AI components

AI for Stored Procedures components are EAServer components that provide access to one or more stored procedures. A component contains one method for each stored procedure it exposes, and each method invokes exactly one stored procedure.

Every component is associated with a single connection. The choice of connection determines which stored procedures the component's methods can invoke. All stored procedures exposed in a component must reside on a single server.

The Component Builder allows you to create components, add methods to components, and deploy components to EAServer.

You can add methods to components either manually or automatically. For more information, see "Creating methods".

Once you have created a component and added one or more methods, you can deploy the component to EAServer, where it is ready to used by EAServer client applications or other EAServer components.

Component properties

When you create a component, you must supply the following information in the Component Builder:

Table 2-2: Component properties

Property	Description
Component name	Name that is used for the component when it is deployed to EAServer.
Java package name	Components are implemented as Java classes. Java package names are used to organize Java classes hierarchically. Typically, Java package names are in reverse Internet domain name format (as in com.xyzcorp.app_name, where app_name is the name of the application you are building).    Java package names are not the same as EAServer package names.
Connection name	Each component requires a connection. When components are created in the Component Builder, you can select an existing connection, or create a new one. The choice of connection determines which stored procedures can be exposed in the component.

Creating methods

Each method in a component provides access to a stored procedure. You can create methods automatically or manually, depending on whether the JDBC driver you are using can access the stored procedure metadata.

See Appendix A, "Target Types" for information that affects automatic and manual method creation.

Creating methods automatically

When the Component Builder accesses the database through the component's connection, it allows you to create a method automatically. You browse the database and select a stored procedure for the method to invoke.

When you create methods automatically, the Component Builder attempts to retrieve information about the stored procedure you select, such as its name and its parameters. The Component Builder retrieves information for each parameter including the parameter mode, datatype, whether it has a return value, and whether it can accept a NULL value. The Component Builder then uses this information to create a method to invoke the stored procedure.

In some cases, JDBC drivers cannot retrieve complete information about a stored procedure or its parameters. If the information that the JDBC driver provides is incomplete, the Component Builder issues warning messages about missing information when the method is created. The Component Builder saves these messages; to view them, select the method in the Sybase Central tree view and click the Details tab.

When warnings are issued for a method, you should obtain the source code for the target stored procedure (usually SQL) and adjust the method definition in the Component Builder as needed. After you have edited the method definition to match the stored procedure, clear the warnings that appear in the Details view.

   Sybase strongly recommends that you become familiar with the target stored procedures that your components will access.

Creating methods manually

When the target cannot supply metadata about its stored procedures, you must create methods manually. To do so, enter information in the Target tab of the Method Properties dialog box (see Table 2-3), such as:

• Target stored procedure name
• Whether the target stored procedure has a return value
• Whether it returns result sets
• Information for each parameter in the stored procedure, including:
  • Parameter name
  • Mode (input, output, or input/output)
  • JDBC datatype
  • Scale (when the JDBC datatype is decimal or numeric)
  • Whether it can accept NULL values

When you create methods manually, you must fully understand the target stored procedure, so that you can enter this information correctly. The most accurate source for this information is the source code (usually SQL) for the stored procedure.

Method properties

For each method, you can define properties in the Method Properties dialog box. The General tab displays the method properties as they appear in EAServer and allows you to customize the method and parameter names. The Target tab displays and allows you to set properties for the stored procedure as it appears on the target.

Statement types

Methods can be defined to use any of the calling conventions supported by JDBC. These statement types include Statement, Prepared Statement, and Callable Statement. The calling conventions determine how the stored procedure is invoked at runtime in EAServer.

Table 2-4: Method statement types and calling conventions

Statement type	Calling convention
Statement	The component invokes the procedure through a java.sql.Statement.
Prepared Statement	The component invokes the procedure through a java.sql.PreparedStatement.
Callable Statement	The component invokes the procedure through a java.sql.CallableStatement . This is the default statement type.    Callable statements yield the best performance and should be used when supported by the target database.

   DirectConnect for MVS/DB2 RSPs Component methods that invoke DirectConnect for MVS/DB2 RSPs must use the Statement or Prepared Statement type. The Callable statement type is not supported.

Using Prepared Statements with DirectConnect

Component methods that invoke DirectConnect stored procedures using the Prepared Statement type require the JDBC LITERAL_PARAMS property be set to true for the connection the component uses. To define the LITERAL_PARAMS property:

1. Select the connection used by the component in the Stored Proc Component Builder, and choose the Properties menu item.
2. Choose the Connectivity tab page, and click Add to add a JDBC property.
3. Enter the name "LITERAL_PARAMS", the value "true", and click OK.

Statement templates

When you choose the statement types Statement or Prepared Statement, you must enter a template of the statement that should be sent to the server to invoke a target stored procedure when the corresponding method is executed.

Click the Compute button to create a default statement template that is derived from the catalog, schema, procedure name, catalog separator, and defined parameters. You can edit the template, provided that the server can understand its syntax. You can substitute parameters defined to a method at runtime by referring to them in the template as {parameter_name}. {parameter_name} is the name of one of the parameters defined in the Parameters field of the Method Properties dialog box.

   The template you create should not contain the question mark (?) character.

See Appendix A, "Target Types" for details on target support for various statement types.

Mapping stored procedures to methods

This section contains information about the relationships between target stored procedures and corresponding components in AI for Stored Procedures and EAServer.

When you add a method to a component, the Component Builder derives a name for the method from the name of the target stored procedure, with the first character in uppercase and the subsequent characters in lowercase.

   If necessary, the Component Builder removes spaces and underscores from the name to comply with EAServer method name restrictions.

The number of result sets declared in the Component Builder affects the method's return type. Table 2-5 shows how the number of result sets are mapped in EAServer:

Parameter information

For each method in an AI for Stored Procedures component, each parameter is either automatically derived from the server using JDBC metadata calls or is manually defined in the Component Builder. If parameter names are invalid, the Component Builder alters the parameter names to conform to EAServer restrictions. You can also use the Component Builder to alter parameter names.

The parameter mode specifies whether the parameter is used for input, output, or both input and output.

When you define the component automatically, for each method in a component, each of the procedure's input, output, or input/output parameters is added as a parameter for that method. If you manually define a component, you must manually define each parameter for each method.

A stored procedure may or may not have a return value. If it is defined to have a return value, add an output parameter to the method, and select the Return Value check box for that parameter.

   If you created the method automatically, the Component Builder may have already set the Return Value property for the parameter correctly

The return value for a stored procedure is mapped as an output parameter in the method.

Parameter datatypes

This section describes mapping that occurs from parameter types to EAServer IDL datatypes. The datatype for each field is either automatically derived using JDBC metadata calls or is manually defined in the Component Builder.

Because each supported target DBMS has a different set of native datatypes, each JDBC driver must map the native datatypes of the target to valid SQL datatypes. In addition, JDBC specifies the default mapping of SQL datatypes to Java datatypes.

The first two columns of Table 2-6 show the SQL-to-Java datatype mapping recommended by the JDBC 1.20 specification. AI for Stored Procedures uses this mapping to convert SQL datatypes in stored procedures to Java datatypes. The third column shows the mapping between the Java datatypes and the EAServer IDL datatypes that ultimately get mapped into the appropriate EAServer client datatype.

For more information about Java datatypes, see the discussion on datatype mapping in the JavaSoft JDBC Specification, version 1.20.

For more information about datatype mappings from third-party DBMSs to Java, see the documentation for that DBMS.

Parameters with NULL values

Most DBMSs and Sybase Open ServerConnect allow parameters to accept NULL values. However, many JDBC drivers cannot obtain this information about parameters from the procedure metadata when you create a component automatically. As a result, when the JDBC driver cannot return information about whether a parameter can accept NULL values, the Component Builder sets the default to not nullable. If it should be NULL, you can set this property in the Methods Properties dialog box.

Parameter nullability affects the signature for an EAServer method. You can view the IDL interface for a method in the Interface view in Sybase Central.

Each non-nullable parameter in a stored procedure corresponds to a single parameter in the method's signature.

Each nullable parameter in a stored procedure corresponds to two parameters in the method's signature:

• The first is the parameter value itself, if the value is not null.
• Because parameters in EAServer components cannot have NULL values, upon deployment, the Component Builder adds a second parameter called a null indicator to the method. The null indicator is a Boolean value (true/false) that indicates whether the first parameter value is NULL. The name of a parameter's null indicator is <param_name>IsNull, where <param_name> is the name of the corresponding nullable parameter.
  
  An application using an AI for Stored Procedures component should set the value of the null indicator correctly to communicate whether the corresponding parameter is NULL. When a method executes, the client application can check the null indicators or any nullable output parameters to determine whether the output value is NULL. At runtime, the client application can set the null indicator to true to indicate that the parameter has a NULL value.

Copyright © 2001 Sybase, Inc. All rights reserved.