SQL: Statements

Requires xelagot version 3.600 or higher, and a few dlls. Please read the introduction for more details before proceeding.

Variables shown in black may be substituted by literal values, variables shown in red are assigned the result of the operation, variables in green may not be substituted by their literal equivalents.

ifSQLer statement1
Else statement2 xelagot 3.600

Checks if the xgDLLSQL.dll has been loaded. It must be in the bot's Plugins folder. This dll is required for SQL connections. One or two more dlls are required for specific SQL engines, see the introduction.

SQLCreateConnector %ID xelagot 3.600

This is the first statement to allow a connection to an SQL database: it creates a connector object. The statement returns a numeric ID in variable %ID, which must be used in all subsequent statements for this connector. If the ID = 0, the statement failed to create the object. You can create as many connectors as you need (maximum 50 per script), each has its own ID. The next step after creating the connector object is to connect, see SQLConnect.

Connectors are freed (destroyed) with the SQLFreeConnector %ID statement, or when the script ends.

You can use one connector for different successive connections, only the last connection will be active.

SQLFreeConnector %ID xelagot 3.600

Frees a connector object, closing the connection if necessary. After this statement is executed, the ID becomes invalid.

SQLConnect %ID /s_Drivers /s_Connection %rc $error xelagot 3.600

Opens a connection to a database using the ID of a connector object (see SQLCreateConnector). It requires two string lists: one contains the drivers used, the second one has the connection parameters, as described in the introduction. It returns a value in %rc. If this value is negative, a connection error occurred and $error contains the text of the error message. If a connection was already open for this connector, this statement closes it before opening the new connection. The next step is to query, see SQLQuerySelect and SQLQueryOther.

Here below is an example of the Drivers and Connection list for a mySQL connection to local host, database TDM:

in /s_Drivers:

LibraryName=plugins\dbx4mysql.dll GetDriverFunc=getSQLDriverDBX4Mysql VendorLib=notused.dll

in /s_Connection:

HostName=localhost Database=TDM User_Name=root Password=my_wonderfull_pw BlobSize=-1 ErrorResourceFile= LocaleCode=0000 Server Port=

SQLDisconnect %ID xelagot 3.600

Closes an open connection to a database. The Drivers and Connection values passed in SQLConnect are remembered by the Connector object. Once a connection has been made, even if you close it, you can issue SQL statements: the Connector will automatic reconnect.

SQLQuerySelect %ID $query /s_rows %keyField %rc $error xelagot 3.600

for sql queries that return rows of data
examples: SELECT, SHOW TABLES, SHOW DATABASES, DESCRIBE and some others.

Syntax:
%ID contains the connector object's ID
$query contains the SQL query statement
/s_rows is a string list that will receive the results of the query. Each line is a comma separated list of fields, optionally organised as a name=value pair (see %KeyField). Please note that if any field in the SQL database contains a percent symbol (%), a comma or an equal sign (=), the following substitutions take place: % to %1, comma to %2, = to %3. The 'new' style string list database statements cope automatically with this encoding, see the string list page.
%keyField allows you to specify which field number will act as name in xelagot name=value pairs of the items in /s_rows. The first field in the row is 0, the next one is 1, and so on. If you set it to -1 or to some other positive number out of range, the rows will not be formatted as name=value, but will be plain comma separated strings (-2 or lower are reserved for future enhancements). The field selected as 'name' is removed from the comma separated list, and added at the front of the string, followed by an equal sign.
%rc receives the number of rows returned. If an error occurs, it has a negative value, and the error message is in $error

The number of rows received is limited by the bot. See statement SQLMaxRows for how to change that for xelagot version 3.606 and higher.

Example of one of the rows returned by a SELECT statement using %keyField = -1 in a citizen database where the primary key is the citizen number:
XelaG,1949-01-22,289499,male

Example of using %keyField = 2 in the same SQL statement:
289499=XelaG,1949-01-22,male

To be able to use this string list as a xelagot database new syle, you must chose your %keyField carefully: 0 for the first field, 1 for the second, etc. The content of the chosen field must be unique, i.e. no two records may have the same content in that field because the name in a name=value pair must be unique. For database 'new' style, see the DB prefixed statements that replace the SList statements on the string list page: there is a whole section dedicate to this.

It is a good idea to test your SQL statements and the %keyField before including them in a script. You can do that with xgTestSQL.exe.

SQLQueryOther %ID $query %rc $error xelagot 3.600

for sql queries that do NOT return rows of data
examples: INSERT, UPDATE, USE, CREATE TABLE, MODIFY TABLE and others

Syntax:
%ID contains the connector object's ID
$query contains the SQL query statement
%rc receives the number of rows affected. If an error occurs, it has a negative value, and the error message is in $error

SQLMaxRows %max xelagot 3.606

The maximum number of rows returned by the SQLQuerySelect statement can be modified with this statement. By default, the script uses the value indicated in the bot's ini file, section [SQL], which, if it has not been manually modified, is 20.

If %max = 0, then the number of rows is unlimited (caution using this setting!); if it is larger than 0, it works as expected. If it is negative or this statement has never been used in the action script, the ini file default is used (this is always the case in versions previous to 3.606).

The steps are: create a connector with SQLCreateConnector and keep the ID, create a connection through this connector with SQLConnect, then use SQLQuerySelect or SQLQueryOther to do your queries.

Date and time in Delphi (the programming language used to make Xelagot) do not allow NULL or invalid values: SQL NULL or invalid dates and times are converted to Delphi's default zero date: either 0000-00-00 or 1899-12-30. Avoid these NULL fields. The date and time format of SQL and of Delphi are very different. To bridge this difference, the following statements have been added to the Action Script:

SQLDateTime $t !t	xelagot 3.600 converts a DateTime variable value to a string in this format: YYYY-MM-DD hh:mm:ss
SQLDateTime !t $t	xelagot 3.600 converts a string to a DateTime variable value. The string must have one of the the following formats: YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss Both the date and the time will be changed in !t. If the string is a literal, you must enclose it in double quotes: SQLDateTime !t "2004-03-24 06:33:12"
SQLDate $d !t	xelagot 3.600 converts the date part of a DateTime value to a string in this format: YYYY-MM-DD
SQLDate !t $d	xelagot 3.600 writes the date part of a string to the date part of the DateTime variable. The string must have one of the following formats: YYYY-MM-DD hh:mm:ss YYYY-MM-DD If the string is a literal, you must enclose it in double quotes: SQLDate !t "2004-03-24"
SQLTime $t !t	xelagot 3.600 converts the time part of a DateTime value to a string in this format: hh:mm:ss
SQLTime !t $t	xelagot 3.600 writes the time part of a string to the time part of a DateTime variable. The string must be in one of these formats: YYYY-MM-DD hh:mm:ss hh:mm:ss If the string is a literal, you must enclose it in double quotes: SQLTime !t "23:05:00"

Example:

GetDateTime !t
SQLDateTime $a !t
SayConcat "the date and time is " $a

More usefull statements added to xelagot 3.6:

EscapeSTR $r $a $c $e	xelagot 3.600 searches in $a for characters specified in string $c, and prepends to them the escape character specified in $e. If the length of $e is zero, changes nothing, if it is bigger than one character, it uses the first character as escape character. The result is assigned to $r. For example: $a = "my dog's tail" EscapeSTR $r $a "'" "\" will have in $r my dog\'s tail Another example: $a = "\my dog's tail says: ""swoosh""" EscapeSTR $r $a "'\""" "\" $a contains: \my dog's tail says: "swoosh" and $r has \\my dog\'s tail says: \"swoosh\" Notice that double quotes must be written twice inside a literal string, a single double quote is interpreted as marking the end of the string by the xelagot action script engine. In the example above they are highlighted in red. In bold fuchsia you see the characters that have been escaped. Before you Concat an SQL query string, you will need to EscapeSTR certain characters in string fields using a backslash \, usually these three: single quote ', double quote " and backslash \, like in the last example above (in bold blue). In some contexts you will need to EscapeSTR the characters percent % and underscore _, if they can be misinterpreted as wildcards. Example: GetChatPerson &p GetName $n &p GetChatline $a EscapeSTR $m $n "'\""" "\" EscapeSTR $r $a "'\""" "\" Concat $q "UPDATE myTable SET chat='" $r "' WHERE name='" $m "';" SQLQueryOther %id $q %rc $error IfInt %rc = 0 SayConcat "a record for " $n " does not exist" Else IfInt %rc < 0 SayConcat "an error happened: " $error Else SayConcat "rows affected: " %rc
DBEncode $r $a	xelagot 3.600 encodes string $a and assigns the result to $r. The characters encoded are: % to %1 , to %2 = to %3 This makes a string suitable to be included in string lists used as databases. Do not use this statement when working with the new DB string list statements, they apply DBEncode and DBDecode automatically! See here for the new DB statements.
DBDecode $r $a	xelagot 3.600 decodes string $a and assigns the result to $r. The characters decoded are: %1 to % %2 to , %3 to = To be used on strings encoded with DBEncode. Do not use this statement when working with the new DB string list statements, they apply DBEncode and DBDecode automatically! See here for the new DB statements.
To help working with xelagot string list databases, a set of string list statements has been added to xelagot 3.600. They automatically encode and decode strings with DBEncode and DBDecode, when related to string variables ($a) or the world part of location family variables (@a, ~a and &a). All these statements start with DB instead of SList. See the string lists page for details.

It is advisable, when querying 'SELECT', not to use 'SELECT *' but to specify the field names, for example:

Concat $query "SELECT citnumber,life_points,shots_left FROM mygame WHERE citnumber='" %c "';"
%keyField = 0
SQLQuerySelect %ID $query /s_rows %keyField %rc $error

will give something like this (1 row if 'citnumber' is a key field):

289499=20,3

In this way, you have full control of the order in which the fields are set when the database replies, and your %KeyField value will always select the correct field, even if you change the table structure.

The use of %keyField = -1 in SQLQuerySelect

Normally, you want to retrieve your data already formatted in the xelagot DB format, using a value for %keyField greater than -1. This requires that your key field be unique, as it will be used as a name in the name=value pair and names must be unique. But ocasionally, this condition is not met. As from xelagot 3.606 you can use a value of %keyField = -1, which retrieves a list (say /s_rows) containing strings with (DBEncoded) comma separated fields, and then use the statement DBRenumber /s_Rows to prepend a numeric name. For example, using %KeyField = -1:

$Obj = "sign1.rwx"
Concat $q "SELECT Obj,Pos,Description,Action FROM MyObjects WHERE Obj='" $Obj "';"
SQLQuerySelect %ID $q /s_rows -1 %rc $error

Now /s_rows might look like this:

sign1.rwx,5n 3w 0a 180,Welcome!,create sign bcolor%3black color%3white
sign1.rwx,2n 3w 0a 180,Hello!,create sign bcolor%3black color%3white

You obviously see you could not have used %keyField = 0, as this field contains duplicate 'sign1.rwx' items. The list you downloaded can not be used as xelagot database. But if you now add the statement DBRenumber /s_rows, the list will look like this:

1=sign1.rwx,5n 3w 0a 180,Welcome!,create sign bcolor%3black color%3white
2=sign1.rwx,2n 3w 0a 180,Hello!,create sign bcolor%3black color%3white

which is a valid xelagot database. The numbers prepended go from 1 to %c as retrieved with DBCount /s_rows %c

Index

SQL: introduction

ifSQLer statement1 Else statement2	xelagot 3.600 Checks if the xgDLLSQL.dll has been loaded. It must be in the bot's Plugins folder. This dll is required for SQL connections. One or two more dlls are required for specific SQL engines, see the introduction.

SQLCreateConnector %ID	xelagot 3.600 This is the first statement to allow a connection to an SQL database: it creates a connector object. The statement returns a numeric ID in variable %ID, which must be used in all subsequent statements for this connector. If the ID = 0, the statement failed to create the object. You can create as many connectors as you need (maximum 50 per script), each has its own ID. The next step after creating the connector object is to connect, see SQLConnect. Connectors are freed (destroyed) with the SQLFreeConnector %ID statement, or when the script ends. You can use one connector for different successive connections, only the last connection will be active.
SQLFreeConnector %ID	xelagot 3.600 Frees a connector object, closing the connection if necessary. After this statement is executed, the ID becomes invalid.

SQLConnect %ID /s_Drivers /s_Connection %rc $error	xelagot 3.600 Opens a connection to a database using the ID of a connector object (see SQLCreateConnector). It requires two string lists: one contains the drivers used, the second one has the connection parameters, as described in the introduction. It returns a value in %rc. If this value is negative, a connection error occurred and $error contains the text of the error message. If a connection was already open for this connector, this statement closes it before opening the new connection. The next step is to query, see SQLQuerySelect and SQLQueryOther. Here below is an example of the Drivers and Connection list for a mySQL connection to local host, database TDM: in /s_Drivers: LibraryName=plugins\dbx4mysql.dll GetDriverFunc=getSQLDriverDBX4Mysql VendorLib=notused.dll in /s_Connection: HostName=localhost Database=TDM User_Name=root Password=my_wonderfull_pw BlobSize=-1 ErrorResourceFile= LocaleCode=0000 Server Port=
SQLDisconnect %ID	xelagot 3.600 Closes an open connection to a database. The Drivers and Connection values passed in SQLConnect are remembered by the Connector object. Once a connection has been made, even if you close it, you can issue SQL statements: the Connector will automatic reconnect.

SQLQuerySelect %ID $query /s_rows %keyField %rc $error	xelagot 3.600 for sql queries that return rows of data examples: SELECT, SHOW TABLES, SHOW DATABASES, DESCRIBE and some others. Syntax: %ID contains the connector object's ID $query contains the SQL query statement /s_rows is a string list that will receive the results of the query. Each line is a comma separated list of fields, optionally organised as a name=value pair (see %KeyField). Please note that if any field in the SQL database contains a percent symbol (%), a comma or an equal sign (=), the following substitutions take place: % to %1, comma to %2, = to %3. The 'new' style string list database statements cope automatically with this encoding, see the string list page. %keyField allows you to specify which field number will act as name in xelagot name=value pairs of the items in /s_rows. The first field in the row is 0, the next one is 1, and so on. If you set it to -1 or to some other positive number out of range, the rows will not be formatted as name=value, but will be plain comma separated strings (-2 or lower are reserved for future enhancements). The field selected as 'name' is removed from the comma separated list, and added at the front of the string, followed by an equal sign. %rc receives the number of rows returned. If an error occurs, it has a negative value, and the error message is in $error The number of rows received is limited by the bot. See statement SQLMaxRows for how to change that for xelagot version 3.606 and higher. Example of one of the rows returned by a SELECT statement using %keyField = -1 in a citizen database where the primary key is the citizen number: XelaG,1949-01-22,289499,male Example of using %keyField = 2 in the same SQL statement: 289499=XelaG,1949-01-22,male To be able to use this string list as a xelagot database new syle, you must chose your %keyField carefully: 0 for the first field, 1 for the second, etc. The content of the chosen field must be unique, i.e. no two records may have the same content in that field because the name in a name=value pair must be unique. For database 'new' style, see the DB prefixed statements that replace the SList statements on the string list page: there is a whole section dedicate to this. It is a good idea to test your SQL statements and the %keyField before including them in a script. You can do that with xgTestSQL.exe.
SQLQueryOther %ID $query %rc $error	xelagot 3.600 for sql queries that do NOT return rows of data examples: INSERT, UPDATE, USE, CREATE TABLE, MODIFY TABLE and others Syntax: %ID contains the connector object's ID $query contains the SQL query statement %rc receives the number of rows affected. If an error occurs, it has a negative value, and the error message is in $error
SQLMaxRows %max	xelagot 3.606 The maximum number of rows returned by the SQLQuerySelect statement can be modified with this statement. By default, the script uses the value indicated in the bot's ini file, section [SQL], which, if it has not been manually modified, is 20. If %max = 0, then the number of rows is unlimited (caution using this setting!); if it is larger than 0, it works as expected. If it is negative or this statement has never been used in the action script, the ini file default is used (this is always the case in versions previous to 3.606).