SQLTeX documentation - Write your SQLTEX file

SQLTeX documentation - Write your SQLTEX file

Write your SQLTEX file

For SQLTEX, you write your L^ATEX document just as you're used to. SQLTEX provides you with some extra commands that you can include in your file.
The basic format of an SQLTEX command is:
\sqlcmd[options]{SQL statement}

All SQLTEX commands can be specified anywhere in a line, and can span multiple lines. When SQLTEX executes, the commands are read, executed, and their results--if they return any--are written to the output:

Above you see the SQLTEX command \sqldb was removed. Only the command was removed, not the newline character at the end of the line, so an empty line will be printed instead. The example below shows the output is an SQLTEX command was found on a line with other L^ATEX directives:

In these examples the SQLTEX commands did not return a value. When commands actually read from the database, the returned value is written instead:

SQL statements

This document assumes the reader is familiar with SQL commands. This section only tells something about implementing them in SQLTEX files, especially with the use of command parameters and variables. Details about the SQLTEX commands will be described in the next sections.

Let's look at a simple example. Suppose we want to retreive all header information from the database for a specific invoice. The SQL statement could look something like this:
SELECT * FROM INVOICE WHERE INVOICE_NR = 12345;
To implement this statement in an SQLTEX file, the \sqlrow command should be used (see section 3.4):

First, it is important to know that SQL statements should not contain the ending semicolon (;) in any of the SQLTEX commands. The command in SQLTEX would be:
\sqlrow{SELECT * FROM INVOICE WHERE INVOICE_NR = 12345}

Next, SQLTEX would be useless if you have to change your input file every time you want to generate the same document for another invoice.

Therefore, you parameters or variables can be used in your SQL statement. Parameters are given at the command line, variables can be defined using the \sqlfield command.

Given the example above, the invoice number can be passed as a parameter by rewriting the command as:
\sqlrow{SELECT * FROM INVOICE WHERE INVOICE_NR = $PAR1}
or as as variable with the code line:
\sqlrow{SELECT * FROM INVOICE WHERE INVOICE_NR = $VAR0}

Note you have to know what datatype is expected by your database. In the example here the datatype is INTEGER. If the field ``INVOICE_NR'' contains a VARCHAR type, the $PARamater or $VARiable should be enclosed by quotes:
\sqlrow{SELECT * FROM INVOICE WHERE INVOICE_NR = '$PAR1'}

Opening the database

Before any information can be read from a database, this database should be opened. This is done with the \sqldb command. \sqldb requires the name of the dabatase. Optionally, a username and password can be given. When omitted, SQLTEX assumes no username and password is required to connect to the database (the user that executes SQLTEX should have access to the specified database).
The format of the command is:
\sqldb[username,password]{database}

The command can be used anywhere in your input file, but should occur before the first command that tries to read data from the database.

Reading a single field

When a single field of information is to be read from the database, the command \sqlfield is used. By default, the command in the inputfile is replaced by its result in the outputfile.
The SQL command is enclosed by curly braces. Square brackets can optionally be used to enter some extra options. Currently, the only supported option is setvar.

The full syntax or the \sqlfield command is:
\sqlrow[options]{SELECT fieldname FROM tablename WHERE your where-clause}
By default, the SQLTEX command is replaced with the value returned by the SQL query. This behaviour can be changed with options.

Define variables

The \sqlfield can also be used to set a variable. The value returned by the SQL query is not displayed in this case. Instead, a variable is created which can be used in any other SQL query later in the document.

Therefore, the option [setvar=n] is used, where n is an integer between 0 and 9.

Suppose you have an invoice in L^ATEX. SQLTEX is executed to retrieve the invoice header information from the database for a specific customer. Next, the invoice lines are read from the database.

You could pass the invoice number as a paramater to SQLTEX for use in your queries, but that could change every month. It is easier to :

• pass the customer number as a parameter,
• retrieve the current date (asuming that is the invoice date as stored in the database by another program), and store it in a variable:
  \sqlfield[setvar=0]{SELECT DATE_FORMAT(NOW(), "%Y-%m-%d")}
  This creates a variable that can be used as $VAR0,
• retrieve the invoice number using the customer number (a command line parameter) and the variable containing the invoice date. Store this invoice number in $VAR1:
  \sqlfield[setvar=1]{SELECT INVOICE_NR FROM INVOICES
  WHERE CUST_NR = '$PAR1' AND INVOICE_DATE = '$VAR0'}
• use $VAR1 to retrieve all invoice information.

The SQL queries used here do not display any output in your L^ATEXdocument.

Reading rows of data

When an SQL query returns more information than one single field, the SQLTEX command \sqlrow should be used. As with the \sqlfield, command, SQLTEX replaces the command with the values it returns, but \sqlrow accepts different options for formating the output.

By default, fields are separated by a comma and a blank (`, '), and rows by a newline character (`\\'). To change this, the options ``fldsep'' and ``rowsep'' can be used.

e.g. In a tabular enviroment the fields should be seperated by an amphesand (&), perhaps a line should seperate the rows of information. (\\ \hline). To do this, the options can be used with \sqlrow as shown here:
\sqlrow[fldsep=&,rowsep=\\ \hline]{SELECT I.LINE_NR, A.ARTICLE_NR, A.PRICE, I.AMOUNT, (A.PRICE * I.AMOUNT) FROM ARTICLE A, INVOICE_LINE I WHERE I.INVOICE_NR = $VAR1 AND I.ARTICLE_NR = A.ARTICLE_NR}

This will produce an output like:
1 & 9712 & 12 & 1 & 12 \\ \hline
2 & 4768 & 9.75 & 3 & 29.25 \\ \hline
3 & 4363 & 1.95 & 10 & 19.5 \\ \hline
4 & 8375 & 12.5 & 2 & 25 \\ \hline