2pack: column size for SQL statement shall be extended
Description
Environment
Attachments
- 26 Dec 2015, 06:23 AM
- 20 Aug 2015, 01:53 PM
- 20 Aug 2015, 01:53 PM
testing discovered
Activity
Hiep Lq August 1, 2016 at 2:45 PM
i remember we discuss about it before but can't find it out to re-test.
thanks for re-clear it.
just summary my test.
c_order.description column on postgresql:
1. choose type = string and length = 10000
database: datatype is character varying(10000)
pack-in like a normal string
2. choose type = text => have to define length, and length = 10000
database: datatype is text
pack-in like a normal string
3. choose type = text long => not need define length
database: datatype is text
pack-in like a binary
before maybe i do something wrong, so can't play with string and text long
from document of postgresql: https://www.postgresql.org/docs/9.1/static/datatype-character.html
so separate type of string to (string, text, text long) is for compatible with oracle isn't mandatory for postgresql
Carlos Ruiz August 1, 2016 at 1:21 PM
Hiep Lq December 26, 2015 at 6:25 AM
idempiere treat clob (text) as binary (Blob). now pack-in, pack-out don't work for blob.
i think clob should treat as String, same jdbc also treat clob as string.
Hiep Lq December 25, 2015 at 4:58 PM
this is query for list column i modified
select
ad_table.tablename, ad_column.columnname, ad_column_id, defaultvalue, ad_column.ismandatory, fieldlength, ad_reference.name
from
ad_column inner join ad_table on ad_column.ad_table_id = ad_table.ad_table_id
inner join ad_reference on ad_column.ad_reference_id = ad_reference.ad_reference_id
where
ad_column.name in ('Sql SELECT', 'Other SQL Clause', 'Sql WHERE', 'Sql ORDER BY', 'Sql FROM', 'Zoom Logic', 'SQLStatement') and
ad_table.isview = 'N'Hiep Lq December 25, 2015 at 2:42 PM
other issue. current ad_changelog.newvalue and ad_changelog.oldvalue is character varying(2000).
so value from text long or text with length great than 2000 will be truncate
For a plugin I need to provide a database function. Its size is about 3000 characters. Even trying to 'compress' it by taking out comments leaves a size of about 2500 characters.
The column is currently of size memo and as implied by the description "limited to 2000 characters".
From a quick look into the sources it seems to me that all text type columns other than text long (which becomes a BLOB) are created as VARCHAR2. So my proposal is to keep it as memo and just extend it to 4000 chars.
WDYT?
Regards,
Dirk Niemeyer