New post
Question
· Oct 16, 2019

Caché Cannot convert ODBC Time

#Caché

I am integrating with Front End using ODBC to the Caché tables.

I have a property of type %Library.Time in one of the Caché classes FrontEnd which is in C#, it is not possible in C# to represent only a Time type, it always has a date as well (DateTime type, no Time only in C#), but that is fine, should not be a problem, so for Time the date simply defaults to 1900/01/01. 

Now when this is sent to Caché it does not like it and I would like to understand why because this is causing us not to be able to use a Caché property of type TIME ever.

This is the error from Caché

Any ideas what can be done to solves this so that we can use properties of type Time in our classes because we need this in some cases?

Thank you in advance.

Discussion (3)0
Log in or sign up to continue
Kevin Chan · Oct 16, 2019

Hello Elize,

Based on the error that you are receiving, it seems that you might be executing a direct SQL statement instead of a parameterized statement. This means that your DateTime object (new DateTime(1900, 1, 1, 12, 03, 30) is being converted to a String and this, in effect, bypasses our ODBC driver's handling of specific nuances regarding temporal types.  

%Library.Time expects either a proper time string (denoted by the regex /(\d{2}:){2}\d{2}(.\d*)?/) or an appropriate $HOROLOG value and for this reason, you get SQL error -147.

Nonetheless, there are several solutions you can go about this:

  • If you cannot change to a parameterized statement nor edit the code, but can change the contents of the direct SQL statement
    • INSERT INTO tablename VALUES(CAST({ ts '1900-01-01 12:03:30' } AS TIME))
  • If you cannot change to a parameterized statement but can edit code
    • Change the DateTime.toString() method to DateTime.toString("T") such that it returns the time string only
  • If you can change to a parameterized statement, be advised that DateTime is not a valid object to bind to a %Time column. Per Microsoft's document, you should be using a TimeSpan object to store time.​​ This is easily achieved by reference DateTime's TimeOfDay property.
1 0
Paul Hegel · Oct 16, 2019

You can convert the DateTime to a Time String and send that in the insert or update strings.

Here is the Cache Class used in my example:

Class User.TestTimeData Extends %Persistent
{

Property TimeFormat As %String [ Required ];

Property TimeTest As %Time [ Required ];
}

Here is the C# .Net Core Console App code example:
using System;
using System.Data.Odbc;

namespace TestCacheTimeType
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            DateTime dateTime = DateTime.MinValue.AddHours(22).AddMinutes(10);

            string timeLong = dateTime.ToLongTimeString();
            InsertRecord("ToLongTimeString", timeLong);

            string timeShort = dateTime.ToShortTimeString();
            InsertRecord("ToShortTimeString", timeShort);

            string timeFormatted = dateTime.ToString("HH:mm:ss");
            InsertRecord("ToString"HH:mm:ss)"", timeFormatted);

            Console.WriteLine("Press any key to exit....");
            Console.Read();
        
        }

        static void InsertRecord(string TimeFormat, string TimeToInsert)
        {
            Console.WriteLine("{0}: {1}", TimeFormat, TimeToInsert);
            using (OdbcConnection connection = new OdbcConnection("DSN=IRISHealth-WebDev; UID=; PWD =; "))
            {
                string insertSQL = "Insert into SQLUser.TestTimeData(TimeFormat, TimeTest) values('" + TimeFormat + "', '" + TimeToInsert + "');";
                // inserts a new row in the source table.
                OdbcCommand command = new OdbcCommand(insertSQL, connection);
                // Open the connection and execute the insert command.
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

            }

        }
    }
}
0 0