dtl

Processing NULLs in DTL

NULLs: The motivation for nonexistence

In many database systems, it is possible for a field in a database record not to have a value. This is represented by a blank entry called a NULL value (or NULL for short). NULLs occur in the real world because certain pieces of data may not apply to a record or may be missing from a record. For example, in the city's public records, a date of death for a person who is still alive would be NULL (a person who is still breathing cannot have a date of death unless he's miraculously been resurrected ... more on that may be found at the true DTL site).

Validating NULL database fields in DTL

The default action in DTL is that if you have a NULL value in a field, DTL will throw an exception. This exception indicates that one of your columns came back with a NULL value and you did not set up a policy for how you wanted to handle it. In, general, policies for how you want to handle NULL data values are defined in:

SelVal - when reading a row with NULL values decide what default values to set for the NULL fields
InsVal - when writing a row with NULL values copy the NULL status flag for these fields back to the database

In general, there are three common ways of handling NULL values:

NULL values are not allowed. Throw an error. (This is the default.)
Set any NULL values to zero. In this case your SelVal function can just make a call to boundIOs.InitNullFields() to set any NULL values to zero.
Use a wrapper that holds the underlying type + a flag to indicate if the value is NULL. This is done for you in the variant_row / DynamicDBView class. If you want a wrapper that you can use for your own classes see the dtl::nullable class as defined in lib/nullable.h.

If you want to write custom logic for how your class handles NULLs, the two dtl classes that are used to read and write NULL flags for fields in a given data row are

BoundIO
variant_field (this only applies if you are using DynamicDBView or DynamicIndexedDBView).

Example: InsVal for variant_row - check the variant row null flags and use this to decide whether or not to write NULL values to the database.

// this example shows how to use the null status flag information from variant_row's data fields
// to properly set the corresponding flags in the BoundIOs for a record to insert

// here we want to make sure that any NULL data in the variant_row
// gets reflected in the resulting parameters that are sent to the database
// *** Note: This example is the actual implementation of the DefaultInsValidate
// for variant_row in the DTL library ***


template<> class dtl::DefaultInsValidate<variant_row>
{
public:

	bool operator()(BoundIOs &boundIOs, variant_row &rowbuf) {

		boundIOs.ClearNull(); // clear null on all BoundIO's

		for (BoundIOs::iterator b_it = boundIOs.begin();
				b_it != boundIOs.end(); b_it++)
		{
			BoundIO &boundIO = (*b_it).second;

			if (boundIO.IsParam()) // rowbufs could have parameters in the case of sql_iterator
				try{
					if(rowbuf[boundIO.GetName()].IsNull())
						boundIO.SetNull();
					else
						boundIO.ClearNull();
				}
				catch(...) 
				{
					// This parameter # is not part of the rowbuf, lives in ParamBuf instead
					boundIO.ClearNull();
				}
			else
				if(rowbuf[boundIO.GetName()].IsNull())
					boundIO.SetNull();
				else
					boundIO.ClearNull();

		}
		return true;
	}

	~DefaultInsValidate() {};
};

Example: SelValidate that checks the null flags in BoundIOs and uses this information to set default values for null fields.

// Assign valid defaults for null values

class Example
{
  public:					// tablename.columnname:
	int exampleInt;	    			// DB_EXAMPLE.INT_VALUE
	string exampleStr;			// DB_EXAMPLE.STRING_VALUE
	double exampleDouble;			// DB_EXAMPLE.DOUBLE_VALUE
	long exampleLong;			// DB_EXAMPLE.EXAMPLE_LONG
	TIMESTAMP_STRUCT exampleDate;		// DB_EXAMPLE.EXAMPLE_DATE
};

// Validation behavior, assign default values for NULL fields
template<> class dtl::DefaultSelValidate<Example> 
{
public:
bool operator()(BoundIOs &boundIOs, Example &rowbuf) {
	if (boundIOs["INT_VALUE"].IsNull()) {
		rowbuf.exampleInt = 0;	
	}
	if (boundIOs["STRING_VALUE"].IsNull()) {
		rowbuf.exampleStr = "";	
	}
	if (boundIOs["DOUBLE_VALUE"].IsNull()) {
		rowbuf.exampleDouble = 0;	
	}
	if (boundIOs["EXAMPLE_LONG"].IsNull()) {
		rowbuf.exampleLong = 0;	
	}
	if (boundIOs["EXAMPLE_DATE"].IsNull()) {
		const TIMESTAMP_STRUCT defaultDate = {2000, 1, 1, 0, 0, 0, 0};
		rowbuf.exampleDate = defaultDate;	
	}

	// Now check that values are in acceptable range
	// Return false/failure if values out of range
	if (rowbuf.exampleDouble > 100)
		return false;

	return true;	// data is OK
}

};

Finally we show an example of using a DynamicDBView to write NULLs to the database

Example: Using a DynamicDBView to insert records into the database. Illustrates writing NULL values to the database.

// Using a DynamicDBView to insert records into the database.

// this example also shows how to set NULL fields in a variant_row

// Insert two rows into a table with unknown fields
void SimpleDynamicWrite() {
	TIMESTAMP_STRUCT paramDate = {2012, 12, 23, 0, 0, 0, 0}; 
	// Mayan DOOMSDAY! End of the Mayan 5126 year long calendar cycle starting from May 1, 3094 B.C.
	// Date is 13.13.13.0.0.0.0  4 Ahaw, 3 K'ank'in
	
	DynamicDBView<> view("DB_EXAMPLE", "*");

	DynamicDBView<>::insert_iterator write_it = view;

	// NOTE: We need to construct r from the view itself since we
	// don't know what fields the table will contain.
	// We therefore make a call to the DataObj() function to have the
	// table return us a template row with the correct number of fields
	// and field types.
	variant_row r(view.GetDataObj());

	// Prepare the number of the beast!
	// Set all fields to the value 6,
	// except for the last column which is a date and cannot
	// currently accept numeric values
	for (size_t i = 0; i < r.size()-1; i++)
	{
		 r[i] = 6;
	}
	r[i] = paramDate;  // set the Doomsdate

	// insert the number
	*write_it = r;
	write_it++;

	// Prepare the number of angels who stand before
	// the throne of God!
	// Set all fields to the value 7,
	// except for the last column which is a date and cannot
	// currently accept numeric values
	for (i = 0; i < r.size()-1; i++)
	{
		 r[i] = 7;
	}
	r[i] = paramDate;

	// insert the number
	*write_it = r;
	write_it++;

	// Insert Purgatory (the void) into the database.
	// Set all fields to NULL
	for (i = 0; i < r.size()-1; i++)
	{
		 r[i] = NullField();
	}
	r[i] = NullField();

	// insert the number
	*write_it = r;
	write_it++;

	// For more on this example - see the *REAL* DTL homepage!
}

Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose is hereby granted without fee, provided that the above copyright notice appears in all copies and that both that copyright notice and this permission notice appear in supporting documentation. Corwin Joy and Michael Gradman make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.

This site written using the ORB.