Update: includes first stab at Delphi Clientdataset. Please test with various datatypes as I haven't been able to map all of them...

Hi list (& Michael Van Canneyt ;),

Earlier I announced I was writing another XML export module for FCL-DB.

After some interesting hours with Excel, Access, .Net and FreePascal, and thanks to Ludo Brands for testing and bugfixes, it seems the work is done.

What is it: an FCL-DB export module that can export in various Unicode XML formats: Access/SQL Server, ADO.Net, Excel and Delphi ClientDataset.

Suggestions for additional XML export formats are welcome.

Is it tested: yep: export and import on Access XP, import by ADO.Net, import on Excel XP. Earlier versions of the code were tested with import on Access 2010.
(Delphi Clientdataset needs to be tested though)

The code comes with some simple tests (basically testing if the various export formats actually finish without error) and a readme.

I think it is stable enough to include in FPC 2.5.1 for more wide scale use and testing.

I've uploaded it to bug 19790:
files:
20110727Final_XMLXSDExport_Code.zip: the code, patch for fpc makefile I've annotated the code with Lazarus todo entries to indicate possible improvements/tests.
20110727Final_XMLXSDExport_Tests.zip: the test code. Note: I haven't patched any makefiles, don't know if that's necessary

Could an administrator remove the rest of the files as they are older, superfluous and will confuse users? Thanks.

Hope it can be applied, of course I'm available for any necessary changes/improvements,

Thanks for all the help,
Reinier

Some problems with Delphi Clientdataset:

-<METADATA> comes after <ROWDATA> and Delphi 2006 complains about missing metadata. Inverting the 2 blocks solves the problem. Moved the <METADATA> block from 2555 to 2462

- datetime and timestamp are missing time part as mentioned in the comments.

Formatdatetime('yyyymmdd"T"hh:nn:ss', EF.Field.AsDateTime) solves that.

- bcd: invalid field type

- the mysql text types (tinytext,text, longtext, etc) are exported as bin.hex with subtype binary. Perhaps a problem in the mysql code.

- when a string field contains the > character, the row is not imported but the previous one is duplicated.

-Delphi has the dfXMLUTF8 and dfXML format for exporting data but no way to specify this for reading. The difference is in the first line: encoding="UTF-8".

I'll investigate further and let you know by tomorrow.

Ludo

Ludo

Reinier Olislagers wrote

Update: includes first stab at Delphi Clientdataset. Please test with various datatypes as I haven't been able to map all of them...

I added your implementation to SVN.

But you may want to check your implementation on 64-bit prior to submitting.

Code like

       ftWideString: //fixed length or at least max length string
       begin
         TDOMElement(ColumnNode).SetAttribute('fieldtype', 'string');
         TDOMElement(ColumnNode).SetAttribute('WIDTH',
           UTF8Decode(string(ExportFields.Fields[ItemCounter].Field.Size)));
       end;

Is dead wrong. It passes on 32-bit (but generating wrong data), but fails to compile on 64-bit.

The compiler has a warning for this, so checking your warnings while compiling for 32-bit may also be a good idea.

Michael.

I'll look into that.

- datetime and timestamp are missing time part as mentioned in the comments. Formatdatetime('yyyymmdd"T"hh:nn:ss', EF.Field.AsDateTime) solves that.

I'll look into that.

- bcd: invalid field type

Ok, what field type do we need ;) I'll wait for your further investigations.

- the mysql text types (tinytext,text, longtext, etc) are exported as bin.hex with subtype binary. Perhaps a problem in the mysql code.

Ok.
Am looking at test coverage, might be a good idea for medium term to use current sqldb testing framework so we can use eg Firebird employee sample database or an mysql database.
For now, I'll wait for your further investigations.

- when a string field contains the > character, the row is not imported but the previous one is duplicated.

Strange, shouldn't this be handled by the XML export code?

-Delphi has the dfXMLUTF8 and dfXML format for exporting data but no way to specify this for reading. The difference is in the first line: encoding="UTF-8".

Was afraid of that. FCL XML has no support for specifying encoding. I might need to add a bug and a hackish workaround (if possible, opening the XML file we just created and rewriting the first line)

I'll investigate further and let you know by tomorrow. Ludo

Hi Ludo,

https://bitbucket.org/reiniero/fpc_laz_patch_playground/issue/2/delphi-createdataset-issues

Thanks, looking forward to your results...

Thanks, Michael.

I noticed your remark on a failing conversion of a longint to a string - that code wouldn't have worked. Obviously a priority to increase the test coverage.

But you may want to check your implementation on 64-bit prior to submitting.

I will compile & test on Linux x64 before submitting an upgrade

Hmmm, hadn't thought about size. (And size obviously matters here ;)

Just to make sure I understand correctly: in a string field, Field.Size indicates the number of characters that can be stored in a field, while Field.DataSize indicates the number of bytes, right?

Complication is that e.g. Access expect character size for their size specification. Delphi Clientdataset will probably require datasize.

Thanks, I'll look into it.

New plans:
1. Expand test coverage: additional datatypes, fixed string width fully filled with East Asian characters.
Use the output of this coverage as baseline to verify code commits still deliver the proper export.
2. Incorporate fixes, e.g. those mentioned by Ludo Brands. Please keep those reports coming ;)
3. Clean up code (needless class-level variables), look into possibility of splitting entire procedures according to export format, instead of multiple case...of statements inside a procedure body.

I'll keep a Mercurial repository on bitbucket to coordinate with others and upload a new patch when done.

Thanks,
Reinier

Yes.

Complication is that e.g. Access expect character size for their size specification. Delphi Clientdataset will probably require datasize.

I don't think so, but I'm not sure. I would have to test that.

I would appreciate that one. I think with some refactoring, the code could be made much simpler.

I'll keep a Mercurial repository on bitbucket to coordinate with others and upload a new patch when done.

Let me know when you're ready for a new patch :-)

Michael.

Submitted in FPC bugtracker, issue 19827

> - when a string field contains the > character, the row is not > imported but the previous one is duplicated. Strange, shouldn't this be handled by the XML export code?

Debugged xmlwrite.pp. In DelphiClientDataset, values are sent out as attributes of ROW. Line 133 in xmlwrite.pp specifies special characters for attributes as AttrSpecialChars = ['<', '"', '&', #9, #10, #13]; It doesn't include '>'. Is delphi violating the rules or is xmlwrite.pp too conservative, I don't know. Adding '>' to AttrSpecialChars seems to be a save solution. Another issue in bugtracker?

Ludo

Yes, and please assign it to sergei gorelkin.

Michael.

Do you have any indication if it is actually a bug to not consider '>' as a special character? I googled a bit but only found contradicting info. Question of correctly tagging it as feature request or bug. Didn't know a "reporter" could assign issues to somebody.

Ludo

Michael.

The XML rereference does not list > as an allowed character for attribute values.

It also says

The right angle bracket (>) may be represented using the string " &gt; ", and MUST, for compatibility, be escaped using either " &gt; " or a character reference when it appears in the string " ]]> " in content, when that string is not marking the end of a CDATA section.

So replacing > with &gt; seems like the correct thing to do.

Michael.

> - bcd: invalid field type Ok, what field type do we need ;) I'll wait for your further investigations.

Line 2087 becomes:

      ftBCD: //WIDTH=precision, DECIMALS=scale
             //negative DECIMALS not allowed !!! oracle does allow negative
scale
      begin
        TDOMElement(ColumnNode).SetAttribute('fieldtype', 'fixed');
        TDOMElement(ColumnNode).SetAttribute('WIDTH', '20' );
        TDOMElement(ColumnNode).SetAttribute('DECIMALS',
IntToStr(ExportFields.Fields[ItemCounter].Field.size) );
      end;

Note that precision and scale are poorly implemented in sqldb. I checked both oracle and mysql and support for precision and scale is barely present and severely limiting scale values. Reason for the fixed width in above code. 20 should fit double type values since scale is limited to 4. So no exponentes.
Haven't tested negative scales as allowed in oracle yet. I know Delphi 2006 crashes on negative values but I can't tell yet if negative scales ripple through to TFieldDef.size.

The sqldb mysql implementation doesn't distinguish text from blob. Nothing that can be done about in the export.

Ludo

<snip>
Ok. Updated in bitbucket issue

<snip>

The sqldb mysql implementation doesn't distinguish text from blob. Nothing that can be done about in the export.

Ok. removed from bitbucket issue

I'll get to the issues some time after the test framework improvements.

If you prefer to, please feel free to directly enter the issues: https://bitbucket.org/reiniero/fpc_laz_patch_playground/issues/new

In addition, I could also give you write access to the bitbucket mercurial repository...

Regards,
Reinier

Hi Ludo, (and the rest)

Regarding the XML XSD export: I'm taking it a bit more easy and am focussing on the test set first, then use the existing export code to generate some output (even if it's wrong) that we can test further output on.
Then we can compare output (e.g. Clientdataset fixes) a bit more easier with a diff tool.

Questions/remarks:
1. Do you know a free GUI diff tool for XML that shows only the changes in contents and nodes, not whitespaces, comments etc? Couldn't find any, except some ancient IBM java app I couldn't get running (probably user error ;)

2. Ludo, or anyone else, if you want to improve the export code, please go ahead as I'm not working on it for the next couple of days. You could send me the results when you're done; I'll add it to the new tests and upload it as a patch. (Or upload it yourself of course ;) I suspect I'll be looking at the export code again after the weekend.

3. Regarding the tests: I thought about better ways of testing the exports. The only fairly easy way I see is testing with more databases (such as mysql which you were doing), maybe by adapting the existing fcl-db test framework. Haven't ever run that though, but I can learn ;)

Another solution would be to verify the xml output, but then you're basically writing an importer for the export format ;) Could be worthwile in the case of Delphi Clientdataset though...

Any thoughts on how to improve the tests?

4. Would it actually make sense to start an importer project that imports e.g. clientdataset, csv, into a dataset? Is there already something like that?

5. I briefly thought about including OpenOffice Calc (ODT) format exporting but that seems a bit overkill as there already is a CSV export format. Any thoughts on new formats - apart from Atom ;) ?

Thanks,
Reinier

I used mysql because I have some testtables in mysql that contain almost all mysql types which I used in another project. Downside is that you test as well the sqldb mysql implementation as the export. IMHO building testcases with MemDataset using the different built-in datatypes and border-line values (limit values for integers, special characters in strings, etc.) is going to be a cleaner solution. Will make regression testion also easier.

Another solution would be to verify the xml output, but then you're basically writing an importer for the export format ;) Could be worthwile in the case of Delphi Clientdataset though...

A different export output doesn't mean that the importing application imports something different. The final test is importing in a foreign application.

IMHO making additional specific exporters for calc / excel type of applications isn't very usefull. Spreadsheets don't have real typed data and can infer quite well a "schema" for xml data that don't contain one. The XML data these apps export contain more presentation type of info than data type related info. As you wrote, csv is good enough for these apps and has far less overhead.

Ludo

Agreed, but you cannot every kind of data type, for that you will probably need the various databases... and hopefully a continuous integration server to run the tests...

But yes, you're then testing the fcl-db drivers/framework as much as the export functionality. Not a completely bad thing.

Whenever you have time, could you send me a dump of the table definition with or without data? That would save me doing that...

Reinier

Agreed, but you cannot every kind of data type, for that you will probably need the various databases... and hopefully a continuous integration server to run the tests...

Sqldb converts databases types to the internal ftxxx datatypes. So, yes, you can everything without external databases.

But yes, you're then testing the fcl-db drivers/framework as much as the export functionality. Not a completely bad thing.

Testing multiple layers at the same time is always dificult since interpreting errors is complex. Unit testing was invented for that purpose.

Whenever you have time, could you send me a dump of the table definition with or without data? That would save me doing that...

No problem. Here it comes:

CREATE TABLE `testodbc` (
`id` int(11) NOT NULL auto_increment,
`s_int` int(11) default NULL,
`u_int` int(10) unsigned default NULL,
`s_tinyint` tinyint(4) default NULL,
`u_tinyint` tinyint(3) unsigned default NULL,
`s_smallint` smallint(6) default NULL,
`u_smallint` smallint(5) unsigned default NULL,
`s_mediumint` mediumint(9) default NULL,
`u_mediumint` mediumint(8) unsigned default NULL,
`s_bigint` bigint(20) default NULL,
`u_bigint` bigint(20) unsigned default NULL,
`t_float` float default NULL,
`t_double` double default NULL,
`t_decimal` decimal(10,4) default NULL,
`t_datetime` datetime default NULL,
`t_date` date default NULL,
`t_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, `t_time` time default NULL,
`t_year` year(4) default NULL,
`l_char1` char(1) character set latin1 collate latin1_general_cs default NULL,
`u_char1` char(1) character set utf8 collate utf8_bin default NULL, `l_char20` char(20) character set latin1 collate latin1_general_cs default NULL,
`u_char20` char(20) character set utf8 collate utf8_bin default NULL, `l_varchar10` varchar(10) character set latin1 collate latin1_general_cs default NULL,
`u_varchar10` varchar(10) character set utf8 collate utf8_bin default NULL,
`l_varchar300` varchar(300) character set latin1 collate latin1_general_cs default NULL,
`u_varchar300` varchar(300) character set utf8 collate utf8_bin default NULL,
`l_tinytext` tinytext character set latin1 collate latin1_general_cs, `u_tinytext` tinytext character set utf8 collate utf8_bin, `u_mediumtext` mediumtext character set utf8 collate utf8_bin, `u_text` text character set utf8 collate utf8_bin, `u_longtext` longtext character set utf8 collate utf8_bin, PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

Note the u_char1 which is too small to hold a multiple byte utf character!

Ludo

I admit I need to delve deeper into this, but to me it's strange we have datatypes like ftOracleBlob and ftParadoxOLE... These do seem to be database-specific (ok, maybe within fcl-db, but still). Earlier today I've written code to dumbly create a bufdataset with all these datatypes for and will see what happens when assigning data to it. If it blows, I'll comment it out... we'll see.

Db.pas line 2020 gives you the mapping between ftxxx and corresponding Tfield descendant. This is a default mapping but AFAIK no db implementation is actually overriding this.
Since there are a lot of ftxxx mapped to only a few TField classes, the testcases can be reduced considerably. It'll help also in reducing the long 'case fieldtype' statements you have now.

Ludo

Thanks, I'll have a look...

Not in FPC, but in Delphi, IBX for instance overrides it. Maybe mseide does it too.

Michael.

Am 29.07.2011 08:27, schrieb

Maybe mseide does it too.

Correct.

Martin

Ok, just to be safe and document mappings in any event, I'll keep the complete list of mappings in the exporter.

I'll only test with the types that work in a bufdataset...

Regards,
Reinier

Reinier Olislagers wrote

5. I briefly thought about including OpenOffice Calc (ODT) format exporting but that seems a bit overkill as there already is a CSV export format. Any thoughts on new formats - apart from Atom ;) ?

That would be export from Dataset to ODS?

Why not simply implement a importer from Dataset for FPSpreadsheet instead of creating a new library to handle ODS?

Because depending on a full-fledged spreadsheet technology for exporting data is overkill.

The idea of the export routines is to be able to export data without too much dependencies.

Which does not mean that the opposite direction (importing data from a dataset into a spreadsheet) is not useful in it's own right, far from it.

Michael.

Yes, sorry for the typo.

Why not simply implement a importer from Dataset for FPSpreadsheet instead of creating a new library to handle ODS?

Very good idea; I'm not going to do it though - when csv works good enough [1], that works for me...

[1]: hint: who will look at issue 0019759: [Patch] CSV export doesn't quote memo fields?
Actually, seems that the fcl-db CSV export module uses its own implementation of CSV (writing) while we already have SDFDataset...

Actually this operation is so trivial that it is even one of the examples from FPSpreadsheet:

http://wiki.lazarus.freepascal.org/FPSpreadsheet#Converting_a_database_to_a_spreadsheet

That's not really possible for ODS. You will need to at least add a dependency to paszlib and possibly to a xml library too.

Well, the exports are already dependent on fcl-XML. So adding paszlib is not really a heavy burden...

Michael.

<snip> The sqldb mysql implementation doesn't distinguish text from blob. Nothing

that can be done about in the export. Ludo

Ludo & the rest,

After my recent adventures with LazUpdater, back to the XML export.

I've written new tests with more data types that at least produce test output now.

My revision 13 has fixes for the Delphi Clientdataset issues, so everyone is welcome to test if you want:
https://bitbucket.org/reiniero/fpc_laz_patch_playground/overview

You might want to just use fpxmlxsdexport.pp and use the existing XMLXSDExportTest code to compare current output with old test output...

I'll be continuing with:
- adding as many small fixes as I can
- streamlining/refactoring the code
- testing ADO.Net, Excel & Access export formats
- preparing patch for (hopefully) inclusion in FPC 2.6 series

Thanks,
Reinier

Problems so far:

TClientDataSet:
- ftWideString has zero length by default: Delphi 6 returns invalid parameter for WIDTH="0" when opening the tclientdataset. Added FieldDef.Size := 255; for ftWideString field definition;

-Access 2000:
- access doesn't accept the empty value for ftAutoInc. TAutoIncField is read only and BufDataSet doesn't do anything with autoincrement fields. Better drop it from the test.
- access falls over the guid not being a real guid. It aborts reading the remainder of the line because dataset not in edit/insert mode anymore ... Guid has to be a string of the form {9F5FBC24-EFE2-4f90-B498-EC0FB7D47D15}
- ftWideString creates sometimes non ascii characters at the end. This is not the case for TClientDataSet. Still looking in that one;
- the year 0001 is imported as 2001. Also manually you can't enter the year 0001 in access 2002. It always becomes 2001.

Ludo

Ok, should be easy.

Ok, but it should recognize its own version of an autoinc (autonumber I think it's called. I'll have a look.)

- access falls over the guid not being a real guid. It aborts reading the remainder of the line because dataset not in edit/insert mode anymore ... Guid has to be a string of the form {9F5FBC24-EFE2-4f90-B498-EC0FB7D47D15}

Yep.

- ftWideString creates sometimes non ascii characters at the end. This is not the case for TClientDataSet. Still looking in that one;

Ok.

- the year 0001 is imported as 2001. Also manually you can't enter the year 0001 in access 2002. It always becomes 2001.

Yeah, I had complaints with Excel as well ;)

Ludo

Thanks Ludo,

I'll put them on my list...

Regards,
Reinier

> - ftWideString creates sometimes non ascii characters at the end. This > is not the case for TClientDataSet. Still looking in that one; Ok.

Problem is that when specifying a string larger than fieldsize, no closing #0 is stored. When reading the string back, the string overruns the field size until the first #0 encountered. Hence the "sometimes". Apparently a bug in fcl-db. I'll raise a bug report.

Ludo

Regarding the snippet above: for Unicode-enabled Delphis, using Clientdataset savetofile as UTF8-based XML (dfXMLUTF8), what do I need to output for a ftString:
- Field.Size (number of characters in string)
- Field.DataSize (number of bytes in string field)

As Michael indicated, it probably is Field.Size, but I'd like to have that confirmed. (Can't seem to find any documentation on ClientDataset XML format on Embarcadero's Delphi help at
http://docwiki.embarcadero.com/VCL/en/DBClient.TCustomClientDataSet.SaveToFile)

Thanks,
Reinier

I created a dataset with 2 fields. A ftString field with size 33, and a ftWideString field with size 44.

Based on the following output from SaveToFile, I would say that you'll need to put Size*SizeOf(CharTypeUsedInfield), which should be DataSize.

Michael.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <DATAPACKET Version="2.0">
<METADATA>
<FIELDS>
<FIELD attrname="stringveld" fieldtype="string" WIDTH="33"/> <FIELD attrname="WideStringVeld" fieldtype="string.uni" WIDTH="44"/> </FIELDS>
<PARAMS/>
</METADATA>
<ROWDATA>
</ROWDATA>
</DATAPACKET>

Thanks, Michael,

Set to .DataSize...

I'm ready for uploading this for inclusion in FPC, I can open a bugreport and attach the patch if you're happy with that.

Fixes/changes since first commit to FPC
=======================================
1. Code cleanup to make it more modular and maintainable: split out code per export format and moved class-level variables there.
2. Delphi Clientdataset export now works thanks to FPC XML fixes by Sergei Gorelkin and tests/improvements by Ludo Brands.
3. Extended test coverage with all field types in a BufDataset so tests cover more.
4. Fixes for invalid data: Access, ADO.Net, Excel date limits respected (generate NULL or text fields respectively)
5. Decimalseparator setting in Access export should now work.
6. Variants are saved as blobs, not strings, because of their variable nature.
7. Fix for proper export of null values to Access NULL values (instead of empty/default values).
8. Changed description of class from "Unicode XML file with XSD" to "Unicode XML file" to better match unit function.
9. Compiled on Windows x86, Linux x64 without warnings using fpc -vweh fpxmlxsdexport.pp. Compared output.
- ftVarBytes gave different output on Windows & Linux. Disregarded as I don't fill these fields in the test because of bug 19930
10. Added option KeepFilesAfterTest = true in xmlxsdexporttestcase1; can be switched to false to delete export test files after use.
11. Lower-cased source code file names.
12. Fixes for Linux invalid date export to Excel by using clocale and specifying dateformat (restored at end of export). 13 .Datasize instead of .Size for string/widestring Delphi fields.

Regards,
Reinier

Decided to upload it - I think it's a great improvement compared to the original code. Unfortunately that has more to do with my skills and other peoples' help, but well... better code is better code :)

Uploaded as issue 19987.

Thanks,
Reinier

I applied the patch, thank you very much for this work :-).

But please:
- Try to split patches in smaller chunks. It's better for bugfixing. If you break something it's harder to track what caused the break with these huge patches.

- Please send diffs.

- Don't change casing of files in between patches. (should be no problem if you send diffs, actually)

Michael.

Ok - and I thought it would be less work for you guys to apply it if I submitted one big one ;)

- Please send diffs.

Ok.

- Don't change casing of files in between patches. (should be no problem if you send diffs, actually)

Mmm, tried to help there, by lowercasing everything...

Thanks a lot, my itch has been scratched for now - back to developing the program that was actually meant to use this functionality.

Of course, bug reports/improvement suggestions still welcome; I'll try and submit small patches for them ;)

Regards,
Reinier