Calling SQL Commands

SQL commands are not embedded directly in the body of a transaction. In Silk Performer they are called like procedures. Silk Performer uses a calling mechanism for SQL commands that is quite similar to the calling mechanism of procedures in a high-level programming language like Pascal. To exchange values with SQL commands, you can use global variables or random variables.

Syntax

SQLCall = Ident1 ":" Ident2.

Ident1 is the name of the database cursor that the SQL command uses (for more information about database cursors, see Database cursors). Ident2 is the name of the SQL command that is declared in the SQL section of the .bdf file.

Example

dclrand
  rArtNo: RndUniN(1..1000);

var
  v_artno, v_stock, v_quantity: number;

dcltrans
  transaction TMain
  begin
    v_artno := rArtNo;
    c1: SelArticle();
    if v_stock = 0 then
      c2: InsOrder();
    end
  end TMain;

dclsql
  SelArticle:
  SELECT stock,quantity
  INTO :v_stock, :v_quantity
  FROM article
  WHERE articlenumber = :v_artno;
  InsOrder:
  INSERT INTO order(articlenumber, quantity)
  VALUES(:v_artno, :v_quantity);

The example above shows a portion of a Silk Performer program focusing on the use of SQL in Silk Performer. Transaction t1 calls the SQL command named SelArticle. SelArticle uses the database cursor named c1. The SQL command SelArticle is declared in the SQL section of the Silk Performer program. The global variable v_artno is used as a bind variable in the SQL command. The global variables v_stock and v_quantity are used as INTO variables (INTO :v_stock, :v_quantity) in the SQL command.

If the value of stock retrieved is equal to zero (0), the transaction executes the SQL command InsOrder using the global variables v_artno and v_quantity as bind variables. InsOrder uses the database cursor named c2.