[Openroad-users] Problems Importing XML Dates

[Bloomfield, Martin (PSD)]

Kim and others
 
Thanks for this suggestion.  I'm not having any other problems.  Fortunately we use "full stops" as our decimal markers.
 
I've discovered that by changing the II_DATE_FORMAT from MULTINATIONAL4 to SWEDEN, then the dates get imported correctly as Sweden's date format is yyyy-mm-dd.  I, therefore, tried using the CurSession.SetEnv method to dynamically change the date format to SWEDEN in my import procedure.  Unfortunately, despite a GetEnv claiming that the variable has changed to Sweden, the dates are not getting imported.
 
Has anyone used the SetEnv method to change environment variables "on the fly", and if so, how do you get the current session to pick up the change.  I was hoping to be able to use this trick rather than having to manually process the dates.
 

Martin Bloomfield.
_______________________
Application Developer & Database Administrator
IT Branch,
Pesticides Safety Directorate,
YORK 
Email: martin.bloomfield at psd.defra.gsi.gov.uk <mailto:martin.bloomfield at psd.defra.gsi.gov.uk> 
Website: www.pesticides.gov.uk  

-----Original Message-----
From: Kim Ginnerup [mailto:kgi at bording.dk] 
Sent: Friday, November 17, 2006 5:18 PM
To: International OpenROAD Users
Cc: Bloomfield, Martin (PSD)
Subject: SV: Problems Importing XML Dates



	Are you only having date problems? 

	In Denmark we also struggle with decimal point as comma.

	I think the easiest way, is to import dates into varchar attributes, and then use a wrapped date_part later in the process.

	I think it is easier and faster than parsing the whole XML-file.

	I think you will run into the same problem, when you output to XML.

	I'm not aware if OpenROAD has any special date conversion tools for the XML.

	I think that the OpenROAD DateObject should have some extra methods handling date conversions.

	Like having 

	a date to varchar that is not dependent on II_DATE_FORMAT

	to and from xml date

	

	Maybe that is a community project when OpenROAD gets Open Source ;-)

	Kim

	

	

	_____________________________________________
	Fra: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] På vegne af Bloomfield, Martin (PSD)
	Sendt: 17. november 2006 17:28
	Til: Openroad-Users
	Emne: Problems Importing XML Dates

	OpenROAD Experts,

	With much assistance form Kim Ginnerup, I have now managed to create a SCP procedure to import an XML file into OpenROAD.  However, I am having problems importing date elements. In the XML file these are defined in the format "yyyy-mm-dd" (the only format allowed by XML).  On our server & clients we use the date format of multinational4 and display dates in the format dd/mm/yyyy.

	When I try to import the XML date, I get the following error in the trace window:

	E_WT0029 Error at line 76 of ConvertToDate.

	E_EO0022 A dynamic expression ('date ( _XMLvar )') could not be evaluated.

	    A getvalue, setvalue, or getfield method failed because the Windows/4GL

	    interpreter detected a fatal error when it tried to evaluate the dynamic

	    expression.  A subsequent message will specify the error.

	E_WT0011 4GL expression evaluation error.

	    An error occurred in the evaluation of a 4GL expression.  The results

	    will be undefined.

	Fri Nov 17 16:15:38 2006 E_US10D0_4304  2006 is not a valid month for a date column.

	ERROR IN GSCP Results: XML IN conversion failed.  Error converting 2006-11-15 

	into a date.

	

	I have tried changing the date format in the XML file, but this causes it to be invalid.  Has anyone else managed to import XML (ISO format) dates into OpenROAD using the XMLIN parameter?  I assume that the ConvertToDate procedure(?) is a built in procedure called as part of OpenROAD's conversion mechanism.

	We are running OpenROAD 4.1 SP2 on our clients.  I have tried to install patch 10464, but this breaks our vnode definitions, did not fix the problem and resulted in a stream of Memory allocation errors in the trace window.

	Thanks in advance,

	Martin Bloomfield.

	____________________________________________

	Application Developer & Database Administrator

	IT Branch,

	Pesticides Safety Directorate

	YORK

	Email: martin.bloomfield at psd.defra.gsi.gov.uk <mailto:martin.bloomfield at psd.defra.gsi.gov.uk> 

	Website: www.pesticides.gov.uk <http://www.pesticides.gov.uk> 

	 <<Fil: ATT09511.txt >> 

Department for Environment, Food and Rural Affairs (Defra)

This email and any attachments is intended for the named recipient only.
If you have received it in error you have no authority to use, disclose,
store or copy any of its contents and you should destroy it and inform
the sender.
Whilst this email and associated attachments will have been checked
for known viruses whilst within Defra systems we can accept no
responsibility once it has left our systems.
Communications on Defra's computer systems may be monitored and/or
recorded to secure the effective operation of the system and for other
lawful purposes.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://peerlessit.com/pipermail/openroad-users/attachments/20061123/1a3483a4/attachment.html