|
Regardless what kind of data-aware site you need to create, it is very common to retrieve information based on the variables for the current session or stored by the user selections.
 Using variables in SQL sentences
There are two kind of variables that could be used inside a SQL sentence: untyped variables and typed variables.
Untyped variables
As you can notice from the name, this kind of variables lacks of type. There are replaced by his value just before the SQL sentence is sent to the server from being interpreted.
Consider the next example:
|
SELECT * FROM COUNTRY WHERE COUNTRYCODE = '<#CC/>'
|
This SQL sentence assumes that you have a variable named CC in your session variables. Suppose that the variable CC contains the value MX.
This kind of variables will be replaced just before sending the query to the server. So in this case, the sentence will be replaced by:
|
SELECT * FROM COUNTRY WHERE COUNTRYCODE = 'MX'
|
Notice how the variable is literally replaced for his value.
|
Note: Inside your SQL sentences, you can use indistinctly the syntax <#VARIABLE> or <#VARIABLE/> for the untyped variables. Both are considered equivalent. How ever, this is not the case inside the DBSP pages, where you must explicitly close your variables with a slash, like this <#VARIABLE/>.
|
Typed variables
This kind of variables has an explicit type linked to it. The possible types are:
| Integer |
32 bit integer field. |
| String |
Character of string fields. The maximum size of the string depends on the database string type being used. In text blobs, use the Memo type instead. |
| Numeric |
Floating point numeric field. |
| Boolean |
Boolean field. |
| Currency |
Money field. |
| Date |
Date field. The format depends of the country settings for the date format defined on the Locale settings. |
| Time |
Time field. The format depends of the country settings for the time format defined on the Locale settings. |
| Time Stamp |
Date and time field. |
| Memo |
Long text fields. |
| Blob |
Binary large object fields. |
Consider the next example. This is the way that a typed variable could be used.
|
SELECT * FROM COUNTRY ORDER BY COUNTRY WHERE COUNTRY = :CC
|
Here, the CC variable is declared as a Text variable. Notice that the quotes are not needed. The DBSP engine manages automatically with these kind of issues.
When to use untyped variables and when to use typed variables?
We encourage you to use typed variables in these cases:
1. When a master - detail relationship needs to be established between two datasets (You will learn how to do it in the next section).
2. When you need to insert or update fields that are Memo or BLOB fields.
In any other case you could use the untyped variables.
Related topics
Examples
Example 1. Simple untyped parameter.
This example shows how to use an untyped parameter inside a SQL sentence.
 Source code
File: examples/Ex6_3_1.dbsp
<html>
<body>
<H1>Untyped parameter example</H1>
<p>This example shows how to use an untyped parameter inside a SQL sentence. For example, type in the text box the letter "A","B" or "M" and see what happens.</p>
<form name="form" method="post" action="Ex6_3_1a.dbsp">
<input name="PREFIX" type="text"><input type="submit">
</form>
</body>
</html>
|
File: examples/Ex6_3_1a.dbsp
<html>
<body>
<H1>Untyped parameter example</H1>
<p>The country names which name starts with <B><#PREFIX/></B> are:</p>
<table border="1" cellpadding="2">
<tr>
<th>Code</th>
<th>Country</th>
<th>Currency</th>
</tr>
<#DATASET ALIAS="OPEX" QUERYID=8>
<tr>
<td><#COUNTRYCODE/></td>
<td><#COUNTRY/></td>
<td><#CURRENCY/></td>
</tr>
<#NODATA>
<td colspan="3">There are no countries in the table which start with <B><#PREFIX/></B>.</td>
<#/DATASET>
</table>
<br>
<button onclick="javascript:history.back()">Back</button>
</body>
</html>
|
SQL Sentence: ID=8
|
SELECT * FROM COUNTRY
WHERE UPPER (COUNTRY) LIKE UPPER ('<#PREFIX>%')
|
 Test example
Example 2. Simple typed parameter.
This example shows how to use a typed parameter inside a SQL sentence.
Source code
File: examples/Ex6_3_2.dbsp
<html>
<body>
<H1>Typed parameter example</H1>
<p>This example shows how to use a typed parameter inside a SQL sentence. For example, type in the text box the letter "A","B" or "M" and see what happens.</p>
<form name="form" method="post" action="Ex6_3_2a.dbsp">
<input name="PREFIX" type="text"><input type="submit">
</form>
</body>
</html>
|
File: examples/Ex6_3_2a.dbsp
<html>
<body>
<H1>Typed parameter example</H1>
<p>The country names which name starts with <B><#PREFIX/></B> are:</p>
<table border="1" cellpadding="2">
<tr>
<th>Code</th>
<th>Country</th>
<th>Currency</th>
</tr>
<#DATASET ALIAS="OPEX" QUERYID=9>
<tr>
<td><#COUNTRYCODE/></td>
<td><#COUNTRY/></td>
<td><#CURRENCY/></td>
</tr>
<#NODATA>
<td colspan="3">There are no countries in the table which start with <B><#PREFIX/></B>.</td>
<#/DATASET>
</table>
<br>
<button onclick="javascript:history.back()">Back</button>
</body>
</html>
|
SQL Sentence: ID=9
|
SELECT * FROM COUNTRY
WHERE UPPER(COUNTRY) LIKE UPPER (:PREFIX || '%')
|
Test example
Previous | Next
|
