View what's in this topicDatabase-Related Functions
Database-related functions include:
Variable Reference
You can use variables in QLingo expressions and SQL queries.
Syntax:
A variable reference is defined by either the variable name (if it starts with a letter and consists of only letters and numerals) or the ‘at’ sign ("@") followed by the variable name in curly brackets. The second syntax option is mandatory in SQL queries that are part of the QLingo program.
Example:
@{first_name} will return the recipient’s ‘first name’. This will occur only if you defined a variable called first_name to return the first name value.
SQL Queries
You can use SQL queries to retrieve data from the database or multiple databases.
SQL queries are either:
-
Select queries, i.e. SELECT field names FROM tables WHERE condition;
-
Execute stored procedures, i.e. EXECUTE stored_procedure_name parameter values;
Note: XMPie is indifferent to how you implement the stored procedure. However, it does require that the end result of the stored procedure is table data. Note that any other return values or output parameters are ignored.
Syntax:
In addition to regular SQL syntax, you can include references to plan variables in the query (making it a parameterized query).
When working with a plan that references multiple data sources, you must specify the data source name to which the query refers, before the query. This is done using the following syntax:
For Select queries:
@{schema name}: SELECT field names FROM tables WHERE condition;
For Stored procedures :
@{schema name}: EXECUTE stored_procedure_name parameter values;
An SQL query that is part of a larger expression must end with a semi-colon (;).
Examples:
If(@{Movie Suggestion ID} != NULL)
{
SELECT @{Type} + [MovieName] FROM movies WHERE MovieId = @{Movie Suggestion ID};
}
else
{
@{Type} + "Gladiator"
}
SELECT firstName FROM customers WHERE id = ?;
SELECT firstName FROM customers WHERE id = |->[id];
SELECT @{title} + firstName FROM customers WHERE id = ?;
EXECUTE sp_name @param = |->[id];
Note: When writing SQL queries referring to Excel and CSV files-based data sources, the native query syntax (for example, [Sheet1$] for Excel) will not work. In this case, you must use the QLingo generic query syntax to refer to table and field names. See "Table Syntax" and "Field Syntax" below for detailed instructions. For other data source types you can use either the native syntax or the QLingo generic syntax.
Use QLingo Generic Query Syntax when Working with Multiple Data Source Types
Your PersonalEffect campaign may be bound to multiple data sources. These data sources may be of different types, for example Excel or Oracle database. Different data source types require slightly different SQL syntax in their queries. Some of these differences occur when using special functions available only with one particular system; other differences are more superficial and can be automatically handled. In particular, the way table and field names are used, differs slightly from one data source type to the other.
Your campaign’s plan may require a calculation that is based on an SQL query. When running campaign’s production, for example, a batch print job, you will bind the plan to a certain data source. Another job in the same campaign may require to bind your plan to a data source of a different type. If the SQL query syntax is data source type-specific, it will fail when a different data source type is selected for production.
The QLingo generic query syntax aims to solve this problem, by providing a method to create SQL queries that are independent of the data source type. This method allows to reference table and field names in a generic manner. Later in production, once a certain data source is bound to the plan, the generic query is translated to the type-specific syntax automatically, and the query is executed correctly.
When writing an SQL query in a uPlan expression, consider first how you would write it for a certain data source type, and then “translate” the table and field references to the QLingo generic syntax. The following tables show how to “translate” the particular database syntax to the QLingo generic syntax.
Table Syntax
|
File/Database |
Native Syntax |
Convert to |
|---|---|---|
|
Text |
[filename#txt] Or [filename#csv] |
@[Tbl]
|
|
Excel |
[Tbl$] |
|
|
Access, SQL Server |
[Tbl] |
|
|
Oracle, IBM DB2 |
"TBL" |
|
|
SyBase |
"Tbl" |
|
|
mySQL |
‘Tbl’ |
|
|
FoxPro |
filename |
|
|
dBase |
[filename#dbf] |
|
|
Informix |
Tbl |
Field Syntax
|
File/Database |
Native Syntax |
Convert to |
|---|---|---|
|
Text |
[Fld] |
@[Tbl].[Fld] |
|
Excel |
[Tbl$].[Fld] |
|
|
Access, SQL Server |
[Tbl].[Fld] |
|
|
Oracle, IBM DB2 |
"TBL". "FLD " |
|
|
SyBase |
"Tbl" ."Fld " |
|
|
mySQL |
‘Tbl’.’Fld’ |
|
|
FoxPro |
Fld |
|
|
dBase |
[Fld] |
|
|
Informix |
Tbl.Fld |
Example:
The query for the text DB format, where filename is the name of the DB file:
SELECT [Fld] FROM [filename#txt]
should be converted in the following way in the uPlan expression:
SELECT @[Tbl].[Fld] FROM @[Tbl]
User View Reference
A User View reference can refer to a complete user view, a complete row, or a single field value.
Syntax:
The reference is made of three parts, each enclosed in square brackets:
[user_view_name][row_index].[field_name]
The row_index and field name are optional. The field name must be preceded by a period (.).
The first row in the user view is referred to by row_index value of zero.
If the row_index is omitted, and the field_name exists, the row_index is assumed to be zero. If the field_name is omitted, a complete row is referenced.
If both the field_name and row_index are omitted, the whole record set is referenced.
Note that QLingo is not case sensitive so the User View Reference can be written in a different case than the actual user view name (or field name).
Example:
A list of possible user view reference syntaxes are as follows:
[UserView_1][2].[lastName]– fetches the data of column "lastName" in row 3
[CustomerUV].[Address] – fetches the Address value of the first row
[PurchasesUV][1] – fetches the entire second row (first row is 0)
[ProductsUV] – fetches the entire user view
An example of using an entire user view reference:
user view 1: select MovieName, MoviePrice from ActionMovies where MovieId = @{MovieSuggestion}
user view 2: select MovieName, MoviePrice from DramaMovies where MovieId = @{MovieSuggestion}
user view 3:
switch(@{Customer Movie Type Liking})
{
case "Action":
[user view 1]
case "Drama":
[user view 2]
}
Row Subscript
The Row Subscript enables you to use an expression to represent the row_index of a user view. The Row Subscript should be an expression that evaluates to a number, and can be used instead of the row_index constant.
Example:
Say, we would like to create a QLingo function that concatenates all rows of a certain field in a user view.
In this example, we have the following table, which is in a user view UV:
|
Child Name |
Child Age |
|---|---|
|
John |
8 |
|
Jin |
12 |
To list of all the children’s names, we create a function of the form:
GetChildrenList(Number i)
{
if(i >= RowCount([UV]))
""
else
{
if(i = 0)
[UV][i].[Child Name] & GetChildrenList(i+1)
else
", " & [UV][i].[Child Name] & GetChildrenList(i+1)
}
}
In the appropriate expression, we can call the function: GetChildrenList(0)
Assignment
The Assignment operator is available for Write expressions only. It allows you to use calculated ADOR value to update one or more database entries.
The Assignment operator can be used by signing a constant value to an ADOR, thus changing the ADOR value to the constant value.
Syntax:
Recipient Field Ref := QLingo Expression
Examples:
In the following example, the literal value ‘senior’ is written to the recipient field (database field) ‘status’ if the ‘age’ variable value is over 40. The literal value ‘adult’ is written to the recipient field (database field) value ‘status’ if the ‘age’ variable value is under 40
|->[status] := if(@{age} > 40)
"senior"
else
"adult"
Another way for using the Assignment operator is by using the key word ‘value’ as a placeholder for input values that will update the Write ADOR through the ICP channel.
Syntax:
Recipient Field Ref := value
Examples:
|->[first] := value
In this example, the control over the update mechanism is in the hands of the application or web site programmer, who wishes to update the database through the ICP channel. For example, a web site developer can create web forms that will be used to update an ADOR value, that, in turn, will be used to update a database entry (or entries), as a part of a customer feedback mechanism.