Do you support INTERVAL types in PostgreSQL? The default mapping maps INTERVAL to String while I was expecting it to map to TimeSpan.

If this is not supported, do you have any suggestions on how I can store and use an interval (duration).

Thanks, Matt

We only support the Npgsql types, and interval isn't one of them (as far as I know)

I think Npgsql lists Intervals as Partially supported.

I also use CoreLab.PostgreSQLDirect which fully supports INTERVALs. Do you have any plans to add support for CoreLab's driver...I noticed you supported it for MySQL?

slipserve wrote:

I think Npgsql lists Intervals as Partially supported.

I also use CoreLab.PostgreSQLDirect which fully supports INTERVALs. Do you have any plans to add support for CoreLab's driver...I noticed you supported it for MySQL?

With all the joy we had with CoreLab's mysql provider, I don't think we'll ever move to their postgresql provider.

"Partially supported", that's not really hopeful. . My docs (which might be outdated) of npgsql don't list Interval. (also the docs on the website don't, but these are also from 2006). I see they're working on v2, we'll move to v2 if they release a stable version.

In the meantime, you might be able to store the timespan differently, e.g. in ticks. This is an Int64 value, and you can then create a typeconverter for LLBLGen Pro (very simple class, check the sourcecode of our numeric->bool converter in the SDK to get started) to convert Int64 to timespan and back. You then can set the entity field to a timespan instance and the conversion to Int64 and back is done by llblgen pro through your typeconverter

Thanks for the quick response! I'll try that approach.

BTW - LLBLGenPro is a great product! It's already saved me a LOT of time and money!

slipserve wrote:

Thanks for the quick response! I'll try that approach.

BTW - LLBLGenPro is a great product! It's already saved me a LOT of time and money!

Thanks!

Hi,

Sorry to bump this thread but I think my exact problem is described here

It seems like you moved to Npgsql 2 some time ago, but the conversion from interval to timespan is not done by LLBLGen (We're using 2.6) at the moment. Do you intend to add this anytime soon ?

Thanks in advance

Haaz

Unless you explain YOUR problem in detail, I'm afraid I can't do much for you. So what's the table look like, what problems do you run into, which versions of the libraries are you using etc.

Hi Otis,

Sorry for not being clear, I should have started a new thread.

Thank you for your answer, here is exactly what I have :

We are using EntrepriseDB 8.3 (which is based on PostGRE 8.2). The table definition is as follows :

CREATE TABLE globaldata."QueueItem"
(
  "QueueItemID" integer NOT NULL DEFAULT nextval('globaldata."QueueItem_QueueItemID_seq"'::regclass),
  "QueueID" integer NOT NULL,
  "CreationDate" timestamp without time zone NOT NULL,
  "MaximumDateOfCompletion" timestamp without time zone,
  "Priority" integer NOT NULL,
  "CreatorID" integer NOT NULL,
  "ProjectID" integer,
  "OwnerPersonID" integer,
  "OwnerGroupID" integer,
  "CompletedByPersonID" integer,
  "LastUpdated" timestamp without time zone NOT NULL,
  "ElapsedTime" interval,
  "CompletionDate" timestamp without time zone,
  "StatusValueID" integer NOT NULL,
  "ParentQueueItemID" integer,
  CONSTRAINT "QueueItem_PK" PRIMARY KEY ("QueueItemID"),
  CONSTRAINT "ItemBelongsToQueue" FOREIGN KEY ("QueueID")
      REFERENCES globaldata."Queue" ("QueueID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "ParentQueueItem" FOREIGN KEY ("ParentQueueItemID")
      REFERENCES globaldata."QueueItem" ("QueueItemID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "ProjectQueueItem" FOREIGN KEY ("ProjectID")
      REFERENCES globaldata."Project" ("ProjectID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "QueueItemAssignedTo" FOREIGN KEY ("OwnerPersonID")
      REFERENCES globaldata."Person" ("PersonID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "QueueItemAssignedToGroup" FOREIGN KEY ("OwnerGroupID")
      REFERENCES globaldata."OrganizationalGroup" ("OrganizationalGroupID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "QueueItemCompletedBy" FOREIGN KEY ("CompletedByPersonID")
      REFERENCES globaldata."Person" ("PersonID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "QueueItemCreator" FOREIGN KEY ("CreatorID")
      REFERENCES globaldata."Person" ("PersonID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "QueueItemStatus" FOREIGN KEY ("StatusValueID")
      REFERENCES globaldata."StatusValue" ("StatusValueID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

The "ElapsedTime" column is an "interval" type in PostGRE, which is mapped by Npgsql to a TimeSpan (NpgsqlInterval) object in C# (http://npgsql.projects.postgresql.org/docs/manual/UserManual.html).

LLBLGen identifies ElapsedTime as a Varchar(10485760) instead of an Interval and thus maps this to a System.String type instead of a TimeSpan (Please see attached picture).

We are using a licensed LLBLGen 2.6 Final, libraries version is 2.6.0.0

Please let me know if you need more information.

With kind regards, Haaz

the driver has no support for 'interval', so it falls back onto the default (varchar).

I'll look into adding this to the driver. Will get back to you on this tomorrow (thursday)

Thanks a lot for looking into this, this is much appreciated.

Please let me know if I can help in any way.

Haaz wrote:

Thanks a lot for looking into this, this is much appreciated.

Please let me know if I can help in any way.

Could you test the attached driver update? You should refresh your project and re-generate the code to get field to change to interval / Timespan.

Hi Otis,

Sure, I will test this right now and let you know the outcome.

OK I replaced the driver , refreshed the catalog and regenerated my project. LLBLGen correctly identified the field as an Interval and mapped this to a timespan.

I now try to use it with this simple code :

QueueItemEntity qie = new QueueItemEntity(1);
            DataAccessAdapter da = new DataAccessAdapter();
            da.FetchEntity(qie);
            System.Console.WriteLine("I am QueueItem number {0} and my Elapsed Time now is {1}", qie.QueueItemId, qie.ElapsedTime);

This fails at runtime with an exception on the following line (in QueueItemEntity.cs):

get { return (Nullable<System.TimeSpan>)GetValue((int)QueueItemFieldIndex.ElapsedTime, false); }

Exception details follow :

System.InvalidCastException was unhandled
  Message="Specified cast is not valid."
  Source="BI.Biopacs.Core.Data"
  StackTrace:
       at BI.Biopacs.Core.Data.EntityClasses.QueueItemEntity.get_ElapsedTime() in X:\BioPACS\BI\Biopacs\Core\Data\DatabaseGeneric\EntityClasses\QueueItemEntity.cs:line 1861
       at Bi.Biopacs.Core.Data.Testing.Program.Main(String[] args) in X:\BioPACS\BI\Biopacs\Core\Data\Testing\Bi.Core.Data.Testing\Program.cs:line 32
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Hmm. Could you see what type the value has right before the cast (in the property getter) ? Looks like the npgsql provider doesn't return the value as a Timespan....

btw, which npgsql provider assembly version are you using?

The value is in a 'NpgsqlInterval' instance, not a Timespan....

(2.0.6). This is very unfortunate, as that would make the entity code require a reference to the npgsql assembly.

You can work around this by creating a type converter for NpgsqlInterval to System.Timestamp and apply it to that field. A type converter takes just a few lines. If you don't get it to work, please let me know.

I find it rather odd that npgsql returns the value in an assembly specific type when one calls GetValues() on the datareader, especially since it seems Timespan is enough to contain the values.

I'll try to get an answer from the Npgsql team why they return an Interval type as a native npgsql type instead of Timespan.

I'm just back from lunch and was starting troubleshooting this.

Thanks for the hint, I'll try to have it running with a type converter for the time being. I also wonder why they changed their logic only for this specific type ...

Thanks again for your help Otis, I'll post here if I run into any trouble with the type converter.

Kind regards, Manu

Ok

I see they implemented an implicit cast operator, but as the value is read from the datareader in object form, casting it in code doesn't call the implicit cast operator it seems, it always casts to the type specified (System.Timespan) which thus isn't going to work... Type converter sourcecode is available in the SDK (the bool converter for numeric fields).

I just had a look at their npgsql.Interval, it contains a "Time" property which is a System.Timespan and is filled correctly from the Database. Most other properties seem only to be wrappers to the Time.XXX property...

Mapping the expected value to their Interval.Time property would probably work.

OK some more testing.

The value is not a NpgsqlTypes.NpgsqlInterval but a object{NpgsqlTypes.NpgsqlInterval} (boxed)

The correct cast (tested and working) is therefore (System.TimeSpan)(NpgsqlTypes.NpgsqlInterval)GetValue((int)QueueItemFieldIndex.ElapsedTime, false)

Strange isn't it ?

Haaz wrote:

OK some more testing.

The value is not a NpgsqlTypes.NpgsqlInterval but a object{NpgsqlTypes.NpgsqlInterval} (boxed)

The correct cast (tested and working) is therefore (System.TimeSpan)(NpgsqlTypes.NpgsqlInterval)GetValue((int)QueueItemFieldIndex.ElapsedTime, false)

Strange isn't it ?

No it's not strange . The values are obtained from the datareader in object form (in an object[] array), and stored in an object value (boxed) in the field. This means that the value to return by GetValue() is an object, and the generated code casts that to System.TimeSpan. That doesn't work here, as the object IN the object reference is not of type System.TimeSpan, so it won't call the implicit cast operator of NpgsqlInterval. When you first cast it to NpgsqlInterval and then to System.TimeSpan, the implicit cast operator is called. The necessity for NpgsqlTypes.NpgsqlInterval to be there requires a reference to the assembly, so it's better of you indeed use a type converter to return the Time value.

OK I got it.

Thanks a lot for your help, I'm in the process of adding a type converter to work around this.

Hi Otis,

I was able to create the type converter and successfully build my project using it. I works perfectly when reading from the Database, but fails when writing :

Here is the generated query :

UPDATE "globaldata"."QueueItem" SET "ElapsedTime"=00:14:28::interval WHERE ( "globaldata"."QueueItem"."QueueItemID" = 1::int4)

While it should be

UPDATE "globaldata"."QueueItem" SET "ElapsedTime"='00:14:28'::interval WHERE ( "globaldata"."QueueItem"."QueueItemID" = 1::int4)

(Single quotes for the interval value are missing).

Should I change something to my type converter or should this be fixed in the driver ?

Simple Type converter :

            public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
            {
                if (value == null)
                {
                    throw new ArgumentNullException("value", "Value can't be null");
                }

                if (!(value is System.TimeSpan))
                {
                    throw new ArgumentException("Value isn't of type TimeSpan", "value");
                }

                switch (destinationType.FullName)
                    {
                        case "NpgsqlTypes.NpgsqlInterval":
                            return (NpgsqlTypes.NpgsqlInterval)value;
                        case "System.TimeSpan":
                            return value;
                      default:
                            throw new NotSupportedException("Conversion to a value of type '" + destinationType.ToString() + "' isn't supported");
                    }
            

            }

Kind regards, Manu

Why aren't there parameters in the query? The parameters are passed with the value, not inside a query string... Or did you grab this query from a profiler?

Hi,

I grabbed the query from the inner exception ErrorSql field.

Here is the original one with parameters :

    Query: UPDATE "globaldata"."QueueItem" SET "ElapsedTime"=:ElapsedTime1 WHERE ( "globaldata"."QueueItem"."QueueItemID" = :QueueItemId2)
    Parameter: :ElapsedTime1 : Object. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: binary lob.
    Parameter: :QueueItemId2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.