sql
|
|
sql
|
The sql function provides a unified interface to the underlying
Embedded MySQL library. This function supports a subset of the
available MySQL API functions. The return type of the function
depends on the arguments passed to the function. Usage The sql function is used to perform the following operations:
The operation to be performed is typically specified in the argument
{operation}. The number and types of the succeeding arguments
depends on the type of specified operation.
(sql operation ...) (sql connect: hostname database username password) (sql connect: "") (sql connect: "" database) (sql connect: #void) (sql connect: #void database) (sql connect: -1) (sql connect: -1 database) (sql connect:) (sql disconnect: sqlHandle) (sql disconnectall:) (sql insert: sqlHandle sqlTable brickObj binarySw) (sql insert: sqlHandle sqlTable brickObj) (sql escape: sqlHandle string) (sql info: sqlHandle) (sql test: sqlHandle) (sql sqlHandle sqlQuery ...) (sql sqlQuery ...) The connect operation returns an Integer representing the
MySQL connection handle. The disconnect operation returns a Boolean representing the
result of the operation. The disconnectall operation also returns a Boolean
representing the result of the operation. The query operation returns an Integer or a Brick depending
on the SQL statement used. For SELECT, SHOW or DESCRIBE queries,
a Brick object containing the results is returned.
For INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP queries,
an Integer value is return. A value zero (0) means that the operation
was successful, and a value greater than zero (0) denotes the
no. of rows or tables which were affected by the operation. The insert operation returns a Boolean
representing the result of the operation. The escape operation returns an escaped String which can
used safely in a query. The info operation returns a Structure which contains
information about the MySQL connection handle such as type, host, and
username. The test operation returns true if the connection handle is
valid and false otherwise.
Expression:
Arguments
Name
Type
Description Argument: operation Symbol
Specifies the operation to be performed. With the exception of
the query operation, this argument is always the first in the
argument list. The following are valid operations: connect,
disconnect, disconnectall, insert, escape, info, and test. Argument: hostname String, Text, Symbol or Integer
Specifies the hostname or IP address of the MySQL server.
This parameter is optional. If not specified, #void or "",
a connection to the embedded server will be returned.
This will become the default embedded connection handle.
This connection handle will be kept internally until the
context is closed or the handle is manually disconnected.
Succeeding calls to connect without any parameters,
using #void or "", would return the same connection handle.
To create a unique connection handle, use -1 instead.
The default embedded connection handle is also kept in a
global context variable named _defaultSqlHandle. Argument: database String, Text or Symbol
Specifies the name of an existing database. This parameter is optional.
It is not necessary that the database is specified during connect.
The database can be set using the USE SQL statement.
Used in the connect operation. Argument: username String, Text or Symbol
Specifies the username to be used for the connection.
This parameter is not required for connections to the embedded
server and optional for connections to external servers.
Used in the connect operation. Argument: password String, Text or Symbol
Specifies the password to be used for the connection.
This parameter is only applicable if the argument {username}
is specified.
Note that an empty or "" password is different from #void.
Using #void as password will only work if the server is configured
to allow it.
Used in the connect operation. Argument: sqlHandle Integer
Specifies the MySQL connection handle, which was previously
returned from a connect operation.
This parameter is required in all operations except
connect, disconnectall, and query.
If set to 0 or #void, the default embedded connection is used.
The default connection is created implicitly. Argument: sqlQuery String, Text, Symbol, Date, Money, or BitVector
Contains the SQL statement to be executed.
The sql function accepts up to 20 arguments which will
be concatenated to a single query string.
Each argument is internally converted to it's string
representation. Some types are converted to an SQL compatible
format.
A Date argument will be converted to the format: YYYY-MM-DD hh:mm:ss.
A BitVector argument will be converted to the format: b'########'.
A Money argument will be converted to a Number type. Argument: sqlTable String, Text, or Symbol
Specifies the database table where the contents of the Brick
object will be saved.
The table will be automatically created if it does not exist yet.
Used in the insert operation. Argument: brickObj Brick
Specifies the Brick object which contains the data to be saved.
Used in the insert operation. Argument: binarySw Boolean
If true, Objects are stored using their binary format, otherwise
the ASCII format is used. This parameter is optional.
The ASCII format is used by default.
Used in the insert operation. Argument: string String, Text, or Symbol
Specifies the string to be escaped.
Used in the escape operation.
Returns:
Here are a number of links to Lambda coding examples which contain this instruction in various use cases.
Here are the links to the data types of the function arguments.
| Symbol | String | Text | Integer |
| Date | BitVector | Money | Brick |
Here are also a number of links to functions having arguments with any of these data types.
|
|
Analytic Information Server (AIS)AIS Component Systems
|