Hi all,

I'm busy with writing an XML export unit targeting Microsoft Access/.Net framework (ADO.NET data access framework in particular).

Runing an ADO.NET demo program I found out that the ADO.NET variant requires timezone info in its date/time fields.
In a sample export I did, it is set to my timezone (UTC+2 at this moment).

1. Is there any way of knowing what kind of timezone a datetime value should be in a FPC dataset? (I suppose not...)
2. If not, I think I'm going to just use the current timezone. Is there any cross-platform way of doing this? In the docs, I just found GetLocalTimezone/TZSeconds in e.g. the linux unit... I could of course use the Windows API to get something similar on Windows, but what about OSX, or BSD or Solaris...

Thanks,
Reinier

Runing an ADO.NET demo program I found out that the ADO.NET variant requires timezone info in its date/time fields. In a sample export I did, it is set to my timezone (UTC+2 at this moment).

ADO.NET exports timezones but it isn't required for import. Since the main concern is to export data from fp that can be read by access and ADO.Net, timezone shouldn't be an issue.

In the same line, trying to match the output of the different access versions and ADO.NET's isn't, IMHO, the correct approach neither. The goal for an exporter should be to create XML files that can be imported correctly and nothing more. I just tested access 2010 and the XML output created by TCustomXMLXSDExporter was just imported fine, except for the decimal point that needed to be a dot. MS has learned in the mean time ;) On the other hand, if you look at the XML output from 2010, it is completely different from all seen before (see attached file, exported from your test.mdb).

Regarding ADO.NET, different tests and googling show that its XML reader is simply not compatible with MS ACCESS XSD (all versions). When reading XML file created by ms access 2010:
- creates 2 datasets, dataroot and tblSample... tblSample has most field types correct, except for field one (didn't get the autonumber property, nor the unique requirement. It got the not null).
- indeces aren't used in ADO.NET datasets.
When reading XML file created by ms access 2002:
- breaks on http://www.w3.org/2000/10/XMLSchema, wants http://www.w3.org/2001/XMLSchema

I would conclude that exporting for ADO.NET and access are 2 different paths.

Ludo

So you're saying that this:
<ADateTime>2011-07-22T15:00:00+02:00</ADateTime>
can be simplified to this:
<ADateTime>2011-07-22T15:00:00</ADateTime>
?
I'll amend my simple ADO.Net test program to import XML and see what it does.

If that is true, it would save a huge amount of aggravation.

Still, I'd rather have an exporter that loses the least amount of data possible: if it can tell it's GMT/UTC+2, it would be nice if the export field could be annotated with whatever timezone info I can come up with. Could even be a comment in the XML file...

Thanks for the tests.

What I have been trying to do all along is to find the biggest common denominator of XML files. As it turns out and you mentioned, there's differences between ADO.Net and (one or more versions of) Access. IIRC, Access XP was the first version to have XML import/export functionality. I know that Access XP is finicky in its XML support, but that's what I have and I'd like support for it ;)
If 2010 imports the XP stuff fine, probably 2007 and 2003 will, too. But it would be nice if that could be confirmed by tests. As I wasn't sure that Microsoft was able to be backward compatible, so I hedged my bets by keeping open the possibility of more modes...

Now I'll probably end up with one single Access version. If newer versions have problems with the XP version, I'll just have to add more modes, though.

I don't completely agree with you that an exporter should just create XML files readable for the target application. The contents/meaning of the imported data must not differ from the contents/meaning of the original data as far as possible.

Regarding ADO.NET, different tests and googling show that its XML reader is simply not compatible with MS ACCESS XSD (all versions).

...

I would conclude that exporting for ADO.NET and access are 2 different paths.

I originally suspected MS might have wised up and would make a newer version of Access interoperable with ADO.Net. Obviously they haven't.

I'm splitting the code into Access and ADO.Net parts. The structure of the documents is similar: metadata followed by actual data, it's just a matter of getting the right elements etc in there.

When I'm done, I can have a look at Excel and see if it can read either format. If not, I'll just have to add another ;)

Finally, thanks for all the suggestions and tests, Ludo, I appreciate it.

It does. It uses TZ to convert if specified but assumes local time if not specified. Tested this when importing access 2010 data.

I think you misunderstood me. All the schema information available should be specified correctly in the XML file. What is not relevant, IMHO, is the format in which it is presented as long it is correctly and fully interpreted by the importer. So if the access 2002 format you created fits the bill, then that should be sufficient. There are for example a lot of presentation type of atttributes added in access 2010 (display width, locale, etc) which are not part of the real data schema. No reason to emulate (invent) these properties for our purpose.

You're welcome.

Ludo

PS attached the XSD as exported by Visual Studio 2008 from test.mdb.

Ok, that simplifies things a lot.

Totally agreed.

There are for example a lot of presentation type of atttributes added in access 2010 (display width, locale, etc) which are not part of the real data schema. No reason to emulate (invent) these properties for our purpose.

Agreed.
I did see one improvement in your 2010 export: descending indexes are supported as opposed to Access XP.... I'll tweak the format to spit that out & see if Access XP can read it...

Regards,
Reinier

Tested the creation of index info from MySQL SQLQuery and found some issues.

Line 222:

      if IsPublishedProp(DataSet, 'ServerIndexDefs') then
      begin
        IndexDefs := GetObjectProp(DataSet, 'ServerIndexDefs') as
TIndexDefs;

IndexDefs isn't the same as ServerIndexDefs for TCustomSQLQuery. IndexDefs just returns one index 'DEFAULT_ORDER' which isn't exported.

Line 252

            TDOMElement(FANode).SetAttribute('index-key',
              UTF8Decode(StringReplace(Index.Fields, ';', ' ',
              [rfReplaceAll, rfIgnoreCase])));

Typo: Index.Fields instead of Index.Name.

After these changes, import in access 2002 and 2010 is ok. Multiple field indices is working also?

I also noticed that the mysql implementation in fcl-db doesn't support index collation (ascending-descending). PostGres at first sight neither and oracle doesn't even support indices, in fcl-db I mean :(
Admittedly the definition of TIndexDef with DescFields string property that supposedly lists the descending fields, isn't facilitating sort order support neither.

Ludo

Ludo Brands wrote

> I did see one improvement in your 2010 export: descending > indexes are supported as opposed to Access XP.... I'll tweak > the format to spit that out & see if Access XP can read it...

Still to do...

Ok, replaced with ServerIndexDefs. Seems simplest.

Line 252 Typo: Index.Fields instead of Index.Name.

Reinier: Fixed

After these changes, import in access 2002 and 2010 is ok. Multiple field indices is working also?

It should be as it now takes the fields property (a semicolon delimited list of fields according to the help) & replaces ; with a space, which is the format that appears in my test xml.

Ludo (and anyone else): you can find the latest source at: https://bitbucket.org/reiniero/fpc_laz_patch_playground/src in the directory
fcl_db_export

In the meantime, I've finished the ADO.Net export functionality.

I'd appreciate further test reports!
One thing outstanding is enabling support for export without XSD; at the moment it doesn't write a file.

Thanks for the help,
Reinier

The ? was a typo of mine. I meant to confirm that multiple field indices work.

I'll do.

In the mean time tested excel import:
- Excel 2002 imports both schema and data as data. Independent if the source is access 2002 or TCustomXMLXSDExporter.
- Excel 2010 says there is no schema information for xml sources created with access 2002, 2010 and TCustomXMLXSDExporter

Apparently yet another schema format is used by excel.

Ludo

I'm getting an "http://www.w3.org/2001/XMLSchema:element not taken into account in this context" (freely translated from French) error. Fixed this by adding <xs:sequence> between <xs:complexType> and <xs:choice maxOccurs="unbounded" minOccurs="0"/>

One other comment on indices:

Line 244:
            if Index.Name = '' then
            begin
              TDOMElement(FANode).SetAttribute('index-name',
                UTF8Decode('idx' + Index.Fields + IntToStr(Index.ID)));
              //Avoids risk for name collision by adding collection id.
            End

Index.Fields contains ; as a field delimiter. AFAIAK ; is an invalid character in index names. Better do a StringReplace(Index.Fields, ';', '_', [rfReplaceAll, rfIgnoreCase])

Ludo

Attached the patch with the changes against git.

Ludo

Did some further testing. Adding sequence wasn't necessary. <xs:element name="table"> parenting to <xs:choice maxOccurs="unbounded" minOccurs="0"> instead of <xs:complexType> is the right solution. This corresponds also with your ADO.Net sample.

Patch attached.

Ludo

You don't even let me sleep... ;)

Thanks, Ludo. Applied in mercurial commit 48 2af395d3102c

Some questions:
1: newb alert: if I do patch -i xmlxsd.diff.txt fpxmlxsdexport.pp I get
patching file `fpxmlxsdexport.pp'
patch: **** `<' expected at line 12 of patch
Maybe some kind of line ending issue?
(I applied the patch manually)

2. UTF8Decode('idx' + UTF8Decode(StringReplace(Index.Fields, ';', '_',
[rfReplaceAll, rfIgnoreCase])) + IntToStr(Index.ID)));

Why are you doing an extra UTF8Decode of the StringReplace part? The entire string will get UTF8Decoded anyway?

Thanks, Ludo. Applied in mercurial commit 48 2af395d3102c

The patch was improved in a later message...

That's possible. I used cygwin diff -> Linux line endings

You're right. Did a cut and paste from a few lines below and didn't pay attention to the double convert.

Ludo

As per mercurial commit 52: 076a4b1d4505
Fixed.

Removed extraneous sequence as you suggested. Seems to work with my simple ado.net xmlexport.exe tool.

I've written support for descending indexes, but haven't tested it. If anyone's testing with descending indexes, I'd appreciate a copy of the XML so I can test it with Access XP...

Changed ExportFormat from AccessXPCompatible to AccessCompatible

1. Just now installed Excel XP, created XML export, committed test file ExcelXPExportSample, and ready to go for a third format.
2. Will also need to fix XSDless export, as that is not working right now.

Now off to do real life things...

Reinier