ELP can perform database searches based upon previously collected data. It can be used for example to extract the customer address from the data stream, search for it in the database and get the e-mail address(es) where the invoice has to be sent to.

Five steps are needed to implement the database usage:

  1. Generate the database, fill it and maybe even store it on the printers flash or hard disk
  2. Collect the searchable information from the data stream
  3. Open the database
  4. Locate the record
  5. Analyze and read the content of the found record

1. Generate your Database

ELP is a pretty much platform independent portable software. In order to maintain this approach, the manufacturer decided to implement a .dbf compatible database. Please use any software that is capable to write in .dbf (dBase3) format. For example from the free office suite LibreOffice Calc like described here. Another easy-to-use software to write/edit .dbf files is DBF Commander. The free version of this tool will be enough to maintain any databases for ELP.

The current implementation does not support indexed databases or memo fields.

In order to generate a new database, it is recommended

  1. To open the test_db.dbf file the .dbf editing software of your choice.
  2. Add / change the needed new record fields, and
  3. Store the content back under a new file name.

As ELP only interprets the first found record, therefore the performed database search should contain as exact expressions as possible.

Once a record is found, ELP searches immediately for fixed named fields and, if they are defined and contain an entry, a specific ELP action is directly activated. Like a triggered rule in the ini files.

2. Collect the searchable information from the data stream

Beside the already available variables at start up, and ELP´s possibility to read from Active Directory or external variable files, the needed information needs to be collected out of the data stream.

Currently ELP supports two ways to collect data for the first one use the new rule/section mechanism:

[Global]

Variable=#CustNo#:NotFound

SetTrigger=1:ON

[Search Item]

Search_Windows_New=Customer No:

; and if text it is found

StoreNextWordToIntVariable=#CustNo#

; the next word is read into the ELP variable #CustNo#

ReadOnlySearchKeys=ON

; speed up

or if your data stream is ASCII based and you have a page and row / column numbering, then the variable can be directly loaded.

[Search Item]

; Search_RowNo=RowNum,ColumNum,PageNum,Length,VarName

Search_RowNo=4,10,1,20,#InvoiceNumber#

 

 

3. Open the database,

This is pretty easy, simply use a second trigger if needed or pack it into the default executed rules! Rules theory, how to trigger them

[TriggerDatabase]

; when above variable was read form the data stream (Primary trigger always true)

Trigger_Binary=1

; and secondary?

Trigger_Variable=#CustNo#!notFound

; then open DB, if path is not provided the DB must be in the #ELP_FORMS_PATH#

DB_OPEN=test_db.dbf

 

One or even several databases can be opened in any activated section. The best performance is obviously; if this is done with a section activated by a trigger_.. key, as this is the last one in the processing row.

Even the open key allows to open several database files, each database is processed sequentially.

The key syntax is

DB_Open=[VolumeChar:[Path]]filename.ext;[[VolumeChar:[Path]]filename.ext[;...]]

 

After the open, ELP automatically executes the db_locate= and closes the database again.

4. Locate the requested record

is "automatically" performed on the DB_Open operation.

Again: Do NOT use the same rule as which got the Search_xx command in. For the method StoreNextWordTo[Int]Variable, the result is available later then the DB_open and DL_Locate will be performed.

DB_Locate="#CustNo#"=CUSTOMERNO

Finally if the located expression could be found in the data stream the provided record is analyzed, the default and the DB_Locate assigned actions are performed and in any case the database is automatically closed.

ELP will always interpret only the first found record!

As just explained, after a successful open, ELP reads in the same section the key db_locate. This command could have a pretty complex syntax:

DB_Locate=Expression [Operator_A Expression][...]

An expression is a comparison command of usually a database field with an ELP variable . Expressions can be combined using the .AND. and .OR. operator.

A sequence of expressions are examined from left to right. There are no parentheses allowed. This results in the rule, that if an expression is followed by the .OR. Operator_A and the expression was examined to be TRUE, the record is found.

Every expression defines 2 operands linked by an Operator

Operand_1 Operator Operand_2 [.AND. | .OR. NextExpression[...]]

The contents of the operands _1 and _2 are compared. Usually one of them reflects a name of a database field the second the ELP variable.

The data field operand defines the type comparison for the two operands. The following types are supported:

 

Bool A logical type value, can be True or False. Those expressions are treated like strings, but only with one character T,t,Y,y,J or j -> True, F,f,N or n -> False.
String A string can be up to 1024 Bytes
Numeric A numeric digit including decimal values. The decimal delimiter is a point

 

The ELP expression parser uses the first character of the Operand to detect its type:

 

Bool

Like a String separated in "" like "T" or "F"

String A string starts and ends with a " (ASCII 34). In the string itself it is illegal to use the " character
Database: Field If first character of the operand is between ASCII_A and ASCII_z.
ELP variable: Any valid ELP variable name can be used as an operand. If it is of type string the value name has to surrounded by " ".
Numeric Starts with any number from 0 to 9 or with a minus. Values between 0 and 1 have to start with 0.xxx

 


The currently supported operators:

 

< (less then) The first operand must be less than the second provided operand.
Numeric operands are compared with double precision
String operands are compared case sensitive
Bool: Not supported
> (larger then) Same as less than, if the operands would be exchanged
= (equal) Numeric: Both values must be identical
String: Case insensitive comparison until up to the length of the shorter item.
Bool: Both operand must be True or False
== exact Same like Equal, but Strings must match 1:1, case sensitive.
!= not equal Numeric: Values are not identical
String: Case sensitive exact comparison, if different -> result is TRUE.
Bool: Both arguments must be different.

 

Db_locate="#CustNo#"==CUSTOMERNO

; The content of the previous collected ELP variable #CustNo# is searched in the database in the fields CustomerNO. The compression is performed exactly

Db_locate="#CustNo#"== CUSTOMERNO .AND."#zipCode#" == ZIP

; This customer may have one customer number but several delivery addresses. This is why the out of the data stream collected ZIP code is also used in the search.

Db_locate=#CustNo# == CUSTOMERNO .AND. #zipCode# == ZIP

 

The performed search is exactly the same as in the last example, only both fields in the database are defined as numeric fields. So the string indicators around the ELP variables are erased. And the variable content needs now to begin with any digit from 0 to 9.

5. Read information from record if found:

In the first implementation of the database engine ELP checked and still does today in the found record for the following field names, which are automatically linked to an ELP action: (If one or all are not provided, nothing will happen!)

 

Field Name Description
EMAIL_ADR The syntax content of the field is analog to the key EMAILADRESS:
address1,address2,...
Any field entry will automatically enable the e-mail function. So it is a good idea to set the subject and the body text earlier, maybe even in the section global.
New addresses are checked if they are already used. Maximum up to 254 Bytes are available.
If there wasn't yet an e-mail address defined, then ELP will enable the e-mail sending function and read the additional email keys of the actual section. Like e-mail arguments, body text etc
EMAIL_CCAD Same as EMAIL_ADR but for names used in -cc carbon copy field.
COPY_FACT Replaces or adds a copy factor in the actual ELP_Command command K (default), Q or R, if the provided database entry is 1 and up. (ELP_Command)
NOPRINTING The is a field of type boolean. It could be set to TRUE -> No Printing is performed, or FALSE, then the job is printed.
Note: When a queue never prints, then set the queue port to the device NULL. So there must be then no printer assigned and powered on. See also DoNotPrintDataStream.
INARCHIVE Turn the archive function for incoming data streams on. The filed contains the complete storage directory. Defining file names is possible.
OUTARCHIVE Same like InArchive field but for outgoing data streams. If this field contains a value, the key OUTARCHIVESPLITFILES is also verified, but must be located in the ini file section.
INPORT Distributes the incoming data stream to a specified port name like \\MyServername\PrinterShareName
OUTPORT Same like InPort but for outgoing data streams. In addition this key can be used to split direct outgoing files to that port. Set key description for further details.
CALL Calls an external program right before the ELP is finished (Call any external Software)
CALLNOPRNT Calls an external program right before the ELP is finished, and no data will be printed. (Call any external Software)

 

In addition content of up to 20 fields can be loaded as variables. The ini file key for this action is DB_VARIABLE. There is an example for this with automatic PIN Codes Search in the ELP database and store the fields into ELP usable variables
Note that the database field name and the ELP variable names are separated by Double Point.

Example:

DB_Variable=PIN_FIELD:#ELP_PinVariable#

Database example

This second example searches for a customer number in the data stream and then checks the database, if the job needs to be printed or it is enough to send it out via email.

[Global]

; no specific entries

[Search Item]

Search_Windows_New=Customer No:

; and if text it is found

StoreNextWordToIntVariable=#CustNo#

; the next word is read into the ELP variable #CustNo#

ReadOnlySearchKeys= ON

; speed up

[TriggerDatabase]

; Trigger again the same term. But as the trigger examined after all bytes are read, the variable #CustNo# is available

Trigger_Windows=Customer No.:

; open the database

DB_OPEN=#ELP_FORMS_PATH#test_db.dbf

; If the database exists, search for the record with the customer number. The filed CUSTOMERNO is of type string.

DB_Locate="#CustNo#"=CUSTOMERNO

; If the record is found, the pre-defined fields are analyzed. Just in case an email needs to be created

EmailSubject=Our Invoice to your order

EmailBodyFile=#ELP_FORMS_PATH#invoice.txt

PDF_Mode=1

EmailLog=ON

 

ELP is shipped with an example database file called TEST_DB.DBF, located in the W-ELP installation directory.

 

Related articles: Get a PIN out of an ELP database to secure jobs