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
[Table_1]Field_1:=null

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.

Automatic cron backup of PostgreSQL database

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.

crontab

# 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

backup.sh

#!/bin/bash
# This script will backup the postgresql database
# and store it in a specified directory

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

# CONSTANTS
# postgres home folder backups directory
# !! DO NOT specify trailing '/' as it is included below for readability !!
BACKUP_DIRECTORY="/var/lib/pgsql/backups"

# 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" ]
then
# No database specified, do a full backup using pg_dumpall
pg_dumpall | gzip - > $BACKUP_DIRECTORY/pg_dumpall_$CURRENT_DATE.sql.gz

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

fi

Use 4D header macro to identify code blocks

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
` ----------------------------------------------------
</text>

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

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("Generic_Error_Proc") 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)

C_LONGINT($errCode;$i)
C_TEXT($errText;$vt_TableName;$errODBC)
C_LONGINT($errSQLServer;vl_Statement)

C_LONGINT($Error)
C_TEXT($Message)

C_POINTER($vp_ParentTable)
C_LONGINT($vl_RecordNum)

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+"General Message(Error Stack):"+Char(Carriage return )
	$Message:=$Message+"_____________________________________"+Char(Carriage return )
	$Message:=$Message+"Error: "+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+"_____________________________________"+Char(Carriage return )
End if 

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

  ` 4D SQL error stack (thrown by new SQL engine)
If ($errCode#0)
	$Message:=$Message+"SQL MESSAGE: "+Char(Carriage return )
	$Message:=$Message+"_____________________________________"+Char(Carriage return )
	$Message:=$Message+"Data Source: "+Get current data source+Char(Carriage return )
	$Message:=$Message+"Error Code: "+String($errCode)+Char(Carriage return )
	$Message:=$Message+"Error Text: "+$errText+Char(Carriage return )
	$Message:=$Message+"Error ODBC: "+$errODBC+Char(Carriage return )
	$Message:=$Message+"Error SQL Server: "+String($errSQLServer)+Char(Carriage return )
	$Message:=$Message+"_____________________________________"+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->)
	$vt_TableName:=Table name($vp_ParentTable)
Else 
	$vl_RecordNum:=-1
	$vt_TableName:="Nil"
End if 

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

  ` alert this to the user. or store it in the database in the above for loops.
ALERT($Message)

Clear empty object methods to avoid developer confusion

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.

Example

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

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.

Solution

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

Results

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

Real programmers use the right tool for the job

I was doing some light reading via Slashdot on why guis suck revisited. Reading the endless debate about graphical interfaces versus command line is like a political debate. Authors often frame the article to one spectrum end or another to entice response.

The myriad of examples back up a specific view point I’m going to say something totally uncontroversial: “real programmers use the right tool for the job”. This post title itself being a reference to xkcd “Real Programmers”.

Yes we all know plenty of examples where a well placed sed saved the day. We also know when popping open an application and clicking a few buttons is much easier than writing individual commands.

Really, how many people do you know who use mail or mutt as a command line interface to their mail? How many people browse the internet using links? My approach to any problem is to find the right tool to accomplish the task. Sometimes this means going straight to the command line, other times it means interacting with a program via a graphical interface.

Real programmers have an arsenal of tools to get the job done and can identify the best way out of a problem.

Java string comparison

Mostly just a ‘duh’ moment by myself.

I was fetching properties out of a database using hibernate objects. In my test scenario I asked if "true" == "true" which does work. However a String object does not always equal a string literal nicely.

public class Test {
	
	public static void main(String args[]) {
		System.out.println("String comparison");
		// BAD could be true, but could also be false
		boolean result = "true" == "true" ? true : false;
		// GOOD will actually compare the strings, not the objects
		boolean result2 = hibernate_object.getProperties().get("boolean property").equalsIgnoreCase("true") ? true : false;
		
		System.out.println(result);
		System.out.println(result2);
	}
}

String JavaDocs