Archive for the ‘Databases’ 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 »

There are lots of great backup tools and utilities out there, but utilizing a simple script and cron task to target specific PostgreSQL database is often the fastest way to a locally based backup procedure.

The below shell script is run as the postgres user on a Linux version 2.6.9-42.0.3.ELsmp (Red Hat 3.4.6-3) with PostgreSQL 8.2.0 that has support for command-line execution. Not that there is anything fancy in the code that would require such specific versions.

Unique to the code is the ability to pass in a database name, or none at all to backup the entire cluster. This code only connects and writes files locally. Assumes appropriate permissions given to executing user. Maybe this works just fine for you, otherwise feel free to make your own modifications.

Personally I gzip the output, but improvements could be made to prevent hard disk saturation on larger databases and archiving utilities.


# CRON table for postgres user.

# run backup every night at 22:00 hours (10PM)
0 22 * * * /var/lib/pgsql/backups/backup.sh database_name

# run backup every week at midnight hour on sunday
0 0 * * 0 /var/lib/pgsql/backups/backup.sh


# This script will backup the postgresql database
# and store it in a specified directory

# $1 database name (if none specified run pg_dumpall)

# postgres home folder backups directory
# !! DO NOT specify trailing '/' as it is included below for readability !!

# Date stamp (formated YYYYMMDD)
# just used in file name
CURRENT_DATE=$(date "+%Y%m%d")

# !!! Important pg_dump command does not export users/groups tables
# still need to maintain a pg_dumpall for full disaster recovery !!!

# this checks to see if the first command line argument is null
if [ -z "$1" ]
# No database specified, do a full backup using pg_dumpall
pg_dumpall | gzip - > $BACKUP_DIRECTORY/pg_dumpall_$CURRENT_DATE.sql.gz

# Database named (command line argument) use pg_dump for targed backup
pg_dump $1 | gzip - > $BACKUP_DIRECTORY/$1_$CURRENT_DATE.sql.gz


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 »

Older Posts »

%d bloggers like this: