EPL Embedded Database

EPL provides you a very compact & powerful database engine that is EDB (Embedded Database). It¡¯s self-contained & zero-configuration. The most functions of EDB is base on XBASE mode (DBASE/FOXBASE/FOXPRO).

EPL also provides an EDB manager that in main IDE for you to manage your database file.

All the functions¡¯ name of EDB has an ¡°edb¡± prefix. EDB functions is provided by EPL Kernel Library. For how to use EDB in details, you may query the information from instant help of this in EPL IDE.

EDB File Components

An EDB database contains three files list below

Suffix	Description
*.edb	Main file of database
*.edt	Assistant file of database, only exist when the database has ¡°Memo¡± type or ¡°Bin¡± type field. This file has the same file name but different suffix
*.enx	Index file of database. Using B+ tree structure, user can create it by user self if necessary, it¡¯s for accelerating the searching speed.

EDB Manager

EPL IDE provides various database tools for EDB.

To design database structure, you may use Structure Editor, click ¡°Database¡± -> ¡°Structure Editor¡± menu.

To manage records of database, you may use Record Editor, click ¡°Database¡± ->¡±Record Editor¡± menu.

To convert other database to EDB, you may use Format Converting, click ¡°Database¡± ->¡±Format Converting¡± menu.

To manage database password, you may use Password, click ¡°Database¡± ->¡±Password¡± menu.

Common Operation

Create Database

To create an EDB database by code, you can call ¡°edbCreate¡± function, you should specify file name to create and specify a pre-defined fields.

Fox examples, if you want to create a database with two fields, first field is Integer type and named as ¡°Num¡±, and second field is String type and named as ¡°String¡±, you can code like the picture below.

 

Open Database

To open a specified database file, you can call ¡°edbOpen¡± function. You can open multi-database in same time.

The ¡°edbOpen¡± function can set alias name of the opening database to indicate it, when multi-opened-databases exist in your application.

 

Set Current Database

In your application, there¡¯s only one Active Database as Current Database that you can operate. Most EDB operation is for operating the current database. Use ¡°edbSetCurrent¡± function to change the current EDB database of the application. Each already opened database has an alias name, use the name to set it as current database. The alias name can set to parameter of ¡°edbOpen¡± function.

 

Close Database

To close an already opened database, you can call ¡°edbClose¡± function and indicate the alias name of the database, if you don¡¯t indicate the alias name, the function will close the current database. To close all already opened databases, you can call ¡°edbCloseAll¡± function.

 

Record Pointer

Each already opened database has a record pointer to indicate current position of record accessing, when you¡¯re using functions such as ¡°edbRead¡±, ¡°edbWrite¡±, ¡°edbDelete¡± etc, the record pointer can indicate which record to operate. To change the record pointer, you can use these functions, ¡°edbMove¡±, ¡°edbMoveFirst¡±, ¡°edbMoveLast¡±, ¡°edbSkip¡±. To retrieve position where the current record pointer is, you can use ¡°edbGetRecNO¡± function.

The record pointer has a very special condition, that¡¯s the record pointer reaches the position before the first record or after the last record of the database, you can use ¡°edbBof¡± and ¡°edbEof¡± function to determine these conditions. Once the record pointer reaches those positions, you can¡¯t operate the current record any more.

Here¡¯s an example to read all the records of the database forward & backward, see picture below

 

Record Access

To read & write records, you can use these functions, ¡°edbRead¡±, ¡°edbWrite¡±, ¡°edbReadField¡±, ¡°edbWriteField¡± and ¡°edbChange¡±.

Function	Description
edbRead	Returns the content of specified field of current record in current database
edbWrite	Writes the data to the specified field in current record
edbReadField	Returns the content of specified field of current record in specifies database
edbWriteField	Writes data to the specified field of current record in specified database
edbChange	Modifies multi-fields of current record in current database

 

Add Record

To add records, you can use these functions as below

Function	Description
edbAddBlank	Adds one empty record to the end of the current database
edbAddRecord	Adds one record to the end of current database

 

The edbAddRecord function can also add records to the end of current database from other databases with specified condition, such as edbAddRecord(¡°DBOther¡±, edbGetRecNO() < 11), this statement adds all of the first 10 records of DBOther database to the end of the current database.

 

Delete Record

To delete records in EDB, you should do the following two steps: first, call ¡°edbDelete¡± to add a ¡°delete flag¡± to current record, and then call ¡°edbPack¡± to delete all the records have ¡°delete flag¡±. To cancel a ¡°delete flag¡±, call ¡°edbUndelete¡± to cancel the ¡°delete flag¡± of specified records.

Call ¡°edbIsDeleted¡± to determine whether the current record has a ¡°delete flag¡±.

Call ¡°edbClear¡± to delete all records of the current database.

 

Find Record

There are two ways to find records, that¡¯s find with index and find without index.

 

Find Without Index

Call ¡°edbFind¡± to search the records that matches the specified condition from the current records, the specified condition can be any appropriate conditional statement, you can use any legal statement in it, such as edbFind(edbRead(¡°Count¡±) > 1000 And edbRead(¡°Name¡±) = ¡°Steve¡±), this statement will find the record that the ¡°Count¡± field is greater than 1000, and the ¡°Name¡± field is ¡°Steve¡±.

 

Find With Index

To create an index for database, you have two ways, first is you specify the index in Structure Editor, and second you can call ¡°edbCreateIndex¡± function, such as

 

      edbCreateIndex(¡°DBIndex¡±, , , , ¡°Name¡±)

 

this statement creates an index of ¡°Name¡± field in DBIndex.enx file.

To open an index file, you can call the edbOpen function, and plus index file name but without suffix, such as

 

       edbOpen(¡°DB¡±, , , , , , ¡°DBIndex¡±)

 

this statement opens ¡°DB¡± database and also opens DBIndex.enx file as index.

Call ¡°edbSeek¡± to search one record by the current index in current database.

 

Function Execution Verification

Most EDB functions will return a boolean type to determine whether the function succeeds, but some functions doesn¡¯t return a value to let you determine that it succeeds or not. You can call ¡°edbGetErrorCode¡± that follow a EDB function statement to determine if the EDB function succeeds or not. If fails to execute, you can retrieve the error description by call ¡°edbGetErrorMessage¡±.

Function List

The EDB supports these functions as below

Function Name	Description
edbIsMultiUser	Returns True if this library supports multi-user database operation, otherwise returns False.
edbGetErrorCode	Returns a nonzero error code if failed to execute a database command, otherwise returns 0.
edbGetErrorMessage	Returns an error message string if failed to execute a database command, returns empty string if there's no error.
edbCreate	Creates specified database file, the file will be overwritten if the file exists. Returns True if succeeds, otherwise returns False.
edbOpen	Opens specified database file. Returns True if succeeds, and sets this database to the current database, otherwise returns False.
edbROpen	Opens specified database file. Returns True if succeeds, and sets this database the current database after automatically close current database, otherwise returns False.
edbSetCurrent	Selects an opened database as current database. If the database dose not exist or has not been opened, returns False, otherwise True.
edbGetCurrent	Returns the alias of the current database, returns the name if there's no alias. Returns empty string if no current database.
edbClose	Closes an opened database. If the database name is empty, current database is closed. ¡°edbSetCurrent¡± can be used to open the database again.
edbGetFileName	Returns the full file name of the current database. Returns empty string if the current database doesn't exist.
edbIsOpen	Returns True if the database is already opened, otherwise returns False.
edbGetAllRecordCount	Returns the numbers of records of the current database. Returns 0 if no current database or fails.
edbGetCreateTime	Returns the creation time of the current database.
edbGetFieldCount	Returns the numbers of fields of the current database.
edbGetFieldName	Returns the name of the specified field in current database. Returns empty string if the specified field doesn't exist.
edbChangeFieldName	Modifies the field name of current database. This function is only valid when the database is opened as "#DisableReadWrite". Returns True if succeeds, otherwise returns False.
edbGetFieldType	Returns the field type of current database. The data type can be one of the following constants: 1,#Byte; 2,#Short; 3,#Integer; 4,#Int64; 5,#Float; 6,#Double; 7,#Boolean; 8,#DateTime; 10,#String; 11,#Bin; 12,#Memo. Returns 0 if the specified field does not exist.
edbGetFieldSize	Returns the size of the specified field in current database. Returns 0 if the specified field doesn't exist. Note that this command returns 4 for Memo or Bin field.
edbCreateIndex	Creates and opens an index file for the current database. Returns True if succeeds, otherwise returns False. Note that we don't recommend using index if the database is very small(such as records number is less than 1000), because it will slow down upgrading speed of the database.
edbSetCurrentIndex	Specifies the current index for the current database, this index will be used when use "SeekIndex". Returns False if the specified index doesn't exist.
edbGetCurrentIndex	Returns the name of the current index in current database. Returns empty string if no current index or haven't set the current index yet.
edbUpdateIndex	Updates the current index of current database through full recreation. Returns True if succeeds, otherwise returns False.
edbGetIndexCount	Returns the number of the indexes has been opened in current database.
edbGetIndexName	Returns the name of specified index in current database. Returns empty string if the specified index doesn't exist.
edbGetIndexField	Returns the names of all the indexed field in an array. Returns empty array if there's no index in the specified position .
edbSetLockRetryInterval	Sets the time interval for retrying when the position is locked or used by other users when locking the database. The default value is 60 seconds.
edbLock	Locks the entire current database, and deny any other users to access this database. Returns True if succeeds, otherwise returns False.
edbUnlock	Unlocks the database locked by "edbLock". Returns True if succeeds, otherwise returns False.
edbLockUpdate	Locks current database exclusively. After this lock, other users can read and write existing records but can not add or delete. Note: "Delete" function only adds a delete mask, so it is not in the range of lock. Returns True if succeeds, otherwise returns False.
edbUnlockUpdate	Unlocks the database locked by "LockCount". Returns True if succeeds, otherwise returns False.
edbLockRecord	Locks one or more records. Returns True if succeeds, otherwise returns False.
edbUnlockRecord	Unlocks the record locked by "LockRecord". Returns True if succeeds, otherwise returns False.
edbUnlockAll	Unlocks all lock such as "edbLock", "edbLockUpdate" and "edbLockRecord". Returns True if succeeds, otherwise returns False.
edbAverage	Returns the average value of an numerical expressing in current database, this function will not change the current record pointer.
edbSum	Returns the summation of an numerical expression in current database, this function will not change the current record pointer.
edbMaxValue	Returns the maximal value of an numerical expression in current database, the current record pointer will move to the record of the maximal value.
edbMinValue	Returns the minimal value of an numerical in current database, the current record pointer will move to the record of the minimal value
edbGetMaxTime	Returns the maximal value of an DateTime expression in current database, the current record pointer will move to the record of the maximal value.
edbGetMinTime	Returns the minimal value of an DateTime expression in current database, the current record pointer will move to the record of the minimal value.
edbCount	Returns the records count of the specified bound in current database, this function will not change the current record pointer.
edbCopyStruct	Copies the database structure from current database to another database. This function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbCopyRecord	Copies records from the current database to another database. This function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbCalcSort	Sorts the records of current database by specified numerical expression and copies the result to another database. This function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbSort	Sorts the records of current database by specified field and copies the result to another database. This function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbTotal	Calculates in current database and copies the result to another database. Note that: all the Memo and Bin field will not be copied. This function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbAppendFrom	Appends the records of the specified database to current database. This function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbAddRecord	Adds one record to the end of current database, and moves the current record pointer to the new record. Returns True if succeeds, otherwise returns False.
edbAddBlank	Adds one empty record to the end of the current database, the record pointer will move to the new record. Returns True if succeeds, otherwise returns False.
edbReplace	Replaces the records of the current database, this function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbChange	Add "Delete" flag to the current record or the records in the specified bound, this function will not change the current record pointer. Note that: this function does not actually delete the physical records but add "delete" flag to records. Returns True if succeeds, otherwise returns False.
edbIsDeleted	Returns True if current record has a delete flag, otherwise returns False.
edbUndelete	Removes the "delete" flag of the current record or records in the specified bound, this function will not change the current record pointer. Returns True if succeeds, otherwise returns False.
edbPack	Deletes all the records that has the "delete" flag in the database, the record pointer will be moved to the first record. The records doesn't exist any more and can't be recovered after the deleting. This is a little slow because it rebuilds the database records. Returns True if succeeds, otherwise returns False.
edbClear	Deletes all the records in current database. Returns True if succeeds, otherwise returns False.
edbRead	Returns the content of specified field of current record in current database. The type of return value is identical to the actual type of the field. If the field does not exist, run-time error occurs. "GetErrorCode" can be called to determine whether the operation is successful.
edbWrite	Writes the data to the specified field in current record. Returns True if succeeds, otherwise returns False.
edbReadField	Returns the content of specified field of current record in specifies database. The type of return value is identical to the actual type of the field. If the field does not exist, run-time error occurs. "GetErrorCode" can be called to determine whether the operation is successful.
edbWriteField	Writes data to the specified field of current record in specified database. Returns True if succeeds, otherwise returns False.
edbAppendBin	Appends the bin data to the end of the specified Bin field of the current record. Returns True if succeeds, otherwise returns False.
edbAppendMemo	Appends Memo(String Type) to the end of specified Memo field of the current record. Returns True if succeeds, otherwise returns False.
edbSeek	Searches one record by the current index in current database, the searching is started from current record position. Note that the count of searching parameters must be identical to the number of fields indexed. If the record is found, the record pointer is moved to the record that found and returns True. If doesn't find or fails, the record pointer will remain unchanged.
edbFind	Searches the record that matches the specified condition from the current record. If a record is found, True is returned and the current record pointer is moved to the found record. If fails or no record found, the record pointer remains unchanged.
edbMoveFirst	Moves the current record pointer to the first record of the current database.
edbMoveLast	Moves the current record pointer to the last record of the current database.
edbSkip	Moves the current record pointer forward or backward to skip several records. Returns True if succeeds, otherwise returns False.
edbGetRecNO	Returns the current RecNo(record number) of current database. Returns 0 if the record pointer is at the front of the first record; returns record count plus 1 if the record pointer is behind the last record.
edbMove	Moves the current record pointer to the specified record in current database. Returns True if succeeds, otherwise returns False.
edbGetBookmark	Returns a non-zero unique integer label of a record. Returns 0 if fails.
edbIsRecordExist	Returns True if a record specified by its non-zero unique integer label exist, otherwise returns False.
edbGotoBookmark	Moves the current record pointer to the record that has the specified non-zero unique integer label. Returns True if the record has been found and the record pointer has been moved successfully. Returns False if fails or the record does not exist; in this case, the record pointer remains unchanged.
edbBof	Returns True if the current record pointer is before the first record; otherwise returns False.
edbEof	Returns True if the current record pointer is after the last record, otherwise returns False.
edbFlush	All the writing operation to database is saved in memory cache first and then write to database file automatically for improving the performance. This function can force the system write data to database file immediately. Normally, there is no need to call this function.
edbFlushAll	Writes all the cached data to database file immediately. Normally, there is no need to call this function.
edbEdit	Displays a dialog to view and edit the records in current database. The starting position is the current record. The record pointer is moved to the first record when this function returns.
edbIsEncrypted	Returns True if need password to access the database file (indicate whether the database has been encrypted), otherwise returns False.
edbSetPassword	Sets or changes the password for accessing the database, Note: the database must can write. Returns True if succeeds, otherwise returns False.
edbInputPassword	Inputs the password of a database. Returns True if password was input successfully, otherwise returns False.
edbCopyPassword	Copies database password from one database to current database. Note: the program must can write the database file. Returns True if succeeds, otherwise returns False.