2.9 ⁑Null and Boolean Values

Recall from Example 8 that there is a difference between the sample people.csv file and the corresponding people SQL table. Some of the entries in the SQL table have null values in the address2 column whereas in the CSV file the values are empty rather than null. You can test for a null value using:

\DTLifnull {⟨cs⟩}{⟨true⟩}{⟨false⟩}

where ⟨cs⟩ is a control sequence, ⟨true⟩ is what to do if ⟨cs⟩ is null and ⟨false⟩ is what to do if ⟨cs⟩ isn't null. To test for an empty value, you can use:

\ifdefempty {⟨cs⟩}{⟨true⟩}{⟨false⟩}

which is provided by the etoolbox package (automatically loaded by datatool). The datatool package provides the command:

\DTLifnullorempty {⟨cs⟩}{⟨true⟩}{⟨false⟩}

which is just a short cut for:

\ifdefempty{⟨cs⟩}{⟨true⟩}{\DTLifnull{⟨cs⟩}{⟨true⟩}{⟨false⟩}}

The other difference is the way boolean values have been stored. Both the people.csv file and the SQL people table used “1” to indicate a true value and “0” to indicate a false value in the subscribed field, but when the data was fetched from the SQL table, these values were converted to “true” and “false” in the datatool (.dbtex) file . There are a number of ways of testing whether a control sequence is equal to “true” or “1”. For example, the etoolbox package defines:

\ifdefstring {⟨cs⟩}{⟨string⟩}{⟨true part⟩}{⟨false part⟩}

This tests if the control sequence ⟨cs⟩ is defined to be ⟨string⟩. If it is, ⟨true part⟩ is done, otherwise ⟨false part⟩ is done. For example:

\newcommand*{\Subscribed}{true}%
\ifdefstring{\Subscribed}{true}{Yes}{No}

produces:

Similarly

\newcommand*{\Subscribed}{1}%
\ifdefstring{\Subscribed}{1}{Yes}{No}

produces:

If you want to test for “true” or “1”, you can combine these:

\ifdefstring
 {\Subscribed}{true}% test
 {Yes}% condition true
 {\ifdefstring{\Subscribed}{1}{Yes}{No}}

Alternatively you can use another etoolbox command:

\ifboolexpr {⟨expression⟩}{⟨true part⟩}{⟨false part⟩}

which evaluates ⟨expression⟩ and does ⟨true part⟩ if true, otherwise it does ⟨false part⟩. In this case I'm going to use the test syntax:

\ifboolexpr
{
  test{\ifdefstring{\Subscribed}{true}} or
  test{\ifdefstring{\Subscribed}{1}}
}
{Yes}{No}

(For further details of the syntax used in ⟨expression⟩, see the etoolbox documentation [51].) You might find it easier to define a command to do this. For example:

\newcommand{\ifcsbool}[3]{%
  \ifboolexpr
  {
    test{\ifdefstring{#1}{true}} or
    test{\ifdefstring{#1}{1}}
  }
  {#2}{#3}%
}

This has the syntax:

\ifcsbool{⟨cs⟩}{⟨true⟩}{⟨false⟩}

where ⟨cs⟩ is a control sequence. For example:

\ifcsbool{\Subscribed}{Yes}{No}

or (recall from Volume 1 the \ding command provided by pifont [84]):

 \ifcsbool{\Subscribed}{\ding{52}}{\ding{56}}

Example 11. Display Customer List (Null Values)

To illustrate the difference between null and empty values, let's first look at what happens if we load the sample people.csv file and display the data using \DTLdisplaydb:

\DTLloaddb{people}{people.csv}

\begin{table}
  \caption{Customers (CSV)}
  \label{tab:peoplecsv}
  \centering
  \DTLdisplaydb{people}
  [id,forenames,title,country,postcode,gender,dob]% omit these columns
  {people}
\end{table}

This produces Table 2.9. Now let's look at what happens if we fetch the people SQL table using:

$ datatooltk --output people.dbtex --sqluser sampleuser --sqldb samples --sql "SELECT * FROM people"

and load the resulting people.dbtex file:

\DTLloaddbtex{\people}{people.dbtex}

Now display the data:

\begin{table}
  \caption{Customers (SQL)}
  \label{tab:peoplesql}
  \centering
  \DTLdisplaydb
  [id,forenames,title,country,postcode,gender,dob]% omit these columns
  {\people}
\end{table}

This produces Table 2.10.

You can use \DTLifnull or \ifdefempty to check for missing entries in the SQL or CSV data, respectively. Alternatively, use \DTLifnullorempty to adapt for either case. However, we now can't simply use \DTLdisplaydb but we can use \DTLforeach* to typeset the table instead:

\begin{table}
  \caption{Customers (Check for Null and Boolean)}
  \label{tab:peoplenullcheck}
  \centering
  \begin{tabular}{lllllc}
    \multicolumn{1}{c}{\bfseries Surname} &
    \multicolumn{1}{c}{\bfseries Address 1} &
    \multicolumn{1}{c}{\bfseries Address 2} &
    \multicolumn{1}{c}{\bfseries Town} &
    \multicolumn{1}{c}{\bfseries County} &
    \multicolumn{1}{c}{\bfseries Subscribed}%
    \DTLforeach*{people}{\Surname=surname,\AddressI=address1,%
    \AddressII=address2,\Town=town,\County=county,\Subscribed=subscribed}
    {%
      \\\Surname & \AddressI &
      \DTLifnullorempty{\AddressII}{\multicolumn{1}{c}{---}}{\AddressII}
      & \Town &
      \DTLifnullorempty{\County}{\multicolumn{1}{c}{---}}{\County}&
      \ifcsbool{\Subscribed}{\ding{52}}{\ding{56}}%
    }%
  \end{tabular}
\end{table}

(Recall \ifcsbool from above.) This produces Table 2.11.

Table 2.9: Customers (CSV)

Table 2.10: Customers (SQL)

Table 2.11: Customers (Check for Null or Empty and Boolean)

You can download or view a complete document.

⇦

⇧

⇨

This book is also available as A4 PDF or 12.8cm x 9.6cm PDF or paperback (ISBN 978-1-909440-07-4).