Free SQL

Free SQL is a proprietary syntax based on SQL that is used in the Voxtron software to allow custom queries to be executed from a module or a plug-in. It is for example used in the ODBC state of the IVR, the call DB connector plug-in of the IVR, the SMS DB connector and the email DB connector. The main purpose of Free SQL is to allow setting and getting variables of the Voxtron software to and from a DB while the Voxtron software has no knowledge of the underlying DB.

Each module or plug-in will have its own set of variables that can be used in Free SQL, depending where the Free SQL is used. The possible variables are described in the documentation of the specific module or plug-in.

Variables should always be encapsulated in brackets like: {this}. When the value of a variables has to be interpreted as a string single quotes have to be put around the brackets.

The supported SQL commands are:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Because the syntax for the SELECT command differs distinctively from the other three, it will be discussed separately.
There is also support for stored procedures through the CALL command which will also be dealt with separately.
One Free SQL statement may contain multiple SQL commands.

SELECT

The SQL SELECT statement is used to retrieve data from the information source. In Free SQL variables can be used at two places in the SELECT: the column list or the condition statement. When a variable is added to the column list the value of that column will be inserted in the variable. If a variables is used in the condition then the value of the variable will be set in the condition statement.
Example:
SELECT name {var1}, age {var2} FROM Members
WHERE Age >= {var3}

If the SELECT command returns a record set that contains multiple records, the variables are filled with the value of the first record in the record set.

INSERT, UPDATE and DELETE

There are no limitations for the usage of variables in the Free SQL statement. They can be used anywhere, as long as the name is put between curly braces. Some examples of INSERT, UPDATE and DELETE:
INSERT INTO Members (Name, Age) VALUES (‘{var1}’, {var2})
UPDATE Members SET Age = {var2} WHERE Name = ‘{var1}’
DELETE FROM Members WHERE Age >= {var3}

CALL

The CALL command allows a stored procedure to be called. The syntax is :
A = CALL ProcName(B, C);
If there is no return value, the syntax is like this:
CALL ProcName(B, C);
When there are no parameters you may not use brackets:
A = CALL ProcName;

The syntax for the Free SQL command includes some additional information:

  • Name of the variables
  • Data type of the parameters
  • I/O type of the parameters

The corresponding Free SQL syntax is:
{var1}[integer,output] = CALL ProcName({var2}[varchar,input], {var3}[integer, input_output]);

Between curly braces:

  • The name of the variables

Between square brackets:

  • Data type: varchar or integer
  • I/O type: input, output or input_output

The data types and the I/O types are not case-sensitive.
A return value must have I/O type OUTPUT and data type INTEGER.
In case of a VARCHAR data type, the maximum size can be specified as follows: {var2}[varchar(250), input]. If the maximum size is not specified, 1023 is assumed.

Debugging Stored Procedures in Free SQL

The VoxLog component is used to log traces and errors.

In the logfile you can find:

  • The command.
  • The values of the parameters that are passed.
  • All errors that occurred, with their full text. Errors are logged with the marker “E>”.

Leave a Reply