Executes arbitrary SQL commands in the currently open database. when a select
query has been processed, and the -usefetch option is not used, the command returns a list
of lists: each element of this list contains the selected values of one record (in a list).
Otherwise nothing is returned
sql contains the commands to be executed. When sql contains
placeholders (indicated by ?), extra parameters must be given (param, that will
be used to fill in the gaps. If an sql error occurs, all changes made in the current
transaction will be rolled back. If no explicit has been explicitly started by the user
using "objectName begin", this means all changes made in the current exec.
The following options can be present:
- -flat
- if present, the result will be returned as a flat list instead of a list of lists.
Each element in the list will contain the value of one column. eg. If a query returns
2 rows of each 2 elements, a list of 4 elements is returned
- -usefetch
- if present, the command does not return the resultset of a select query as a list.
The fetch method will be used to get the results instead.
- -nullvalue nullval
- nullval will be returned for each NULL value in the result instead of an empty string
For sql commands using input parameters, parameters which are given a value of nullval
will put a NULL value in the ?
- -blobid
- if present, return blobids instead of blob values for select queries. The id can be used to access the
blob using the blob subcommand (eg. to get only parts of really large blob). If the dbi object does not
support this function (check with "object supportd blobid"), this option will be ignored
For update and insert functions, the blob fields must be given a blobid (created with the newblob subcommand)
as a parameter.
- -cache
- if present, the current query is cached (using prepared statement) for future use. If the
same query is run again with the -cache option, it will run faster. dbi objects that do not
support caching should ignore the option
example:
db exec {select "value" from "test_table" where "id" = ?} 100
Fetches one line of data of the result of a previous query. This will only work after
the exec method has been invoked with the -usefetch option. Normally, the next line in
the result will be fetched. The optional arguments can be used to fetch a specific
line and/or field. by specifying option, other information about the result may
be obtained.
When calling exec again, the results are cleared, and cannot be fetched any longer
using the fetch method. If you want to use the exec method while keeping the
resultset available, use a clone (see further) of the dbi object.
If present, line is an an integer >= 0 indicating which line of the result to fetch.
If line is "current", the current line will be fetched. Not all databases allow
backward positioning in a result set, so not all objects that support the dbi interface
will be able to fetch lines earlier than the current line. field is also an integer
>= 0 indicating the field of the result to fetch.
If option is present,
information about the result is returned instead of the result data itself. It can have
any of the following values:
- pos
- return the current position in the result. The first line is position 0
- fields
- return the fields present in the resultset
- isnull
- returns 1 if the result contains a NULL value at the given line and field
- lines
- returns the number of lines in the result. This function cannot always be supported by a dbi object
The following options can be present:
- -nullvalue nullval
- nullval will be returned for each NULL value in the result instead of an empty string
examples:
db fetch
db fetch isnull current 1