\documentclass{article} \newcommand{\bs}{\ensuremath{\backslash}} \newcommand{\vs}{\vspace{3mm}} \newcommand{\sqltexversion}{3.0} \newcommand{\sqltexvmsversion}{3\_0} \usepackage{makeidx} \usepackage[pdftex ,pagebackref=true ,colorlinks=true ,linkcolor=blue ,unicode]{hyperref} \begin{document} \title{SQL\TeX\ v\sqltexversion} \date{Sep 20, 2024} \author{Oscar van Eijk} \maketitle \hrulefill \tableofcontents \hrulefill \section{Introduction} SQL\TeX\ is a preprocessor to enable the use of SQL statements in \LaTeX. It is a perl script that reads an input file containing the SQL commands, and writes a \LaTeX\ file that can be processed with your \LaTeX\ package. The SQL commands will be replaced by their values. It's possible to select a single field for substitution substitution in your \LaTeX\ document, or to be used as input in another SQL command. When an SQL command returns multiple fields and or rows, the values can only be used for substitution in the document. \subsection{Known limitations} \begin{itemize} \item The \LaTeX\ \texttt{\bs includeonly} directive is ignored; all documents included with \texttt{\bs include} will be parsed and written to the output file. \item Currently, only 9 command- line parameters (1-9), and 10 variables (0-9) can be used in SQL statements. \item Replace files can hold only 1,000 items. \end{itemize} \section{Installing SQL\TeX} Since v3.0, SQL\TeX\ is part of \TeX\ Live and doesn't need further installation. \\ If you are using a different LaTeX distro, please follow the steps below for your OS. Before installing SQL\TeX, you need to have it. The latest version can always be found at \url{https://github.com/oveas/sqltex}. The download consists of this do\-cumentation, an installation script for Unix (\texttt{install}), and the Perl script \texttt{sqltex}, and a replace- file (\texttt{SQLTeX\_r.dat}) for manual installation on non- unix platforms\footnote{on Unix, this file will be generated by the install script}. \subsection{Requirements} SQL\TeX\ requires the following software: \begin{itemize} \item Perl v5.10 or higher (\url{http://perl.org/}) \item Perl-DBI (\url{http://dbi.perl.org/}) \item The DBI driver for your database\\(see: \url{http://search.cpan.org/search?query=DBD\%3A\%3A\&mode=module}) \item Getopt::Long (\url{https://metacpan.org/pod/Getopt::Long}) \item Term::ReadKey (\url{https://metacpan.org/pod/Term::ReadKey}) \end{itemize} \subsection{Installation} If you are using a \TeX\ Live distribution, SQL\TeX\ is already available. For all other distros, follow the steps in this section. \vs First unpack the archive in a location of your choice and continue with one if the subsections below depending on you operating system. \subsubsection{Linux} Go to the top directory where the archive was unpacked (`\texttt{cd sqltex-\sqltexversion}') and execute the following commands: \vs \noindent\texttt{\$ ./configure \textit{[options]}\\ \$ make \\ \$ \textit{[sudo] }make install} \vs In the last command, \texttt{sudo} is only required if the install destination (\texttt{PREFIX}, see below) is outside the own user environment. \vs For \texttt{configure}, the following options are user buy SQL\TeX\ (type \texttt{./configure --help} for a full list): \begin{description} \item[\texttt{--prefix=PREFIX}] install architecture-independent files in PREFIX. Default is \texttt{/usr/local}. \item[\texttt{--exec-prefix=EPREFIX}] install architecture-dependent files in EPREFIX. Default is \texttt{PREFIX}. \end{description} The directives above are used by the ones below: \begin{description} \item[\texttt{--bindir=DIR}] Location of the SQL\TeX\ script. Default is \texttt{EPREFIX/bin} \item[\texttt{--sysconfdir=DIR}] Location of the Configuration- and replacefiles. Default is \texttt{PREFIX/etc} \item[\texttt{--datarootdir=DIR}] Data root, used by the directives below. Default is \texttt{PREFIX/share} \item[\texttt{--mandir=DIR}] Location of the SQL\TeX\ manpage. Default is \texttt{DATAROOTDIR/man} \item[\texttt{--docdir=DIR}] Documentation root, used by \texttt{pdfdir} below. Default is \texttt{DATAROOTDIR/doc/sqltex} \item[\texttt{--pdfdir=DIR}] Location of SQL\TeX.pdf. Default is \texttt{DOCDIR} \end{description} \vs After installation, the archive and unpack- directory can be removed. \subsubsection{Windows} \noindent\hspace{-3mm}\textit{\underline{Note:}}Since v3.0, the binary \texttt{SQLTEX.EXE} for Windows is not included in the distribution anymore\footnote{ It can be generated with any (portable) perl version for Windows, like Strawberry Perl (\url{https://strawberryperl.com/}\label{winexe}), with \texttt{PAR::Packer} (\url{https://metacpan.org/pod/PAR::Packer}) using the command:\\ \texttt{pp -o sqltex.exe sqltex}} \vs The files \texttt{sqltex-\sqltexversion\ensuremath{\backslash}sqltex}, \texttt{sqltex-\sqltexversion\ensuremath{\backslash}src\ensuremath{\backslash}SQLTeX.cfg} and \\ \texttt{sqltex-\sqltexversion\ensuremath{\backslash}src\ensuremath{\backslash}SQLTeX\_r.dat} must be placed manually in the directory of your choice, all in the same direcrtory. \subsubsection{OpenVMS} On \textsc{OpenVMS} the files must be copied manually to the destination. All files must reside in the same location:\\ \texttt{\$ COPY [.SQLTEX-\sqltexvmsversion.SRC]SQLTEX. SYS\$SYSTEM:SQLTEX.PL\\ \$ COPY [.SQLTEX-\sqltexvmsversion.SRC]SQLTEX.CFG SYS\$SYSTEM:\\ \$ COPY [.SQLTEX-\sqltexvmsversion.SRC]SQLTEX\_R.DAT SYS\$SYSTEM:\\ \$ SET FILE/PROTECTION=(W:RE) SYS\$SYSTEM:SQLTEX.PL} \vs Next, define the command \texttt{SQLTEX} by setting a symbol, either in the \texttt{LOGIN.COM} for all users who need to execute this script, or in some group-- or system wide login procedure, with the command: \\ \texttt{\$ SQLTEX :== "PERL SYS\$SYSTEM:SQLTEX.PL"} \subsection{Configuration}\label{config} The configuration file \texttt{SQLTeX.cfg} is located in \texttt{/usr/local/etc} (linux) or the same location where SQL\TeX\ is installed (all other operating systems and in \TeX\ Live distros)\footnote{ If a 1.x version of SQL\TeX\ is installed on your system, make sure you save the configuration section, which was inline in older versions}. Multiple configuration files can be created, the command line option \texttt{--configfile} can be used to select the requested configuration. \vs \noindent\hspace{-3mm}\textit{\underline{Note:}}\label{cfg:disable} Use of the \texttt{--configfile} commandfile option can be disabled on system wide installations. To do so, the script \texttt{sqltex} must be modified.\\ At the top of the file (line 4), set the value for \texttt{\$main::ext\_cfgfile\_allowed} to \texttt{0}. \vs Some values can be overwritten using command line options (see section~\ref{cmdline}). When the command line options are omitted, the values from the requested configuration file will be used. \begin{description} \item[dbdriver] Database driver. The default is \texttt{mysql}. Other supported databases are \texttt{Pg}, \texttt{Sybase}, \texttt{Oracle}\footnote{ This requires the configuration setting \texttt{oracle\_sid}}, \texttt{Ingres}, \texttt{mSQL}, \texttt{PostgreSQL} and \texttt{ODBC}\footnote{ The actual driver can specified with the configuration setting \texttt{odbc\_driver}}, but also others might work without modification. \\ \vs If your database driver is not support, look for the function \texttt{db\_connect} to add support (and please notify me :) \item[oracle\_sid] Oracle Site Identifier, required when the \texttt{Oracle} database driver is selected. \item[odbc\_driver] Specification of the ODBC driver. Default is ``\texttt{SQL Server}'' \item[texex] The default file extension for \LaTeX\ file. When SQL\TeX\ is called, the first parameter should be the name of the input file. If this filename has no extension, SQL\TeX\ looks for one with the default extension. \item[stx] An output file can be given explicitly using the `\texttt{--output}' option. When omitted, SQL\TeX\ composes an output file name using this string.\\ E.g, if your input file is called \texttt{db-doc.tex}, SQL\TeX\ will produce an outputfile with the name \texttt{db-docstx.tex}. \item[def\_out\_is\_in] By default, when no output file is specified or an output file without (relative) path is given, the output file will be generated in the current directory.\\ This behaviour changed in version 2.1. In older version, the location of the output file always was the same as the input file location. To revert to the old behaviour, set \texttt{def\_out\_is\_in} to `\texttt{1}'\footnote{ Note the pre-v2.1 implementation also contained a bug: if the output file name contained an absolute or relative path, this path was always taken as relative from the input file location. In the new implementation, \texttt{def\_out\_is\_in} is ignored if the output file name contains a path.}. \item[multi\_rfile]If the commandline option \texttt{--replacementfile} is given, by default the given replacement file will be parsed and after that the default replacement file will be parsed as well.\\ If only the given replacement file should be parsed skipping the default file, set this value to \texttt{0}. \item[rfile\_comment] The comment-sign used in replace files. If this is empty, comments are not allowed in the replace files. \item[rfile\_regexploc] This must be part of the value \texttt{rfile\_regexp} below. \item[rfile\_regexp] Explains how a regular expression is identified in the replace files (see section~\ref{regexp}). \item[cmd\_prefix]\label{prefix} SQL\TeX\ looks for SQL commands in the input file. Commands are specified in the same way all \LaTeX\ commands are specified: a backslash (\bs) followed by the name of the command.\\ All SQL\TeX\ commands start with the same string. By default, this is the string \texttt{\textbf{sql}}. When user commands are defined that start with the same string, this can be changed here to prevent conflicts. \item[sql\_open] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command for opening a database.\\ With the default configuration this command is ``\texttt{\bs sqldb}''. \item[sql\_field] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command to read a single field from the database.\\ With the default configuration this command is ``\texttt{\bs sqlfield}''. \item[sql\_row] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command to read one or more rows from the database.\\ With the default configuration this command is ``\texttt{\bs sqlrow}''. \item[sql\_params] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command to retrieve a list if fields that will be used as parameters (\texttt{\$PAR1}, see section~\ref{params}) in the multidocument environment (see section~\ref{multidoc}).\\ With the default configuration this command is ``\texttt{\bs sqlparams}''. \item[sql\_update] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command to update one or more rows in the database.\\ With the default configuration this command is ``\texttt{\bs sqlupdate}''. \item[sql\_start] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command start a section that will be repeated for every row from an array (see section~\ref{loops}).\\ With the default configuration this command is ``\texttt{\bs sqlstart}''. \item[sql\_use] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command use a named variable from the array that is currently being processed in a loop context (see section~\ref{loops}).\\ With the default configuration this command is ``\texttt{\bs sqluse}''. \item[sql\_end] This string is appended to the \texttt{cmd\_prefix} to get the complete SQL\TeX\ command to end a loop context (see section~\ref{loops}).\\ With the default configuration this command is ``\texttt{\bs sqlend}''. \item[sqlsystem\_allowed] Set this to ``1'' to allow the use of the \texttt{\bs sqlsystem} command (see section~\ref{sqlsystem}). \item[repl\_step] Replacing strings (see section~\ref{replfiles} below) is done two steps, to prevent values from being replaced twice. This setting---followed by a three-digit integer - ``000'' to ``999''---is used in the first step and replaces values from the first column. In the second step, values from the second column replace the temporary value. \\ If the first column in the replace file contains a character sequence that occurs in this temporary value, or if query results might contain the full string followed by three digits, this value might need to be changed in something unique. \item[alt\_cmd\_prefix] In loop context, this setting is used internally to differentiate between sql statements to process immediately and sql statements on stack.\\ Normally, this setting should never change, but if the value for \texttt{cmd\_prefix} has been changed and a conflict is found, the message ``\texttt{Configuration item 'alt\_cmd\_prefix' cannot start with \textit{}}'' indicates this setting should change as well. \end{description} \subsection{Create replace files}\label{replfiles} Replace files can be used to substitute values in the output of your SQL commands with a different value. This is especially useful when the database contains characters that are special characters in \LaTeX, like the percent sign (`\%'), underscore (`\_') etc. When SQL\TeX\ is installed, it comes with a standard file---\texttt{SQLTeX\_r.dat}---which is located in \texttt{/usr/local/etc}\footnote{ if a replace file with that name already exists, it will be stored there as \texttt{SQLTeX\_r.dat.new}} (linux) or the same location where SQL\TeX\ is installed (all other operating systems and in \TeX\ Live distros). \vspace{3mm} \noindent Example: \begin{verbatim} $ \$ _ \_ % \% & \& < \texttt{<} > \texttt{>} { \{ } \} # \# ~ \~{} \ \ensuremath{\backslash} \end{verbatim} \vspace{3mm} These are all single character replacements, but you can add your own replacements that consist of a single character or a character sequence (or even regular expressions, see section~\ref{regexp}). \vs To do so, enter a new line with the character(string) that should be replaced, followed by one or more \texttt{TAB}-character(s) (\textit{not} blanks!) and the character(string) it should be replaced with.\\ That last one can be empty if the input character(string) should be ignored, but the \texttt{TAB} after the input character(string) is mandatory! \vs If the first non-blank character is a semicolon (`;'), the line is considered a comment line\footnote{ in the default configuration. See the description for \texttt{rfile\_comment} in section~\ref{config} to change of disable comment lines.}. Blank lines are ignored. \vspace{3mm} The contents of the file are case sensitive, so of you add the line: \\ \verb+LaTeX \LaTeX\+ \\ the word ``LaTeX'' will be changed, but ``latex'' is untouched. \vspace{3mm} Different replace files can be created. To select a different replace file for a certain SQL\TeX\ source, use the commandline option `\texttt{--replacementfile \textit{filename}}'. To disable the use of replace files, use `\texttt{no-replacementfile}'. \subsubsection{Regular expressions}\label{regexp} The replace file can include regular expressions, which are recognized by a pattern given in the configuration setting \texttt{rfile\_regexp}. A part of the pattern, configurable as \texttt{rfile\_regexploc}, will be the actual regular expression. \vs By default, \texttt{rfile\_regexploc} is ``\texttt{...}'' and \texttt{rfile\_regexp} is ``\texttt{re(...)}''. If the sequence of three dots can appear anywhere else in the replace file, \texttt{rfile\_regexploc} can be changed to any other sequence of characters, e.g. ``\texttt{regexpHere}''.\\ This also requires \texttt{rfile\_regexp} to be changed. Its new value has to be ``\texttt{re(regexpHere)}'' \vs Both in the default configuration and with the modification example given above, the key for regular expressions is \texttt{re(<\textit{regular expression}>)}, e.g.:\\ \hspace{3mm}\verb+re() \paragraph*{}+ \\ will replace all HTML \texttt{<}p\texttt{>} variants (\texttt{<}p style='font-size: normal'\texttt{>}, \texttt{<}p align='center'\texttt{>} etc) \vs An example replacement file using regular expressions to handle HTML codes could look like this: \noindent\begin{verbatim} & \& \textbf{ } \textit{ } re() \\ re() \paragraph*{}

\\[0pt] $^{ }$ re() \textsl{ } re() \section{ re() \subsection{ re() \subsubsection{ re() } \end{verbatim} \section{Write your SQL\TeX\ file} For SQL\TeX, you write your \LaTeX\ document just as you're used to. SQL\TeX\ provides you with some extra commands that you can include in your file.\\ The basic format\footnote{in this document, in all examples will be assumed the default values in the configuration section as described in section~\ref{config}, have not been changed} of an SQL\TeX\ command is: \\ \texttt{\bs sql\emph{cmd}[options]\{SQL statement\}} \vs All SQL\TeX\ commands can be specified anywhere in a line, and can span multiple lines. When SQL\TeX\ executes, the commands are read, executed, and their results---if they return any---are written to the output: \vs \begin{minipage}[t]{0.5\textwidth}\textsl{Input file:}\\\texttt{\footnotesize{\bs documentclass[article] \\ \bs pagestyle\{empty\} \\ \bs sqldb[oscar]\{mydb\} \\ \bs begin\{document\} \\ }}\end{minipage}\hfill\begin{minipage}[t]{0.5\textwidth}\textsl{Output file:}\\\texttt{\footnotesize{\bs documentclass[article] \\ \bs pagestyle\{empty\} \\ \\ \bs begin\{document\} \\ }}\end{minipage} \vs Above you see the SQL\TeX\ command \texttt{\bs sqldb} was removed. Only the command was removed, not the \textsl{newline} character at the end of the line, so an empty line will be printed instead. The example below shows the output if an SQL\TeX\ command was found on a line with other \LaTeX\ directives: \vs \begin{minipage}[t]{0.5\textwidth}\textsl{Input file:}\\\texttt{\footnotesize{\bs documentclass[article] \\ \bs pagestyle\{empty\}\bs sqldb[oscar]\{mydb\} \\ \bs begin\{document\} \\ \hrulefill}}\end{minipage}\hfill\begin{minipage}[t]{0.5\textwidth}\textsl{Output file:}\\\texttt{\footnotesize{\bs documentclass[article] \\ \bs pagestyle\{empty\} \\ \bs begin\{document\} \\ }}\end{minipage} \vs In these examples the SQL\TeX\ commands did not return a value. When commands actually read from the database, the returned value is written instead: \vs \begin{minipage}[t]{0.5\textwidth}\textsl{Input file:}\\\texttt{\footnotesize{This invoice has \bs sqlfield\{SELECT COUNT(*) FROM INVOICE\_LINE \\ WHERE INVOICE\_NR = 20190062\} lines.\\ \hrulefill}}\end{minipage}\hfill\begin{minipage}[t]{0.5\textwidth}\textsl{Output file:}\\\texttt{\footnotesize{This invoice has 3 lines \\ }}\end{minipage} \subsection{SQL statements}\label{sqlstatements} This document assumes the reader is familiar with SQL commands. This section only tells something about implementing them in SQL\TeX\ files, especially with the use of command parameters and variables. Details about the SQL\TeX\ commands will be described in the next sections. \vs Let's look at a simple example. Suppose we want to retrieve all header information from the database for a specific invoice. The SQL statement could look something like this: \\ \texttt{SELECT $\ast$ FROM INVOICE WHERE NR = 20190062;}\\ To implement this statement in an SQL\TeX\ file, the \texttt{\bs sqlrow} command should be used (see section~\ref{sqlrow}): First, it is important to know that SQL statements should \textit{not} contain the ending semicolon (;) in any of the SQL\TeX\ commands. The command in SQL\TeX\ would be:\\ \texttt{\bs sqlrow\{SELECT $\ast$ FROM INVOICE WHERE NR = 20190062\}} Next, SQL\TeX\ would be useless if you have to change your input file every time you want to generate the same document for another invoice. \vs Therefore, you parameters or variables can be used in your SQL statement. Parameters are given at the command line (see section~\ref{params}), variables can be defined using the \texttt{\bs sqlfield} command as described in section~\ref{vars}. Given the example above, the invoice number can be passed as a parameter by rewriting the command as: \\ \texttt{\bs sqlrow\{SELECT $\ast$ FROM INVOICE WHERE NR = \$PAR1\}} \\ or as a variable with the code line: \\ \texttt{\bs sqlrow\{SELECT $\ast$ FROM INVOICE WHERE NR = \$VAR0\}} Note you have to know what datatype is expected by your database. In the example here the datatype is \textsc{integer}. If the field ``\textsc{invoice\_nr}'' contains a \textsc{varchar} type, the \texttt{\$PAR}ameter or \texttt{\$VAR}iable should be enclosed by quotes: \\ \texttt{\bs sqlrow\{SELECT $\ast$ FROM INVOICE WHERE NR = '\$PAR1'\}} \subsection{Opening the database}\label{opendb} Before any information can be read from a database, this database should be opened. This is done with the \texttt{\textbf{\bs sqldb}} command. \texttt{\bs sqldb} requires the name of the dabatase. Optionally, a username, password and remote database host can be given. \\ The format of the command is:\\ \texttt{\bs sqldb[user=\textit{username},passwd=\textit{password},host=\textit{host}]\{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. \vs If the keywords \texttt{user}, \texttt{passwd} and \texttt{host} are omitted, SQL\TeX\ assumes the options are given in this order:\\ \texttt{\bs sqldb[\textit{username},\textit{password},\textit{host}]\{database\}} Default host is localhost, the default user is the current user. \vs \noindent\hspace{-3mm}\textit{\underline{Note:}} The \texttt{\bs sqldb} command cannot span multiple lines! \subsubsection{Prompt for password and/or username} If a password is omitted, SQL\TeX\ will try connect to the database without a password, unless the commandline option \texttt{--password} is given (see section \ref{cmdline}). \vs Forcing a user to enter a database password when SQL\TeX\ runs can be achieved by specifying \texttt{?} as password:\\ \texttt{\bs sqldb[user=dbUser,passwd=?]\{database\}} \vs When different database users should be able to use the same SQL\TeX\ file, the username can also be a question mark, forcing SQL\TeX to prompt for a username:\\ \texttt{\bs sqldb[user=?,passwd=?]\{database\}} \subsection{Reading a single field}\label{sqlfield} When a single field of information is to be read from the database, the command \texttt{\textbf{\bs sqlfield}} is used. By default, the command in the input file is replaced by its result in the output file.\\ 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 \texttt{setvar} (see section~\ref{vars}). The full syntax or the \texttt{\bs sqlfield} command is:\\ \texttt{\bs sqlfield[\textit{options}]\{SELECT \textit{fieldname} FROM \textit{tablename} WHERE \textit{your where-clause}\}} \\ By default, the SQL\TeX\ command is replaced with the value returned by the SQL query. This behaviour can be changed with options. \subsubsection{Define variables}\label{vars} The \texttt{\bs 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 (see also section~\ref{sqlstatements}). Therefore, the option \texttt{\textbf{[setvar=\textit{n}]}} is used, where \textit{n} is an integer between 0 and 9. \vs Suppose you have an invoice in \LaTeX. SQL\TeX\ 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 parameter to SQL\TeX\ for use in your queries, but that could change every month. It is easier to :\\ \begin{itemize} \item pass the customer number as a parameter, \item retrieve the current date (assuming that is the invoice date as stored in the database by another program), and store it in a variable: \\ \texttt{\bs sqlfield[setvar=0]\{SELECT DATE\_FORMAT(NOW(), "\%Y-\%m-\%d")\}} \\ This creates a variable that can be used as \texttt{\$VAR0}, \item retrieve the invoice number using the customer number (a command line parameter, see also section~\ref{params}) and the variable containing the invoice date. Store this invoice number in \texttt{\$VAR1}: \\ \texttt{\bs sqlfield[setvar=1]\{SELECT NR FROM INVOICES \\ WHERE CUST\_NR = '\$PAR1' AND INVOICE\_DATE = '\$VAR0'\}} \item use \texttt{\$VAR1} to retrieve all invoice information. \end{itemize} \vs The SQL queries used here do not display any output in your \LaTeX\ document. \subsection{Reading rows of data}\label{sqlrow} When an SQL query returns more information than one single field, the SQL\TeX\ command \texttt{\textbf{\bs sqlrow}} should be used. As with the \texttt{\bs sqlfield}, command, SQL\TeX\ replaces the command with the values it returns, but \texttt{\bs sqlrow} accepts different options for formatting the output. \vs By default, fields are separated by a comma and a blank (`\texttt{,~}'), and rows by a newline character (`\texttt{\bs\bs}'). To change this, the options ``\texttt{fldsep}'' and ``\texttt{rowsep}'' can be used. e.g. In a \texttt{tabular} environment the fields should be separated by an ampersand (\texttt{\&}), perhaps a line should separate the rows of information. (\texttt{\bs\bs~\bs hline}). To do this, the options can be used with \texttt{\bs sqlrow} as shown here: \\ \texttt{\bs sqlrow[fldsep=\&,rowsep=\bs\bs~\bs hline]\{SELECT I.NR, A.NR, A.PRICE, I.AMOUNT, (A.PRICE * I.AMOUNT) FROM ARTICLE A, INVOICE\_LINE I WHERE I.NR = \$VAR1 AND I.ARTICLE\_NR = A.NR\}} \vs This will produce an output like: \\ \texttt{1 \& 9712 \& 12 \& 1 \& 12 \bs\bs~\bs hline 2 \& 4768 \& 9.75 \& 3 \& 29.25 \bs\bs~\bs hline 3 \& 4363 \& 1.95 \& 10 \& 19.5 \bs\bs~\bs hline 4 \& 8375 \& 12.5 \& 2 \& 25 \bs\bs~\bs hline} \subsubsection{Output rows on separate lines} Some \LaTeX\ packages require input on a separate line. If this output is to be read from a database, this can be set with the \texttt{rowsep} option using the fixed text ``\texttt{NEWLINE}''. Changing the example from section \ref{sqlrow} above to:\\ \texttt{\bs sqlrow[fldsep=\&,rowsep=\bs\bs~\bs hline NEWLINE]\{SELECT I.NR, A.NR, A.PRICE, I.AMOUNT, (A.PRICE * I.AMOUNT) FROM ARTICLE A, INVOICE\_LINE I WHERE I.NR = \$VAR1 AND I.ARTICLE\_NR = A.NR\}} \vs would produce the following result: \\ \texttt{1 \& 9712 \& 12 \& 1 \& 12 \bs\bs~\bs hline \\ 2 \& 4768 \& 9.75 \& 3 \& 29.25 \bs\bs~\bs hline \\ 3 \& 4363 \& 1.95 \& 10 \& 19.5 \bs\bs~\bs hline \\ 4 \& 8375 \& 12.5 \& 2 \& 25 \bs\bs~\bs hline} \subsubsection{Store data in an array} The \texttt{\bs sqlrow} command can also be used to store the data in an array. The value returned by the SQL query is not displayed in this case. Instead, an array is created which can be used later in the document in a loop context (see section~\ref{loops}). Therefore, the option \texttt{\textbf{[setarr=\textit{n}]}} is used, where \textit{n} is an integer between 0 and 9. \subsection{Loop context}\label{loops} In a loop context, an array is filled with data from the database using \texttt{\bs sqlrow}.\\ Later in the document, the data can be used in a text block that will be written to the output file once for every record retrieved. \vs The text block is between the \texttt{\bs sqlstart\{\textit{n}\}} and \texttt{\bs sqlend\{\textit{n}\}} commands, where \textit{n} is the sequence number of the array to use\footnote{ in \texttt{\bs sqlend}, the sequence number is ignored, but required by syntax.}. Multiple text blocks can occur in the document, but they can \textit{not} be nested! \vs In the example below, data for unpaid invoices is stored in an array identified with sequence number 0: \texttt{\bs sqlrow[setarr=0]\{SELECT I.NR AS nr\\ \hspace*{15mm}, I.DUE\_DATE AS date\\ \hspace*{15mm}, I.TOTAL AS amount\\ \hspace*{15mm}, C.NAME AS customer\\ \hspace*{15mm}FROM INVOICE I\\ \hspace*{15mm}LEFT OUTER JOIN CUSTOMER C\\ \hspace*{20mm}ON C.NR = I.CUST\_NR\\ \hspace*{15mm}WHERE I.PAY\_DATE IS NULL\}} \vs To use this data, a text block must start with: \texttt{\bs sqlstart\{0\}}\\ Between this command and the first occurrence of \texttt{\bs sqlend\{\}}, an unlimited amount\footnote{ limited by your computer's memory only} of \LaTeX\ text can be written. Within this text, every occurrence of \texttt{\bs sqluse\{<\textit{field name}>\}} will be replaced with the matching field from the current row, e.g.: \noindent\begin{verbatim} \sqlstart{0} \begin{flushright} Regarding: invoicenumber \sqluse{nr} \end{flushright} Dear \sqluse{customer}, On \today, the invoice with number \sqluse{nr}, payable before \sqluse{date}, was not yet received by us. We kindly request you to pay the amount of \texteuro\sqluse{amount} as soon as possible. \newpage \sqlend{} \end{verbatim} \subsubsection{If-endif control block} In the loop context, parts of the document can be enabled if certain conditions are met, using a control block with \texttt{\bs sqlif\{\textit{condition(s)}\}} and \texttt{\bs sqlendif\{\}}. \vs \textit{Conditions} can be up to 2 conditions separated by an \textit{and} (\texttt{\&\&}) or \textit{or} (\texttt{||}). The condition(s) consist of an left value and an right value seperated by 1 of the following comparisson operators: `\texttt{==}', `\texttt{!=}', `\texttt{<}',. `\texttt{>}', `\texttt{<=}' or `\texttt{>=}'.\\ Numeric values will be used as is. When the value is text, it is expected to be the name of a field and `\texttt{\bs sqluse\{\}}` will be called to retrieve the value. \vs \noindent Example:\\ \texttt{\bs sqlif\{article\_nr == 123 \&\& \bs stock < 5\}\\ Stock is below threshold, please reorder. }\\ \bs sqlendif\{\} \vs Note the conditions are very basic with the following limitations: \begin{itemize} \item A maximum of 2 conditions is supported per if-statement. \item Only numeric comparissons are supported. \item If-elsif blocks cannot be nested. \end{itemize} When checks are needed that are not supported by SQL\TeX, a workaround can be implemented in the SQL code. \subsection{Get input from external programs}\label{sqlsystem} The \texttt{\bs sqlsystem} command can be used to call commands at the operating system or external scripts and use their output in the location where the command was given. Any command arguments can be given in the command line. When used in a loop context (see section~\ref{loops}), \texttt{\bs sqluse} can also be used to provide data to the script. If command arguments must be given for database access, the following tags can be used: \begin{itemize} \item[\texttt{}] Name of the database server. \item[\texttt{}] Username to connect to the database. \item[\texttt{}] Password to connect to the database. \item[\texttt{}] Name of the database. \end{itemize} They will be replaced by the credentials for connecting to the database (see section~\ref{opendb}). \vs \noindent Example:\\ \texttt{\bs sqlsystem\{./add\_vat --usr --db --pwd $\hookleftarrow$ \\ --inv \bs sqluse\{invoice\_nr\}\}} \vs By default, use of this command is disallowed. To enable it, set the value of \texttt{sqlsystem\_allowed} to ``1'' in the configuration file (see also section~\ref{config}. If the command is disabled, occurances of this command will be replaced by the fixed text ``\texttt{use of the \bs sqlsystem command is disallowed in the configuration}''. \vs \noindent\hspace{-3mm}\textit{\underline{Note:}} The \texttt{\bs sqlsystem} command cannot span multiple lines! \subsection{Output multiple documents}\label{multidoc} A single input file can be created to generate more output files using the \texttt{--multidoc-numbered} or \texttt{--multidoc-named} commandline option. The input document must contain the command \texttt{\bs sqlsetparams} without any options. The query that follows can return an unlimited number of rows: \\ \texttt{\bs sqlsetparams\{SELECT NR, CUST\_NR FROM INVOICES WHERE REMINDERS = \$PAR1\}} \vs By processing this command, SQL\TeX\ builds a list with all values retrieved and processes the input file again for each row.\\ In those runs, the queries are executed as described in the previous sections, using the returned fields to replace \texttt{\$MPAR\textit{n}} placeholders, where \textit{n} starts with 1 and represents the fields in the order as they have been retrieved:\\ \texttt{\bs sqlrow\{SELECT * FROM INVOICES WHERE NR = \$MPAR1\}} \\ \texttt{\bs sqlrow\{SELECT * FROM CUSTOMER WHERE CUST\_NR = \$MPAR2\}} \vs The options \texttt{--multidoc-numbered} or \texttt{--multidoc-named} cannot be used together.\\ Without these options, a parameter can be given and a single output document will be created, ignoring the \texttt{\bs sqlsetparams} command. \vs With the \texttt{--multidoc-numbered} option, output filenames will be numbered \texttt{\emph{filename}\_1.tex} to \texttt{\emph{filename}\_\emph{n}.tex}.\\ With the \texttt{--multidoc-named} option, output filenames will be numbered \\\texttt{\emph{filename}\_\emph{parameter}.tex}, where \emph{parameter} is the first value taken from the database (\texttt{\$MPAR1}, the invoice number \texttt{nr} in the example above). \\ Note the parameter will not be formatted to be filename-friendly!\\ \subsection{Update database records} Since version 1.5, SQL\TeX\ supports database updates as well: \\ \texttt{\bs sqlupdate\{UPDATE INVOICE SET REMINDERS = REMINDERS + 1, LAST\_REMINDER = NOW() WHERE NR = \$VAR1\}} This command accepts no options. \vs By default, the update statements will be ignored. To actually process them, the commandline options \texttt{--updates} must be given! \section{Process your SQL\TeX\ file} To process your SQL\TeX\ file and create a \LaTeX\ file with all information read from the database, call SQL\TeX\ with the parameter(s) and (optional) command\-line options as described here. \subsection{Parameters}\label{params} SQL\TeX\ accepts more than one parameter. The first parameter is required; this should be the input file, pointing to your \LaTeX\ document containing the SQL\TeX\ commands. By default, SQL\TeX\ looks for a file with extension `\texttt{.tex}'. \vs All other parameters are used by the queries, if required. If an SQL query contains the string \texttt{\$PAR\textit{n}}\footnote{where \textit{n} is a number between 1 and 9. Note parameter `0' cannot be used, since that contains the filename!}, it is replaced by that parameter (see also section~\ref{sqlstatements}). \subsection{Command line options}\label{cmdline} SQL\TeX\ accepts the following command- line options: \begin{description} \item[\texttt{--configfile \textit{file}, -c \textit{file}}] SQL\TeX\ configuration file. Default is \texttt{SQLTeX.cfg} in the systems default location (see section \ref{config}). \item[\texttt{--file-extension \textit{string}, -E \textit{string}}] replace input file extension in outputfile: \texttt{input.tex} will be \texttt{input.\textit{string}}. \\ For further notes, see option \texttt{--filename-extend} below. \item[\texttt{--filename-extend \textit{string}, -e \textit{string}}] add \textit{string} to the output filename: \texttt{input.tex} will be \texttt{input\textit{string}.tex}. This overwrites the configuration setting \texttt{stx}. \\ In \textit{string}, the values between curly braces \{\} will be substituted: \begin{description} \item[P\textit{n}] parameter \textit{n} \item[M] current monthname (\textit{Mon}) \item[W] current weekday (\textit{Wdy}) \item[D] current date (\textit{yyyymmdd}) \item[DT] current date and time (\textit{yyyymmddhhmmss}) \item[T] current time (\textit{hhmmss}) \end{description} e.g., the command\\ \hspace*{1em}\texttt{sqltex --filename-extend \_\{P1\}\_\{W\} my\_file code}\\ will read `\texttt{my\_file.tex}' and write `\texttt{myfile\_code\_Tue.tex}'.\\ The same command, but with option \texttt{---file-extension} would create the outputfile \texttt{my\_file.\_code\_Tue}\\ The options \texttt{--file-extension} and \texttt{--filename-extend} cannot be used together or with \texttt{--output}. \item[\texttt{--force, -f}] force overwrite of existing files. By default, SQL\TeX\ exits with a warning message it the outputfile already exists. \item[\texttt{--help, -h}] print this help message and exit. \item[\texttt{--multidoc-numbered, -m}] Multidocument mode; create one document for each parameter that is retrieved from the database in the input document (see section~\ref{multidoc}). This option cannot be used with \texttt{--output}. \item[\texttt{--multidoc-named, -M}] Same as \texttt{--multidoc-numbered}, but with the parameter in the filename instead of a serial number (see section~\ref{multidoc}). \item[\texttt{--null-allowed, -N}] \texttt{NULL} return values allowed. By default SQL\TeX\ exits if a query returns an empty set. \item[\texttt{--output \textit{file}, -o \textit{file}}] specify an output file. Cannot be used with \texttt{--file-extension}, \texttt{--filename-extend} or the \texttt{--multidoc} options. \item[\texttt{--skip-empty-lines, -S}] All SQL\TeX\ commands will be removed from the input line or replaced by the corresponding value. The rest of the input line is written to the output file. This includes lines that only contain a SQL\TeX\ command (and a newline character). This will result in an empty line in the output file.\\ By specifying this option, these empty lines will be skipped. Lines that were empty in the input will be written. \item[\texttt{--write-comments, -C}] \LaTeX\ comments in the input file will be skipped by default. With this option, comments will also be copied to the output file. \item[\texttt{--prefix \textit{prefix}, -p \textit{prefix}}] prefix used in the SQL\TeX\ file. Default is \texttt{sql} (see also section~\ref{config} on page~\pageref{prefix}. This overwrites the configurarion setting \texttt{cmd\_prefix}. \item[\texttt{--password \textit{[password]}, -P} \textit{[password]}] database password. The value is optional; if omitted, SQL\TeX\ will prompt for a password. This overwrites the password in the input file. \item[\texttt{--quiet, -q}] run in quiet mode. \item[\texttt{--replacementfile \textit{replace}, -r \textit{replace}}] Specify a file that contains the replace characters (see section~\ref{replfiles}). \\ Default is \texttt{SQLTeX{\_}r.dat} in the systems default location (see section \ref{replfiles}). This default file will always be used after the given replacement file, unless \texttt{multi\_rfile} is set to \texttt{0} in the configuration (see secion \ref{config}). \item[\texttt{--no-replacementfile, -R}] Do not use a replace file. \texttt{--no-replacementfile} and \texttt{--replacementfile \textit{file}} are handled in the same order as they appear on the command line, overwriting each other.\\ For backwards compatibility, \texttt{-rn} is also still supported. \item[\texttt{--sqlserver \textit{server}, -s \textit{server}}] SQL server to connect to. Default is \texttt{localhost}. \item[\texttt{--updates, -u}] if the input file contains updates, process them. \item[\texttt{--username \textit{user}, -U \textit{user}}] database username. This overwrites the username in the input file. \item[\texttt{--version, -V}] print version number and exit. \end{description} \section{SQL\TeX\ errors and warnings} \noindent\textbf{\texttt{no input file specified}} \vspace{1mm} \noindent SQL\TeX\ was called without any parameters.\\ \textit{Action:} Specify at least one parameter at the commandline. This parameter should be the name of your input file. \vs \noindent\textbf{\texttt{File \textit{input filename} does not exist}} \vspace{1mm} \noindent The input file does not exist.\\ \textit{Action:} Make sure the first parameter points to the input file. \vs \noindent\textbf{\texttt{outputfile \textit{output filename} already exists}} \vspace{1mm} \noindent The outputfile cannot be created because it already exists.\\ \textit{Action:} Specify another output filename with command line option \texttt{-e}, \texttt{-E} or \texttt{-o}, or force an overwrite with option \texttt{-f} (see also section\ref{cmdline}). \vs \noindent\textbf{\texttt{no database opened at line \textit{line nr}}} \vspace{1mm} \noindent A query starts at line \textit{line nr}, but at that point no database was opened yet. \\ \textit{Action:} Add an \texttt{\bs sqldb} command prior to the first query statement. \vs \noindent\textbf{\texttt{insufficient parameters to substitute variable on line \textit{line nr}}} \vspace{1mm} \noindent The query starting at line \textit{line nr} uses a parameter in a \textsc{where}- clause with \texttt{\$PAR\textit{n}}, where \textit{n} is a number bigger than the number of parameters passed to SQL\TeX\. \\ \textit{Action:} Specify all required parameters at the command line. \vs \noindent\textbf{\texttt{trying to substitute with non existing on line \textit{line nr}}} \vspace{1mm} \noindent The query starting at line \textit{line nr} requires a variable \texttt{\$VAR\textit{n}} in its \textsc{where}- clause, where \textit{n} points to a variable that has not (yet) been set. \\ \textit{Action:} Change the number or set the variable prior to this statement. \vs \noindent\textbf{\texttt{trying to overwrite an existing variable on line \textit{line nr}}} \vspace{1mm} \noindent At line \textit{line nr}, a \texttt{\bs sqlfield} query tries to set a variable \textit{n} using the option \texttt{[setvar=\textit{n}]}, but \texttt{\$VAR\textit{n}} already exists at that point. \\ \textit{Action:} Change the number. \vs \noindent\textbf{\texttt{no result set found on line \textit{line nr}}} \vspace{1mm} \noindent The query starting at line \textit{line nr} returned a \texttt{NULL} value. If the option \texttt{-N} was specified at the commandline, this is just a warning message. Otherwise, SQL\TeX\ exits. \\ \textit{Action:} None. \vs \noindent\textbf{\texttt{result set too big on line \textit{line nr}}} \vspace{1mm} \noindent The query starting at line \textit{line nr}, called with \texttt{\bs sqlfield} returned more than one field. \\ \textit{Action:} Change your query or use \texttt{\bs sqlrow} instead. \vs \noindent\textbf{\texttt{no parameters for multidocument found on line \textit{line nr}}} \vspace{1mm} \noindent SQL\TeX\ is executed in multidocument mode, but the statement on line \textit{line nr} did not provide any parameters for the documents. \\ \textit{Action:} Check your query. \vs \noindent\textbf{\texttt{too many fields returned in multidocument mode on \textit{line nr}}} \vspace{1mm} \noindent In multidocument mode, the lis of parameters retrieved on line \textit{line nr} returned more than one fields per row. \\ \textit{Action:} Check your query. \vs \noindent\textbf{\texttt{start using a non-existing array on line \textit{line nr}}} \vspace{1mm} \noindent An \texttt{\bs sqlstart} command occurs, but refers to a non-existing array. \\ \textit{Action:} Check the sequence number of the array filled with \texttt{\bs sqlrow[setarr=\textit{n}]} and retrieved with \texttt{\bs sqlstart\{\textit{n}\}} in your input file. \vs \noindent\textbf{\texttt{\bs sqluse command encountered outside loop context on line \textit{line nr}}} \vspace{1mm} \noindent Data from array is used, but the current input file position is not in the context where this data is available.\\ \textit{Action:} Check the presence and positions of the \texttt{\bs sqlstart} and \texttt{\bs sqlend} commands in your input file. \vs \noindent\textbf{\texttt{unrecognized command on line \textit{line nr}}} \vspace{1mm} \noindent At line \textit{line nr}, a command was found that starts with ``\texttt{\bs sql}'', but this command was not recognized by SQL\TeX\. \\ \textit{Action:} Check for typos. If the command is a user- defined command, it will conflict with default SQL\TeX\ commands. Change the SQL\TeX\ command prefix (see section~\ref{config}). \vs \noindent\textbf{\texttt{no sql statements found in \textit{input filename}}} \vspace{1mm} \noindent SQL\TeX\ did not find any valid SQL\TeX\ commands. \\ \textit{Action:} Check your input file. \section{Copyright and disclaimer} \noindent\hrulefill \\ The SQL\TeX\ project is available from GitHub: \url{https://github.com/oveas/sqltex}\\ For bugs, questions and comments, please use the issue tracker available at \url{https://github.com/oveas/sqltex/issues} \vspace{3mm} \noindent Copyright\copyright\ 2001-2024 - Oscar van Eijk, Oveas Functionality Provider \noindent\hrulefill \\ \noindent This software is subject to the terms of the LaTeX Project Public License; see \url{http://www.ctan.org/tex-archive/help/Catalogue/licenses.lppl.html}. \section{History} \begin{description} \item[v3.0] \textit{released: Sep 20, 2024} \begin{itemize} \item Made it possible to run SQL\TeX\ directly from the distribution without \texttt{configure} and \texttt{make [install]} to make integration in \TeX\ Live possible. \item Renamed the script to \texttt{sqltex}. For backwards compatibility, during installation on linux a symbolic link \texttt{SQLTeX} is created. \item The \texttt{SQLTeX.exe} binary is no longer included in the distribution (see footnote \footref{winexe} on page \pageref{winexe}). \item Removed support for the \texttt{--use-local-config} commandline option. The options \texttt{--configfile} and \texttt{--replacementfile} can be used instead. \item Added an option to disable the \texttt{--configfile} command line option (see note on page \pageref{cfg:disable}). \item Added the \texttt{--skip-empty-lines} and \texttt{--write-comments} commandline options. \item Added support for multiple replacement files. \item Fix: ordering in the replacement file. \end{itemize} \item[v2.2] \textit{released: Jul 31, 2024} \begin{itemize} \item Extended the default replace file (see \ref{replfiles}) with more special characters (e.g. with diacritics) and HTML tags. \item Issue \#6 (\url{https://github.com/oveas/sqltex/issues/6}): added support for ODBC drivers \item Issue \#8 (\url{https://github.com/oveas/sqltex/issues/8}): added support for parameter-driven in \texttt{\bs sqlsetparams} statements (multi-document mode).\\ \textit{\textbf{Note:}} This requires an update of your input files for multi-document mode that have been created before v2.2. Refer to section \ref{mdocupdates} for details. \item Added the \texttt{\bs sqlsystem} command. \item Added the \texttt{\bs sqlif}-\texttt{\bs sqlendif} control block. \end{itemize} \item[v2.1] \textit{released: Jan 21, 2022} \begin{itemize} \item Fix bug \#2 (\url{https://github.com/oveas/sqltex/issues/2}): standard path management for output files.\\ See config item \texttt{def\_out\_is\_in} in section \ref{config} to revert to pre v2.1 behaviour. \item Fix: help was not displayed on Windows \item Implemented '?' as password in \texttt{dbopen} \item Implemented '?' as username in \texttt{dbopen} \item Implemented long options \item Allow overwriting variables in multidocument mode \item Added simple automated regression tests \item Added a man page for linux users \item Rewrote the installation procedure, now using \texttt{autotools} on linux. \item On linux, change the default installation directory to \texttt{/usr/bin} and store the configuration- and replacement files is \texttt{/etc}. \item Added option \texttt{--use-local-config}. \end{itemize} \item[v2.0] \textit{released: Jan 12, 2016} \begin{itemize} \item Fix: Oracle support using ORASID \item Fix: Ensure replacements are handled in the same order as they appear in the replacements file \item Separate configuration file(s) \item Added the options \texttt{-c} and \texttt{-M} \item Support for regular expressions in replace files \item Implemented support for the \LaTeX\ \texttt{\bs input} and \texttt{\bs include} directives \item Implemented loop context \item Skip commentlines \item Project moved from local CVS to GitHub \end{itemize} \item[v1.5] \textit{released: Nov 23, 2007} \begin{itemize} \item Support for multiple databases \item Implemented database updates (\texttt{sqlupdate}) \item Implemented multiple output documents (option \texttt{-m}) \end{itemize} \item[v1.4.1] \textit{released: Feb 15, 2005}\\ Fix: removed leading whitespaces added to database results before replace \item[v1.4] \textit{released: May 2, 2002}\\ Implemented replace files \item[v1.3] \textit{released: Mar 16, 2001}\\ First public release \end{description} \subsection{Changes that require updates in your input files} \subsubsection{Multi-document mode since v2.2}\label{mdocupdates} Up until v2.1, the statement in \texttt{\bs sqlsetparams} could return only one field per row and the statement itself could not handle parameters. The placeholder \texttt{\$PAR1} was reserved for the subsequent statements. Since v2.2 it is possible to retrieve multiple values per row. They will replace the placeholders \texttt{\$MPAR\textit{n}} in the subsequent statements, while \texttt{\$PAR\textit{n}} placeholders can now also be used for regular parametes in the \texttt{\bs sqlsetparams} statement itself. \vs This means, in input documents created before v2.2, all ``\texttt{\$PAR1}'' placeholders must be replaced by ``\texttt{\$MPAR1}''. \end{document}