Connector Xpress Guide › Creating Connectors › Operation Bindings › Stored Procedure and Column Considerations

Stored Procedure and Column Considerations

This section covers some recommendations for your database schema to ensure smooth operations with stored procedures.

Stored procedure arguments that do not have a common SQL type reported by their drivers (that is, the driver returns java.sql.Type.OTHER = 1111) are treated as Type.VARCHAR (16), assuming that the driver converts from string to the argument’s desired native type. For example, this works for Oracle NVARCHAR2 arguments. If this does not work, your stored procedure is not invoked and a failure message ending with “Invalid column type” appears.

We recommend that you use basic types (for example, basic types related to VARCHAR) for stored procedure arguments where possible, and verify other argument types against a single stored procedure against your desired vendor and version before proceeding to wide spread usage.

Verify that the native type of account/group table column you select as the key when creating a Group-Account association matches the type of corresponding columns in your chosen membership table. If they do not match, then membership information is not retrieved successfully. We recommend that you use strict constraints as much as possible. For example, if the column you select as the group naming attribute is of type NVARCHAR2 and the matching column is of type VARCHAR2 in the membership table, then looking up the groups an account belongs to returns an empty list (or at least is missing groups which have multibyte characters in their names).

We recommend that you do not use the percent (%) and underscore (_) characters because they act as wildcard characters when searching for database objects like schemas, tables, table columns, and stored procedure arguments. These values are quoted where they do appear, but this is an area of considerable divergence between vendors and versions. For example, some vendors do not report the quotation character used in some releases correctly. There are no longer any known problems with %/_ for any of our supported vendors.

Important! Connector Xpress and CA IAM Connector Server read and write data from stored procedures through their arguments. However, they do not verify the validity of the code of any stored procedures you bind to. We recommend that you verify the validity of the code of any stored procedures you bind to.