You can use SQL components cataloged in your Project catalog as instance variables. This is very useful when you want to persist data attached to a process instance. You do not have to store each database value as separate instance variables.
The configuration used to access the database is the one used when the sql component was cataloged.
The load action is automatically performed, so you do not have to worry about retrieving the information. The load is performed in a lazy way.
You have to manually do the store and remove of the sql component.
store--you should call the store method if you are adding a new item to the database or if you have modified it during processing. store is "intelligent" and will execute an insert or update depending on the situation.
remove if you need to delete the item from the database. It is not possible to automatically determine this situation.
When a process instance variable is "persisted" (when the task is completed) all their fields are stored into the database. However, SQL objects are a bit more intelligent and decide which fields have to be stored depending on the value of the “accessDatabase” attribute.
If “accessDatabase” is set to true, it assumes that the object is already stored in a database and therefore it only stores its primary key. And if it is false the whole object is stored.
However, these rules do not apply for Screenflow because their variables are not persisted between tasks. Everything works as you are running only one task.
Using a SQL component as an instance variable is a practice that helps you optimize the server database as the only data stored regarding the SQL Component is its primary key. This reduces the storage usage as you do not need to store the data that you are interested in attaching to the process instance in different instance variables. For example, if you want to store the customer's id, its credit limit and discount percentage, you will have to define three instance variables. Instead, if you store the sql component, the only data stored would be the customer id which is the primary key of the identity customer.
Using the SQL component as an instance variable brings the additional benefit of having the data always updated. As you do not keep the information separately from the database, the information may be changed by other users and next time you access the data, it will be updated. If you store information, such as the credit limit of a customer in an instance variable and it is changed, you may be processing with outdated information and you must write extra code to check these changes. Using the SQL component as an instance variable, you always have the latest updated information.
Another important benefit is that the load is automatically performed. More than that, it is done in a lazy way. This means that the data is not retrieved until it is required. The load is performed the first time an attribute of the component is referenced.