SQL_CONNECT
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.
NOTE: 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.
It is recommended that you keep your SQL operations through these activities as simple as possible.
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:
- EXAMPLE_SQL01: Example of using the SQL_QUERY activity
- EXAMPLE_SQL02: Example of using the SQL_UPDATE activity
- EXAMPLE_SQL03: Example of using the SQL_CALLQRYCSV activity
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:
- Use the following activities to establish or disconnect an SQL database connection:
|
- Use the following activities to query the database:
|
- Use the following activities to perfom insert, update and delete operations in the database:
|
- Use the following activities to execute an SQL stored procedure in the database:
|
- Use the following activities to implement transaction control relating to any database insert, update or delete operations you have performed:
|
- Use the following activities to set the parameter values for an SQL operation:
|
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 effectively 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.
INPUT Parameters:
DBCONFIG : Required
This parameter must specify the name of a 2.3.8 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.