[fpc-pascal] Timezone information in a dataset datetimefield?

Ludo Brands ludo.brands at free.fr
Mon Jul 25 13:31:09 CEST 2011

> 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.

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

> 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...
> > 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).
> 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.

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.

> > 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.

You're welcome.


PS attached the XSD as exported by Visual Studio 2008 from test.mdb. 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: testDataSet.xsd
Type: application/xml
Size: 28696 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20110725/9b79afe0/attachment.xsd>

More information about the fpc-pascal mailing list