Versions Compared

Old Version 2

changes.mady.by.user Fabiola Arias Navia

Saved on

compared with

New Version Current

changes.mady.by.user user-ff8ce

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

Info

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

Note
iconfalse
titleNote

...

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.


Anchor
Example Processing Sequences using the SQL database activities
Example Processing Sequences using the SQL database activities
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:

SQL_CONNECT

Connect to database using SQL

SQL_DISCONNECT

Disconnect from database using SQL

  • Use the following activities to query the database:

    FOR_EACH_SQL_QUERY

    Query database using SQL and iterate the results~

    SQL_QUERY

    Query database using SQL

    SQL_QUERYTOCSV

    Query database using SQL to output CSV file

  • Use the following activities to perfom insert, update and delete operations in the database:

    SQL_UPDATE

    Update database using SQL

  • Use the following activities to execute an SQL stored procedure in the database:

    SQL_CALL

    Execute an SQL stored procedure

    SQL_CALLQRYCSV

    Query database using an SQL stored procedure to CSV

    SQL_CALLQUERY

    Query database using an SQL stored procedure

  • Use the following activities to implement transaction control relating to any database insert, update or delete operations you have performed:

    SQL_COMMIT

    Commit a database transaction using SQL

    SQL_ROLLBACK

    Rollback a database transaction using SQL

  • Use the following activities to set the parameter values for an SQL operation:

    SQL_PARAMS

    Set parameter values for SQL operation

    SQL_PARAMSCSV

    Set parameter values for SQL operation from CSV

  • Use the following activity in custom solutions to directly access the SQLServerService JSM session:

    SQL_GETJSMHANDLE

    Get JSM session handle for SQL connection

Anchor
Eligibility for Processing Sequence Restart
Eligibility for Processing Sequence Restart
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.

...

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.

...

Note
iconfalse
titleNote

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.

...

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.