Issue with mail template - too many characters

[Nicolas Micoud]

Hi,

On the mail text of mail template window we can use 2000 characters.
If the content is higher, then we loose what was written

See 1st screenshot : the content is 2132 characters :


When I click on OK, the panel close and a message says that too many characters are entered :




And what i wrote in the panel is lost.

I'm not sure what is the best way to deal with that issue :
 - display a popup saying that content may be lost
 - forbid to close the panel
 - split what is written on MailText2 and MailText3 field ? (but what if there is already something)
 - [...]

Havent' tested but i fear we will encounter the issue on all those kind of field.

WDYT ?

Thanks,

Nicolas

[Hiep Lq]

what's reason to limit number of char?

if don't have reason, better move datatype to clob/blob

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/d67487c5-18f0-4ee8-a040-61e413012dec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Lê Quý Hiệp
Email: hie...@hasuvimex.vn
Skype: admin.hasuvimex

Company: Thanh Hoa Fishery Import - Export J.s.c  (Hasuvimex) DL 47

Add: Lot E, Le Mon Industrial Zone, Thanh Hoa, Vietnam

[Nicolas Micoud]

I haven't changed the type of the column (using standard one).
Means the solution would be to move all columns Text(2000) to CLOB/BLOB ?

Haven't tested but could it be difficult to migration from Oracle to Posgresql ?

Thanks,

Nicolas

Le mercredi 10 mai 2017 18:29:55 UTC+2, Hiep Lq a écrit :

what's reason to limit number of char?

if don't have reason, better move datatype to clob/blob

On Wed, May 10, 2017 at 10:03 PM, Nicolas Micoud <nicolas...@gmail.com> wrote:

Hi,

On the mail text of mail template window we can use 2000 characters.
If the content is higher, then we loose what was written

See 1st screenshot : the content is 2132 characters :


When I click on OK, the panel close and a message says that too many characters are entered :




And what i wrote in the panel is lost.

I'm not sure what is the best way to deal with that issue :
 - display a popup saying that content may be lost
 - forbid to close the panel
 - split what is written on MailText2 and MailText3 field ? (but what if there is already something)
 - [...]

Havent' tested but i fear we will encounter the issue on all those kind of field.

WDYT ?

Thanks,

Nicolas

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/d67487c5-18f0-4ee8-a040-61e413012dec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marco Longo]

Hi Nicolas,

look at this

https://groups.google.com/forum/#!searchin/idempiere/2000|sort:relevance/idempiere/HFpPd_x0i0Q/heFO9szjBQAJ

bye 

Marco

[Nicolas Micoud]

Hello Marco,
So the solution would be to change the length of the columns from 2000 to 4000 ?

Thanks,

Nicolas

[Carlos Antonio Ruiz Gomez]

Nicolas, I re-checked today - and re-reviewed my comments on that thread and ticket IDEMPIERE-2778.

It seems - again  :(  - that we're constrained by Oracle.

Oracle varchar2 is limited to 4000 - and when you use i18n then you're limited to 2000.  It seems like it can be increased to 32K  in oracle 12c with some configuration - but still not dropping that limit.
As an alternative for free-length strings oracle provide BLOB/CLOB type.
But, still reading, it seems LOB types in oracle impose a performance issue reading and writing.
Also, you'll have a hard time to use long values in normal SQL - oracle also restrict literals to 4000, so, for SQL scripts you need to do some tricks if you plan to insert/update directly.
You also cannot select directly on SQL, you need to use dbms_lob if you want to get CLOB values on a script.


Now, reading and testing about postgres, it seems it doesn't have any of those problems, VARCHAR can be defined without a limit, or use the TEXT datatype.  And from what I read there is no performance penalty there.


I see some reasons to avoid spreading many CLOB columns in oracle, but I don't see a reason for that in postgresql.

I'm ok about changing most of those long columns to TEXT (hiep suggested to do that for all SQL columns in IDEMPIERE-2778), as all of my supported databases are postgresql.
But, as you're one of the few in community using oracle, WDYT?

Regards,

Carlos Ruiz

[Nicolas Micoud]

Hi Carlos,

And thanks for your detailled answer.


My issue is only for one customer of our SAAS instance. For some mail templates they need to write lots of stuff...
A workaround would be to add a new column in R_MailText (LOB) and use it instead of MailText, MailText2 and MailText3 in some cases.
Haven't tested but i don't think we will have performance issue as this table contains few records and will be used by few users.


For a common solution for all those long columns, I'm not sure about what to do.

ATM, I have migrated one of our production instance (a little one) on postgresql (using ora2pg). It was complicated (need to correct lots of scripts, ...).
So I fear to migrate other instances (as I'm not sure i can handle it) - but as it will become unavoidable, we will have to find a way to do it.


In a first time, perhaps those columns can have limits if Oracle is used ; and no limit for postgresql ?

I think that new databases are created directly on postgresql and Oracle is still used because of migration from Compiere/ADempiere.
So that could make sense to have such differences.


nb: there will still be the issue where we can loose what was written in the WTextEditorDialog when the content has more characters than column capacity.


Thanks & Regards,


Nicolas