RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
1. Is there any way of knowing what kind of timezone a datetime value should be in a FPC dataset? (I suppose not...) No, there is no way. It's always supposed to be 'local time'. That is also what most of the underlying databases do. They store everything in UTC and convert to local time. Only for those that support timezoned field types (fe. oracle timestamp_TZ) you can specify a timezone when entering data. MySQL fe. doesn't have support for this. Ludo ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 10:50, Ludo Brands wrote:
1. Is there any way of knowing what kind of timezone a
datetime value
should be in a FPC dataset? (I suppose not...)
No, there is no way. It's always supposed to be 'local time'.
That is also what most of the underlying databases do. They store everything
in UTC and convert to local time. Only for those that support timezoned
field types (fe. oracle timestamp_TZ) you can specify a timezone when
entering data. MySQL fe. doesn't have support for this.
Ludo
Mmmm. For example, IIRC, Firebird just stores it as entered - no UTC
conversion.
Sensible developers on multi country Firebird db projects would probably
store convert date/time info to UTC in the database.
How about this choice:
TDateTimeStorageFormat = (UTC, Local {, StoredInDatabase});
//Treat date/time fields in datasets as stored in local time or UTC.
Defaults to local time.
{ TODO 7 -oAnyone -cNice to have : If we can support databases that
store timezone info with date/time, e.g. Oracle, add an option
StoredInDatabase }
TXMLXSDFormatSettings = class(TExportFormatSettings)
property DateTimeStorageFormat: TDateTimeStorageFormat
read FDateTimeStorageFormat write FDateTimeStorageFormat;
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 11:21, Reinier Olislagers wrote:
On 25-7-2011 10:50, Ludo Brands wrote:
1. Is there any way of knowing what kind of timezone a
datetime value
should be in a FPC dataset? (I suppose not...)
No, there is no way. It's always supposed to be 'local time'.
That is also what most of the underlying databases do. They store everything
in UTC and convert to local time. Only for those that support timezoned
field types (fe. oracle timestamp_TZ) you can specify a timezone when
entering data. MySQL fe. doesn't have support for this.
Ludo
Mmmm. For example, IIRC, Firebird just stores it as entered - no UTC
conversion.
Sensible developers on multi country Firebird db projects would probably
store convert date/time info to UTC in the database.
How about this choice:
TDateTimeStorageFormat = (UTC, Local {, StoredInDatabase});
//Treat date/time fields in datasets as stored in local time or UTC.
Defaults to local time.
{ TODO 7 -oAnyone -cNice to have : If we can support databases that
store timezone info with date/time, e.g. Oracle, add an option
StoredInDatabase }
TXMLXSDFormatSettings = class(TExportFormatSettings)
property DateTimeStorageFormat: TDateTimeStorageFormat
read FDateTimeStorageFormat write FDateTimeStorageFormat;
I realize having a local timezone option will result in a hornet's nest
of adjustments for Daylight Saving Time etc... I'll try and check to see
if it is possible to specify the timezone description instead of the
offset in the export datetime field.
If so, I'll try and get timezone description from the OS...
Thanks,
Reinier
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
RE : [fpc-pascal] Timezone information in a dataset datetime field?
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 ?xml version=1.0 encoding=UTF-8? root xmlns:xsd=http://www.w3.org/2001/XMLSchema; xmlns:od=urn:schemas-microsoft-com:officedata xsd:schema xsd:element name=dataroot xsd:complexType xsd:sequence xsd:element ref=tblSample minOccurs=0 maxOccurs=unbounded/ /xsd:sequence xsd:attribute name=generated type=xsd:dateTime/ /xsd:complexType /xsd:element xsd:element name=tblSample xsd:annotation xsd:appinfo od:index index-name=AGUID index-key=AGUID primary=no unique=no clustered=no order=asc/ od:index index-name=ID index-key=ID primary=no unique=no clustered=no order=asc/ od:index index-name=idxMultipleFieldIndex index-key=ADate ADateTime primary=no unique=no clustered=no order=asc asc/ od:index index-name=PrimaryKey index-key=ID primary=yes unique=yes clustered=no order=asc/ od:tableProperty name=Orientation type=2 value=0/ od:tableProperty name=OrderByOn type=1 value=0/ od:tableProperty name=DefaultView type=2 value=2/ od:tableProperty name=GUID type=9 value=rVdjLTiQKUeH0a+8EUBgwQ== / od:tableProperty name=NameMap type=11 value=CswOVQCtV2MtOJApR4fRr7wRQGDBAMUUNWd65eNAAAB0AGIA bABTAGEAbQBwAGwAZQADqT5Tb8YjTJjgv59PNUt5BwAAAK1XYy04kClH h9GvvBFAYMFJAEQAP0/7dsX7LEu04jukb9IHpwcAAACtV2MtOJApR4fR r7wRQGDBVgBlAHIAcwBpAG8AbgADMfXvxT6fSoDTf0wh6IwoBwAAAK1X Yy04kClHh9GvvBFAYMFBAEQAYQB0AGUAikLaRvWht0un+TP3LY3d1gcA AACtV2MtOJApR4fRr7wRQGDBQQBEAGEAdABlAFQAaQBtAGUApKHg1vY0 b0qjYaX14lq+IAcAAACtV2MtOJApR4fRr7wRQGDBQQBCAGwAbwBiAGIq Fg9RkaJHvrd+hWcWvWAHrVdjLTiQKUeH0a+8EUBgwUEAQwB1AHIAcgBlAG4A YwB5ADuWXBjXui5DjewhqU/ZAlQHrVdjLTiQKUeH0a+8EUBgwUEA RgBsAG8AYQB0ALUebN/3RIdPr0IpiSTofYAHrVdjLTiQKUeH0a+8 EUBgwUEAQgBvAG8AbABlAGEAbgD5qaGc0FW5Tqc3c1VtPZmyBwAAAK1X Yy04kClHh9GvvBFAYMFBAEwAbwBuAGcASQBuAHQAZQBnAGUAcgBxD4vg nhJFQ6Aq7X671TYKBwAAAK1XYy04kClHh9GvvBFAYMFBAE4AbwByAG0AYQBsAEkA bgB0AGUAZwBlAHIAX9xpVcH8oU6gEyK+SWM3TwcAAACtV2MtOJApR4fR r7wRQGDBQQBtAGUAbQBvAGJ0qbukBmpDkUIeUb4yTnEHrVdjLTiQ KUeH0a+8EUBgwUEARwBVAEkARAAADAUA AA== / od:tableProperty name=DisplayViewsOnSharePointSite type=2 value=1/ od:tableProperty name=TotalsRow type=1 value=0/ od:tableProperty name=FilterOnLoad type=1 value=0/ od:tableProperty name=OrderByOnLoad type=1 value=1/ od:tableProperty name=HideNewField type=1 value=0/ od:tableProperty name=BackTint type=6 value=100/ od:tableProperty name=BackShade type=6 value=100/ od:tableProperty name=ThemeFontIndex type=4 value=-1/ od:tableProperty name=AlternateBackThemeColorIndex type=4 value=-1/ od:tableProperty name=AlternateBackTint type=6 value=100/ od:tableProperty name=AlternateBackShade type=6 value=100/ od:tableProperty name=ReadOnlyWhenDisconnected type=1 value=0/ od:tableProperty name=DatasheetGridlinesThemeColorIndex type=4 value=-1/ od:tableProperty name=DatasheetForeThemeColorIndex type=4 value=-1/ /xsd:appinfo /xsd:annotation xsd:complexType xsd:sequence xsd:element name=ID minOccurs=1 od:jetType=autonumber od:sqlSType=int od:autoUnique=yes od:nonNullable=yes type=xsd:int xsd:annotation xsd:appinfo od:fieldProperty name=ColumnWidth type=3 value=-1/ od:fieldProperty name=ColumnOrder type=3 value=0/ od:fieldProperty name=ColumnHidden type=1 value=0/ od:fieldProperty name=GUID type=9 value=A6k+U2/GI0yY4L+fTzVLeQ== / od:fieldProperty name=TextAlign type=2
RE : RE : [fpc-pascal] Timezone information in a dataset datetimefield?- updated
That is also what most of the underlying databases do. They store everything in UTC and convert to local time. Only for those that support timezoned field types (fe. oracle timestamp_TZ) you can specify a timezone when entering data. MySQL fe. doesn't have support for this. Ludo Mmmm. For example, IIRC, Firebird just stores it as entered - no UTC conversion. Sensible developers on multi country Firebird db projects would probably store convert date/time info to UTC in the database. I'm talking about internal database storage. On the binary level. If you move a database to a system with a different timezone the dates display differently when stored internally in UTC. Ludo ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
RE : RE : [fpc-pascal] Timezone information in a dataset datetimefield?- updated
I realize having a local timezone option will result in a hornet's nest of adjustments for Daylight Saving Time etc... As per previous messages, why bother? It is not a requirement for importing data in ADO.NET. Ludo ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Ludo Brands wrote: 1. Is there any way of knowing what kind of timezone a datetime value should be in a FPC dataset? (I suppose not...) No, there is no way. It's always supposed to be 'local time'. That is also what most of the underlying databases do. They store everything in UTC and convert to local time. Only for those that support timezoned field types (fe. oracle timestamp_TZ) you can specify a timezone when entering data. MySQL fe. doesn't have support for this. Pretty much the same for PostgreSQL. I went through all this a few months ago, and while I didn't explore data entry I found that for data output I had to apply corrections myself although I could get properties of the current timezone from an internal table. In all cases the fundamental question is whether the OS has been installed with correct notification of where it's operating, and whether is has any mechanism to track what's going on if it's moved around. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 12:09, Mark Morgan Lloyd wrote: Pretty much the same for PostgreSQL. I went through all this a few months ago, and while I didn't explore data entry I found that for data output I had to apply corrections myself although I could get properties of the current timezone from an internal table. In all cases the fundamental question is whether the OS has been installed with correct notification of where it's operating, and whether is has any mechanism to track what's going on if it's moved around. Correct. Would you happen to have some cross platform code readily available that spits out either: 1. Offset from UTC for a certain (historical or future) date/time 2. Timezone abbreviation suitable for inclusion in my XML export (don't know yet what formats Access/ADO.Net/Excel/whatever needs) ... never hurts to ask, does it ;) Thanks, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?
On 25-7-2011 11:51, Ludo Brands wrote: 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. So you're saying that this: ADateTime2011-07-22T15:00:00+02:00/ADateTime can be simplified to this: ADateTime2011-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... 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. 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. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : RE : [fpc-pascal] Timezone information in a dataset datetimefield?- updated
On 25-7-2011 11:57, Ludo Brands wrote: That is also what most of the underlying databases do. They store everything in UTC and convert to local time. Only for those that support timezoned field types (fe. oracle timestamp_TZ) you can specify a timezone when entering data. MySQL fe. doesn't have support for this. Ludo Mmmm. For example, IIRC, Firebird just stores it as entered - no UTC conversion. Sensible developers on multi country Firebird db projects would probably store convert date/time info to UTC in the database. I'm talking about internal database storage. On the binary level. If you move a database to a system with a different timezone the dates display differently when stored internally in UTC. Ludo I understand. If I remember correctly, Firebird just stores date/time as some bits. AFAIR, it doesn't convert any given times to/from UTC when storing/retrieving times. I'm not willing to play around with timezones and Firebird right now, though ;)... ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Reinier Olislagers wrote: On 25-7-2011 12:09, Mark Morgan Lloyd wrote: Pretty much the same for PostgreSQL. I went through all this a few months ago, and while I didn't explore data entry I found that for data output I had to apply corrections myself although I could get properties of the current timezone from an internal table. In all cases the fundamental question is whether the OS has been installed with correct notification of where it's operating, and whether is has any mechanism to track what's going on if it's moved around. Correct. Would you happen to have some cross platform code readily available that spits out either: 1. Offset from UTC for a certain (historical or future) date/time 2. Timezone abbreviation suitable for inclusion in my XML export (don't know yet what formats Access/ADO.Net/Excel/whatever needs) ... never hurts to ask, does it ;) Indeed :-) Unfortunately not, since what I did was have a .ini-format configuration file specifying (the current system's) lat/long and basic timezone, and then used an SQL query to ask the database what it thought was going on from a standard table (i.e. whether DST is currently in operation). Using this it was trivial to plug a DST correction back into the code, and also to display the effective timezone name. I doubt any of this is portable since it makes a lot of assumptions about the server. I've made a point for the last few years of having all critical machines locked to GMT. I once found myself having to deal with three different timezones in a forensic problem, and once is quite enough. If GPS could be persuaded to cough up basic information about the operative timezone in a standardised form (i.e. you're in a country that uses WET) it would make things much easier. Or failing that if somebody had a comprehensive translation between lat/long and basic timezone info, i.e. this polygon is GMT, this polygon is WET and so on; unlike transition dates etc. this wouldn't change very often. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
RE : RE : [fpc-pascal] Timezone information in a dataset datetimefield?
So you're saying that this: ADateTime2011-07-22T15:00:00+02:00/ADateTime can be simplified to this: ADateTime2011-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. Ludo PS attached the XSD as exported by Visual Studio 2008 from test.mdb. testDataSet.xsd Description: XML document ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : RE : [fpc-pascal] Timezone information in a dataset datetimefield?
On 25-7-2011 13:31, Ludo Brands wrote: So you're saying that this: ADateTime2011-07-22T15:00:00+02:00/ADateTime can be simplified to this: ADateTime2011-07-22T15:00:00/ADateTime ? It does. It uses TZ to convert if specified but assumes local time if not specified. Tested this when importing access 2010 data. Ok, that simplifies things a lot. 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. 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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Download EmbWeb sources
In our previous episode, Lee Jenkins said: M:\lazarus_current\lazarus\fpc\2.5.1\bin\i386-win32fppkg install embweb The FPC Package tool encountered the following error: [embweb] Package fcl-web none is not available M:\lazarus_current\lazarus\fpc\2.5.1\bin\i386-win32fppkg list Name InstalledAvailable embweb -0.9.0-926 gecko-0.9.0-1504 jquery -0.9.0-842 lazmkunit-0.9.2-1 lnet -0.6.4-2534 webdesign-0.9.0-906 Looks like snapshot generation is not yet adapted to the fppkg model. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Download EmbWeb sources
On 7/25/2011 11:17 AM, Marco van de Voort wrote: In our previous episode, Lee Jenkins said: M:\lazarus_current\lazarus\fpc\2.5.1\bin\i386-win32fppkg install embweb The FPC Package tool encountered the following error: [embweb] Package fcl-webnone is not available M:\lazarus_current\lazarus\fpc\2.5.1\bin\i386-win32fppkg list Name InstalledAvailable embweb -0.9.0-926 gecko-0.9.0-1504 jquery -0.9.0-842 lazmkunit-0.9.2-1 lnet -0.6.4-2534 webdesign-0.9.0-906 Looks like snapshot generation is not yet adapted to the fppkg model. OK, thanks Marco. I wasn't sure what that error msg was trying to convey. Looks like fppkg will make life a lot easier in the long run. -- Warm Regards, Lee ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
RE : RE : RE : [fpc-pascal] Timezone information in adataset datetimefield?
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...
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
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Mark Morgan Lloyd wrote: If GPS could be persuaded to cough up basic information about the operative timezone in a standardised form (i.e. you're in a country that uses WET) it would make things much easier. Or failing that if somebody had a comprehensive translation between lat/long and basic timezone info, i.e. this polygon is GMT, this polygon is WET and so on; unlike transition dates etc. this wouldn't change very often. Link below looks directly relevant: it contains polygon data for each timezone, subject to knowing the current location. http://efele.net/maps/tz/world/ I've confirmed that files are accessible (i.e. not subscription-only) but not investigated the format of the content. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
[fpc-pascal] XML_XSD export was: Timezone information in a dataset datetimefield?
On 25-7-2011 17:37, 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... 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. 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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
RE : [fpc-pascal] XML_XSD export was: Timezone information in a datasetdatetimefield?
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. The ? was a typo of mine. I meant to confirm that multiple field indices work. 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! 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 ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Reinier Olislagers schrieb: I realize having a local timezone option will result in a hornet's nest of adjustments for Daylight Saving Time etc... I'll try and check to see if it is possible to specify the timezone description instead of the offset in the export datetime field. If so, I'll try and get timezone description from the OS... Timezone information is not enough to get reliable dates. Dependend on where dates origin from they can be wrong (wrong clock on computer, wrong time zone on computer, file times extracted from archives not storing time zone). Also, when calculating UTC time on a local (computer) there is an ambiguity for times 2:00h to 3:00h when daylight saving is invoked in spring. A time of 2:30h can be before or after having switched back the clock but a program cannot determine which case applies from the computer clock alone. And even if dates are correctly stored in UTC it is not easy to reliably get back the local time. Daylight savings were changed in the past (and may also change in the future). If you have a date/time of 2001-05-01 18:00 UTC it is not easy to predict what local time it was in time zone x. You would need a history of all daylight saving algorithms of the past and the future for all time zones (or even countries). Having time zones added (or storing it in UTC) is better than not doing this but it's still far away from being correct. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 18:23, Jürgen Hestermann wrote: Reinier Olislagers schrieb: ... If so, I'll try and get timezone description from the OS... Timezone information is not enough to get reliable dates. Dependend on where dates origin from they can be wrong (wrong clock on computer, wrong time zone on computer, file times extracted from archives not storing time zone). Also, when calculating UTC time on a local (computer) there is an ambiguity for times 2:00h to 3:00h when daylight saving is invoked in spring. A time of 2:30h can be before or after having switched back the clock but a program cannot determine which case applies from the computer clock alone. Yep, I know, depressing... And even if dates are correctly stored in UTC it is not easy to reliably get back the local time. Daylight savings were changed in the past (and may also change in the future). If you have a date/time of 2001-05-01 18:00 UTC it is not easy to predict what local time it was in time zone x. You would need a history of all daylight saving algorithms of the past and the future for all time zones (or even countries). Agreed. Having time zones added (or storing it in UTC) is better than not doing this but it's still far away from being correct. Fortunately Ludo Brands showed me that storing timezone info in the XML file I was exporting (for ADO.Net) was not necessary. I just tested it as well... I suppose it's best left to the user to interpret what a date/time means exactly, just as he will have to do with currency fields without any currency indicator... However, what I can do is add an XML comment indicating what offset/timezone the computer is currently using. This may help interpretation... Thanks for the detailed explanation, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
RE : [fpc-pascal] XML_XSD export was: Timezone information in a datasetdatetimefield?
In the meantime, I've finished the ADO.Net export functionality.
I'd appreciate further test reports!
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
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On Mon, Jul 25, 2011 at 11:53 AM, Mark Morgan Lloyd markmll.fpc-pas...@telemetry.co.uk wrote: Link below looks directly relevant: it contains polygon data for each timezone, subject to knowing the current location. http://efele.net/maps/tz/world/ I've confirmed that files are accessible (i.e. not subscription-only) but not investigated the format of the content. Location may be off too. All of the online location reporting tool lists my work machine as being in TX, even though I'm in TN, since all our networks are routed back to the home office before hitting the cloud. Jeff. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re[2]: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Hello FPC-Pascal, Monday, July 25, 2011, 12:54:13 PM, you wrote: RO Correct. Would you happen to have some cross platform code readily RO available that spits out either: RO 1. Offset from UTC for a certain (historical or future) date/time RO 2. Timezone abbreviation suitable for inclusion in my XML export (don't RO know yet what formats Access/ADO.Net/Excel/whatever needs) RO ... never hurts to ask, does it ;) Maybe this could help: http://wiki.lazarus.freepascal.org/PascalTZ -- Best regards, José ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re[2]: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Hello FPC-Pascal, Monday, July 25, 2011, 6:23:21 PM, you wrote: JH And even if dates are correctly stored in UTC it is not easy to reliably JH get back the local time. Daylight savings were changed in the past JH (and may also change in the future). If you have a date/time of JH 2001-05-01 18:00 UTC it is not easy to predict what local time it was in JH time zone x. You would need a history of all daylight saving algorithms JH of the past and the future for all time zones (or even countries). This already exists, but future settings are impossible to predict, so a local time in the future is a no-no and should be avoided as much as possible. -- Best regards, José ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
DaWorm wrote: On Mon, Jul 25, 2011 at 11:53 AM, Mark Morgan Lloyd markmll.fpc-pas...@telemetry.co.uk wrote: Link below looks directly relevant: it contains polygon data for each timezone, subject to knowing the current location. http://efele.net/maps/tz/world/ I've confirmed that files are accessible (i.e. not subscription-only) but not investigated the format of the content. Location may be off too. All of the online location reporting tool lists my work machine as being in TX, even though I'm in TN, since all our networks are routed back to the home office before hitting the cloud. Yes, I noticed several ways of getting timezone info knowing a named location (I'm reluctant to use the terms city or town here, since they mean different things in different countries) and they obviously depend on the apparent location being relevant. The files I found were the only source of raw geographical data which could be used with e.g. a GPS receiver or manual lat/long entry (on or over land). Since they appear to contain timezones in the form Europe/London they should be compatible with e.g. Postgres's internal tables that describe named timezones, although I haven't investigated whether there's a one-to-one mapping. I assume that the Postgres files are GPL, so could be extracted for other use. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
[fpc-pascal] Re: Library for network calculation
Some time ago someone asked for a library able to do network calculations. Here is something that might evolve into such library : Uses Classes, SysUtils, StrUtils; Type TNetworkIP = Record Case Mode : Boolean Of True : (IP1, IP2, IP3, IP4 : Byte); False : (IP : LongWord); End; Function StringToIP(aIP : AnsiString): TNetworkIP; Begin Result.Mode := True; Result.IP4 := StrToInt(Copy2SymbDel(aIP, '.')); Result.IP3 := StrToInt(Copy2SymbDel(aIP, '.')); Result.IP2 := StrToInt(Copy2SymbDel(aIP, '.')); Result.IP1 := StrToInt(Copy2SymbDel(aIP, '.')); End; Function IPToString(aIP : TNetworkIP): String; Begin aIP.Mode := True; Result := IntToStr(aIP.IP4) + '.' + IntToStr(aIP.IP3) + '.' + IntToStr(aIP.IP2) + '.' + IntToStr(aIP.IP1); End; Function IPToBits(aIP : TNetworkIP): String; Begin aIP.Mode := True; Result := BinStr(aIP.IP4, 8) + BinStr(aIP.IP3, 8) + BinStr(aIP.IP2, 8) + BinStr(aIP.IP1, 8); End; Function NetMaskToHostMask(NetMask : TNetworkIP): TNetworkIP; Begin Result.Mode := False; NetMask.Mode := False; Result.IP := NetMask.IP Xor %; End; Function HostMaskToNetMask(HostMask : TNetworkIP): TNetworkIP; Begin Result.Mode := False; HostMask.Mode := False; Result.IP := HostMask.IP Xor %; End; Function CIDRToNetMask(Bits : Byte): TNetworkIP; Var CurBit : Byte; HostMask : TNetworkIP; Begin HostMask.Mode := False; HostMask.IP := 0; For CurBit := 1 To 32 - Bits Do HostMask.IP := HostMask.IP + (1 Shl (CurBit - 1)); Result := HostMaskToNetMask(HostMask); End; Function NetMaskToCIDR(NetMask : TNetworkIP): Byte; Var CurBit : Byte; Begin Result := 0; For CurBit := 32 DownTo 1 Do If (NetMask.IP And (1 Shl (CurBit - 1))) = (1 Shl (CurBit - 1)) Then Result := CurBit; End; Function BroadcastOf(IP, NetMask : TNetworkIP): TNetworkIP; Var HostMask : TNetworkIP; Begin HostMask := NetMaskToHostMask(NetMask); Result.Mode := False; IP.Mode := False; HostMask.Mode := False; Result.IP := IP.IP Or HostMask.IP; End; Function NetworkOf(IP, NetMask : TNetworkIP): TNetworkIP; Begin IP.Mode := False; NetMask.Mode := False; Result.Mode := False; Result.IP := IP.IP And NetMask.IP; End; Function HostCount(NetMask : TNetworkIP): LongWord; Var HostMask : TNetworkIP; Begin HostMask := NetMaskToHostMask(NetMask); HostMask.Mode := False; Result := HostMask.IP + 1; End; Function SubNetCount(DefMask, Mask : TNetworkIP): LongWord; Var DefHosts, Hosts : LongWord; Begin DefHosts := HostCount(DefMask); Hosts := HostCount(Mask); Result := DefHosts Div Hosts; End; Function IPToRange(IP, NetMask : TNetworkIP): String; Var Net, Broad : TNetworkIP; Begin Net := NetworkOf(IP, NetMask); Broad := BroadCastOf(IP, NetMask); Net.Mode := False; Broad.Mode := False; Net.IP := Net.IP + 1; Broad.IP := Broad.IP - 1; Result := IPToString(Net) + '-' + IPToString(Broad); End; Function IncSubNet(BaseIP, Mask : TNetworkIP): TNetworkIP; Var HostMask : TNetworkIP; Begin HostMask := NetMaskToHostMask(Mask); HostMask.Mode := False; HostMask.IP := HostMask.IP + 1; BaseIP.Mode := False; Result.Mode := False; Result.IP := BaseIP.IP + HostMask.IP; End; Function HostCountToCIDR(Hosts : LongWord): Byte; Var CurBit : Byte; HostMask : TNetworkIP; Last1Bit : Byte; Begin HostMask.Mode := False; HostMask.IP := Hosts; Last1Bit := 0; For CurBit := 1 To 32 Do If (HostMask.IP And (1 Shl (CurBit - 1))) = (1 Shl (CurBit - 1)) Then Last1Bit := CurBit; Result := 32 - Last1Bit; End; Function HostCountToNetMask(Hosts : LongWord): TNetworkIP; Begin Result := CIDRToNetMask(HostCountToCIDR(Hosts)); End; Function IsMemberOfSubNet(IP, SubNet : TNetworkIP): Boolean; Begin IP.Mode := False; Subnet.Mode := False; Result := IP.IP And Subnet.IP = Subnet.IP; End; Begin WriteLn(IsMemberOfSubNet(StringToIP(ParamStr(1)), StringToIP(Paramstr(2; End. Thats old code, i dont remmember what i was thinking when i wrote that. It surely could be improved (reformated as a unit, for example). Could this be added to the FCL ? ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
[fpc-pascal] Re: Library for network calculation
Jorge Aldo G. de F. Junior jagf...@gmail.com wrote in message news:CAAHHabS9aUe9gwyNjkve-XVXsRyf2UPsArh6=fsdpgokugj...@mail.gmail.com... Some time ago someone asked for a library able to do network calculations. Here is something that might evolve into such library : SNIP Function NetMaskToHostMask(NetMask : TNetworkIP): TNetworkIP; Begin Result.Mode := False; NetMask.Mode := False; Result.IP := NetMask.IP Xor %; End; SNIP I didn't know that freepascal handled binary formatted numbers?!? % cheers, Paul ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 7/25/2011 15:53, José Mejuto wrote: Hello FPC-Pascal, Monday, July 25, 2011, 6:23:21 PM, you wrote: JH And even if dates are correctly stored in UTC it is not easy to reliably JH get back the local time. Daylight savings were changed in the past JH (and may also change in the future). If you have a date/time of JH 2001-05-01 18:00 UTC it is not easy to predict what local time it was in JH time zone x. You would need a history of all daylight saving algorithms JH of the past and the future for all time zones (or even countries). This already exists, but future settings are impossible to predict, so a local time in the future is a no-no and should be avoided as much as possible. while i agree, i feel that it is also important to point out that one may easily perform future date calculations based on several different formulas with the understanding that they may not be accurate when that date arrives if the formula changes for some reason... the recent examples given are the US daylight saving time changes (note: NOT daylight savings [specifically note there is NO 's' on daylightsaving]) but it is easy enough to work with this... i've done it for years with TP3 and TP6 code... easier in recent years but still... i've formulas from somewhere that convert between numerous calendars... i believe i even have one for the myan calendar but like most of what i have, it is all older TP3-6 code and it is also a matter if me finding it in my library :? for general purposes, storing in local time is OK but it really needs to include the timezone info for the local time or it needs to be converted to UTC so that multi-timezone apps can properly correlate the actual events on a uniform timeline... this can be a very critical point in some applications... PoS apps, being one... database transactions being another one... ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
