apiUi – Embedded SQL statements

It is possible to embed SQL statements in your apiUi scripts.
apiUi supports the four basic SQL statements Select, Insert, Update and Delete. Embedded SQL statement are always terminated with a semicolon. Another way to access an SQL database is by functions. To use Embedded SQL you first have to connect a database Menu->Project-Options.

Syntax

The syntax of the apiUi embedded Select statement does not comply to the standard SQL select statement. For readability and convenience the selected attributes and the fields to accept the values are named in pairs instead of first listing all the attributes and thereafter listing all the fields in the into-clause. Another violation against the standard SQL syntax is the Loop clause. Because of the Loop clause you do not have to declare cursors and program opens and fetches. Within the Loop clause you can place program logic that will be executed for every retrieved row. In this way you can even nest embedded SQL queries.

Select

EXEC SQL
    SELECT ColumnsClause
        FROM TablesClause
        [WHERE WhereClause]
        [GROUP BY GroupByClause ]
        [HAVING HavingClause]
        [ORDER BY OrderByClause]
        [LOOP ‘{‘ StatementList ‘}’]
’;’

Standard Embedded SQL syntax has an INTO clause between the SELECT and the FROM clauses. apiUi uses a different ColumnsClause instead.

Only the ColumnsClause is explained here. An explanation of the other clauses can be found in the manual of your DBMS.

Select ColumnsClause

[TableName.]Attributename AS :FieldId [,[TableName.]Attributename AS :FieldId)]...

See the sample code for an example.

Insert

The apiUi embedded SQL Insert statement differs from the standard embedded SQL insert statement.

EXEC SQL
    INSERT INTO TableName
    ‘(‘ ColumnsClause ‘)’
;

Insert ColumnsClause

Attributename AS :FieldId [,Attributename AS :FieldId)]...

See the sample code for an example.

Update

The embedded SQL Update statement uses the standard syntax.

Delete

The embedded SQL Update statement uses the standard syntax.

Sample code

This sample retrieves input data from the SQL table TestCases, placing received values in In.InputIpm and stores returned values in the SQL table TestResults along with a timestamp and the responsetime.

integer ws.TimeStamp;
exec sql
  select AccountId as :In.InputIpm.AccountId
       , Balance as :In.InputIpm.Balance
  from TestCases
  order by AccountId
  loop
  {
    ws.TimeStamp := now ();
    exec sql
      insert into TestResults
      ( ExecuteTime as :ws.TimeStamp
      , ResponseTime as :Response.Time
      , AccountId as :Out.OutputIpm.AccountId
      , Balance as :Out.OutputIpm.Balance
      , Interest as :Out.OutputIpm.Interest
      )
    ;
  }
;