Using SQL variables

 
This feature can be used anywhere where you use SQL Queries. With SQL variables, you can write cleaner code and easily implement custom dropdown boxes or advanced security.

 

Note: SQL variables are case-insensitive.

A list of SQL variables

:field

the current field value on an Add, Edit or Register page, for instance :make

:master.field

any field from the master record.

:session.key

any session variable.

:user.field

any field from the login table (from the record that belongs to the current user).

:old.field

an old field value (before the changes were applied).

:new.field

a new field value.

Where to use the SQL variables

1. In regular SQL queries that you enter on the SQL Query screen.

 

2. In WHERE Tabs on the SQL Query screen.

 

3. In a Lookup Wizard WHERE clause. It can be used in dropdowns that are dependent on any type of field, or a master dropdown. Alternatively, it can be used when dependent dropdowns follow a more complex rule than equality (i.e., age is more than the selected). Note, that you need to add single quotes around text values.

 

CustomerID = :UserID
CustomerID = ':user.CustomerID'
CustomerID = ':session.UserID'

 

You can also use session variables that are arrays. For instance, you have a session variable named CustomerIDs that is an array and stores a list of Customer IDs like: 3, 5, 17. You can now use this array in WHERE clause this way:

 

SQL Query screen:

 

customer_id IN ( :session.CustomerIDs )

 

 

Event code:

 

 

XSession.Session["CustomerIDs"] = XVar.Array( 3, 5, 17);

 

 

WHERE clause in the actual SQL query:

 

customer_id IN ( 3, 5, 17 )

 

 

And the same idea with text values. If you have a session variable named Postcodes that is an array of the following values: 'ABC', 'CBD', DEC'. Now in your WHERE clause you can use the following.

 

SQL query screen:

 

postcode IN ( ':session.Postcodes' )

 

Event code:

 

Event code:

 

 

XSession.Session["Postcodes"] = XVar.Array( "ABC", "CBD", "DEC" );

 

 

WHERE clause in the actual SQL query:

 

postcode IN ( 'ABC', 'CBD', DEC' )

 

 

4. In events by using the PrepareSQL function. For example, in the After Add event you can use:

 

dynamic sql = DB.PrepareSQL("insert into log set lastid=:new.id");
DB.Exec( sql );

 

In the Server code of a Custom Button or in Field events for View/Edit pages:

 

dynamic sql = DB.PrepareSQL("insert into log set lastname=':name'");
DB.Exec( sql );

See also:

•Database API: PrepareSQL()

•Database API: Exec()

•Database API: Query()

•Database API: Select()

•Insert custom button

•Lookup wizard: WHERE expression

•Field events

•Tri-part events

•About Database API

•Preventing SQL injection in web applications