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.

Author’s Note

Initial md Generated using https://github.com/jsr6720/wordpress-html-scraper-to-md

Original Wordpress categories: [‘4D’]

Original Wordpress tags: “4D”, “4D”, “CAST”, “data types”, “Error code: 1108”, “sql”

Original Wordpress comments: 1 Comment

Significant revisions

tags: 2011, wordpress, txcowboycoder, technology, 4D, sql

  • May 6th, 2024 Converted to jekyll markdown format and copied to personal site
  • Apr 26th, 2011 Originally published on txcowboycoder wordpress site

EOF/Footnotes