Index
SQL: statements
Index |
SQL: statements |
As from version 3.600 (actually 3.507), xelagots can connect to and query a number of SQL databases, using xgDLLSQL.dll. This dll uses a lightweight unidirectional set of delphi 7.1 dbExpress components, TSQLConnection and TSQLDataSet. It does not work on its own, it requires one or two more dlls per database engine type to be able to establish a connection. It must reside in the bot's Plugins folder.
| xelagot |  |
xgDLLSQL.dll | |
dbExpress DLL | |
Vendor DLL | |
Database engine |
The database engine mySQL (3.23 to 4.1) is freely supported using the dbExpress DLL dbx4mysql.dll, which has been licensed to me by microOLAP, and may be used royalty-free as it does not call the Vendor DLL libmysql.dll (which is either GPL or requires a commercial license). dbx4mysql.dll combines the functions of dbExpress DLL and Vendor DLL. Put it with xgDLLSQL.dll in the bot's Plugins folder.
| xelagot | |
xgDLLSQL.dll | |
dbx4mysql.dll | |
mySQL |
Some other SQL engines are supported too and require two dlls: a dbExpress DLL and a Vendor DLL. See table below. Note: I do not provide the Vendor DLLs, contact the vendors or the licensed owners of a database server.
For a connection to succeed, xelagot (xgDLLSQL.dll, to be precise) needs to know which drivers (i.e. dbExpress and Vendor DLLs) it must call, and what connection parameters it must pass to these dlls. You will find these in the table below, entries in italics must be edited. The Action Script uses two string lists for this, one contains the drivers, and the other the connection parameters. See the statement SQLConnect.
For mySQL, put xgDLLSQL.dll and dbx4mysql.dll in the bot's Plugins folder. Use the test application xgTestSQL.exe as described below to configure your connection.
Xelagots can only load xgDLLSQL.dll if the versions are compatible. Xelagot checks the major and minor version of the DLL before loading it.
To check for compatibility, run your xelagot, then open the bot's ini file and find the section called [SQL]. You will find two keys called xgDLLSQLRequiredVersion and xgDLLSQLFoundVersion, which will inform you about this.
xgDLLSQL.dll uses SQL queries, with some restrictions. The following features are not supported:
Downloaded rows (records) are each presented as a comma separated string in a string list. To avoid conflicts with the xelagot string list database convention, three characters are re-coded: the % sign to %1, the comma to %2 and the = sign to %3. There are new statements that can handle this convention, based on DBDecode and DBEncode functions.
Xelagot records are based on name=value pairs, the name is a unique key field and is not case sensitive. Make sure to take this into account when building your key fields in SQL, they may be case sensitive there and cause duplicate records for xelagots.
Connections are blocking ones, there are no callbacks for SQL in the Action Script. Take this into account when writing scripts so as not to choke the bot.
The ini file of x1, av99bot and srvcXlgBot has a section called [SQL]. The parameter SQLMaxRows limits the amount of records (rows) that can be downloaded with one SQL query. The default is 20, a 0 would mean 'no limit' but is strongly NOT recomended unless you know what you are doing. As from xelagot 3.606, scripts can change this value on the fly with SQLMaxRows %m
All dlls (except xgDLLSQL.dll) can be anywhere on the hard disk as long as they are found on the windows path, but I recommend putting them in the bot's Plugins folder, together with xgDLLSQL.dll. You will have to test to see what works best.
I asume that the dlls are all in the bot's Plugins folder. If that is not the case, omit or change the path of LibraryName or VendorLib in the drivers list.
If your database system is not listed here, see the section Links.
Xelagot's xgDLLSQL.dll calls the following dlls, and also requires two lists of parameters for drivers and connection:
| SQL engine | dbExpress DLL | Vendor DLL | Drivers and Connection lists | comments | |
| mySQL 3.23 - 4.1 |
dbx4mysql.dll | drivers:
GetDriverFunc=getSQLDriverDBX4Mysql LibraryName=Plugins\dbx4mysql.dll VendorLib=notused.dllconnection: HostName=localhost Database=DBNAME User_Name=user Password=password BlobSize=-1 ErrorResourceFile= LocaleCode=0000 Server Port=note: as from version 1.7 of dbx4mysql.dll (1st March 2005), instead of using 'Server Port' if the default port is not used, you can directly attach the port number to the HostName parameter, for example: HostName=localhost:2345 |
|||
| DB2 | dbexpdb2.dll | db2cli.dll | drivers:
GetDriverFunc=getSQLDriverDB2 LibraryName=Plugins\dbexpdb2.dll VendorLib=Plugins\db2cli.dllconnection: Database=DBNAME User_Name=user Password=password BlobSize=-1 ErrorResourceFile= LocaleCode=0000 DB2 TransIsolation=ReadCommited |
||
| Interbase | dbexpint.dll | gds32.dll | drivers:
GetDriverFunc=getSQLDriverINTERBASE LibraryName=Plugins\dbexpint.dll VendorLib=Plugins\gds32.dllconnection: Database=database.gdb RoleName=RoleName User_Name=user Password=password ServerCharSet= SQLDialect=1 BlobSize=-1 CommitRetain=False WaitOnLocks=True ErrorResourceFile= LocaleCode=0000 Interbase TransIsolation=ReadCommited Trim Char=False |
||
| Oracle | dbexpora.dll | oci.dll | drivers:
GetDriverFunc=getSQLDriverORACLE LibraryName=Plugins\dbexpora.dll VendorLib=Plugins\oci.dllconnection: DataBase=Database Name User_Name=user Password=password BlobSize=-1 ErrorResourceFile= LocaleCode=0000 Oracle TransIsolation=ReadCommited RowsetSize=20 OS Authentication=False Multiple Transaction=False Trim Char=False |
||
| Informix | dbexpinf.dll | isqlb09a.dll | drivers:
GetDriverFunc=getSQLDriverINFORMIX LibraryName=Plugins\dbexpinf.dll VendorLib=Plugins\isqlb09a.dllconnection: HostName=ServerName DataBase=Database Name User_Name=user Password=password BlobSize=-1 ErrorResourceFile= LocaleCode=0000 Informix TransIsolation=ReadCommited Trim Char=False |
||
| MSSQL | dbexpmss.dll | oledb | drivers:
GetDriverFunc=getSQLDriverMSSQL LibraryName=Plugins\dbexpmss.dll VendorLib=Plugins\oledbconnection: HostName=ServerName DataBase=Database Name User_Name=user Password=password BlobSize=-1 ErrorResourceFile= LocaleCode=0000 MSSQL TransIsolation=ReadCommited OS Authentication=False |
||
xgTestSQL.exe is a small application to help you set up and test your SQL connection. Put it in the same folder as your xelagot (x1.exe). It will search in the Plugins folder for xgDLLSQL.dll.
Xelagot uses the following statement to connect:
SQLConnect %ID /s_Drivers /s_Connection %rc $error
You can configure the drivers and connection parameters in xgTestSQL.exe, see the above table. These correspond to the data in /s_Drivers and /s_Connection. By default, these are set for mySQL in xgTestSQL.exe and you only need to edit the HostName, Database, User_Name and Password, optionally adding a Server Port parameter (notice the space between Server and Port) if the standard port is not used.
When your parameters are correct, press Connect. If all is well, you will see a message near the bottom of the screen: connected.
You can then practice doing queries. The top query SELECT field is for all queries that return rows (records). The bottom query OTHER field is for queries that do not return rows, like an INSERT command.
Xelagot uses the following statement to query when rows are expected:
SQLQuerySelect %ID $query /s_rows %keyField %rc $error
Key Field for SELECT, corresponding to %keyField, lets you change how the records are formatted. Setting it to -1 will not work with the xelagot database format, which requires a name=value pair. Set it to 0 or higher and watch how the formatting changes when you query SELECT. The name side (to the left of the equal sign) must be a unique key field.
Maximum number of rows sets a limit to the number of records received. Xelagot, by default, allows up to 20 records. This can be changed in the ini file. 0 means no limit. As from xelagot 3.606, the limit can be changed for a specific script, using the statement SQLMaxRows %m
Once you are satisfied with the connection, close xgTestSQL.exe. In it's folder, it will have dumped 4 files, two of them are important: sqldriver.txt contains the driver information, sqlconnection.txt contains the connection parameters. You can copy these files to the folder where your script is located, and load them into string lists to use in the statement SQLConnect.
Caution: both this application and xelagot require that the major and minor version of xgDLLSQL.dll be compatible with the xelagot version. If you use a xgTestSQL.exe that requires the dll to be version 1.0.x.x, the dll must be a 1.0, for example 1.0.29.0 would be fine, 1.1.0.0 would not. Always use the latest versions of xelagot, xgDLLSQL.dll and xgTestSQL.exe.
Download the latest version of xelagot's xgDLLSQL.dll (1.0.0.0). The zip also contains xgTestSQL.exe. REQUIRED!
Download dbx4mysql.dll (latest version 1.7, march 1st 2005). REQUIRED for MySQL.
If you are using MySQL, the two above mentioned downloads are all you need.
Download supported dbExpress DLLs, including dbx4mysql.dll (latest version 1.7, march 1st 2005).
Vendor DLLs must be acquired or purchased from the corresponding vendors. mySQL does not require a vendor DLL, xgDLLSQL.dll and dbx4mysql.dll are all you need in your bot's Plugins folder.
For Delphi programmers (and for others if they can port it), the source code of the loader for xgDLLSQL.dll can be downloaded here.
Open-dbExpress has open-source drivers for ODBC.
Borland page with a list of dbExpress drivers.
You can try a google on SQL dbExpress, where SQL is a placeholder for the database system, for example, google on 'SQLite dbExpress'. You will need a dbExpress library and the vendor library.
Index |
SQL: statements |