Chapter 39 Scripted JDBC

Waveset provides a Scripted JDBC resource adapter to support management of user accounts in any database schema and in any JDBC-accessible database. This adapter also supports Active Sync to poll for account changes in the database.

Adapter Details

The Scripted JDBC resource adapter is a general purpose adapter, and is therefore highly configurable. The adapter makes no assumptions about the database schema that is being managed. Instead, the adapter calls out to a set of customer-supplied scripts to perform JDBC interactions with the database. Currently, customer-supplied scripts can be written in JavaScript (Rhino) or BeanShell.

The Scripted JDBC resource adapter is defined in the com.waveset.adapter.ScriptedJdbcResourceAdapter class.

---

Note –

All connections to SQL Server must be performed using the same version of the Microsoft SQL Server JDBC driver. (The possible versions are the 2005 or the 2000 version.) This includes the repository as well as all resource adapters that manage or require SQL Server accounts or tables, including the Microsoft SQL adapter, Microsoft Identity Integration Server adapter, Database Table adapter, Scripted JDBC adapter, and any custom adapter based on these adapters. Conflict errors occur if you attempt use different versions of the driver.

---

Installation Notes

Copy the appropriate JDBC driver jar for the database you will manage to the WEB-INF\lib directory of your Waveset installation.

Resource Configuration Notes

None

Usage Notes

The customer-supplied scripts called by the Scripted JDBC adapter must be written in Javascript or BeanShell. Waveset stores these scripts in the Waveset repository as named ResourceAction objects.

Each Scripted JDBC resource instance is configured through a set of resource attributes that reference the appropriate ResourceAction objects by name. At run-time, the adapter

Run-Time Adapter Actions

1. Loads the script from the ResourceAction corresponding to the current provisioning action (such as create, delete, or update).

2. Prepares the necessary Java input objects to make them available to the script.

3. Invokes the script.

4. Processes the result returned (or exceptions/errors) from the script.

   The remainder of these Usage Notes describes the Scripted JDBC adapter provisioning actions and the expected behavior for a script assigned to each provisioning action.

   Scripts should never close the JDBC Connection that is passed to them. The adapter automatically closes the connection at the appropriate time.

   See the file hierarchy under sample/ScriptedJdbc folder.

   Each example subfolder (SimpleTable, MultiValue, and StoredProc) contains a README.txt file that explains the set of files used in that example.

   The Scripted JDBC adapter supports end-user scripting for the following provisioning actions:

   Action	Description	Required?
   create	Create a new user	No, but if not provided, you cannot create users
   delete	Delete an existing user	No, but if not provided, you cannot delete users
   disable	Natively disable an existing user	No, but if not provided, you cannot natively disable users
   enable	Natively enable an existing user	No, but if not provided, you cannot natively enable users
   getAccountIterator	Return an object used to perform iteration of existing users.	No, but if you do not provide either getAccountIterator or listAll, you cannot perform account iteration
   getActiveSyncIterator	Return an object used to perform Active Sync iteration	No, but if not provided, Active Sync is not supported
   test	Perform a custom test during Test Configuration	No.
   getUser	Fetch attributes for an existing user	No, but if not provided, user actions are not supported
   listAll	Return a list of existing user (or other object type) IDs	No, but if you do not provide getAccountIterator or listAll, you cannot perform account iteration
   update	Update attributes, rename, or change password of an existing user	No, but if not provided, you cannot modify, rename, or change user passwords, or create a user and set his password. (If you don't define an action for update user, then when you create a user, the password is null.)
   authenticate	Verify user ID and password	No, but required to perform pass-through authentication

   Every action script receives an actionContext map, as defined by the java.util.Map class. The possible map content varies for each action.

   For additional information about the actions listed in the previous table, see the following sections in this chapter:

   • create Action

   • getUser Action

   • delete Action

   • update Action

   • enable Action

   • disable Action

   • listAll Action

   • getAccountIterator Action

   • getActiveSyncIterator Action

   • authenticate Action

   • test Action

   • getActiveSyncIterator Action

   In addition to a description of these action, each section provides the following information:

   • Context. This section describes the set of entries that are available in the actionContext map the adapter adds into the JavaScript execution context before the script executes.

   • Error Handling. This section describes how the script is expected to handle abnormal or error conditions.

create Action

Use the create action to create a user in the customer’s database. If the create action is not defined, then the adapter cannot create new users in the customer’s database.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.waveset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The createUser string
id	java.lang.String	Account ID of the user to create
password	java.lang.String	If present, this value is the new user’s decrypted password
attributes	java.util.Map	Map of attributes to set for the new user.  The key identifies which attribute to set The value specifies the decrypted value to which the attribute should be set.
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution  Scripts can use methods from this class to be “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may also add appropriate strings to the errors key. The presence of any items in the errors List is considered a creation failure.

getUser Action

The getUser action retrieves a map of existing user attributes from the customer’s database. If the getUser action is not defined, the adapter cannot perform any user actions.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The getUser string
id	java.lang.String	The user account ID to fetch
attrsToGet	java.util.List	List of strings identifying which user attributes to fetch. This list is derived from the right-hand side of the schema map.
result	java.util.Map	If the user does not currently exist in the database, the script should leave this map empty. If the user does exist, see the following description of the expected map.
errors	java.util.List	Initially this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

The adapter expects the result map to be populated with the following entries:

Key	Value Type	Value Description
attrMap	java.util.Map	If the script is capable of directly retrieving the user attributes, then the script can set this entry with a map of the user attributes. The attribute names are defined in the Resource User Attribute column of the resource’s schema map.
isDisabled	java.lang.Boolean or java.lang.String	If set by the script to a Boolean.TRUE or a true string, then the user is considered disabled.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, it may add appropriate strings to the errors key. The presence of any items in the errors List is considered a fetch failure.

delete Action

Use the delete action to delete users from the customer’s database. If the delete action is not defined, then the adapter cannot delete users from the customer’s database.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The deleteUser string
id	java.lang.String	User account ID to delete
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add appropriate strings to the errors key. The presence of any items in the errors List is considered a deletion failure.

update Action

Use the update action to update existing users in the customer’s database. An update can include changing attributes, changing passwords, or renaming. If you do not define the update action, the adapter cannot update users in the customer’s database.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The updateUser string
id	java.lang.String	Existing user’s account ID
attributes	java.util.Map	Map of attributes to set for the new user.  The key identifies which attribute to set The value is a decrypted value to which the attribute should be set. If there is no map entry for an attribute, do not change the attribute.
newId	java.lang.String	If present, the script is expected to change the existing user’s account ID (identified by the value of id attribute) to the new account ID specified by the newId attribute value.
password	java.lang.String	If present, this value is the decrypted value of the user’s new password.
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered an update failure.

enable Action

Use the enable action to enable users in the customer’s database. Implement this action if the schema of a user in the customer’s database supports the concept of enabled/disabled. If you do not define the enable action, the adapter cannot enable users directly in the customer’s database.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The enableUser string
id	java.lang.String	User account ID to enable
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

disable Action

Use the disable action to disable users in the customer’s database. Implement this action if the schema of a user in the customer’s database supports the concept of enabled/disabled. If you do not define the disable action, the adapter cannot disable users directly in the customer’s database.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The disableUser string
id	java.lang.String	User account ID to enable
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

listAll Action

Use the listAll action to retrieve a list of user (or other object type) IDs found in the customer’s database. If you do not define the listAll action, you cannot call the FormUtil.listResourceObjects methods from a form for this resource instance.

In addition, if you do not define the listAll action or the getAccountIterator action, then account iteration (reconciliation, Load From Resource) is not supported.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The listAllObjects string
objectType	java.lang.String	Indicates which type of object IDs should be listed.  Typically, you use the account object type to list user IDs. You can use other object type IDs (such as group) if the script is written to produce IDs for other object types.
options	java.util.Map	Additional (optional) options that can be passed in to the listResourceObjects invocation
resultList	java.util.List	The script adds entries to this list.  Each item the script adds to the list should be a string ID.
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may also add appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

getAccountIterator Action

Use the getAccountIterator action to return an object to the adapter used to perform iteration of existing users.

To perform account iteration (reconciliation, Load From Resource), you must define this action or the listAll action. If you do not define the getAccountIterator action, account iteration will be performed by calling listAll, and then calling getUser for each ID in the list from listAll.

In addition, if you do not define the getAccountIterator or the listAll action, then account iteration is not supported.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database.
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The getAccountIterator string
result	java.util.Map	(See result description below)
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

The adapter expects the result map to be populated with the following entry:

Key	Value Type	Value Description
iterator	com.waveset.adapter.script.ScriptedIterator	The script must set this value to a generated instance of the ScriptedIterator interface.    public interface ScriptedIterator { public boolean hasNext(); public void next(java.util.Map nextObj); public void close();} See the next table for information about the nextObj map. The object must be capable of iterating over all the users in the customer’s database.  The samples demonstrate how to accomplish this in BeanShell and Javascript.

The adapter expects the nextObj map passed to the next method to be populated by the iterator with attributes for each iterated user.

Key	Value Type	Value Description
attrMap	java.util.Map	If the script is capable of directly retrieving the user attributes, then the script can set this entry with a map of the user attributes. The attribute names are defined in the Resource User Attribute column of the resource’s schema map.
isDisabled	java.lang.Boolean or java.lang.String	If set by the script to a Boolean.TRUE or a true string, then the user is considered disabled.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may also add appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

getActiveSyncIterator Action

The getActiveSyncIterator action returns an object to the adapter used to perform Active Sync iteration.

If you want the resource to support Active Sync, you must define this action.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The getActiveSyncIterator string
options	java.util.Map	The map may contain an entry with the lastProcessed key. This entry value is a map of the attributes of the last user successfully processed by Active Sync. See the SimpleTable example (SimpleTable-activeSyncIter-bsh.xml script) for an example of how to use the lastProcessed entry to compose a query that filters out uninteresting users from the iterator.
activeSyncLogger	com.waveset.adapter.logging.IActiveSyncLogger	Object used to write log entries to the resource’s Active Sync log file or files.
result	java.util.Map	(See the following result description)
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

The adapter expects the result map to be populated with the following entry:

Key	Value Type	Value Description
iterator	com.waveset.adapter.script.ScriptedIterator	The script must set this value to a generated instance of the ScriptedIterator interface. public interface ScriptedIterator { public boolean hasNext(); public void next(java.util.Map nextObj); public void close();} See the next table for information about the nextObj map. The object must be capable of iterating over all the users in the customer’s database.  The samples demonstrate how to accomplish this in BeanShell and Javascript.

The adapter expects the nextObj map passed to the next method to be populated by the iterator with attributes for each iterated user.

Key	Value Type	Value Description
attrMap	java.util.Map	If the script is capable of directly retrieving the user attributes, then the script can set this entry with a map of the user attributes. The attribute names are defined in the Resource User Attribute column of the resource’s schema map.
isDisabled	java.lang.Boolean or java.lang.String	If set by the script to a Boolean.TRUE or a true string, then the user is considered disabled.

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may also add appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

authenticate Action

Use the authentication action to authenticate user IDs/passwords against the customer’s database. If you do not define the authentication action, the resource cannot support pass-through authentication.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The authenticateUser string
id	java.lang.String	Account ID of the user to authenticate
password	java.lang.String	The decrypted password to authenticate
result	java.util.Map	The script can add an entry with the expired key and a Boolean.TRUE value to indicate that the user’s password has expired.
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

If the script executes without failure, the ID and password are considered valid.

Any throw from within the script is considered an authentication failure.

If the script encounters any errors, the script may alias appropriate strings to the errors key. The presence of any items in the errors List is considered an authentication failure.

test Action

If defined, the test action is called during Test Configuration of the resource. A common use of the test script is to verify the adapter’s ability to access required database tables.

Context

The actionContext map contains the following entries:

Key	Value Type	Value Description
conn	java.sql.Connection	JDBC connection to the customer’s database
adapter	com.wavset.adapter.ScriptedJdbcResourceAdapter	Adapter instance
action	java.lang.String	The test string
errors	java.util.List	Initially, this value is an empty list.  The script may add java.lang.String objects to this list if any errors are found during processing.
trace	com.waveset.adapter.Trace	Object used to trace execution.  Scripts can use the methods of this class to make themselves “debuggable” in a customer environment.

Error Handling

Any throw from within the script is considered a test failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a test failure.

Provisioning Notes

The following table summarizes the provisioning capabilities of this adapter:

Feature	Supported?
Enable/disable account	Yes
Rename account	Yes
Create Account	Yes
Update Account	Yes
Delete Account	Yes
Pass-through Authentication	Yes
Password update	Yes
Data loading methods	Import directly from resource Reconciliation Active Sync

Account Attributes

The Scripted JDBC adapter does not provide any default account attributes because account attributes vary greatly depending on the database schema being managed.

This adapter supports binary datatypes, including BLOBs in Oracle. The corresponding attributes must be marked as binary on the schema map. Sample binary attributes include graphics files, audio files, and certificates.

Security Notes

To determine supported connections and which administrative privileges are required, refer to the product documentation for your managed database.

Resource Object Management

The only resource object management supported is the ability to list all objects. The adapter can retrieve a list of IDs for any resource object type.

Identify Template

$accountId$

Sample Forms

• MultiValueUserForm.xml

• SimpleTableUserForm.xml

Troubleshooting

Use the Waveset debug pages to set trace options on the following classes/packages:

• com.waveset.adapter.ScriptedJdbcResourceAdapter

• com.waveset.adapter.JdbcResourceAdapter

• com.waveset.adapter.script

A com.sun.idm.logging.trace.Trace object is always passed in the action context passed to the scripts.

Enable trace on com.waveset.adapter.ScriptedJdbcResourceAdapter to enable tracing in the scripts.

Additionally, you can use the following scripts to perform tracing or writing output.

• With Beanshell, the following statement enables line tracing:

  this.interpreter.TRACE=true;

• With BeanShell, the following, Java-style statement writes a string to stdout:

  java.lang.System.out.println(“Hello World”);

• With JavaScript, the following, Java-style statement writes a string to stdout:

  Packages.java.lang.System.out.println(“Hello World”);

If Active Sync is being performed, then you can set the following Waveset Active Sync logging parameters for the resource instance:

• Maximum Log Archives

• Maximum Active Log Age

• Maximum Log File Size

• Log File Path

• Log Level