| Warning |
|---|
Looking for the documentation on the newest versions of SIL Engine and the Simple Issue Language for Jira 8 for Server/Data Center? Click here ! |
| Info | ||
|---|---|---|
| ||
This routine is available since katl-commons starting with SIL Engine™ 1.0, changed in 2.5.8. |
Syntax
...
sql(datasourceName, sqlstring, [...])
Description
...
| Excerpt |
|---|
Executes the SQL phrase over the defined datasource. |
For selects returning multiple rows, it concatenates the values (
...
for instance you select 2 values and the select returns 4 rows, you will have 2*4 = 8 values). For updates, it returns the update count.
Parameters
...
...
Parameter name | Type | Required | Description |
|---|---|---|---|
datasourceName | string | yes |
Datasource name or JNDI name. For |
Jira database, this is set to "jdbc/JiraDS" by default. | ||
sqlstring | string | yes |
SQL string |
...
. |
Starting with SIL Engine™ 2.5.8, the routine accepts multiple parameters, in this case the sql statement being pushed as prepared into the database (check the second example below for right syntax). The old syntax is still functional too.
Returns
...
string []
Example
...
Example 1
...
| Code Block |
|---|
string [] results = sql("datasourceName", "select project_id from project_lookaside where project_code='" + project + "'");
//Note: this example is open to SQL injection if 'project' is not the standard issue project (derived) but a variable supplied by user. Take care. |
Example 2
...
| Code Block |
|---|
string [] results = sql("datasourceName", "select project_id from project_lookaside where project_code=?", project);
//this is better. No sql injection possible. |
Example 3
...
It is tedious to iterate over a result in its flat form. The better way is to do it like this:
| Code Block |
|---|
struct Person {
string fName;
string lName;
number age;
}
Person [] results = sql("datasourceName", "SELECT fName, lName, age FROM person WHERE group_member = 'Y'");
//checks to see if there are results there are skipped for brevity, but
//now you can iterate over and use the dot notation, and 'age' is really a number.
string firstResult = results[0].fName + " " + results[0].lName + " " + results[0].age; |
...
Notes
...
| Note |
|---|
To see how you should configure the data source, check the corresponding SQL data sources configuration chapter: SQL Data Sources. |
See
...
also
| Filter by label (Content by label) | ||||||||
|---|---|---|---|---|---|---|---|---|
|