Postgresql Arrays

Posts   
 
    
PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 21-Apr-2020 14:59:42   

Hi,

Referring to thread https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=24485

Still no plans to support array-fields in Postgresql?

Designer (v5.6.2) still happily sets its type as "string". ;-)

Kind regards,

Peter

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 22-Apr-2020 08:00:51   

Hi Peter,

Not possible AFAIK, according to the docs and the explanation in the thread you posted. You must use the TypeConverter workaround.

David Elizondo | LLBLGen Support Team
PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 22-Apr-2020 08:46:22   

Hi Daelmo,

Surely somebody has already developed this TypeConverter? :-) Is there some kind of library of TypeConverters on github or so?

Kind regards,

Peter

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Apr-2020 09:56:31   

There's no such type converter available ready for download but I don't think it's that much work to create yourself, in theory. (See the SDK docs: https://www.llblgen.com/Documentation/5.6/SDK/gui_implementingtypeconverter.htm )

In practice it might be more difficult as I dont' know (have to check) if npgsql requires extra settings on the parameter for the array. If it just accepts the array as-is it might work.

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 25-May-2021 14:06:51   

Otis wrote:

There's no such type converter available ready for download but I don't think it's that much work to create yourself, in theory. (See the SDK docs: https://www.llblgen.com/Documentation/5.6/SDK/gui_implementingtypeconverter.htm )

In practice it might be more difficult as I dont' know (have to check) if npgsql requires extra settings on the parameter for the array. If it just accepts the array as-is it might work.

It definetly does not work.

First of all when a postgres table has a column of type int[] LLBLGen will identify its db type as varchar(max) and thus maps it to string. The Npgsql driver internally used to fetch the data from this colum, sees the type of the column as what it is: an int[] array.

The line

object currentValue = this._fields.GetCurrentValue(fieldIndex);

in the GetValue() method from EntityCore reads and returns the value as an int[].

So far all good.

Unfortunately the getter from the entities corresponding property then casts this int[] to string, which leads to a crash.

public virtual System.String CostelloDistinctSiteAuthorIds
        {
            get { return (System.String)GetValue((int)SiteAuthorNameFieldIndex.CostelloDistinctSiteAuthorIds, true); }
            set { SetValue((int)SiteAuthorNameFieldIndex.CostelloDistinctSiteAuthorIds, value); }
        }

So what I'm saying is, that currently LLBLGen cannot retrieve entities with int[] columns at all. Accessing these columns in code will inevitably lead to a crash.

And the idea of using a type converter as suggested on several spots here on forum doesn't work either because it is based on the idea that you convert string to int[] and vice versa .

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-May-2021 09:30:20   

@kamiwa, We'll continue your problem in the thread you opened in the helpdesk forum.

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 26-May-2021 09:33:17   

Otis wrote:

@kamiwa, We'll continue your problem in the thread you opened in the helpdesk forum.

Sorry for cross posting. Wasn't sure whether this thread was still monitored due to its age.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-May-2021 09:47:53   

kamiwa wrote:

And the idea of using a type converter as suggested on several spots here on forum doesn't work either because it is based on the idea that you convert string to int[] and vice versa .

This is possible though, you can split the string on , and convert the fragments and vice versa, concat the values to a string.

Frans Bouma | Lead developer LLBLGen Pro
kamiwa avatar
kamiwa
User
Posts: 164
Joined: 12-Jun-2007
# Posted on: 26-May-2021 11:28:10   

Otis wrote:

kamiwa wrote:

And the idea of using a type converter as suggested on several spots here on forum doesn't work either because it is based on the idea that you convert string to int[] and vice versa .

This is possible though, you can split the string on , and convert the fragments and vice versa, concat the values to a string.

The problem with this approach (TypeConverter) is, that if you write a type converter that converts from string to int[] and vice versa, you would need to be able to pick System.Int32[] (aka int[]) as .NET datatype for the entity's corresponding property. Unfortunatly appart from byte[] there is no array type supported in the LLBLGen Designer GUI.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-May-2021 11:59:52   

Let's not do a conversation in two places, but for completeness, as the other thread is in helpdesk, and for the people reading this: the type returned by the TypeConverter as its core type (so the general instance it creates) is the one that's added to the list of types supported. So if the type converter's core type is MyOwnSuperType then you'll get that added to the list of types. Saving a value the other way around will be a bit problematic I think as (to my knowledge), Npgsql requires some parameter property values for arrays, which aren't there, but this might be working nowadays when you set the parameter value to an array value.

Frans Bouma | Lead developer LLBLGen Pro