SQLexecute
SQLexecute |
Purpose
Synopsis
procedure SQLexecute(s:string|text)
procedure SQLexecute(s:string|text, a:array)
procedure SQLexecute(s:string|text, l:list)
procedure SQLexecute(s:string|text, m:set)
procedure SQLexecute(s:string|text, lp:list, a:array)
procedure SQLexecute(s:string|text, lp:list, l:list)
procedure SQLexecute(s:string|text, lp:list, m:set)
Arguments
|
s
|
SQL command to be executed
|
|
a
|
An array
|
|
l
|
A list
|
|
m
|
A set
|
|
lp
|
A list of parameters
|
Example
The following example contains four
SQLexecute statements performing the following tasks:
- Get all different values of the column color in the table pricelist.
- Initialize the arrays colors and prices with the values of the columns color and price of the table pricelist.
- Create a new table newtab in the active database with 2 columns, ndx and price.
- Add data entries to table newtab.
declarations
prices: array(1001..1004) of real
colors: array(1001..1004) of string
allcolors: set of string
end-declarations
SQLexecute("select color from pricelist", allcolors)
SQLexecute("select articlenum,color,price from pricelist",
[colors,prices])
SQLexecute("create table newtab (ndx integer, price double)")
SQLexecute("insert into newtab (ndx, price) values (?,?)", prices)
Further information
1. This procedure executes the given SQL command. The user is referred to the documentation of the database driver he is using for more information about the commands that are supported by it. Note that if extended syntax is in use (default), parameters usually noted '?' in normal SQL queries may be numbered (like '?1','?2',...) in order to control in which order are mapped columns of data source table to Mosel arrays. This feature is especially useful when writing 'update' queries for which indices must appear after values (
e.g.
"update mytable set datacol=?2 where ndxcol=?1").
2. For output commands (like
insert into) this procedure accepts arrays, sets and lists of basic types (integer, real, string or Boolean) as well as module types for which from/to string conversions are available. Record types composed of scalars or other records can also be used (the fields that cannot be handled are silently ignored). It is also possible to use a list of arrays of basic types (all arrays must be indexed by the same sets) or a list of scalar elements of different basic or module types.
3. For input commands (like
select from) the same restrictions apply for arrays,lists and list of arrays but sets must be of a basic type.
4. The form using an extra list argument will be used with input commands requiring parameters: the list defines the value of the parameters.
Related topics
Module