none
Functoid to convert to oracle date error RRS feed

  • Question

  • Hello,

    I have a schema that polls datetime data from sqlserver and another schema that receives that piece of information to store it in Oracle. Because of the difference in datetime format between sql and oracle, I had a scripting functoid to convert datetime in sql to dd-MMM-yy that is used by Oracle.

    The following error was encountered:

    xlang/s engine event log entry: Uncaught exception (see the 'inner exception' below) has suspended an instance of service 'TestOrchestration00010.Orchestration_1(a3136226-b9f6-d0d7-d738-b76eb88f596a)'. The service instance will remain suspended until administratively resumed or terminated. If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception. InstanceId: f4e58bb6-f812-4808-9c1c-191004182d0d Shape name: ConstructMessage_1 ShapeId: 7bc82734-816d-4ced-b6e4-95677dd0bca8 Exception thrown from: segment 1, progress 6 Inner exception: Error encountered while executing the transform TestOrchestration00010.Transform_1. Error:Transformation failed.. Exception type: XTransformationFailureException Source: Microsoft.XLANGs.Engine Target Site: Void ApplyInMemoryTransform(System.Type, Microsoft.XLANGs.RuntimeTypes.TransformMetaData, System.Object[], System.IO.Stream[]) The following is a stack trace that identifies the location where the exception occured at Microsoft.XLANGs.Core.Service.ApplyInMemoryTransform(Type mapRef, TransformMetaData trfMetaData, Object[] outParams, Stream[] inStreams) at Microsoft.XLANGs.Core.Service.ApplyTransform(Type mapRef, Object[] outParams, Object[] inParams) at TestOrchestration00010.Orchestration_1.segment1(StopConditions stopOn) at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment(Segment s, StopConditions stopCond, Exception& exp) Additional error information: Function 'userCSharp:MyConcat()' has failed. Exception type: XPathException Source: System.Xml Target Site: System.Object Evaluate(System.Xml.XPath.XPathNodeIterator) The following is a stack trace that identifies the location where the exception occured at MS.Internal.Xml.XPath.FunctionQuery.Evaluate(XPathNodeIterator nodeIterator) at System.Xml.Xsl.XsltOld.Processor.RunQuery(ActionFrame context, Int32 key) at System.Xml.Xsl.XsltOld.VariableAction.Execute(Processor processor, ActionFrame frame) at System.Xml.Xsl.XsltOld.ActionFrame.Execute(Processor processor) at System.Xml.Xsl.XsltOld.Processor.Execute() at System.Xml.Xsl.XsltOld.Processor.Execute(Stream stream) at System.Xml.Xsl.XslTransform.Transform(XPathNavigator input, XsltArgumentList args, Stream output, XmlResolver resolver) at System.Xml.Xsl.XslTransform.Transform(IXPathNavigable input, XsltArgumentList args, Stream output, XmlResolver resolver) at Microsoft.XLANGs.Core.Service.ApplyInMemoryTransform(Type mapRef, TransformMetaData trfMetaData, Object[] outParams, Stream[] inStreams) Additional error information: Object of type 'System.String' cannot be converted to type 'System.DateTime'. Exception type: ArgumentException Source: mscorlib Target Site: System.Object TryChangeType(System.Object, System.Reflection.Binder, System.Globalization.CultureInfo, Boolean) The following is a stack trace that identifies the location where the exception occured at System.RuntimeType.TryChangeType(Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast) at System.RuntimeType.CheckValue(Object value, Binder binder, CultureInfo culture, BindingFlags invokeAttr) at System.Reflection.MethodBase.CheckArguments(Object[] parameters, Binder binder, BindingFlags invokeAttr, CultureInfo culture, Signature sig) at System.Reflection.RuntimeMethodInfo.InvokeArgumentsCheck(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) at System.Xml.Xsl.XsltOld.XsltCompileContext.FuncExtension.Invoke(XsltContext xsltContext, Object[] args, XPathNavigator docContext) at MS.Internal.Xml.XPath.FunctionQuery.Evaluate(XPathNodeIterator nodeIterator)

    This is the inline C# script inside the functoid:

    public string MyConcat(DateTime d)
    {
     return d.ToString("dd-MMM-yy");
    }

    Please advice what is the cause for the error above and how to fix the issue. Thanks!

    Please note, the structures of the table in SQL and Oracle databases could not be changed. My functoid needs to be able to do the necessity for Oracle to understand its format... 



    • Edited by FrankTo Friday, March 14, 2014 2:07 PM
    Friday, March 14, 2014 2:03 PM

Answers

  • I mean the element content should match the Xml data type and the Xml format for a date (no time element) is yyyy-MM-dd.

    So you can try some variation of this code:

    public static String FormatDate(string inputDate, string inputFormat)
    {
        DateTime parsedDate;
        if (DateTime.TryParse(inputDate, out parsedDate))
        {
            return parsedDate.ToString("yyyy-MM-dd");
        }
        return null;
    }


    • Edited by Johns-305MVP Monday, March 17, 2014 10:12 PM completeness
    • Marked as answer by FrankTo Tuesday, March 18, 2014 12:23 PM
    Monday, March 17, 2014 10:11 PM

All replies

  • You can attach Visual Studio to the BizTalk Host process, BTSNTSvs[64].exe, and it will break at the error.  That will give you much more information on what's going on.

    However, if both fields/paramaters in SQL Server and Oracle are DateTime, you shouldn't have to convert at all.  The Adapters will handle the conversion to/from xs:dateTime automatically.

    If one side is string, you'll have to Parse or Format accordingly.  In this case, the problem is the source field is apparently a String:

    "Object of type 'System.String' cannot be converted to type 'System.DateTime'. Exception type: ArgumentException"

    So, you'll have to use DateTime.Parse, or even better DateTime.TryParse, to get a DateTime instance.  Then, if the schema field is xs:dateTime, use myDate.ToString("o").  If the field is String and Oracle parses the date, use your .ToString("dd-MMM-yy") or other compatible format.

    Friday, March 14, 2014 2:45 PM
  • Hi Boatseller,

    Thank you again for your response. I wonder why BizTalk thinks that the field is a string. It is a datetime field in sql database. But when I looked at the message content, I saw the date being transferred as: "2014-08-07T00:00:00Z".

    Would this be an issue? I think it is the culprit and I am not really sure how to make it transfer the date only. This may be the part that choked BizTalk.

    <TypedPolling xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/TEST_SQL_ORCH_00010">
    < TypedPollingResultSet0>
    < TypedPollingResultSet0>
    < RotationID>D4-2015CO1</RotationID>
    < Year>2015</Year>
    < Class>D4</Class>
    < Rotation>CROSSOVER</Rotation>
    < FromDate>2014-08-07T00:00:00Z</FromDate>
    < ToDate>2015-03-06T00:00:00Z</ToDate>
    < NumberOfConsecutiveSession>1</NumberOfConsecutiveSession>
    < AmOrPm>1</AmOrPm>
    < DayOfWeek>5</DayOfWeek>
    < /TypedPollingResultSet0>
    < /TypedPolling>


    • Edited by FrankTo Friday, March 14, 2014 6:41 PM
    Friday, March 14, 2014 6:31 PM
  • Unfortunately, that's one of the behaviors of the generated XSLT, that the source field is passed as a String so if you have to reformat, you have to Parse it first.

    Those are valid Xml DateTime formats.  Have you tried just mapping it directly?  If the Oracle field is also DateTime, you shouldn't have to convert.

    Friday, March 14, 2014 7:29 PM
  • Oracle default is dd-MMM-yy. So, what I have to do is get the string and reformat it to dd-MMM-yy to fix the issue, correct?

    Friday, March 14, 2014 8:29 PM
  • No, I'm pretty sure the WCF Oracle Adapter will properly handle the conversion from xs:dateTime to the Oracle DateTime format.

    So, as long as the format you see in the Xml is a valid xs:dateTime, which "2014-08-07T00:00:00Z" is, you shouldn't have to do any conversion at all since technically, it's a datetime Type throughout.

    Friday, March 14, 2014 9:21 PM
  • BoatSeller,

    I finally was able to duplicate the original error if not using functoid:

    SEND_PORT_TESTORCHESTRATION_00010

         

    oracledb://OracleServer/?PollingId=TEST_ORCHESTRATION_ORACLE_00010

         

    Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Value for the field "From" is invalid. DateTime.Kind must be DateTimeKind.Unspecified. Ensure that there is no TimeZone or TimeZoneOffset contained in the DateTime value. Server stack trace: at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

    Do you think this was caused by WCF oracle adapter not properly translating sql datetime to oracle date or do you think it was something else? How do I get around this error?
    Monday, March 17, 2014 6:57 PM
  • Maybe.  Is the Oracle type just Date and not DateTime?

    I would first try the Xml Date format which is "yyyy-MM-dd".

    Monday, March 17, 2014 7:19 PM
  • That is what I thought, conversion issue. Could you please elaborate regarding your comment: try the xml date? Do you mean having a functoid doing conversion from sql datetime to yyyy-MM-dd?
    Monday, March 17, 2014 8:18 PM
  • I mean the element content should match the Xml data type and the Xml format for a date (no time element) is yyyy-MM-dd.

    So you can try some variation of this code:

    public static String FormatDate(string inputDate, string inputFormat)
    {
        DateTime parsedDate;
        if (DateTime.TryParse(inputDate, out parsedDate))
        {
            return parsedDate.ToString("yyyy-MM-dd");
        }
        return null;
    }


    • Edited by Johns-305MVP Monday, March 17, 2014 10:12 PM completeness
    • Marked as answer by FrankTo Tuesday, March 18, 2014 12:23 PM
    Monday, March 17, 2014 10:11 PM