SQL statements

To access data, in addition to using data sources, you can also code SQL statements manually at page level.

SQL statements give you extra flexibility and manageability. They can be used to define extra queries for special purposes that are only relevant locally. On the downside, SQL statements require coding work and this coding work cannot easily be re-used at other levels than the web GUI level.

Before you start coding SQL, always ask yourself if you cannot reach your goal simply by using SQL properties.

Create an executeSQLStatement Action object in your page that executes your SQL statement. Unless there are special considerations, best practice is to create a dummy SQL Event object with name "SQL" at the highest level in the page (as a child of the Page node).

Keep all the Action objects for SQL Statements under this "SQL" node, so that the top of your object tree looks like this:

WD_clip0037

1.	Open the Page class where you want to insert your SQL Statement.

2.	In the Controls catalog on the left of your screen, open the Listeners node, and drag an Event object onto the top-level Page node in the object tree on the right of your screen. Double-click on the inserted Event and set its Name to "SQL".

3.	Now, open the Actions node, and drag an executeSQLStatement Event object onto the "SQL" Event in the object tree.

4.	Double-click on the inserted Action object. Think of a good name that describes the action the SQL will perform (in the example we have "renameConcept" and "getRecentTours"). Set the Id property of the Action to that name.

You can also set a Name property, but setting the Id property is essential.

Next time you open or refresh the page, the Id value will appear in parentheses in the object tree node, as shown in the example picture.

5.	Write your SQL Statement as a value of the SQL property. Use colons ( : ) to introduce bind variables. The value of these bind variables will come from the page when the SQL is executed at runtime. For example, here is a SQL Statement with a bind variable :COUNTRY.

SELECT j1.variant "SUGG_DEPARTURE"

, j2.variant "SUGG_ARRIVAL"

FROM journey_leg j1

, journey_leg j2

WHERE j1.country = :COUNTRY

AND j2.country = :COUNTRY

AND j2.start_node = j1.end_node

AND j1.type = 'INTERNATIONAL'

AND j2.type = 'DOMESTIC'

In addition to SELECT statements, you can also write standard DML (INSERT, UPDATE, DELETE) statements and USoft-proprietary INVOKE statements. Here is an example of an INVOKE statement:

INVOKE BatchRunner.RENAME_CONCEPT

WITH

SELECT :AGENCY AGENCY

, :RENAME_FROM RENAME_FROM

, :RENAME_TO RENAME_TO

, 'True' "-quiet"

Execute your SQL statement by associating it with an HTML event on your page. For example, maybe you want to execute the statement when a user presses a button. In that case, the HTML event is onclick. The Button object in the object tree must have an onclick child Event and that Event must have a callClientScript Action:

WD_clip0038

1.	Create the relevant object structure, including the HTML event with which you associate your SQL statement.

2.	At the point where you write your script, use a call to $.udb.executeSQLStatement in which you call the statement by its Id value. In our example, the following code snippet could be the value of the Script property of a CallClientScript run when the user presses the RenameConcept button:

$.udb.wait(true);

$.udb.executeSQLStatement('renameConcept',

{

async: false,

hostvars: {

AGENCY: $.udb('PARTNER').rows('current').cols('AGENCY').val(),

RENAME_FROM: $('#concept-old').val(),

RENAME_TO: $('#concept-new').val()

error: function() {

$.udb.wait(false);

success: function(){

$.udb.wait(false);

$.udb('ORDERS').refresh();

}

);

This code snippet illustrates that:

•You need to use the async: false setting to indicate that you want SQL execution to be synchronous (= the application needs to wait for it to complete).

•You can use hostvars settings to pass values from the web page as input parameters to the statement.

•You can use error and success settings to script what must happen if the SQL execution raises an error, and what must happen if it completes normally.