Sign in Join
Wiki copytemporary

ZMSQL

About

ZMSQL is an open source and free, TBufDataset SQL enhanced in-memory database for Free Pascal (FPC), operating with semicolon-separated values flat text tables as a persistence store.

This means you can use files to store your data but get the speed of in-memory processing. Completely written in Pascal, it has no dependencies on external libraries.

ZMSQL package consists of three components:

1. TZMConnection - describes "database" as folder containing CSV files, and some common properties for all datasets.

2. TZMQueryDataset - an in-memory dataset (TBufDataset descendent) capable of loading/saving from CSV files, loading/executing SQL queries (via integrated JanSQL database engine) and loading data from any other datasets. ZMQueryDatasets also have integrated properties for easy setting master/detail filtration.

3. TZMReferentialKey - component that describes relation between master and slave dataset for referential integrity (insert, update, delete).

4. TZMQueryBuilder - visual query builder.

ZMSQL offers:

The download contains the source code, some demo applications illustrating the features of the component as well as a readme.

Author

- Zlatko Matić (matalab@gmail.com); includes code from Jan Verhoeven and Martin Waldenburg.

Contact Zlatko Matić ("tatamata" in Lazarus forum) by e-mail: matic.zlatko@gmail.com or matalab@gmail.com

Contributors:

License

Modified LGPL and MPL 1.1 license: static and dynamic linking both allowed, even in commercial applications. Please see the individual licenses in the source files for details.

Contains:

Download

The latest stable release can be found on http://sourceforge.net/projects/lazarus-ccr/files/zmsql/.

Published and Public properties and methods of TZMQueryDataset

pascal
  public
    { Public declarations }
    procedure QueryExecute; //Executes SQL query defined in SQL property, on .csv files that are placed in folder defined in ZMConnection property. Resultset of select query is loaded into the the zmquerydataset (self).
    procedure PrepareQuery; //Prepares parameterized queries for execution: replaces parameters with parameter values for parameterized queries.
    procedure EmptyDataSet; //Deletes all records from dataset.
    procedure ClearDataSet; //Deletes records, fields and fielddefs.
    procedure CopyFromDataset (pDataset:TDataSet); //Copies schema and data from any TDataset.
    function SortDataset (const pFieldName:String):Boolean; //Ascending/Descending sorting of memory dataset.
    procedure LoadFromTable; //Loads data (or data and schema) from a .csv file (TableName.csv), set in property TableName, from path specified in ZMConnection property.
    procedure SaveToTable;overload; //Saves data and schema to a .csv file (TableName.csv), set in Tablename property, in path specified in ZMConnection property.
    procedure SaveToTable(pDecimaSeparator:Char);overload; //Saves data and schema to a .csv file (TableName.csv), set in Tablename property, in path specified in ZMConnection property.
    procedure CreateDynamicFieldsFromFieldDefs; // Creates fields from predefined fielddefs. To be used in design-time or run-time for memory dataset creation according to predefined fielddefs.
    procedure CreatePersistentFieldsFromFieldDefs; // Creates PERSISTENT fields from predefined fielddefs. To be used in design-time only.
    procedure MemoryDataSetOpen; //Executes CreateDynamicFieldsFromFieldDefs and set dataset to Active.
    constructor Create(AOwner: TComponent); override;
    destructor  Destroy; override;
    //Properties needed for master/detail and referential integrity
    property MasterDataSetTo:TList read FMasterDataSetTo write SetMasterDataSetTo; // Defines master/detail relationship for purpose of master/detail filtration
    property MasterReferentialKeys:TList read FMasterReferentialKeys write SetMasterReferentialKeys;//Defines master/detail relationship for purpose of referential integrity
    property SlaveReferentialKeys:TList read FSlaveReferentialKeys write SetSlaveReferentialKeys; //Defines master/detail relationship for purpose of referential integrity
    property DisableMasterDetailFiltration:Boolean read FDisableMasterDetailFiltration write SetDisableMasterDetailFiltration; //Master/detail filrtation should be temporarily desabled during bulk inserts or updates...
    property OldRecord:TZMBufDataSet {TBufDataSet} read FOldRecord; //Last delete/insert/edit is preserved in this property.
  published
    { Published declarations }
    property ZMConnection:TZMConnection read FZMConnection write SetConnection; //Defines "database" folder path where .csv tables are placed. Instantiates JanSQL database engine.
    property SQL:TStrings read FSQL write SetSQL; //Unprepared SQL query text.
    property QueryExecuted:Boolean read FQueryExecuted write SetQueryExecuted;  //"True" executes QueryExecute and loads resultset into dataset.
    property TableName:String read FTableName write SetTableName; //Name of .csv file (without extension) from which is data loaded by LoadFromTable and to which is data and schema saved by SaveToTable.
    property TableLoaded:Boolean read FTableLoaded write SetTableLoaded; //"True" executes LoadFromTable and loads resultset into dataset.
    property TableSaved:Boolean read FTableSaved write SetTableSaved; //"True" executes SaveToTable and saves dataset to .csv file defined in TableName property, placed in folder specified by ZMConnection property.
    property DynamicFieldsCreated:Boolean read FDynamicFieldsCreated write SetDynamicFieldsCreated; //"True" executes CreateDynamicFieldsFromFieldDefs, which creates fields from predefined fielddefs.
    property PeristentFieldsCreated:Boolean read FPersistentFieldsCreated write SetPersistentFieldsCreated; //"True" executes CreatePersistentFieldsFromFieldDefs, which creates PERSISTENT fields from predefined fielddefs.
    property MemoryDataSetOpened:Boolean read FMemoryDataSetOpened write SetMemoryDataSetOpened; //"True" executes CreateDynamicFieldsFromFieldDefs and activates dataset for editing.
    property PersistentSave:Boolean read FPersistentSave write SetPersistentSave; //If "True", insert/delete/edit will immediately be written to underlying .csv file. If "False", then dataset is only in-memory.
    property Parameters: TParams read FParameters write SetParameters; //Parameters for parameterized SQL text.
    //Master/detail filtration
    property MasterFields: TStrings read FMasterFields write SetMasterFields; //Fields in masterdatasource, (separated by ";") to be used for master/detail filtration.
    property MasterSource: TDataSource read FMasterSource  write SetMasterSource;//Master datasource for master/detail filtration.

JanSQL SQL language

janSQL database engine supports only a subset of standard SQL but the supported statements are sufficient for single-user desktop application.

Table updates

janSQL loads tables automatically into memory when needed by a query. Any changes to tables (INSERT, UPDATE, DELETE) are performed in memory. Tables are saved to disk when you use the COMMIT statement. The only exceptions to this are the CREATE TABLE statement, where the new table is saved to disk immediately and the DROP TABLE statement, where the table is immediately deleted from both memory and disk.

Indexes

janSQL does not use indexes. You will find that for single-user desktop applications running in memory there is no urgent need for indexes.

Case sensitivity

janSQL is case-insensitive for its keywords: you can use both SELECT and select.

Non-standard

janSQL has several non-standard SQL statements for manipulation of recordsets.

Compound Queries

You can execute multiple queries in a batch. Query expressions in a compound query must be separated by semi-colon. By using the non-standard ASSIGN TO statement you can store the result of a select query as a named variable that can be used in subsequent queries. This resembles SQL stored procedures in other database systems.

SQL syntax

A) Data Definition Language

CONNECT TO

Connects to a database. In janSQL a database is a folder. Tables are stored in this folder as delimited text files with the .txt extension. Syntax: CONNECT TO 'absolute-folder-path'

Example:

text
    connect to 'G:'

Notes

This is always the first statement that you use with janSQL. All other SQL statements require that the engine knows which folder to use.

CREATE TABLE

Creates a new table in the current catalog.

Syntax:

text
CREATE TABLE tablename (field1,[fieldN])

Example:

text
CREATE TABLE users (userid,username,accountname, accountpassword)

Notes

janSQL does not use fieldtypes. Everything is stored as text. Internally janSQL treats all data as variants. This means that in your SQL queries you can use fields pretty much the way you want to.

DROP TABLE

Drops a table from the database.

Syntax:

text
DROP TABLE tablename

Syntax:

text
DROP TABLE users

Notes

Use with care, because it deletes a file from hard disk.

ALTER TABLE

Allows you to alter the structure of a table.

Syntax:

text
ALTER TABLE ADD COLUMN columnname
ALTER TABLE DROP COLUMN columnname

You can only add or drop one column at the time.

B) Data Manipulation Language

SELECT FROM

Allows you to select data from one or two tables.

Syntax:

text
 SELECT fieldlist FROM tablename
 SELECT fieldlist FROM tablename WHERE condition
 SELECT fieldlist FROM tablename1 [alias1], tablenameN [aliasN]
 SELECT fieldlist FROM tablename1 [alias1], tablenameN [aliasN] WHERE condition

fieldlist can be * for selecting all fields or field1[,fieldN]

field: fieldname [AS fieldalias]

condition: see the WHERE topic.

Notes

When you join two or more tables you must use fully qualified field names: tablename.fieldname in the WHERE clause. Both tablenames and fieldnames can be aliased.

text
SELECT u.userid as mio, u.username as ma, p.productname as muu
FROM users u,products p
WHERE u.productid=p.productid

Using a table alias can save you typing.

text
    select products.productname as product,count(users.userid) as quantity
    from users,products
    where users.productid=products.productid
    group by product
    having quantity>10
    order by product desc

The example above shows you that in the WHERE clause you refer to source tables (e.g. products.productid) where as in the GROUP BY, HAVING and ORDER BY clause, you refer to the result table.

Always use an aliased field name when using an aggregate function:

text
count(users.userid) as quantity

WHERE

The WHERE clause can be used together with the SELECT, UPDATE and DELETE clauses.

Syntax:

text
WHERE condition

condition:

The condition is an expression that must evaluate to a boolean true or false. The following operators are allowed:

Arithmetic:

+ - * / ( )

Logic:

and, or

comparison: < <= = > >=

string constants: e.g. 'Jan Verhoeven'

numeric constans: e.g. 12.45

fieldnames e.g. userid, users.userid

IMPORTANT NOTE: Sometimes JanSQL engine can't process WHERE clause if there are several conditions and joins. In that case application will freeze. Therefore, try to simplify WHERE clause of the main expression by filtering one of the joined tables prior the main expression. Split the main query into few steps (use subqueries!). This means you have to split the main expression into, for example, two expressions and assign the first one to a variable (ASSIGN TO) that you will use instead of the original table.

IN

e.g.

text
    userid IN (300,401,402)
    username IN ('Verhoeven','Smith')

LIKE

e.g.

text
     username Like '%Verhoeven'

You can use the % character as a placeholder to match any series of characters:

pascal
     '%Verhoeven' will match Verhoeven at the end of username
    'Verhoeven%' will match Verhoeven at the beginning of username
     '%Verhoeven%' will match Verhoeven anywhere in username

Sub queries

You can use a subquery after the IN clause. Only non-correlated sub queries are allowed at the moment. A sub query must select a single field from a table. A sub query is executed at parsing time and returns a comma seperated list of values that replaces the query text in the IN clause. A sub query must be enclosed by brackets. Example: select * from users where productid in (select product id from products where productname like 'Ico%')

Notes When using a SELECT with a join between 2 tables you must use fully qualified names (tablename.fieldname) in every part of the query. In all other cases (UPDATE, INSERT) you must use the short form fieldname without the tablename. Repeat it once again: if more than one table joined in sql query, you must use tablename.fieldname; if only one table in a query, you must use fieldname without table name (only fieldname).

GROUP BY

Allows you to group data according grouping fields. Syntax:

text
    group by fieldlist

fieldlist is a comma seperated list of one or more fields that you want to grouping to be applied. Example:

text
    select count(userid), username, productid
    from users
    group by productid
    order by productid

Aggregate functions

You can apply the count, sum, avg, max, min, stddev function to an input field. When you use these functions without a GROUP BY clause, the resultset will contain only one row.

HAVING

Allows you to filter a recordset resulting from a GROUP BY clause. Syntax:

text
    HAVING expression

Example:

text
    select count(userid), username, productid
    from users
    group by productid
    having userid>10
    order by productid

Notes

Experienced SQL users will notice that janSQL uses a non-standard syntax in the HAVING clause. Instead of the standard having count(userid)>10, in janSQL you just use the name of the base table field, in this case userid.

You should be aware of the difference between the WHERE clause and the HAVING clause. The WHERE clause is applied to table(s) in the FROM clause. The HAVING is applied after filtering with where and grouping with group by have been applied. The same applies to the ORDER BY clause which is also applied to the final result set.

ORDER BY

Allows you to sort the resulting recordsets. Syntax:

text
    ORDER BY orderlist

Example:

text
    select * from users order by #userid asc, productid desc

orderlist is a comma seperated list of one or more order by components: component1[,componentN] ordercomponent: [#]fieldname [ASC|DESC]

By placing the optional # before a fieldname it will be treated as a numeric field in the sort. Remember that in janSQL all data is stored as text. After the fieldname you can optionally put ASC for an ascending sort, or DESC for a descending sort. When you omit the sort direction the default ascending sort order is used.

ASSIGN TO

Allows you to assign the result of a SELECT statement to a named recordset that can be referred to in subsequent statements. This is a non-standard SQL statement. ASSIGN TO is like a variable assignment. You can create very complex compound queries with ASSIGN TO.

Syntax:

text
    ASSIGN TO tablename selectstatement

Example:

text
    ASSIGN TO mis SELECT userid, username FROM users

If tablename allready exists in the catalog then an error occurs. When tablename does not exist in the catalog but was allready assigned to then the existing recordset is overwritten.

Notes Make sure that you use output field alias names when you ASSIGN TO using a SELECT with joined tables.

When you execute the ASSIGN TO the given name will be assigned to the new recordset and the recordset itself will not be released until you use RELEASE TABLE.

RELEASE TABLE

Allows you to release any open table from memory, including intermediate tables created with ASIGN TO. This is a non-standard SQL statement. Syntax:

text
    RELEASE TABLE tablename

Example:

text
    ASSIGN TO mis SELECT * FROM users
    RELEASE TABLE mis

SAVE TABLE

Allows you to save any open table, including intermediate tables, to a file. This is a non-standard SQL statement. Syntax:

text
    SAVE TABLE tablename

When tablename is not open, an error occurs. When you save an intermediate table (created with ASSIGN TO), the intermediate table becomes a persistant table that is also saved with the COMMIT statement.

Example: ASSIGN TO mis SELECT * FROM users

text
    SAVE TABLE mis

Notes Once you have saved an intermediate table with TABLE SAVE you can not ASSIGN TO anymore.

INSERT INTO

Allows you to insert data in a table, either row by row or from a recordset resulting from a SELECT. Syntax:

text
     INSERT INTO tablename [(column1[,column])] VALUES (field1[,fieldN])
     INSERT INTO tablename selectstatement

Example:

text
     INSERT INTO users VALUES (600,'user-600');
     INSERT INTO users (userid,username) VALUES (601,'user-601');
     INSERT INTO users SELECT * FROM users WHERE userid>400

Notes When you insert records using a sub select you must make sure that the output fields of the sub select match the fieldnames of tablename. Only values of matching field will be inserted.

UPDATE

Allows you to update existing data. Syntax:

text
    UPDATE tablename SET updatelist [WHERE condition]

updatelist: field1=value1[,fieldN=valueN]

condition: see WHERE for the optional condition

DELETE FROM

Allows you to delete data. Syntax:

text
    DELETE FROM tablename WHERE condition

condition: see WHERE clause for the condition.

C) Functions

In janSQL you can use functions wherever you can use an expression to be calculated (Calculated output fields, WHERE clause, HAVING clause).

Use extra brackets around function parameters when you have a function with more than one parameter:

text
 SELECT trunc((userid/7),2) as foo FROM users

and not:

text
 SELECT trunc(userid/7,2) as foo FROM users

Conversion

fix(expression,precision)

Returns the string presentation of (numeric) expression with precision number of decimals.

text
 select fix((userid/7), 2) as bobo from users order by bobo

You can also use TRUNC i.s.o. FIX.

asnumber(expression)

Returns (number or string) expression as number. If expression is not a valid floating point number then the function returns 0.

FORMAT function

Formats a integer or floating point value to a string in a way specified by a format string. Syntax:

text
    format(value,formatstring)

Example:

text
    update users set userid=format(userid,'%.8d')

Format strings have the following form: [literalstring]"%" [width] ["." prec] type

• An optional literal string that is copied to the output • An optional width specifier, [width] • An optional precision specifier, ["." prec] • The conversion type character, type

The following table summarizes the possible values for type:

d

Decimal. The argument must be an integer value. The value is converted to a string of decimal digits. If the format string contains a precision specifier, it indicates that the resulting string must contain at least the specified number of digits; if the value has less digits, the resulting string is left-padded with zeros.

u

Unsigned decimal. Similar to 'd' but no sign is output.

e

Scientific. The argument must be a floating-point value. The value is converted to a string of the form "-d.ddd...E+ddd". The resulting string starts with a minus sign if the number is negative. One digit always precedes the decimal point.The total number of digits in the resulting string (including the one before the decimal point) is given by the precision specifier in the format string—a default precision of 15 is assumed if no precision specifier is present. The "E" exponent character in the resulting string is always followed by a plus or minus sign and at least three digits.

f

Fixed. The argument must be a floating-point value. The value is converted to a string of the form "-ddd.ddd...". The resulting string starts with a minus sign if the number is negative.The number of digits after the decimal point is given by the precision specifier in the format string—a default of 2 decimal digits is assumed if no precision specifier is present.

g

General. The argument must be a floating-point value. The value is converted to the shortest possible decimal string using fixed or scientific format. The number of significant digits in the resulting string is given by the precision specifier in the format string—a default precision of 15 is assumed if no precision specifier is present.Trailing zeros are removed from the resulting string, and a decimal point appears only if necessary. The resulting string uses fixed point format if the number of digits to the left of the decimal point in the value is less than or equal to the specified precision, and if the value is greater than or equal to 0.00001. Otherwise the resulting string uses scientific format.

n

Number. The argument must be a floating-point value. The value is converted to a string of the form "-d,ddd,ddd.ddd...". The "n" format corresponds to the "f" format, except that the resulting string contains thousand separators.

m

Money. The argument must be a floating-point value. The value is converted to a string that represents a currency amount. The conversion is controlled by the CurrencyString, CurrencyFormat, NegCurrFormat, ThousandSeparator, DecimalSeparator, and CurrencyDecimals global variables, all of which are initialized from the Currency Format in the International section of the Windows Control Panel. If the format string contains a precision specifier, it overrides the value given by the CurrencyDecimals global variable.

s

String. The argument must be a string value. The string is inserted in place of the format specifier. The precision specifier, if present in the format string, specifies the maximum length of the resulting string. If the argument is a string that is longer than this maximum, the string is truncated.

x

Hexadecimal. The argument must be an integer value. The value is converted to a string of hexadecimal digits. If the format string contains a precision specifier, it indicates that the resulting string must contain at least the specified number of digits; if the value has fewer digits, the resulting string is left-padded with zeros.

text
Date functions

Several date functions make working with date strings easier.

YEAR

Extracts the integer year part of a yyyy-mm-dd date string.

MONTH

Extracts the integer month part of a yyyy-mm-dd date string.

DAY

Extracts the integer day part of a yyyy-mm-dd date string.

WEEKNUMBER

Returns the integer weeknumber of a yyyy-mm-dd date string.

EASTER

Returns the easter yyyy-mm-dd date string of a given integer year.

DATEADD

Adds a given number of time intervals to a given data and returns the resulting data as a yyyy-mm-dd data string. Syntax:

text
     DATEADD(interval,number,datestring)

Interval can be: 'd' (day), 'm' (month), 'y' (year), 'w' (week), 'q' (quarter). Number must be an integer number. datestring must be in the yyyy-mm-dd format.

text
String Functions

janSQL comes with a range of functions that work on strings.

soundex(expression) Calculates the soundex value of (string) expression. Only usefull with english terms.

lower(expression) Converts (string) expression to lower case.

upper(expression) Converts (string) expression to upper case.

trim(expression) Trims (string) expression from leading and trailing spaces.

left(expression,count) Returns the first count characters of expression

right(expression,count) Returns the last count characters of expression

mid(expression,from,count) Returns count characters of expression starting at from.

length(expression) Returns the length of (string) expression. Can be used to e.g. select fields that exceed a given length.

replace(source,oldpattern,newpattern) Replaces oldpattern with new pattern in the source string. Is case-insensitive.

text
    UPDATE users SET username=replace(username,'user-','foo-')

substr_after(source,substring) Returns the part of source that comes after substring. If substring is not found an empty string is returned.

substr_before(source,substring) Returns the part of source that comes before substring. If substring is not found an empty string is returned.

text
Numeric Functions

Numeric functions work on strings as if they were numbers. Although janSQL is based on strings you can still enter values like 1234, which can be treated like numbers.

sqr(expression) Calculates the square of (numeric) expression.

sqrt(expression) Calculates the square root of (numeric) expression.

sin(expression) Calculates the sin of (numeric) expression.

cos(expression) Calculates the cos of (numeric) expression.

ceil(expression) Returns the lowest integer greater than or equal to (numeric) expression.

floor(expression) Returns the the highest integer less than or equal to (numeric) expression.

Dependencies / System Requirements

Status: Alpha

Open issues/improvement ideas

Below is a list of open issues (or bugs, if you want) and improvement ideas. If you have any patches/fixes for the code (not limited to these issues), please let use know via the ZMSQL thread on the forum: 1 Of course, ZMSQL is part of the Lazarus CCR repository, so you can post bug reports/patches on Mantis using the CCR category.

1. TBufDataset bug Currently TBufDataSet (ancestor of TZMQueryDataSet) has a bug (http://bugs.freepascal.org/view.php?id=19631 is solved!) that causes Refresh method to delete all records

2. Slow query execution in JanSQL Extremely slow query execution when more than one table joined in query when there is an additional where clause in the query. It can be overcome with the "ASSIGN TO variable" non-standard expression First execute query on a table with where clasue, assign resultset to a variable and then use the variable in a second query (instead of the table).

Thus, instead of: "SELECT ordrs.ordr AS order, ordrs.ordr_type AS order type,ordrs.prdct AS product,ordrs.prdct_dscr AS product description,ordrs.prdct_targ_qty AS order quantity,ordrs.prdct_targ_qty_unt AS order unit,rqrmts.cmpnt AS component,rqrmts.cmpnt_dscr AS component description,rqrmts.cmpnt_rqrd AS required quantity of component FROM ordrs,rqrmts WHERE ordrs.ordr=rqrmts.ordr AND ordrs.prdct=10010356;", use this non-standard SQL expression: "ASSIGN TO temp_ordrs SELECT * from ordrs WHERE prdct=10010356; SELECT temp_ordrs.ordr AS order, temp_ordrs.ordr_type AS order type,temp_ordrs.prdct AS product,temp_ordrs.prdct_dscr AS product description,temp_ordrs.prdct_targ_qty AS order quantity,temp_ordrs.prdct_targ_qty_unt AS order unit,rqrmts.cmpnt AS component,rqrmts.cmpnt_dscr AS component description,rqrmts.cmpnt_rqrd AS required quantity of component FROM temp_ordrs,rqrmts WHERE temp_ordrs.ordr=rqrmts.ordr;"

3. JanSQL has problems with typecasts. It can be tricky to guess how to cast values. Use 'ASNUMBER(some_value) AS something' whenever you do something with numerical values.

4. Parameter support is currently quite limited. Basically, named parameters must be used and they are replaced by its values as literal strings. You must enclose parameter identifiers in SQL string by quotes!

5. Multiline support Investigate setting the sdfdata.AllowMultiline property to true when required to allow CSV data that contains line breaks. See http://bugs.freepascal.org/view.php?id=17285 Not sure whether JanSQL database engine is able to handle this? The parameters set for SdfDataset in zmsql corresponds to the csv format that JanSQL uses

6. Missing outer (lef and right) joins in SQL queries Absence of outer joins in sql queries siriously limits sql capabilities. It can be partially overcomed by using non-standard 'ASSIGN TO variable' statement, which means that you can create subqueries to be used in further sql statement. However, improving janSQL engine to be able to execute left and righ outer joins should be solved till beta.

Installation

You may also need to add LCLPlatformDef to the implementation uses list of unit QBuilder. ZMSQL uses some constants defined in LCLPlatformDef. At least under Lazarus 1.7 development version.

The Demo Applications

Installation

See Also

Change Log

For more details, see zmquerydataset.pas

Current version 0.1.20: 19.08.2017
ZMSQL version 0.1.20, 19.08.2017: various improvements by Edgar Fernando Rodriguez Pacheco (edgarrod71@gmail.com)

ZMSQL version 0.1.19, 08.02.2015: by Zlatko Matić

TZMQueryBuilder uses TOQBEngineZmsql, which is TOQBEngine descendant. TOQBEngineZmsql is in based on code of the Open QBuilder Engine for SQLDB Sources created by Reinier Olislagers, modified and adapted for the ZMSQL by Zlatko Matić. It incorporates QBuilder visual query builder(Copyright (c) 1996-2003 Sergey Orlik , Copyright (c) 2003 Fast Reports, Inc.)

ZMSQL version 0.1.18, 10.04.2014: by Zlatko Matić

This was wrong, causing saving CSV file copy in wrong directory.

ZMSQL version 0.1.17, 07.03.2014: by Mario Ferrari

ZMSQL version 0.1.16, 28.01.2014: by Zlatko Matić

ZMSQL version 0.1.15, 28.01.2014: by Zlatko Matić

(Solved by a trick: persistent fields loaded from .lfm are recreated, propertie from old fields are copied to new fields and old fielsa are deleted.

Current version 0.1.14: 01 January 2014
ZMSQL version 0.1.14, 01.01.2014: by Zlatko Matić

ZMSQL version 0.1.13, 13.01.2013: by Zlatko Matić

If this does not work for your FPC version, then replace TBufDataSet with TZMBufDataset as ancestor. The TZMBufDataset will stay present latent inside the package, just in case that further development od TBufDataSet goes in direction incompatible with zmsql.

TBufDataSet's InternalRefresh does troubles, so it is overriden to do nothing. It seems that all functionalities which ZMQueryDataset needs are implemented inside TDataSet's Refresh method and that InternalRefresh is not needed at all.

ZMSQL version 0.1.12, 12.02.2012

ZMSQL version 0.1.11, 05.02.2012

ZMSQL version 0.1.10, 20 January 2012

In case of previously defined fielddefs, they will be used, otherwise, fielddefs from a dataset will be copied to ZMQueryDataset.

ZMSQL version 0.1.9, 15 January 2012

The formula is: MaxIndexesCount:=(2*(self.FieldDefs.Count)+3).This default value includes ascending and descending index on every column, two default TBufDataset indexes and one spare index. The formula is used in cases when LoadFromCSV or QueryExecute is called without prior FiedlDefs definition. You can still set MaxIndexesCount to any value before calling CreateDataset. Note that in zmsql you can use function SortDataset (const pFieldName:String):Boolean that creates indexes on the fly.

ZMSQL version 0.1.8, 08 January 2012

Therefore, new zmbufdataset.pas and zmbufdatasetparser.pp units are now actually downgraded to fpc 2.2.4. This choise will not change anymore and this is starting point for further independent development of zmbufdataset unit as direct ancestor of zmquerydataset. However, we can always come back to TBufDataset as direct ancestor of zmquerydataset (while preserving zmbufdataset as an alternative) if following criteria is met:

text
 1. DoFilterRecord method is virtual and protected.
 2. Refresh method works correctly

Until then, zmbufdataset remains direct ancestor of zmquerydataset and will fork from TBufDataset development.

This prevents peculiar inconsistences observed during query execution in some circumstances. This odd behavior of jansql has to be throughly investigated...

ZMSQL version 0.1.7, 01 January 2012

ZMSQL version 0.1.6, 28 December 2011

Change commit: 4 September 2011

Version 0.1.5: 12 August 2011

Version 0.1.4: 8 August 2011

Version 0.1.3: 2 August 2011

Version 0.1.2: 28 July 2011

Version 0.1.1: 26 July 2011

Version 0.1.0: 13 July 2011

From the Free Pascal wiki, page "ZMSQL", as it stood on 2023-10-20 after a change by Bart, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.

Revision 2, last changed by WikiArchive .

Talk about this page

Nobody has opened a bench for this page yet. Sign in to open one.