About
Shop
LaTeX
Software
Books
Gallery
News
Contact
Blog
Settings
Latest news 2019-11-04: The giveaway of two signed copies of “Quack, quack, quack. Give my hat back!” has closed and the winning entrants have been selected. Thank you to everyone who took part.


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:

Yes

Similarly

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

produces:

Yes

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)
surname address1 address2 town county subscribed
Parrot 42 The Lane   Some Town Noshire 1
Canary 24 The Street Some Village Some Town Noshire 0
Zebra 856 The Avenue   Some City CA 1
Arara Nenhuma Rua   São Paulo   0
Duck 1 The Street Another Village Some City Imagineshire 0
Canary 24 The Street Some Village Some City Noshire 1
End of Image.




Table 2.10: Customers (SQL)

surname address1 address2 town county subscribed
Parrot 42 The Lane NULL Some Town Noshire true
Canary 24 The Street Some Village Some Town Noshire false
Zebra 856 The Avenue NULL Some City CA true
Arara Nenhuma Rua NULL São Paulo NULL false
Duck 1 The Street Another Village Some City Imagineshire false
Canary 24 The Street Some Village Some City Noshire true
End of Image.




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

Surname Address 1 Address 2 Town County Subscribed
Parrot 42 The Lane Some Town Noshire
Canary 24 The Street Some Village Some Town Noshire
Zebra 856 The Avenue Some City CA
Arara Nenhuma Rua São Paulo
Duck 1 The Street Another Village Some City Imagineshire
Canary 24 The Street Some Village Some City Noshire
End of Image.



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).

© 2015 Dickimaw Books. "Dickimaw", "Dickimaw Books" and the Dickimaw parrot logo are trademarks. The Dickimaw parrot was painted by Magdalene Pritchett.

Terms of Use Privacy Policy Cookies Site Map FAQs