Programming the Perl DBI

Datatypes and NULL Values

One of the most important aspects of the structures defined within a database, such as tables and views, is the datatype of each of the columns. Perl is a loosely typed language, whereas SQL is strongly typed. Thus, each field or value is of a given datatype that determines how values and fields are compared. For example, the mapref field within the megaliths table would not be much use if it could hold only dates!

Therefore, it is important to assign an appropriate datatype to each column. This avoids any potential confusion as to how the values stored within each column are to be interpreted, and also establishes how these values can be compared in query condition clauses.

There are several common datatypes. The most widely used of these can be grouped as follows:

Numeric datatypes

The grouping of numeric datatypes includes types such as integer and floating point (or real) numbers. These types, depending on your database, may include FLOAT , REAL , INTEGER , and NUMBER . Numeric datatypes are compared in the obvious way; that is, the actual values are tested.

Character datatypes

Character datatypes are used to store and manipulate textual data. Any characters whatsoever—digits or letters—can be stored within a character datatype.

However, if digits are stored within a character datatype, they will be treated as being a string of characters as opposed to a number. For example, they’ll be sorted and ordered as strings and not numbers, so "10" will be less ...