Creating fields via SQL vs creating fields via structure editor

Beware when creating fields via SQL engine with 4Dv12/v11. Creating fields via SQL does not allow setting field property “Map NULL values to blank values”. The suggested work around is to define the field with NOT NULL constraint.

The different outcomes of the two ways to create fields is terrible behavior because of lack of support for null values in the 4DDB engine. 4D seems to assume developers are either using 4D with all native code, or all SQL code, not hybrid solutions.

Ultimately the concern to the developer is having assumptions regarding the data respected. Coming from previous version of 4D all fields have the property checked for mapping null values to blank values. Legacy applications can have code reliant on the assumption of no null values.

From 4D Docs on integrating 4D and the 4D SQL engine:

The NULL values are implemented in the 4D SQL language as well as in the 4D database engine. However, they are not supported in the 4D language

More red flags from the knowledge base:

Sorting fields w/NULLS changes the current selection

In version 11.4, if you have NULL values in any field and then do an ORDER BY on that field, any records that contain NULL values will be removed from the Current Selection.

Also displaying and saving null values to the database is even more difficult.

Create field via structure

By default the “Map NULL values to blank values” field property is enabled.

Create field via SQL

This field was created with NOT NULL constraint.

Read Full Post »

Allow NULL values in 4DB engine

Developers need to be able to use nulls if using SQL engine and 4DB engine in 4D. As of this writing the concepts of NULL values are very loosely integrated with 4D.

NULL Values in 4D

The NULL values are implemented in the 4D SQL language as well as in the 4D database engine. However, they are not supported in the 4D language. It is nevertheless possible to read and write NULL values in a 4D field using the Is field value Null and SET FIELD VALUE NULL commands.

Never mind the very logic of a null value is to indicate not set. Mapping null values to ‘blank’ values only mucks up the data. Does a widget inventory level of 0 indicate ‘not yet set’ or ‘depleted to zero’? Yes business logic will clear these things up, but integration of null values with the 4D native engine would be better.

// http://doc.4d.com/4Dv11.6/help/command/en/page965.html
// This is a useless command, and is only used by the SQL kernal of 4D
SET FIELD VALUE NULL([Table_1]Field_1)

// having a null keyword would be useful
[Table_1]Field_1:=null

For example, you cannot search null values using the standard query window. Also, variables cannot accept null values. Null comparisons are limited to Is field value Null which can only look at fields.

Seems as if 4D is not giving developers all the tools to make a hybrid deployable solution. Its been my experience to code solutions that either go 100% native 4D code or go 100% SQL engine, because integrating both together is a pain.

4D does annoying things like convert null values to blank values when trying to display them in an object, or puts into object property definitions display attributes to control how null values are rendered.

See my comments on float data types.

Read Full Post »

Use CAST in 4D volatile SQL statements

Hint, to avoid hair pulling use the 4D function CAST to explicitly type data types in 4D SQL statements.

Consider the following for boolean field [ODBCTest]FieldBoolean.

// 4Dv12.1 sees this as a string assignment to boolean
// FieldBoolean = true throws an unfound column error
Begin SQL
	UPDATE ODBCTest 
	SET FieldBoolean = 'true'
	WHERE id=29168
End SQL

This will throw error 1108:

Error code: 1108
Operation VK_STRING  = VK_BOOLEAN  is not type safe.
component: 'SQLS'
task 11, name: 'P_2'

However, using CAST will give us the desired result.

// also works: CAST(1 as BOOLEAN)
Begin SQL
	UPDATE ODBCTest 
	SET FieldBoolean =CAST( 'true' as BOOLEAN)
	WHERE id=29168
End SQL

Of course it would be nice if 4D compiler could detect these types of problems before they occur.

Read Full Post »