Please enable JavaScript to view this site.

Navigation: Advanced topics > Programming topics > Database API

Using SQL variables

Scroll Prev Next More

 
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:

 

customer_id IN ( :session.CustomerIDs )

 

which will produce the the following WHERE clause:

 

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.

 

postcode IN ( ':session.Postcodes' )

 

which will produce the the following WHERE clause:

 

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

 

 

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

 

$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:

 

$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

 

Created with Help+Manual 7 and styled with Premium Pack Version 3 © by EC Software