Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space WBRIDGE and version BRIDGE_21.1.3
Div
Classe2e-refDiv

Otp
Floatingfalse

Rp

In order to make an activity interact with a database, use the <<SQLAdapter>> stereotype for an action node. The tagged value sql then will contain the SQL statement (see Performing Static SQL Statements). However, there is also the possibility to give the SQL statement as input string (see Performing Dynamic SQL Statements ).

...

The example accesses the Employee.sqlite database. This SQLite database is installed together with the E2E SQL examples.

Infotip
iconfalse
titleExample databases can be found in:

<your example path>\Add-ons\SQL\resources\templates\binaries

...

In the SQL script, all input variables have to be prefixed with IN::. All columns that are returned from the database should be prefixed with OUT::. This prefix is optional, but enables consistency checks during the compilation phase.
In the example above , the E2E xUML Model Compiler checks if the columns ID , TYPE , STREET , ZIPCODE , CITY , and COUNTRY can be mapped to attributes having the same name in the ResultEmployee class. If the names do not match, the Model Compiler will display an error message.

After receiving the result from the DBMS at runtime, the Bridge checks if the columns can be mapped to the attributes of the output object  (upper and lower case is not distinguished). If the E2E xUML Runtime cannot map the result sets to objects, it will throw an error.
In the above example, only one record is expected to be returned from the DBMS because the <<SQLAdapter>> action returns one object only. If the DBMS were to return multiple records, the xUML Runtime would throw an error.

...

Some databases handle BLOB columns differently to their other types. Therefore, the E2E xUML Model Compiler must recognize BLOB columns. This is achieved by marking the column name within the IN:: keyword, i.e. IN:<blob column name>.

...

If you want to write database records containing columns that are represented by E2E type Blob, you have to use the following in your SQL query

  • for inserts

    Code Block
    languagesql
    IN:<column_name>:<variable_name>

    for example:

    Code Block
    languagesql
    INSERT INTO E2ETYPES
    (E2E_BLOB) VALUES (IN:E2E_BLOB:e2etypes.e2e_blob)
  • for updates

    Code Block
    languagesql
    <column_name> = IN:<column_name>:<variable_name>

    for example

    Code Block
    languagesql
    UPDATE E2ETYPES 
    SET E2E_BLOB = IN:E2E_BLOB:e2etypes.e2e_blob
    WHERE ID = IN::id

 


Noteinfo
iconfalse

Note that the usage of BLOB:: is deprecated and will not work with multiple blobs in one query.
Be aware of the fact that some DBMS do not support more than one blob column.

...

Parameterized SQL Statements

Since Builder 6.0.20.1 / Runtime 2016.9, you You can use the SQL Adapter with parameterized statements that get their parameters via a map.

...

Within the SQL statement, replace values by :<name of the parameter>. Provide parameter/value pairs in a map called inputBindings. The E2E xUML Runtime will identify the map by this name and automatically replace the parameters in the SQL string by the values given by the map.

Noteinfo
iconfalse

Do not use numeric parameter identifiers, e.g. :12. However, identifiers like :id2 are allowed.

...

Figure: Example of a Dynamic SQL Statement

 


Info
Note
iconfalse

The SQL string must not contain IN:: or OUT:: qualifiers. For instance, a valid SQL string might look like: set sqlStatement = "SELECT select NAME, FIRSTNAME FROM from TEMPLOYEE WHERE where Id=5";

Using a Dynamic Table Name (Security Considerations)

...

Code Block
local tableName = setting("Table Name", "temployee");
set sqlStatement = concat("select nameNAME, firstnameFIRSTNAME, birthdateDEPARTMENT from ", tableName, " where id=:id");
Note
iconfalse

Security remark: From a security point of view it is important to control the building of the SQL statement.

  • Getting the table name from a service setting is safe, getting the table name via an operation parameter would be not.
  • It is also recommended to use SQL parameters (see Parameterized SQL Statements) and not to use concat() to add operation parameters directly to the where clause.

Using Parameterized SQL With the IN Clause

The xUML Runtime does not allow to build dynamic SQL statements like select NAME, FIRSTNAME from TEMPLOYEE where name in (IN::<a list of concatenated values>) . SQL statements like this will not return the expected results or not work at all.
If you want to build a dynamic SQL statement and use the IN clause, you need use parameterized SQL as described above (see Parameterized SQL Statements).

The activity diagram below shows an example implementation from the sqlQueries example.

Image Added

  1. Build a map that contains a list of parameters that represent the values from the IN clause.
    Implement a loop to create the map. The map key mustn't be numeric: The example above uses a concatenation of "p" and the index.
  2. Build the parameterized SQL statement and concatenate the parameters from the map.
    Get the map entries to

...

  1. an auxiliary array and reduce this array to a string containing the list of parameters. Then, you can use this string to populate the list of values for the IN clause.

SQL Adapter Output

For SELECT statements, the SQL adapter needs an output record class (or an array of this class, if the statement creates multiple output records) to store the adapter output to. Here the SQL Adapter tries to match the table column names with the attribute names of the output class.
For all types of statements there is an additional output parameter affectedRows. This parameter returns the number of rows affected by the SQL statement. This comes in handy if the modeler must take into account if e.g. updates had some effect or not.

...

In general, database-specific types are mapped to the E2E Bridge base types like described on Database-Specific Mappings.

...