Tech Tips — The 4 Tips of April are Here!

by Add Komoncharoensiri, Director of Technical Services at 4D Inc

 

As a 4D developer, you have to keep up with the new advances 4D is continuously shipping. Besides tips and tricks provided by the product team, Tech Tips are another reference for learning some of the 4D concepts.

This article covers 4 tips:

• How to treat SQL wildcard characters as a literal string
• Programmatically get the project name
• Preemptive methods under the same call chain should also be preemptive
• Creating a new View Pro document based on a Sheet

How to Treat SQL Wildcard Characters as Literal String?

Unlike 4D, which has only one wildcard character (“@”), SQL has multiple wildcard characters (eg. “%”, “?”, “_”, “#”, etc). SQL uses wildcard characters to substitute one or more characters in a string. See here for a list of SQL wildcard characters. SQL wildcards are predominantly used with the LIKE operator and WHERE clause to search for a specified pattern in a column.

When querying for strings that contain SQL wildcard characters using the WHERE/LIKE operator in 4D SQL, the wildcard characters must be escaped to be treated as a literal characters. The ESCAPE clause is supported in the LIKE operator to indicate the escape character.

For example, say the data to query contains “Customer_1”, “Customer_11”, “Employee_1”, and “Employee_11”. The goal is to query for all strings that end with “_1”.

Since the underscore (“_”) is one of the wildcard characters in SQL, it must be escaped to be treated as a literal character. This can be written as such.

ARRAY TEXT($results; 0)
Begin SQL
   SELECT ID from Table_1
   WHERE Field_2 LIKE '%\_1' ESCAPE '\'
   INTO : $results
End SQL

A backslash character (“\”) is placed in front of the underscore (“_”), and then the backslash is defined as the escape character. The escape character makes SQL treat the following character as a literal character.

Programmatically get the project name.

When working in project mode, sometimes the name of the project application is needed. To get the project name programmatically, use the Folder command with the fk database folder constant. The property “name” of the returned folder object is the project’s name. For example, given that a project is created with the name “example project,” in the following line:

$projectName:=Folder(fk database folder).name

$projectName will return “example project” in the project application.

Preemptive methods under the same call chain should also be preemptive

All methods within that same call chain must also be preemptive / thread-safe when working with a preemptive method. This includes typical project methods and methods within class methods like the collection.sort() where the 1st argument would be a method name.

In addition, thread-safe methods will include conditions such as:

• Must have “Can be run in preemptive processes” or “indifferent” property enabled
• Must not include thread-unsafe plugins
• Must not use any interprocess variables
• Must not call interface related commands (e.g., DIALOG)

 

Creating a new VP document based on a Sheet

It is possible to create a new 4D View Pro document based on a specific sheet of another 4D View Pro document.

Below is a utility method that will take the View Pro area’s name as the first parameter and the desired sheet index number to create a new document only containing the sheet as an object:

// VP_Extract_Sheet
#DECLARE($vpAreaName_t : Text; \
$sheetIdx_l : Integer)\
->$newVP_ob : Object

var $srcVP_ob : Object

$srcVP_ob:=VP Export to object($vpAreaName_t)

$newVP_ob:=OB Copy($srcVP_ob)

$newVP_ob.spreadJS.sheets:=New object( \
VP Get sheet name($vpAreaName_t; $sheetIdx_l); \
$newVP_ob.spreadJS.sheets[VP Get sheet name($vpAreaName_t; $sheetIdx_l)])

$newVP_ob.spreadJS.activeSheetIndex:=0
$newVP_ob.spreadJS.sheetCount:=1

This will allow specific sheets from a VP document or any document imported into a VP area to be extracted as its own VP document.

Wrap up 

There you have it, our 4 tips and tricks for April! I hope you learned something new and will apply these in your daily coding.

If you liked the tips above, you’d find more in the 4D Knowledge Base, a library of information about the 4D technology where weekly tech tips and monthly technotes are constantly published. It has been helping customers find answers to solve their problems for years and will continue to do so in the years to come.