ZMSQL - TBufDataset SQL enhanced in-memory database

[tatamata]

I'm just working on new version (0.1.10) of zmsql. I have rewritten CopyFromDataset method and few other things related.
One interesting possible usage of zmsql is to load data from "real" databases such as PostgreSQL, Firebird etc. into ZMQueryDatasets, perform some editing and then append results into real database again.
Once I had programmed such application in MS Access+PostgreSQL, in which case MS Access database was holding temporary data being transactionally appended to PostgreSQL. This is handy for complicated formulars that should have contain predefined values queried from database and input values in the same row. So you have to query database to create formulars (data input forms) from some predefined data matrix, while only one field is for user data input. When user finishes input, all rows are transactionally transferred to PostgreSQL database.
ZMQueryDataset could do the same.
Since I don't have any database server currently installed on my machine, could someone test this CopyFromDataset method with different database servers (PostgreSQL, Firebird....)?
The testing zmsql version is here:
http://www.4shared.com/zip/u1ww-Gv_/TZMSQL-0110.html


Thanks!

[CaptBill]

Thanks for your hard work with this. We really need this.
Wish I could help with testing.

Good luck with it.

[tatamata]

New version of zmsql, v. 0.1.11 is released.
See change log here:
http://wiki.lazarus.freepascal.org/ZMSQL#Change_Log
Download here: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/

I must say I am very happy with this release, since now zmsql does not rely on American decimal separator any more.
Now zmsql recognizes system decimal and thousand separator and uses it for both loading data from csv tables and for sql queries.
Moreover, you can explicitly set the decimal separator in ZMConnection. In that case, this will override system settings.
Interestingly, if you load or query csv tables that have float values with dot decimal separator, but you set "," as decimal separator, when loading values, zmquerydataset will replace "." with ","...and vice versa...

[BigChimp]

Mmmm, yes, I have seen the release notes on the wiki page. Guess you and I disagree on using decimal points and commas, but that's fine as it's your project,... and you provide a way to retain the old behaviour.

Keep up the good work!

[tatamata]

Well, BigChimp, it's a practical issue.
I use zmsql for a material requirements planning for manufacturing. The program relies on data exported from SAP ERP, in .xls format. It is formatted with local decimal separator, in this case ",".
Previously I had to save .xls in .csv, then manually replace all "," with ".", then import csv into zmsql, then do calculation, then export results into .csv and manually replace "." with "," again....Too much complication for users...
I supppose most zmsql users will use it for similar purposes and it has sense to enable flexibility in choosing formatting and especially to use local formatting by default.

[BigChimp]

  I see your point, thought it would be something like that. Wouldn't agree with the local formatting by default (if everybody else is writing wrong output should we, too) but... I'm glad the flexibility is there.

[tatamata]

So, I think most of basic goals are acomplished:
- integration of SQL database engine into TBUfDataset descendant in-memory database
- flat textual database storage
- optional implicit or explicit save to underlying csv text file
- loading data from csv files (with .txt extension)
- loading data from sql queries perforemed on csv files (with .txt extension)
- loading from other datasets (any dataset)
- referential integrity between zmquery datasets (referential insert/update/delete)
- master/detail filtration
- parameterized queries (limited at the moment)
- flexibility in choosing decimal separator (local format by default, optionally overidden by explicitly set decimal separator)
- smart creation and usage of indexes
- explicit (by fielddefs definition) or on-the-fly implicit fields creation (during loading from csv or SQL query)

However, there is lot to improve:
- improve jansql database engine
- refactoring and optimization of existing code
- improving parameterized queries implementation
- improving zmbufdataset unit
- improving persistent save. Currently, if PersistentSave is enabled, on every post the whole dataset will be saved to csv file. It should be improved to be row-based, instead of saving the whole table.
- implemetation of AutoInc field type

[almsoares]

I start use ZMSQL and I saw that you are doing a good job in this component.
So I found a kind of error in the function TZMQueryDataSet.InspectFieldDefs:Boolean;
the line is:
   sdOtherDataset:vNewFieldNames:=vNewFieldNames FSdfDatasetImport.FieldDefs.Name ';';   
and the correct is:
   sdOtherDataset:vNewFieldNames:=vNewFieldNames FOtherDatasetImport.FieldDefs.Name ';';   

Thanks

[fredycc]

Since I don't have any database server currently installed on my machine, could someone test this CopyFromDataset method with different database servers (PostgreSQL, Firebird....)?

I'll try to test it in my Firebird database 

Regards

[hrayon]

Hello!
I found a small bug in "destructor TZMQueryDataSet.Destroy;"
If "FRecordsetIndex" was not initialized, then after to delete a "ZMQueryDataSet" from a form, an error is generated.
The initialization is in "procedure TZMQueryDataSet.QueryExecute;".
Maybe, initializing in "constructor TZMQueryDataSet.Create(AOwner: TComponent);" and checking if it was initialized before executing:
"ZMConnection.JanSQLInstance.ReleaseRecordset(FRecordsetIndex);" helps.

Regards

[tatamata]

@almsoares and @hrayon, thanks for reporting the bugs.

Currently i don't work actively on new version, if I do I will take care if these reported issues.

In meantime, you are all free to prepare new zmsql version your own and upload it to Lazarus CCR. Just send the zipped source code to Vincent...

[teos]

I wonder if someone has a ZMSQL management tool and if folks are working on this database.

I was about starting to develop a client-server database for Lazarus, but maybe this is a bit more interesting. What is required to take over the project? (as experienced Delphi developer, also with knowledge in TDataset)

[tatamata]

I wonder if someone has a ZMSQL management tool and if folks are working on this database.

I was about starting to develop a client-server database for Lazarus, but maybe this is a bit more interesting. What is required to take over the project? (as experienced Delphi developer, also with knowledge in TDataset)

Hi!
What do you mean by "ZMSQL Management tool"?
If you are willing to continue where I stopped, you are welcome to take it over!
Just download the source-code and play with it and yo can ask Vincent to upload your newversion of zmsql.
Or you can ask Vincent to allow you to upload directly to sourceforge. Ask Vincent!
I used to send him zipped folder with new zmsql version and he would upload it to Sourceforge...

[tatamata]

I wonder if someone has a ZMSQL management tool and if folks are working on this database.

I was about starting to develop a client-server database for Lazarus, but maybe this is a bit more interesting. What is required to take over the project? (as experienced Delphi developer, also with knowledge in TDataset)

Hi!
What do you mean by "ZMSQL Management tool"?
If you are willing to continue where I stopped, you are welcome to take it over!
Just download the source-code and play with it and yo can ask Vincent to upload your newversion of zmsql.
Or you can ask Vincent to allow you to upload directly to sourceforge. Ask Vincent!

Maybe by management tool you mean something like my simple MightyQuery?
Download setup files:
http://matalab.freehostia.com/MightyQuery-1.0.9.exe (Win 32)
http://matalab.freehostia.com/MightyQuery-1.0.9.pet (Puppy Linux)
Download portable binaries for Linux and Windows: http://matalab.freehostia.com/MightyQuery-Binary.zip (Just unzip the folder. Both Linux and Windows binaries are inside the folder.)
Source code is available for download, too: http://matalab.freehostia.com/MightyQuery-Source.zip

[teos]

Yes, That is what I ment with database management tool. 

I will give the database a try first, see what it is and how it works.