Archive for the ‘4D’ Category

This allows multiple data stores but without rewriting all sql queries. Note you have to create two functions, one to accept dates, the other to accept times.

Specifically the 4D SQL function DATE_TO_CHAR. Luckily PostgreSQL has the equivalent as a formatting function to_char.

For business reasons it’s not practical to replace all instances of DATE_TO_CHAR to to_char.


Create a function in the postgresql data base that maps the DATE_TO_CHAR function to to_char. Luckily the formatting options I need are available.

Now SELECT DATE_TO_CHAR(DateField1, "YYYY-MM-DD") FROM Table1 will return the correct value regardless of the database queried. It’s important to note this works great for getting integer values from dates and casting as date objects. If queries rely on returning non-iso formatting your mileage may vary.

-- Function: date_to_char(date, text)
CREATE OR REPLACE FUNCTION date_to_char(date, text)
       RETURN to_char($1,$2)::text;
  COST 100;
ALTER FUNCTION date_to_char(date, text) OWNER TO postgres;
-- Function: date_to_char(time without time zone, text)
CREATE OR REPLACE FUNCTION date_to_char(time without time zone, text)
       RETURN to_char($1,$2)::text;
  COST 100;
ALTER FUNCTION date_to_char(time without time zone, text) OWNER TO postgres;

Read Full Post »

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 »

Standard installation of 4Dv11 comes with a set of macros to use in design mode.

The one I find most useful is the Header macro. This macro takes my name, date time stamp, the method name and places it directly into whatever type of method I’m working in.

Most analogous to function/class/package documentation blocks in other languages, they are also an excellent starting point for other developers looking at your code. In 4D specifically, header blocks also serve as insurance against a corrupted structure file.

After repairing a corrupted structure file, orphan method(s) can appear without context. The orphan methods are not linked to the original code, and the type of method (object/form/project) and original method name are lost.

Worse, the repair process could have replaced the original code in it’s entirety with a comment “automatically repaired method“.

The method_name tag of the macro generates the text as shown in the title portion of the method editor. So a project method will render Method: Project_Method while form and object methods will render Method: Form Method [Table]Form and Method: Object Method [Table].Form.Object respectively.

A header documents all the missing information to find the original code location and the confidence to delete or restore the orphan code. Having the headers in place has definitely saved me lots of time analyzing and recovering from a structure corruption.

Example Macro

<macro name="Header">
<text>` ----------------------------------------------------
` User name (OS): <user_os/>
` Date and time: <date format="0"/>, <time format="0"/>
` ----------------------------------------------------
` Method: <method_name/>
` Description
` <caret/>
` Parameters
` ----------------------------------------------------

Note The above macro is formatted for v11. 4D v12 has // for commenting out lines

A tech tip on creating header content automatically.
4D Tech Tip – Header Macro

Read Full Post »

Generic 4D error catcher

In 4D v11/12 there is not a strict concept of try/catch blocks. Instead there is ON ERR CALL that can be used to trap errors.

A common challenge in production environments is identifying errors and reporting them without user initiative. The following code when included with the ON ERR CALL procedure is valuable in tracking down these problems.

The sample code builds a string with all the relevant error information. Additional information is added for each type of error. Here it displays an alert box, but this starting point could easily be written to database, or passed into an e-mail.

Feedback welcome, sound off in the comments, what do you do for error handling?

  ` ----------------------------------------------------
  ` User name (OS):
  ` Date and time: 
  ` ----------------------------------------------------
  ` Method: Generic_Error_Proc
  ` Description
  ` Specify with ON ERR CALL(&quot;Generic_Error_Proc&quot;) to
  ` capture and report 4D errors. This method can handle
  ` 4D Engine Errors, SQL Engine Errors and Trigger Errors
  ` It also collects some information about the execution context
  ` The diagnostic information included is intended for developers, not end users.
  ` Parameters
  ` ----------------------------------------------------
  ` 4D does not pass in params to this function instead it sets
  ` System Variables Error, Error method, and Error Line (the latter two only in v12 http://kb.4d.com/search/assetid=76150)




SQL GET LAST ERROR($errCode;$errText;$errODBC;$errSQLServer)

ARRAY INTEGER($codesArray;0)
ARRAY STRING(50;$internalCompArray;0)
ARRAY STRING(255;$textArray;0)

GET LAST ERROR STACK($codesArray;$internalCompArray;$textArray)

  ` general 4D error stack only if there is an error
If (Error#0)
	$Message:=$Message+&quot;General Message(Error Stack):&quot;+Char(Carriage return )
	$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
	$Message:=$Message+&quot;Error: &quot;+String(Error)+Char(Carriage return )
	  ` Available in 4Dv12 is Error Line and Error method process variables
	For ($i;1;Size of array($codesArray))
		  ` Do something with the element
		$Message:=$Message+String($codesArray{$i})+Char(Tab Key )+$internalCompArray{$i}+Char(Carriage return )+$textArray{$i}+Char(Carriage return )+Char(Carriage return )
	End for 
	$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
End if 

  ` return information if it is a trigger based error too
$tLevel:=Trigger level
If ($tLevel#0)
	TRIGGER PROPERTIES($tLevel;$dbEvent;$tableNum;$recordNum)
	$Message:=$Message+&quot;Trigger Message: &quot;+Char(Carriage return )
	$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
	$Message:=$Message+&quot;Table Name - Record Number: &quot;+Table name($tableNum)+&quot; - &quot;+String($recordNum)+Char(Carriage return )
	` Choose is 0 based, Database event is 1 based. so pad the results with a blank string
	$Message:=$Message+&quot;Event: &quot;+Choose($dbEvent;&quot;&quot;;&quot;Saving New&quot;;&quot;Saving Existing&quot;;&quot;Deleting&quot;)+Char(Carriage return )
	$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
	$Message:=$Message+Char(Carriage return )
End if 

  ` 4D SQL error stack (thrown by new SQL engine)
If ($errCode#0)
	$Message:=$Message+&quot;SQL MESSAGE: &quot;+Char(Carriage return )
	$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
	$Message:=$Message+&quot;Data Source: &quot;+Get current data source+Char(Carriage return )
	$Message:=$Message+&quot;Error Code: &quot;+String($errCode)+Char(Carriage return )
	$Message:=$Message+&quot;Error Text: &quot;+$errText+Char(Carriage return )
	$Message:=$Message+&quot;Error ODBC: &quot;+$errODBC+Char(Carriage return )
	$Message:=$Message+&quot;Error SQL Server: &quot;+String($errSQLServer)+Char(Carriage return )
	$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
	$Message:=$Message+Char(Carriage return )
End if 

  ` Finally lets collect some user information and timestamps
$vp_ParentTable:=Current form table
If (Not(Nil($vp_ParentTable)))
	$vl_RecordNum:=Record number($vp_ParentTable-&gt;)
	$vt_TableName:=Table name($vp_ParentTable)
End if 

$Message:=$Message+Char(Carriage return )+&quot;Client Information: &quot;+Char(Carriage return )
$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )
$Message:=$Message+&quot;Current User: &quot;+Current user+Char(Carriage return )
$Message:=$Message+&quot;Current Machine: &quot;+Current machine+Char(Carriage return )
$Message:=$Message+&quot;Current form table: &quot;+$vt_TableName+Char(Carriage return )
$Message:=$Message+&quot;Current record num: &quot;+String($vl_RecordNum)+Char(Carriage return )
  ` only in 4D V12
  ` $Message:=$Message+&quot;Error method: &quot;+Error method+Char(Carriage return )
  ` $Message:=$Message+&quot;Error line: &quot;+String(Error Line)+Char(Carriage return )
$Message:=$Message+&quot;Date:  &quot;+String(Current date(*);Internal date long )+Char(Carriage return )
$Message:=$Message+&quot;Time:  &quot;+String(Current time(*);HH MM AM PM )+Char(Carriage return )
$Message:=$Message+&quot;_____________________________________&quot;+Char(Carriage return )

  ` alert this to the user. or store it in the database in the above for loops.

Read Full Post »

Part of working with any code base is understanding what is already developed. Luckily shields help show actions associated with objects in the form editor. However, an object method shield will show for empty (‘blank’) object methods. We should clear any empty object methods to avoid confusion and optimize the code base.


Two objects on a form, both with an object method shield indicating the presence of an object method.

two objects with method shield

The last developer removed the object method content from the variable input area but did not explicitly clear the object method. This falsely indicates object method content, and worse yet 4D will execute the blank object method for each event enabled on that object.

The best approach is to clear the object method so that no shield displays, and reduce the number of lines the 4D engine executes.


Select the object to clear the method from, then from the Object drop down menu select Clear Object Method


No misleading shields and no more tracing through empty object method.
Object method cleared

Read Full Post »

This example was reproduced with 4Dv11.8 HF3. This requires 4D Server and 4D Client setup. I was unable to reproduce in a stand alone environment.

While working with SQL and 4D, sometimes records would show locked status of blank.

Standard locked dialog

LOCKED ATTRIBUTES returns -1 for the process in addition to the ‘blank’ user process and machine. Even though the documentation says this means you are trying to load a deleted record, this example has an existing record.

There have been a couple of examples on the 4D tech mailing list regarding blank locked records. The locked record appeared to manifest from the SQL engine not ‘letting’ go of the record.

In the following simplified example a SQL query updates a record in a table, which loads another record from another table in the trigger. If that record is not unloaded it remains ‘locked’. The only way I’ve found to unlock this record is to write a project method, make it available to SQL calls and load/unload this record. Regardless of how the ‘blank’ locked record manifest itself the IsRecordLocked should unlock it.


[Table_1]Field_1 (is alpha)
[Table_2]Field_2 (is alpha)

` the SQL code
Begin SQL
	UPDATE Table_1 SET Field_1='test' WHERE Field_1='a record'

` [Table_1] trigger code
READ WRITE([Table_2])
ALL RECORDS([Table_2])
LOAD RECORD([Table_2]) ` this 'locks' it for SQL server
` without an UNLOAD RECORD it will stay locked



` Using the SQL engine to LOAD and the UNLOAD the record works
Begin SQL
	SELECT {fn IsRecordLocked('Table_2','Field_2="test"') AS TEXT}
	INTO :$result


IsRecordLocked function

  ` ----------------------------------------------------
  ` User name (OS): jrowe
  ` Date and time: 05/20/11, 12:26:45
  ` ----------------------------------------------------
  ` Method: IsRecordLocked
  ` Description
  ` Unlock any record via the SQL engine
  ` Parameters
  ` ----------------------------------------------------
  `$1 table name
  `$2 where clause supports any type of where clause (executed with QUERY BY SQL)
  `$0 returns string "False" if not locked (or success), "" if error, otherwise locked information

  `Error handling
ON ERR CALL("Generic_Error_Proc")



` cycle through the tables to get a pointer
For ($tableNumber;1;Get last table number)
	If (Is table number valid($tableNumber))
		$tableName:=Table name($tableNumber)
		If ($tableName=$1)
			` now execute the where clause
			QUERY BY SQL($tablePointer->;$2)
			LOAD RECORD($tablePointer->)
			$returnText:="Read only: "+String(Read only state($tablePointer->))+" Records: "+String(Records in selection($tablePointer->))+"\n"
			  `We expect an update statment to only have one result
			If ((Locked($tablePointer->)) & (Records in selection($tablePointer->)=1))
				LOCKED ATTRIBUTES($tablePointer->;$process;$4Duser;$sessionUser;$processName)
				$returnText:=$returnText+"Process ID: "+String($process)+"\n"
				$returnText:=$returnText+"4D User: "+$4Duser+"\n"
				$returnText:=$returnText+"Session User: "+$sessionUser+"\n"
				$returnText:=$returnText+"Process Name: "+$processName+"\n"
				$returnText:="False" ` ie this is not locked, or was locked by sql engine
			End if 
			UNLOAD RECORD($tablePointer->) ` this will free the locked status
			  `lets break the loop so we don't keep processing
			$tableNumber:=Get last table number
		End if 
	End if 
End for 

  `Error handling

Source code replicating this problem available upon request.

Read Full Post »

Older Posts »

%d bloggers like this: