EQWatcher Evolution > Scripting  > SQL


Introduction

As of version 1.4, EQWatcher Evolution now supports connecting to an SQL (Structured Query Language) database and performing standard SQL operations.  This SQL database support is provided using ODBC, a standard Windows (and other OS) component.  If you need help with the actual SQL, you may need to refer to documentation for your target database's ODBC drivers, but here is a link that may help you  MySQL.com Tutorial.  Some drivers may operate differently under some conditions, it is up to the script developer to make sure that the SQL used is as standard as possible.  Since ODBC is designed to use any database (given the correct drivers) via SQL, this system can be expected to work with any database as such -- for example, MS Access files (.mdb), MS Excel files (.xls), Comma Separated Values (.csv), MySQL servers, etc.  There may be some cases that do not work correctly (I have heard a rumor that the system used by EQWA may not work with Oracle databases.  I do not know if this is true or not, and do not have access to an Oracle db to test it).

EQWatcher uses an unsigned long "handle" for each SQL session to allow many different sessions at once and to allow scripts to share an SQL session if desired (keeping in mind any script synchronization issues this might cause).  That being said, the SQL handle is global scope and is valid until the SQL session is explicitly closed by a script, or the program terminates.  Unloading a script will not close an SQL session -- it will remain open until the program terminates.

EQWatcher's SQL functionality provides methods for retrieving data from any given column of each row resulting from an SQL "SELECT" statement.  After a successful SELECT, through the SQLExecute function, each row may be retrieved sequentially by calling the SQLFetch iterator.  This function will skip to the next row of the result (and must be called to retrieve the first row).  Until SQLFetch returns false, data can be retrieved from the current row using the SQLColumn function, which retrieves data in string form, from either the name or number of the column.

EQWatcher Evolution (versions later than 1.45) comes with a pre-installed and set up Access database (as long as ODBC is set up on the system, it will be set up as a data source).  The ODBC data source name is "EQWatcher" and does not require a username or password.  You may create tables and do what you want with it, please make sure to take necessary steps so that your table names are not the same as others (prepend them with your script name, etc).

List of commands


SQLClose, SQLColumn, SQLColumns, SQLColumnsD, SQLColumnCount, SQLColumnName, SQLColumnNames, SQLColumnNamesD, SQLConnect, SQLError, SQLExecute, SQLFetch, SQLFetchArray, SQLFetchArrayD, SQLRowCount, SQLSafe



The "SQLClose" Command

This command closes an existing SQL connection handle.

Syntax:

SQLClose([Handle])


Handle: The SQL Handle returned by SQLConnect



The "SQLColumn" Command

This command retrieves data in string form, from a specific column of the currently fetched row.

Syntax:

SQLColumn([Handle],[Column])


Handle: The SQL Handle returned by SQLConnect

Column: Either a string OR an integer value corresponding with the column name or number you wish to retrieve data from


Return Value:

This command returns a string containing the data from that specific column, of the currently fetched row.  An empty string may indicate either a failure or an empty field (this column from this row).


The "SQLColumns" Command

This command retrieves data in array (not dynarray) form, creating the array if necessary, from the currently fetched row.  Existing data in the array will be cleared.

Syntax:

SQLColumns([Handle],[Array Handle])


Handle: The SQL Handle returned by SQLConnect

Array Handle: Handle to an array (MUST be a variable because of auto-creation, this variable will be modified if necessary with the new handle)


Return Value:

none


The "SQLColumnsD" Command

This command retrieves data in dynarray form, from the currently fetched row.

Syntax:

SQLColumnsD([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

A dynarray-formatted string containing the set of data from the currently fetched row.


The "SQLColumnNames" Command

This command retrieves column names in array (not dynarray) form, creating the array if necessary, from the currently fetched row.  Existing data in the array will be cleared.

Syntax:

SQLColumnNames([Handle],[Array Handle])


Handle: The SQL Handle returned by SQLConnect

Array Handle: Handle to an array (MUST be a variable because of auto-creation, this variable will be modified if necessary with the new handle)


Return Value:

none


The "SQLColumnsNamesD" Command

This command retrieves column names in dynarray form, from the currently fetched row.

Syntax:

SQLColumnsNamesD([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

A dynarray-formatted string containing the set of column names from the currently fetched row.


The "SQLColumnCount" Command

This command retrieves the number of columns in this result set.

Syntax:

SQLColumnCount([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

This command returns the unsigned long number of columns in the set.


The "SQLColumnName" Command

This command retrieves the name of a specified column

Syntax:

SQLColumn([Handle],[Column])


Handle: The SQL Handle returned by SQLConnect

Column: An integer value corresponding with the column name you wish to retrieve the name of


Return Value:

This command returns a string containing the name of that specific column, of the current result set.  An empty result string likely indicates an error.


The "SQLConnect" Command

This command attempts to connect to the specified source database.

Syntax:

SQLConnect([Data Source],[User Name],[Password])


Data Source: A string containing the name of the requested data source, as identified in ODBC

User Name: A string containing the user name to connect to the data source with

Password: A string containing the password to connect to the data source with


Return Value:

This command returns an unsigned long "handle" to the newly opened SQL database connection.  If the handle is zero, this indicates an error.


The "SQLError" Command

This command retrieves the description of the last error if available.

Syntax:

SQLError([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

This command returns a string containing the last error description


The "SQLExecute" Command

This command attempts to execute an SQL query

Syntax:

SQLExecute([Handle],[SQL])


Handle: The SQL Handle returned by SQLConnect

SQL: A string containing the SQL query (example: "SELECT * FROM addresses").  Do NOT use terminators like ;, \g, etc


Return Value:

This command returns a boolean result, true indicates success, false indicates an error.


The "SQLFetch" Command

This command attempts to fetch the next row in a result set

Syntax:

SQLFetch([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

This command returns a boolean result, true indicates success, false indicates an error or no more rows


The "SQLFetchArray" Command

This command attempts to fetch the next row in a result set into an array

Syntax:

SQLFetchArray([Handle],[Array Handle])


Handle: The SQL Handle returned by SQLConnect

Array Handle: Handle to an array (MUST be a variable because of auto-creation, this variable will be modified if necessary with the new handle)


Return Value:

none.  To check success, test the ArrayCount


The "SQLFetchArrayD" Command

This command attempts to fetch the next row in a result set into a dynarray-formatted string

Syntax:

SQLFetchArrayD([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

A dynarray-formatted string containing the entire row.  To check success, test the DATotalElements


The "SQLRowCount" Command

This command retrieves the number of columns affected by INSERT, UPDATE, or DELETE statements

Syntax:

SQLRowCount([Handle])


Handle: The SQL Handle returned by SQLConnect


Return Value:

This command returns the unsigned long number of rows affected by the statement.


The "SQLSafe" Command

This command returns a field-safe version of a string for use with SQLExecute.  Specifically, any apostrophe is converted to double apostrophes, so the string is safe to use in field values (which generally require the apostrophe as delimiter).  Example: "Naltron's Mark" is converted to "Naltron''s Mark", thus indicating the apostrophe is not a delimiter.

Syntax:

SQLSafe([Text])


Text: The text to return a safe version of


Return Value:

This command returns a string containing the field-safe version of Text