DSQL Syntax Reference

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.

<catch>

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.

<dbms id="dbmsid">

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.

<include file="filename">

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").

'string constant'

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.

$varname

Reference to a variable. $ARGS is a predefined variable with the command-line parameters. $FILEPATH is set to the script path.

$varname = expression

Assignment. For example:

$staff = <sql>select * from Staff</sql> $staff[1].status = 'd';

expression ~ expression

Concatenation. For example:

$fullName = $onestaff.FirstName ~ ' ' ~ $onestaff.LastName

expr == expr expr != expr

Test for equality or inequality, respectively. Currently only scalar values can be compared.

table [index]

Returns the specified record in an array (first index is 1). The array can be a table, XML data, etc.

[exp, ...]

Array initialiser. The elements of an array can be any object.

{key[->value], ...}

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.

count expression

Returns the number of rows in a table.

delete table current

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.

exists expr.field exists expr

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 [(error-code)]

Exit DSQL interpreter with error code (default 0).

false
true
null

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.

expr in table

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>

label (expr)

The label of an XML element.

left (string,count)

The count first characters of string.

len string length 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.

meta [allresults] expression

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>

table minus 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>

new javaclass (exp, ...)

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.

right (string,count)

The count last characters of string.

sendemail expr

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>

substring (string,start,len)

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.

sysdate [(format)]

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).

table union [all] table

The set union of two tables (normally removes duplicates, unless all is specified). Example:

<sql>select * from Staff where FirstName='Francois'</sql> union <sql>select * from Staff where LastName='Paradis'</sql>

update expression set ...


    Not implemented yet.

    
      while condition statement

    Execute statement while
     condition is true.

    
      xml expression

        xml (option,...,expression)

    Converts to XML.

    
    Fran�ois Paradis


Last modified: Tue Aug 16 16:57:36 Est (heure d'�t�) 2005