Chapter 2 Working with the AI Component Builder

Understanding AI Components

AI for Stored Procedures components are Jaguar 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 Jaguar.

You can add methods to components either manually or automatically. For more information, see the section in this chapter called "Creating Methods".

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

The rest of this section covers the following topics:

• Component Properties
• Creating Methods
• Method Properties
• Mapping Stored Procedures to Methods

Component Properties

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

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 that the method will 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 that will 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.

   We strongly recommend 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, you enter specific information in the Target tab of the Method Properties dialog box about the target stored procedure (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 the following:
  • 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 Jaguar 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 run time in Jaguar.

java.sql.Statement.

java.sql.PreparedStatement.

java.sql.CallableStatement

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 in any way you prefer, provided that the server can understand its syntax. Parameters defined to a method can be substituted at run time 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 Jaguar.

This section includes the following topics:

• Target Stored Procedures and Method Names
• Result Sets and Method Return Value
• Parameter Information

Target Stored Procedures and Method Names

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 upper case and the subsequent characters in lower case.

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

Result Sets and Method Return Value

The number of result sets declared in the Component Builder affects the method's return type. The following table shows how the number of result sets are mapped in Jaguar:

Parameter Information

This section covers the following topics:

• Parameter Names
• Parameter Modes
• Stored Procedure Return Values
• Parameter Datatypes
• Parameters with NULL Values

Parameter Names

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 the names of the parameters are not valid parameter names, the Component Builder alters the parameter names to conform to Jaguar restrictions. You can also alter parameter names in the Component Builder.

Parameter Modes

The parameter mode refers to 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.

Stored Procedure Return Values

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

   The Component Builder may have already set the Return Value property for the parameter correctly if you created the method automatically.

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 Jaguar 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 the following table 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 Jaguar IDL datatypes that ultimately get mapped into the appropriate Jaguar 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 a Jaguar method. You can view the IDL interface for a method in the Interface view in the Sybase Central window.

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 Jaguar 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 run time, the client application can set the null indicator to true to indicate that the parameter has a NULL value.
  

Copyright © 1999 Sybase, Inc. All rights reserved.