Shortcuts: <!-- -->, <connect>, <data>, <dbms>, <dsql>, <include>, <quote>, <sql>, <table>, #, //, /* */, '', "", + - * /, $, =, ~, != ==, [], [expr1,], {expr1,}, . (dot), .., and, count, delete, diff, exists, exit, false, foreach, gt gte, if, in, insert, inter, label, left, len, length, lt lte, m://, meta, minus, new, not, null, or, print println, right, s:///, sendemail, sub, substring, sysdate, true, union, update, while, xml
XML comment.
Not implemented yet. Will be used to catch exceptions inside an sql or dsql element.
<connect [id="connectionid"] dbmsid="dbmsid"
[driverid="driverid"]>
Parameters for a connection. The id
can be left
out if only one connection is used. If the driverid
is left out, DSQL will use the first driver matching the parameters.
The contents
of this element defines the parameters for the connection. It can
vary according to the dbms and the chosen driver,
but usually contains
at least username
and password
.
(see more details)
<username>
the user name
<password [encrypt="dsql"]>
the password.
To generate the encrypted value of a password,
use dsqlencrypt
.
<data [type="content-type"]
[file="filename"]>
Complex data, which can either be
inline (i.e. contents of the data element) or in an external
file (specified with a file
attribute or element).
The type
specifies the type of data: xml, excel,
text, etc.
The data is meant to be accessed one "record" at a time, e.g. a row for excel tables, an element for XML documents, etc. For example:
foreach $row in <data type="excel" file="test.xls"/>
 ace;  ace; println $row;
$xml = <data>
<Name>
<FirstName>François</FirstName>
<LastName>Paradis</LastName>
</Name>
<Age>Young</Age>
</data>
More details on xml and other data support.
Defines the supported options and drivers of a
database. Normally a script just references the dbms definitions in
dbmsdefs.dsql
, but it can also define its own.
<driver id="driverid"
javaclass="driverclass">
the driver and its
connection url.
<dsql [trace="nowarning|no|yes|sql|debug"]
[out="variable-or-filename"]>
The document element, also used for "blocks" of instructions. For example:
if $name='Francois' then
<dsql>
$nameHTML ='François' print 'Great name!' </dsql>
The trace
attribute controls the output
of messages for debugging. The default is no
: yes
prints some, sql
also prints the SQL code sent to the databases,
and debug
prints full information. Use nowarning
to suppress warning messages about drivers or connections. Note that the
trace is inherited from parent elements; to set a trace for the whole script
just put the trace attribute in the document tag.
The out
attribute can be used to
redirect output within this block to a variable or file.
Includes a user-defined script. Will look in
$DSQL_HOME/scripts
or in the semicolon-separated list
in $DSQL_INCLUDE
, if defined.
Note that dbmsdefs.dsql
is always included by default.
<sql [connectid="connection id"]
[mode="dynamic|prepared"]
[primaryKeys="comma-separated-keys"]
>
Returns the results of an SQL statement.
If the connectid
attribute is omitted, the first
connection in the file is assumed. Note that the static, textual contents
must conform to valid XML: therefore the < and > operators
should be written <
and >
respectively.
For example:
print
<sql>select * from Department
where DeptName like '$pattern'
and nbemp > 50</sql>
Variables are automatically "escaped" when
surrounded by single quotes, i.e. in the example above,
any quote in $pattern
would be escaped.
The following is not implement yet!
By default the SQL expression dynamic
, which
means it is re-compiled by the DBMS every time.
By setting the mode
to prepared
, you can request it to be pre-compiled (NB: not
supported by all drivers!). The following elements are available to
compile the parameters:
<in>
<out>
<inout>
<table [connectid="connection id"]
[primaryKeys="comma-separated-keys"]
>
The contents of this element should be a table
name. The results are the table contents, i.e. equivalent to
<sql>select * from tablename</sql>
.
For example:
print <table primaryKeys="ficheno">OldStaff</table>
inter <table
primaryKeys="ficheno">NewStaff</table>
The primaryKeys
attribute is optional, it is used
to optimise operations such as minus
or inter
.
DSQL comment. # and // are line comments (i.e. the remaining of the line is commented). # can be used on the first line of a file as a so-called SheBang on unix/linux to execute the scripts directly from the command-line (i.e. without invoking "dsql").
A string constant. Following the SQL format, the escape code for ' is ''.
"string with variable substitution"
String à-la Perl, where variables are interpreted, and escape codes can be used (e.g. \n, \", \$, \.). For example:
print "staff #$i: $staff.name"
expr + expr
expr - expr
expr * expr
expr / expr
Arithmetic operations. These operators have the usual precedence, which can be overriden with brackets. They also try to preserve the type of their arguments, i.e. two integers will return an integer, except for division, which always returns a float.
Reference to a variable. $ARGS
is a
predefined variable with the command-line parameters.
$FILEPATH
is set to the script path.
Assignment. For example:
$staff =
<sql>select * from Staff</sql>
$staff[1].status = 'd';
Concatenation. For example:
$fullName = $onestaff.FirstName ~
' ' ~ $onestaff.LastName
Test for equality or inequality, respectively. Currently only scalar values can be compared.
Returns the specified record in an array (first index is 1). The array can be a table, XML data, etc.
Array initialiser. The elements of an array can be any object.
Hash (associative array) initialiser.
table . column-name
table . 'column-name'
table . @column-index
table . $varname
table . (expr)
Returns the specified column (first column index is 1).
value1 .. value2
expr [value1 .. value2]
Returns a range from value1
to
value2
. Example:
println
<table>staff</table>[20..30];
expr and expr
expr or expr
not expr
Boolean operators. expr
are evaluated as
follows: boolean values (e.g. result of a comparison operator)
take their values, strings return true if non-empty, numbers return
true if non-zero, tables return true if they have a row.
Returns the number of rows in a table.
Delete current row from table.
expr1 diff [new] [upd] [del] expr2
Returns differences between tables. The following
options are allowed: new
returns all new rows in expr2,
upd
returns updated rows in expr2, and del
returns missing (deleted) rows in expr2. Default is new
and upd
.
Note that primary keys need to be known for
upd
. New and Del can work without primary keys, although
less efficiently.
If expr
is a table, returns true
if it contains one or more rows
(i.e. similar to SQL exists); otherwise return true if field
exists (for example to test if a key is defined in a hash table).
Exit DSQL interpreter with error code (default 0).
Constants.
foreach $varname in expression statement
Execute statement for each record in expression.
exp gt exp
exp gte exp
exp lt exp
exp lte exp
Numeric comparison: greather than (gt) or equal (gte); less than (lt) or equal (lte).
if(condition) statement; [else statement;]
Conditional statement.
Returns true if expr
is included
in table
. expr
can be a scalar
value (in which case it is compared to the first column in the table),
a record or a table. Examples:
print 'Francois' in $allstaff // compares only with first column
print $newstaff[1] in $allstaff
print $newstaff in $allstaff
insert into table values (exp,...)
insert into table values exp
Insert values into a table. In the first form, expressions are scalars, while in the second form they can be records or tables.
insert into <table>Staff</table>
values $record
table inter table
table intersection table
The intersection of two tables. Example:
<sql>select * from Staff
where FirstName='Francois'</sql>
inter <sql>select * from Staff
where LastName='Paradis'</sql>
The label of an XML element.
The count first characters of string.
Length of string.
m:input[fields]/match-expr/modifiers
s:input[fields]/match-expr/replace-expr/modifiers
Match and substitute operations using regular expressions. The expressions supported are those in Sun JDK Pattern class. Both operations return a record holder, which can be iterated to get all matches, or accessed directly to get the current (or first) match. Captured groups can be accessed in three ways: .@int, .groupint, or .name (if name was defined as a field). Each group is a string, with two additional fields: start and end (matching offsets in input string).
foreach $match in m:it's raining cats and dogs:animal/(cat|dog)s/
println $match.animal; // or $match.@1, or $match.group1
println (m:$text/dogs/i).group0.start; // offset of match
For more information, see regular expressions.
Returns meta-information about a data structure. For examples, for SQL table results a table is returned with the following columns: ColumnName, SQLType, NativeType, DisplaySize, Precision, Scale, Nullable and PrimaryKey. Example:
print meta
<table>Staff</table>
The set difference of two tables. Example:
<sql>select * from Staff
where FirstName='Francois'</sql>
minus <sql>select * from Staff
where LastName='Paradis'</sql>
Creates a new object (see java support for more details). For example:
new java.io.File("myfile")
print expression
print [allresults] [noheader] [format] expression
[[append] to [file] var-or-file]
println ...
Print expression to the screen or into the variable or file, if specified.
The option allresults
is used
for databases (such as SQL Server) that support multiple results:
in that case all results will be printed (as opposed to just the
first one). This option should be used with care because DSQL
cannot backtrack results, and print will "consume" all results
(in other words, results cannot be accessed after a print all).
The option format
controls the format
of the output: table
(the default) prints tables
in a "graphical" way, csv
is for comma-separated values.
To print XML, use the xml function.
The count last characters of string.
Sends an email. expr must be tabular data with the following columns defined: host, from, to, subject, and message. Multiple recipients are currently not supported. Example:
sendemail <data>
<host>smtp.iro.umontreal.ca</host>
<from>paradifr@iro.umontreal.ca</from>
<to>paradifr@iro.umontreal.ca</to>
<subject>Hello!</subject>
<message>Hello!</message>
</data>
The substring (first position is 1).
sub name [($par1,$par2,...)] <dsql>...
</dsql>
sub name [($par1,$par2,...)] <sql>...
</sql>
sub name [($par1,$par2,...)] <data>...
</data>
Defines a subroutine.
Returns a Date object set to the current time.
In a text context, returns the date formatted as a string
using the locale default, or format
if supplied.
The following convenience fields are defined: year
month,
day,
dayOfWeek,
hour,
minute,
second,
milliSecond,
zone (the plain text time zone),
and time (the date value in milliseconds).
The set union of two tables (normally removes
duplicates, unless all
is specified). Example:
Not implemented yet. Execute
Converts to XML.<sql>select * from Staff
where FirstName='Francois'</sql>
union <sql>select * from Staff
where LastName='Paradis'</sql>
statement
while
condition
is true.xml expression
xml (option,...,expression)
François Paradis
Last modified: Tue Aug 16 16:57:36 Est (heure d'été) 2005