Alertra Script Language 1.9

[ next ] [ language reference ] [ table of contents ]

mysql

Interact with a MySQL database server by connecting and optionally executing queries.

connection

On the initial connection, this command will attempt to execute the query “SHOW STATUS” to gain the value of all status variables maintained by MySQL. These variables will be added to the global namespace as MYSQL_STATUS_X where “X” is the name of the variable. For instance, after executing this command:


mysql connect "guest" "love" "mysql"

You could query the value of a status variable like this:


if $MYSQL_STATUS_ABORTED_CLIENTS > 10 then error "aborted clients"

More information on the “SHOW STATUS” query can be found here.

executing queries

After connecting to a MySQL server, you can execute any number of queries. The scripting engine uses a single cursor for a connection to the server, so each query will over write the results from the last. This sample code illustrates how to execute a query and retrieve the results:


mysql exec 'select user_id, email, status from users'
for REC = 1 to $MYSQL_ROW_COUNT begin
mysql cursor next
if $MYSQL_COLUMN_status = "A" then begin
info $MYSQL_COLUMN_0 + ", " + $MYSQL_COLUMN_email + ", Active"
else
info $MYSQL_COLUMN_0 + ", " + $MYSQL_COLUMN_email + ", Not Active"
end
end

After the query is executed, the variable MYSQL_ROW_COUNT is set to the number of rows returned by the query. That variable can be used within a for loop to retrieve the records. Calling mysql cursor next “loads” the next record into global variables. You can access the column values by index or by name (e.g. MYSQL_COLUMN_0 and MYSQL_COLUMN_email respectively).

usage: mysql [connect [user] [password] [database]] [exec [query]] [cursor next] [close]

parameters:

req	name	type	description
Y	operation	keyword	One of: - connect: Connects to the MySQL server, logs in and selects the given database. - exec: Executes the query with the MySQL connection opened by a call to connect . - cursor: Performs cursor operations based on the previously executed query. - close: Closes the connection to MySQL.
N	user	expression	Login ID used to connect to the server
N	password	expression	Password used to authenticate the connection to the server
N	database	expression	Name of database to use after connecting
N	query	expression	The query to execute
N	next	expression	Moves the cursor to the next record

examples

mysql connect “guest” “love” “mysql”

Connect to the given MySQL server with the user ID “guest”, a password of “love”. The database “mysql” will be used.

mysql exec “select * from users”

Executes the query using the existing connection to the MySQL server.

mysql cursor next

Move the resultset cursor from the current record (or the beginning of the resultset) to the next record.

mysql close

Close the current connection to the MySQL server. Also closes the outstanding cursor if necessary.

Alertra Script Language: Language Reference