Since version 7.1. ELP supports performing 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 needed to implement the database usage:
Generate the database, fill it and maybe even store it on the printers flash or hard disk
Collect the searchable information from the data stream
Open the database
Locate the record
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
To open the test_db.dbf file the .dbf editing software of your choice.
Add / change the needed new record fields, and
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:
; and if text it is found
; the next word is read into the ELP variable #CustNo#
; 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.
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
; when above variable was read form the data stream (Primary trigger always true)
; and secondary?
; then open DB, if path is not provided the DB must be in the #ELP_FORMS_PATH#
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
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.
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:
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.
; 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!)
|EMAIL_ADR||The syntax content of the field is analog to the key EMAILADRESS:
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.
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.
; no specific entries
; and if text it is found
; the next word is read into the ELP variable #CustNo#
; speed up
; Trigger again the same term. But as the trigger examined after all bytes are read, the variable #CustNo# is available
; open the database
; If the database exists, search for the record with the customer number. The filed CUSTOMERNO is of type string.
; 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
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