Posts Tagged ‘data types’

Recently while importing a new physical schema from PostgreSQL 8.4 into Oracle Data Integrator(ODI) via JDBC driver all of the tables were reversing into the model without data types defined for text, double precision, smallint, integer and boolean.

If one or two fields are missing it’s easy enough to set the data type manually for the column, but for a whole mess of tables and thousands of fields it’s unrealistic.

The solution is to define the proper data types using the physical architecture tab with PostgreSQL internal names for the data types. Even though by default there is one for integer, looking at the data type definitions smallint maps to int2, integer maps to int4, double precision maps to float8, boolean maps to bool.

Using these aliases which are used internally by PostgreSQL for historical reasons in the ODI Physical Architecture will fix the missing data types during the reverse process.

Read Full Post »

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
	SET FieldBoolean = 'true'
	WHERE id=29168

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
	SET FieldBoolean =CAST( 'true' as BOOLEAN)
	WHERE id=29168

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

Read Full Post »

Now that 4Dv11 SQL supports float data type; it would be nice to match that with a C_FLOAT. Using C_REAL throws an error for incompatible assignment at runtime.

I.e. if your database has a field with float data type don’t plan on using it with native 4D code, and don’t expect the complier to warn you. See my feature request with 4D.

` throws error (from GET LAST ERROR STACK): 54 4DRT Argument types are incompatible.

There is an Is Float constant to compare to GET FIELD PROPERTIES data type return longint.

And while we’re at it, why are SQL data type values different from GET FIELD PROPERTIES data type values.

Read Full Post »

%d bloggers like this: