Free Trial   |  Purchase   |  Products   |  Downloads   |  Support     

Apollo 9 Database Engine


The Apollo Engine is included with Apollo Embedded and Apollo Server

Overview

The Apollo database engine is the underlying low-level technology that is responsible for reading, writing, and updating the DBF/Xbase database files, indexes and memo files. It is included with Apollo Embedded and Apollo Server. The database engine consists of several small .DLL files that total under 1MB in size, and which may be freely deployed with your applications. These files do not require special registration or configuration on the end-user machine.

The engine files can be placed in the same directory as your Apollo applications (C:\YourApp) or can be copied to the C:\Windows\System32 directory to be shared by all of your Apollo applications. Included as part of the Apollo database engine is a SQL query processor that provides SQL-92 support for managing your DBF/Xbase data files.

Note: Developers should not use the Apollo database engine directly. Instead, use the Apollo Embedded components to develop your applications. These components wrap the Apollo database engine.

Features

  • Native 32-bit and 64-bit support
  • Small 1MB footprint
  • Royalty free unlimited distribution
  • High-speed SQL-92 query processor
  • Included with Apollo Embedded and Apollo Server
  • Fully supported by Apollo Embedded components (i.e. no need to call engine API directly)
  • Single and multi-user support
  • Table and row level locking support
  • Database level encryption
  • Field types: Character Numeric, Logical, Date, Memo, BLOB
  • Direct low-level CRUD support (create, read, update and delete rows, tables, indexes)
  • Built-in Mach SIx Query Optimizer similar to Microsoft RushMore Technology used in FoxPro and MS-Access, allows filters and non-SQL queries to execute efficiently
  • Index Scoping lets you limit your indexed views in a flash
  • Roll-Your-Own (RYO) Queries and Indexes lets you define high-speed ad-hoc queries with no fixed conditions
  • Flip existing indexes between ascending and descending order
  • Conditional Indexing allows indexes to selectively include data
  • Low-level power using direct calls to the Apollo API functions
  • Includes Fast Text Search (FTS) support which is a search engine that provides hyper-fast searches on text-based databases similar to the Find option in the Windows help system
  • User-Defined Function (UDF) support in index/query expressions

SQL-92 Support

Apollo includes a SQL-92 compatible query processor that allows developers to access and manage DBF/Xbase database files using traditional SQL commands. Apollo provides support for CRUD operations (Create, Read, Update and Delete).

SQL Commands

SQL Command Extra Commands and Clauses
SELECT Extracts data from one or more fields from one or more tables

Aliases (AS and IN)
CASE
DISTINCT
FROM
GROUP BY [HAVING]
IS [NOT] NULL
JOIN (INNER, LEFT, LEFT OUTER)
LASTIDENTITY
LIKE
ORDER BY
WHERE

RIGHT [OUTER] JOIN and FULL [OUTER] JOIN are supported in 3.0
INSERT The INSERT statement is used to add or append records to tables.
UPDATE The UPDATE statement is used to update, modify or change data in tables.
DELETE The DELETE statement is used to remove rows in tables.
CREATE DATABASE This command is used to create new DBF/Xbase database.

COLLATE
CREATE TABLE Creates a new DBF/Xbase file.
NULL [NOT NULL]
IDENTITY
DEFAULT
CAPTION
HIDDEN
COMPRESSED
ENCRYPTED
READONLY

Constraints:
CHECK
PRIMARY KEY
FOREIGN KEY
REFERENCES
ON UPDATE [CASCADE]
ON DELETE [CASCADE]
ALTER TABLE Changes data tables
DROP COLUMN
ADD COLUMN
DROP TABLE Deletes the specified table from the database.
CREATE INDEX Creates a new index for a given table.
UNIQUE
ASC | DESC
CASE SENSITIVE
DROP INDEX Delete the specified index from the database.

Aggregate Functions

SQL Clauses Notes
AVG Returns the average of the values in a specified column or an expression.
COUNT Counts the number of rows retrieved by a SELECT statement.
MAX Calculates the largest value for a numeric column.
MIN Calculate the smallest value for a numeric column.
STDEV Computes the standard deviation.
SUM Returns the sum of all values in a column.

Arithmetic Functions

SQL Clauses Notes
ABS Returns the absolute value of the argument.
ACRTAN Calculates the arctangent of the given number.
COS Returns the cosine of the angle X, in radians.
EXP Returns the value of e raised to the power of X, where e is the base of the natural logarithms.
FRAC Returns the fractional part of the argument X.
INT Returns the Integer position of a Float value.
LN Returns the natural logarithm (Ln(e) = 1) of the float-type expression X.
MINOF Returns the lowest value of the n figures.
MAXOF Returns the highest value of the n figures.
PI Pi, approximated as 3.1415926535897932385.
POWER Raises Base to any power.
ROUND Rounds a float-type value to an integer-type value.
ROUNDDEC Rounds a float-type value to a given precision (decimals).
SIN Returns the sine of the argument.
SQR Returns the sine of the argument.
SQRT Returns the square root of the argument.
TRUNC Truncates a float-type value to an integer-type value.
TRUNCDEC Truncates a float-type value to a given precision (decimals).

Conditional Functions

SQL Clauses Notes
IF Evaluates a Boolean statement, returning one value for True and another for False.

Date and Time Functions

SQL Clauses Notes
NOW Returns the current system date in floating point format.
DAY Return the day for the given date
EXTRACT Used to get Day, Month and Year values
MONTH Return the month for the given date
YEAR Return the year for the given date
HOUR Return the hour for the given time
MIN Return the minutes for the given time
MSEC Return the milliseconds for the given time
SEC Return the seconds for the given time

String Functions

SQL Clauses Notes
COPY Returns a substring of a string.
EXTRACT Formats the series of arguments in the list of arguments
FORMAT Formats the series of arguments in the list of arguments
FORMATDATETIME Formats the date-and-time value given by DateTime using a specified format
FORMATFLOAT Formats the floating-point value given by Value using a specified format
LEFT Returns the leftmost Count characters contained in a string.
LENGTH Returns the number of characters actually used in a string.
LOWER Returns a string converted to lowercase.
POS Searches for a substring within a string.
RIGHT Returns the rightmost Count characters contained in a string.
SUBSTRING Extracts a substring from a table column or character literal
TRIM Deletes the leading or trailing character, or both, from a table column.
UPPER Returns a string converted to uppercase.

Database File Format

Apollo supports industry standard CA-Clipper, FoxPro 2.6 and Apollo DBF/Xbase database files, which include the following file extension combinations DBF/NTX/DBT, DBF/CDX/FPT, DBF/IDX/FPT, DBF/NSX/SMT. Apollo supports both legacy DOS and Windows data storage and indexing rules, which allows Apollo to support data managed by legacy CA-Clipper applications.

File types

  • CA-Clipper - DBF data, NTX indexes, DBT memo
  • CA-Clipper (hybrid) - DBF data, NSX indexes, DBT memo
  • FoxPro 2.6 - DBF data, CDX/IDX indexes, FPT memo
  • Apollo/SIX RDD - DBF data, NSX indexes, SMT memo

Field types

  • Character (String, Char, Byte)
  • Numeric (Integer, Decimal, Float, Currency types of data)
  • Logical (Boolean)
  • Date (DateTime)
  • Memo (String, Varchar, BLOB)

Specifications

Database Files Limit
Max records per table 1 billion (1)
Max characters per record 64k
Max fields per record 1022
Max tables open all tasks 250 (2)
Index Files Limit
Max characters per key (NTX) 338
Max characters per key (CDX/IDX/NSX) 240
Max length of index expression 255
Max length of conditional expression 255
Max tags in a .CDX index 255
Max tags in an .NSX index 64
Max open index files per table 249 (2)
Max open index files per task 249 (2)
Fields Limit
Max characters per field: Character 1023 (3)
Max characters per field: Numeric 20
Max characters per field: Logical 1 (4)
Max characters per field: Date 8 (4)
Max characters per field: memo ref in DBF 10 (4)
Max characters per memo in DBT/FPT/SMT 64k
Max BLOB size stored in a memo 16MB
Max characters in a field name 10
Numeric precision 16

Notes

(1) Actual file size is limited to 2GB
(2) Limited by memory and number of file handles available
(3) Up to 32K if ApolloGetRecord is used to read the entire record buffer
(4) Non adjustable field size

Architecture

Apollo database engine supports the following scenarios with applications developed using the Apollo components, including the Apollo Server since it was developed using the Apollo VCL components.

Single User - Local Access
 

Single User - File Share or LAN Data Access
 

Multiple Users - File Share or LAN Data Access
 

Download the free Apollo 30-day trial

Getting Started

Apollo is an EXCELLENT product which I use for EVERY application I write which requires a database, and nowadays that means all of them.

Apollo just got Hardcore!

I love your products. Applications I developed with Apollo over the last decade have aced the test of time, with heavy daily use. I purchased the upgrade today.

Congratulations on winning Best Database Engine Award!

Download the free Apollo 30-day trial
  Products Technologies Support Company
 
© Copyright 1999-2022 ApolloDB. All rights reserved.