Posts Tagged ‘v11’

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

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 »

My first attempt to mirror data from 4D to another database didn’t work. So, I’m taking another crack at it with lessons learned from my previous attempt.

My aim now is to make a component that will be transaction safe, and model itself after the 4D KB article published about Synchronization and Replication in v12 (4D partner login required). Since I am still using v11 and a v12 upgrade is not on the horizon, I am taking my own approach.


Allow definition of multiple ‘mirror preferences’. Each of these linkages would define:

tables and fields to mirror
-> table pk field name
-> table last updated date/time field names
-> boolean field name to escape mirroring (so you can accept changes to the mirrored database and write them back without mirroring them out again)
interval to execute at
service to utilize (soap, file, sql, plugin)
last run date/time

@todo build synchronization of table/field names via alter table


On the defined interval, or manually, the server process would spawn processes to look at target tables for updated/new records (making it transaction safe) and then of the records that were updated send over the target data.

Feel free to take it and run with it. Feedback and your thoughts welcome.

Say is there a market for companies that want to have their data available in a SQL database of repute?

More post to follow.

Read Full Post »

%d bloggers like this: