SQL statements embedded into PowerScript code

SQL statements can be embedded directly into PowerScript code, allowing you to include PowerScript variables in any area of the statement ,whereas you can’t include them if using PowerBuilder Database painter.
To work with variables, the SELECT statement accept the INTO clause, that enable assignment of the result to existent variables.
For example:
SELECT COUNT(*) INTO :il_bools FROM books;

PowerScript variables can be used in a statement as values in WHERE clause:
WHERE name = :ls_name;

In embedded SQL ,which produce multiple-row result, can be used cursor object for traverse the result. For cursor object was introduced a set of statements including DECLARE, OPEN and FETCH statements.
DECLARE statement it is used to declare variables.
DECLARE cursor_name CURSOR FOR selected_statement {USING transaction_object};

OPEN statement it is used to execute selected_statement.
OPEN cursor_name;

FETCH it is needed to step to the first row and then to the subsequent rows.
FETCH cursor_name INTO variable_list;

Same syntax can be used for stored procedures, except OPEN statement which need to be replaced with EXECUTE statement.

After an embedded SQL statements it is executed, is important to check it for failure. For this task PowerBuilder provide SQLCA transaction object with SQLCode property. SQLCode property has various values as 0 for command succeeded, -1 for SQL failed, 100 for no value returned.

Let’s look to an exemple:
DECLARE author_books CURSOR FOR SELECT * FROM books WHERE author = :ls_author USING SQLCA; OPEN author_books;

FETCH author_books INTO :ls_author_name, :ls_author_book;
DO WHILE SQLCA.SQLCode = 0
FETCH author_books INTO :ls_author_name, :ls_author_book;
LOOP

CLOSE author_books;