This activity establishes a connection to an SQL database and returns a "handle" that can be used to identify this database connection for use in other SQL database activities.

Typically in LANSA Composer, most database activity is accomplished through the use of Transformation Maps.  However, this activity along with its related SQL database activities provides another option for performing limited database query and update operations on an SQL database, as well as a means of invoking SQL stored procedures in the database.

The SQL_CONNECT activity establishes the database connection using a database configuration whose name you provide in the DBCONFIG parameter.  The database configuration contains the implementation-specific database connection information and user credentials.  Because the connections use JDBC, like transformation maps, the SQL activities are capable of addressing any compatible database that is network-addressable from the server system running LANSA Composer, if a suitable JDBC driver is available.  This means that a LANSA Composer system running on an IBM i server, for example, could address an SQL server database running on a Windows server in the same network, or vice-versa.

More than one SQL database connection may be active at one time in a single Processing Sequence.

The SQL database activities are not intended and not usually suitable for high-throughput, high-volume database operations.  Rather they provide a simple means to complete a business process integration solution that may not otherwise have been possible, with some simple, low-volume database access and/or maintenance.

It is recommended that you keep your SQL operations through these activities as simple as possible.

LANSA Composer does not guarantee that any form of SQL statement that is valid for your target database can successfully be executed through the SQL database activities, nor that every form of SQL stored procedure can successfully be executed.
Nor will it be possible to successfully address every possible data type in your database.  Since processing sequence variables that might be used to pass or receive data to the SQL database are untyped, not all conversions can be successful or yield useful results.  It is your responsibility to ensure that any data passed through the SQL database suite of activities is in a form that can be accepted and processed by both the JDBC driver and the target database.


Example Processing Sequences using the SQL database activities

Refer to the following example processing sequences supplied with LANSA Composer for working (*) examples that use the SQL database activities.  (*)  Note that some setup will be required on your system to enable these examples to execute successfully.  Refer to the notes accompanying the example processing sequences for details:

Related SQL database activities

The SQL_CONNECT activity returns a "handle" that can then be passed on to the other SQL database activities to accomplish a range of database tasks.  The full suite of SQL database activities are briefly described below:

SQL_CONNECT

Connect to database using SQL

SQL_DISCONNECT

Disconnect from database using SQL

Eligibility for Processing Sequence Restart

When a LANSA Composer Processing Sequence run ends in error, it is often possible to restart it from the point of failure—once the cause of the failure has been corrected. This is a very powerful feature of LANSA Composer.

For a LANSA Composer solution using the SQL database activities, processing sequence restart is supported, but needs to be heavily qualified by exactly what database operations are being performed.

LANSA Composer restart support remembers and can re-establish a previously-established SQL connection, but whether a particular process can be restarted depends on the types of database operations that are being performed and in particular whether they are dependent on earlier SQL database operations that may have completed before the restart.

For example, a process that performs database updates under transaction control may not be restartable in practice, depending on where the failure occurs.  On the other hand a solution that exclusively performs SQL query operations will usually be capable of being successfully restarted.

Therefore this decision is left to the solution designer by means of the RESTARTELIGIBLE parameter to the SQL_CONNECT activity.

If your solution uses the SQL database activities in such a way that restart eligibility cannot be assured for the life of the database connection, then you should specify NO for this parameter.

In any event, to maximize the benefit of LANSA Composer's restart capability, you should complete your SQL database operations and execute the SQL_DISCONNECT activity at the earliest opportunity. Once the SQL database connection has been closed, normal restart eligibility resumes.

The FOR_EACH_SQL_QUERY activity is not restartable, irrespective of the values specified for the RESTARTELIGIBLE parameter of the SQL_CONNECT activity.

INPUT Parameters:

DBCONFIG: Required

This parameter must specify the name of a Database Configuration that specifies the details necessary to establish the database connection.

RESTARTELIGIBLE: Optional

This parameter specifies whether the LANSA Composer processing sequence that contains this activity should remain eligible for restart while the SQL database connection remains open.  The default value is YES.

If your solution uses the SQL database activities in such a way that restart eligibility cannot be assured for the life of the database connection, then you should specify NO for this parameter.

For more information refer to Eligibility for Processing Sequence Restart above.

OUTPUT Parameters:

SQLHANDLE:

If successful, the value of this output parameter identifies the SQL connection established by this instance of the SQL_CONNECT activity.  The same value must be specified as the SQLHANDLE input parameter value for all subsequent SQL database activities that are to operate on the same SQL database connection.