Depending on your database or the programming language used to update your database, strings may be stored as null-terminated values. This causes a problem when filtering on those fields, because a WHERE clause such as MYFIELD = 'SomeValue' fails.

There are two ways to deal with null-terminated strings. The first way is to put an expression into the Output Expression of each character field that removed nulls from the value. The exact expression to use depends on what functions your database has. For example, with Microsoft SQL Server or Pervasive use:

REPLACE(fieldname, CHAR(0), '')

where fieldname is the name of the field. Be sure to turn off the Stonefield Query expression setting since this expression is evaluated by the database engine.

Of course, the downside to this approach is that it's more work in setting up because you have to do this for every character field. Fortunately, it's a one-time only issue.

The second way is similar but instead of doing it manually for every field, you automate it at run time by creating a DataEngine.GetCustomMetaData script that does this programmatically.

© Stonefield Software Inc., 2018 • Updated: 06/01/18
Comment or report problem with topic