Informix 12 Unicode

Hi, I am trying to get IBM Informix Dynamic Server Version 12.10.FC3E work with DB_LOCALE=fi_fi.utf8 Problem is CHAR type and how informix reports it's size/length. As we know single byte locales eq ISO8859-15 character 'r' size is 1 byte and 'ä' size is 1 byte, but UTF-8 'a' size is 1 byte and 'ä' size is 2 bytes. create table ttable ( test char(5) ); Informix handles column sizes as bytes, so ttable.test size is 5 bytes, so there are enought space for 5 't' characters but only 2 for 'ä' characters. So problem is length reporting because it does not report column size as logical chars but instead bytes. Can i configure informix so that it will report column size in logical characters? I did found configuration parameter SQL_LOGICAL_CHAR and i thouht this is answer for my prayers, but not. This just makes informix to raport column size as division of SQL_LOGICAL_CHAR. Example, if i have SQL_LOGICAL_CHAR=3 and is excec "create table ttable ( test char(5) );". Informix create table ttable and column test with size of 15, but somplaces it reports column size as 5 and someplaces 15. That 15 is problematic because we are using ESQL/C and sqlvar_struct.sqllen reports columns sizes as 15, but we want that column length is 5 characters. What whould i do should i just forget informix and UTF-8? Obviosly i can't use UTF-16 or UTF-32 locales in informix? Second problem is that when i use ODBC driver i get UTF-8 text as messed up. Have you got the informix and UTF-8 working, how? could you give me examples? best regards Matti Jaatinen

Made couple of test cases for it. Test case 1: SQL_LOGICAL_CHAR OFF CLIENT_LOCALE=fi_fi.UTF8 DB_LOCALE=fi_fi.utf8 LANG=fi_FI.UTF-8 create table eka ( sar1 char(5), sar2 nchar(5) ); INSERT INTO eka(sar1,sar2) VALUES ('1ä2345','1ä2345'); SELECT * FROM eka; sar1 sar2 1ä23 1ä23 Test case 2: SQL_LOGICAL_CHAR 3 CLIENT_LOCALE=fi_fi.UTF8 DB_LOCALE=fi_fi.utf8 LANG=fi_FI.UTF-8 create table eka ( sar1 char(5), sar2 nchar(5) ); INSERT INTO eka(sar1,sar2) VALUES ('1ä2345','1ä2345'); SELECT * FROM eka; sar1 sar2 1ä2345 1ä2345 INSERT INTO eka(sar1,sar2) VALUES ('1ä234567890qwerty','1ä234567890qwerty'); SELECT * FROM eka; sar1 sar2 1ä2345 1ä2345 1ä234567890qwe 1ä234567890qwe select * from syscolumns where tabid = 100; colname sar1 tabid 100 colno 1 coltype 0 collength 15 colmin colmax extended_id 0 seclabelid 0 colattr 0 colname sar2 tabid 100 colno 2 coltype 15 collength 15 colmin colmax extended_id 0 seclabelid 0 colattr 0

Appendix for Test case 2: Select length(sar1), length(sar2) from eka; (expression) (expression) 7 7 15 15 Select char_length(sar1), char_length(sar2) from eka; (expression) (expression) 14 14 14 14 Notice, it should be char_length because length return byte size len and char_length return number of characters(logical characters). When i set onconfig parameter SQL_LOGICAL_CHAR to 3, it tells informix to alloc 3 bytes for 1 character, when creating tables but how informix reports that(column size), IT IS MESSY. So when is exec create table eka ( sar1 char(5), sar2 nchar(5) ); Informix actually creates table where sar1 is char(15) and sar2 nchar(15). So next time i do dbschema -d testi -t eka i got create table "informix".eka ( sar1 char(5), sar2 nchar(5) ); But when i go into dbaccess and check the table eka i got: Column name Type Nulls sar1 char(15) yes sar2 nchar(15) yes or if i check the column size from syscolumns is get 15.

Hello... Please don't take this as a definitive answer. Actually it's not really an answer.... You're right that we report char lengths as bytes. This may be inconvenient... but you mentioned sqllen.... that's supposed to be used for memory allocation on the client side. If it reported 5 how would you know the required memory? And because UTF-8 is variable size, the answer would be... Nobody knows.... I think this is an area where we need to improve, but honestly I'm not sure how we should handle things... And I personally think SQL_LOGICAL_CHAR was a bad idea, as it applies to all CHAR columns.... Meaning it will unnecessarily enlarge your whole database. Regarding ODBC, I'm not aware of the issues, but I believe there is some work on it being done... maybe someone else can give more details. Regards On Tue, Oct 21, 2014 at 10:34 AM, MATTI JAATINEN <matti.jaatinen@norelco.fi> wrote: > Hi, > > I am trying to get IBM Informix Dynamic Server Version 12.10.FC3E work with > DB_LOCALE=fi_fi.utf8> > Problem is CHAR type and how informix reports it's size/length. > > As we know single byte locales eq ISO8859-15 character 'r' size is 1 byte > and > 'ä' size is 1 byte, but UTF-8 'a' size is 1 byte and 'ä' size is 2 bytes. > > create table ttable ( test char(5) );> > Informix handles column sizes as bytes, so ttable.test size is 5 bytes, so > there are enought space for 5 't' characters but only 2 for 'ä' characters. > So problem is length reporting because it does not report column size as > logical chars but instead bytes. > > Can i configure informix so that it will report column size in logical > characters? > > I did found configuration parameter SQL_LOGICAL_CHAR and i thouht this is > answer for my prayers, but not. This just makes informix to raport column > size > as division of SQL_LOGICAL_CHAR. > > Example, if i have SQL_LOGICAL_CHAR=3 and is excec "create table ttable ( > test > char(5) );". Informix create table ttable and column test with size of 15, > but > somplaces it reports column size as 5 and someplaces 15. > > That 15 is problematic because we are using ESQL/C and sqlvar_struct.sqllen > reports columns sizes as 15, but we want that column length is 5 > characters. > > What whould i do should i just forget informix and UTF-8? > Obviosly i can't use UTF-16 or UTF-32 locales in informix? > > Second problem is that when i use ODBC driver i get UTF-8 text as messed > up. > > Have you got the informix and UTF-8 working, how? could you give me > examples? > > best regards Matti Jaatinen > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f7d5ea846420505ef3a3c

Hi, ..."but you mentioned sqllen.... that's supposed to be used for memory allocation on the client side. If it reported 5 how would you know the required memory?" Yes the sqllen is used for that(memory allocation) purpose but the problem is where i can get the wanted/actual characted column length. When i use ESQL/C. Can i check somewhere if the database is created with SQL_LOGICAL_CHAR param 3? Where can i find the actual char column length? syscolumns reports its size.

The big problem is that there is no character length because nowhere informix allows you to define that. The current syntax just defines the bytes. It's up to the developer/dba to assume the ammount of bytes needed for the ammount of characters desired. You can find the value from SQL_LOGICAL_CHAR.... Run the following query: SELECT flags FROM 'informix'.systables WHERE tabname = ' VERSION'; SQL_LOGICAL_CHAR = ( flags & 3 ) + 1. So... if flags = 0, SQL_LOGICAL_CHAR is 1 (or off) flags = 1 SQL_LOGICAL_CHAR = 2 flags = 2 SQL_LOGICAL_CHAR = 3 I tried to set SQL_LOGICAL_CHAR to 4 and I still got flags = 2... Regards On Tue, Oct 21, 2014 at 3:13 PM, MATTI JAATINEN <matti.jaatinen@norelco.fi> wrote: > Hi, > > ...."but you mentioned sqllen.... that's supposed to be used for > memory allocation on the client side. If it reported 5 how would you know > the required memory?" > > Yes the sqllen is used for that(memory allocation) purpose but the problem > is > where i can get the wanted/actual characted column length. When i use > ESQL/C. > > Can i check somewhere if the database is created with SQL_LOGICAL_CHAR > param > 3? > > Where can i find the actual char column length? syscolumns reports its > size. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7bd755847314cc0505f03145

All correct, and expected behavior I would say... SQL_LOGICAL_CHAR acts as a multiplier... But you get the byte length in the catalogs. DBSchema shows the value you specify. Regards On Tue, Oct 21, 2014 at 2:46 PM, MATTI JAATINEN <matti.jaatinen@norelco.fi> wrote: > Appendix for Test case 2: > > Select length(sar1), length(sar2) from eka; > (expression) (expression) > > 7 7 > > 15 15 > > Select char_length(sar1), char_length(sar2) from eka; > (expression) (expression) > > 14 14 > > 14 14 > > Notice, it should be char_length because length return byte size len and > char_length return number of characters(logical characters). > > When i set onconfig parameter SQL_LOGICAL_CHAR to 3, it tells informix to > alloc 3 bytes for 1 character, when creating tables but how informix > reports > that(column size), IT IS MESSY. > > So when is exec > > create table eka> > ( > > sar1 char(5), > > sar2 nchar(5) > > ); > > Informix actually creates table where sar1 is char(15) and sar2 nchar(15). > So > next time i do dbschema -d testi -t eka i got > create table "informix".eka > ( > > sar1 char(5), > > sar2 nchar(5) > ); > > But when i go into dbaccess and check the table eka i got: > Column name Type Nulls > > sar1 char(15) yes > sar2 nchar(15) yes > > or if i check the column size from syscolumns is get 15. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f3756e567030505f04b7f

I feel I need to explain a bit better. I agree the way we handle UTF-8 is not perfect. But It's not clear to me what are the real requirements and complains... 1- SQL_LOGICAL_CHAR seems a bit useless... I'd prefer something at column level 2- Even if we could specify the CHAR length, would that help? I find it a bit weird how to handle that... would we "enlarge" the field automatically? We use CHARs for a reason... the fact that they don't "expand" brings a lot of advantages. And what would happen if the expansion would hit the maximum size (VARCHAR 255) 3- Why would a developer need to know the CHAR specification? He should be able to understand is a value fits or not... (more on this later) 4- I HATE the fact that we truncate data without errors... this can be specially bad in UTF-8 databases or when we're storing the result of encryption... There is an RFE to change this. Basically allow the DBA to activate the behavior we have for ANSI databases where we raise an error 5- I think the CHAR vs BYTE length is a non subject... For situation where we're storing variable length data (names, company names, addresses etc.) this has always been a guess work... so with UTF8 we need to continue guessing, but with a bit for uncertainty.... what is the avg. percentage of 2 byte characters in a name? Just accomodate that avg plus a bit more margin.... Am I wrong? There will always be times when we notice a defined length was not enough.... be it bytes or CHARs Regards On Tue, Oct 21, 2014 at 4:15 PM, Fernando Nunes <domusonline@gmail.com> wrote: > All correct, and expected behavior I would say... SQL_LOGICAL_CHAR acts as > a multiplier... But you get the byte length in the catalogs. > DBSchema shows the value you specify. > Regards > > On Tue, Oct 21, 2014 at 2:46 PM, MATTI JAATINEN <matti.jaatinen@norelco.fi > > > wrote: > > > Appendix for Test case 2: > > > > Select length(sar1), length(sar2) from eka; > > (expression) (expression) > > > > 7 7 > > > > 15 15 > > > > Select char_length(sar1), char_length(sar2) from eka; > > (expression) (expression) > > > > 14 14 > > > > 14 14 > > > > Notice, it should be char_length because length return byte size len and > > char_length return number of characters(logical characters). > > > > When i set onconfig parameter SQL_LOGICAL_CHAR to 3, it tells informix to > > alloc 3 bytes for 1 character, when creating tables but how informix > > reports > > that(column size), IT IS MESSY. > > > > So when is exec > > > > create table eka> > > > ( > > > > sar1 char(5), > > > > sar2 nchar(5) > > > > ); > > > > Informix actually creates table where sar1 is char(15) and sar2 > nchar(15). > > So > > next time i do dbschema -d testi -t eka i got > > create table "informix".eka > > ( > > > > sar1 char(5), > > > > sar2 nchar(5) > > ); > > > > But when i go into dbaccess and check the table eka i got: > > Column name Type Nulls > > > > sar1 char(15) yes > > sar2 nchar(15) yes > > > > or if i check the column size from syscolumns is get 15. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f3756e567030505f04b7f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c3634e9575ef0505f0e7df

Hi, "But It's not clear to me what are the real requirements and complains..." It is just that i am bit frustrated pecause i did research and tested informix and did not find way to make informix report logical character length and now i know it's impossible. So im bit of relieved but.... no good. 1) Yes, i tested bit further SQL_LOGICAL_CHAR and it did not resolve the problem. Example SQL_LOGICAL_CHAR is 3 make query "select 'test' AS test,* from eka;" We have no clue what is the length of column test. SQL_LOGICAL_CHAR is useless in my case. 2) We have kind of RAD(Rapid Application Development)-tool which we use to develop GUIs; the tool gets field length from prepared SQL-statements. This information can be used to prevent user from flooding the data. The tool uses logical character length, not the character byte size. 3) Not understood the question. 4) If the informix could report the logical character length, the program could notify the user that there is no enough space for what he's putting into program. 5) Our idea was that we could put the cyrillic texts in our database, every single character is at leat 2-bytes, then the chinese 3-bytes. This means that programmer have to manually control how many characters he allows to put into field. Again this is more work for him. So Informix canno't produce the information about logical character length. This is the information which i came here to ask, pecause i did myself try to find way to make informix report actual logical character length but now i know iformix canno't do it.

Hi, Disclaimer: I have to admit that I did not really follow this discussion. Therefore the following suggestion may be neither new nor applicable. If you need to know the length in characters of an individual value, you may use a function called CHAR_LENGTH. Excerpt from the SQL Syntax Guide: CHAR_LENGTH Function The CHAR_LENGTH function returns the number of logical characters in its argument, which can be a character column, a character variable, or a quoted string. This built-in function can also be invoked as CHARACTER_LENGTH. In the default U.S. English locale and other single-byte locales, CHAR_LENGTH behaves exactly like the LENGTH function, and returns the number of bytes in its argument. For multibyte code sets, however, which various Unicode, East Asian, and other nondefault locales support, the return value can be less than the number of bytes in the argument. For a discussion of this function, see the IBM Informix GLS User's Guide. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Read about the Informix Warehouse Accelerator: http://tinyurl.com/the-iwa-blog IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martina Koederitz Board of Management: Dirk Wittkopp Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 From: "MATTI JAATINEN" <matti.jaatinen@norelco.fi> To: ids@iiug.org Date: 10/22/2014 07:51 Subject: Re: Informix 12 Unicode [34010] Sent by: ids-bounces@iiug.org Hi, "But It's not clear to me what are the real requirements and complains..." It is just that i am bit frustrated pecause i did research and tested informix and did not find way to make informix report logical character length and now i know it's impossible. So im bit of relieved but.... no good. 1) Yes, i tested bit further SQL_LOGICAL_CHAR and it did not resolve the problem. Example SQL_LOGICAL_CHAR is 3 make query "select 'test' AS test,* from eka;" We have no clue what is the length of column test. SQL_LOGICAL_CHAR is useless in my case. 2) We have kind of RAD(Rapid Application Development)-tool which we use to develop GUIs; the tool gets field length from prepared SQL-statements. This information can be used to prevent user from flooding the data. The tool uses logical character length, not the character byte size. 3) Not understood the question. 4) If the informix could report the logical character length, the program could notify the user that there is no enough space for what he's putting into program. 5) Our idea was that we could put the cyrillic texts in our database, every single character is at leat 2-bytes, then the chinese 3-bytes. This means that programmer have to manually control how many characters he allows to put into field. Again this is more work for him. So Informix canno't produce the information about logical character length. This is the information which i came here to ask, pecause i did myself try to find way to make informix report actual logical character length but now i know iformix canno't do it. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, http://www.iiug.org/forums/ids/index.cgi/read/34002 Char_length return length of the characters in data not the length of column. Example you have column sar1 char(5) and there is row which data is '4ä4'. Char_length(sar1) return 3 pecause there is 3 characters in that row. Length(sar1) return 4 pecause there is 4 bytes in that row. The problem is how to determine how many characted can be put into the column sar1. Informix reports column size as byte-size so i appears that there is enought space for 5 characters, but when we use myltibyte characters eq. '&#39740;' there can be put only 1-characted into column sar1.

Yes... My 3rd question was not clear. But I think we manage to establish a couple of things: 1- Informix does nto report the real number of chars you can store in a column, because that's undefined. It does not guarante the "char allocation". Only "byte" allocation 2- This makes programer's life harder I would say this could be translated in a clear requirement: "Informix should allow and guarantee character allocation for variable length character set codes" To be honest I think this would be very hard to implement and would require radical changes in the code. But that's not up to me to decide.... You could create an RFE for that and see what happens. And for VARCHARs I think we would easily hit the maximum limit (255). Regards. On Wed, Oct 22, 2014 at 6:50 AM, MATTI JAATINEN <matti.jaatinen@norelco.fi> wrote: > Hi, > > "But It's not clear to me > what are the real requirements and complains..." > > It is just that i am bit frustrated pecause i > did research and tested informix and did not find way to make informix > report > logical character length and > now i know it's impossible. So im bit of relieved but.... no good. > > 1) > Yes, i tested bit further SQL_LOGICAL_CHAR and it did not resolve the > problem. > Example SQL_LOGICAL_CHAR is 3 make query "select 'test' AS test,* from > eka;" > We have no clue what is the length of column test. > SQL_LOGICAL_CHAR is useless in my case.> > 2) > We have kind of RAD(Rapid Application Development)-tool which we use to > develop GUIs; > the tool gets field length from prepared SQL-statements. This information > can > be > used to prevent user from flooding the data. The tool uses logical > character > length, not the > character byte size. > > 3) > Not understood the question. > > 4) > If the informix could report the logical character length, the program > could > notify the user that there is no enough space for what he's putting into > program. > > 5) > Our idea was that we could put the cyrillic texts in our database, every > single character > is at leat 2-bytes, then the chinese 3-bytes. This means that programmer > have to manually control how many characters he allows to put into field. > Again > this is more work for him. > > So Informix canno't produce the information about logical character length. > This is the information which i came here to ask, pecause i did myself try > to > find way to make informix report > actual logical character length but now i know iformix canno't do it. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f7d5ea24d9c0505ff9985

Hi, you may use function ifx_gl_mb_loc_max() in an application to determine, how "wide" a character can be in the current locale. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Read about the Informix Warehouse Accelerator: http://tinyurl.com/the-iwa-blog IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martina Koederitz Board of Management: Dirk Wittkopp Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 From: "MATTI JAATINEN" <matti.jaatinen@norelco.fi> To: ids@iiug.org Date: 10/22/2014 10:14 Subject: Re: Informix 12 Unicode [34012] Sent by: ids-bounces@iiug.org Hi, http://www.iiug.org/forums/ids/index.cgi/read/34002 Char_length return length of the characters in data not the length of column. Example you have column sar1 char(5) and there is row which data is '4ä4'. Char_length(sar1) return 3 pecause there is 3 characters in that row. Length(sar1) return 4 pecause there is 4 bytes in that row. The problem is how to determine how many characted can be put into the column sar1. Informix reports column size as byte-size so i appears that there is enought space for 5 characters, but when we use myltibyte characters eq. '&#39740;' there can be put only 1-characted into column sar1. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

That is good. But I think the issue the OP is facing (which I think is very common), is that it's impossible to know how many characters we're able to store in a column, because we don't guarantee that... This seems to be a fundamental design structure... Big O for example allows you to specify: CREATE TABLE ... ( col1 VARCHAR2(30 CHAR)) This means you'll always be able to fit 30 characters, no matter how many bytes it takes. There is a disclaimer stating that if you do something like: CREATE TABLE... ( col1 VARCHAR2(4000)) -- 4000 is the "hard limit" of VARCHAR2. Then it's not guaranteed that you'll be able to store 4000 characters... it will accept up to the maximum bytes (4000) allowed in the datatype). I think big O is also able to "extend" CHAR fields... but I'm not really sure. It could be easier to implement this (just for VARCHAR) in Informix.... Since by design (AFAIK) varchar(N) doesn't impose any physical limit on the row. My concern is that because our VARCHAR() limit is relatively low (256) that this could easily lead to columns exceeding the byte limit.... And this, in turn, leads to the other possible discussion about this: We don't raise an error when the user tries to exceed the limit..... (INSERTing a VARCHAR(200) in a VARCHAR(100) column). We just truncate the data without warning. From my point of view (and I recently had customers complaining about that) this is an undesirable behavior (not a bug as it works as design). There is an RFE for this.... and because we raise an error on ANSI databases, I'd expect this change to classify as "very possible", but without seeing the code I can only imagine. So.... in short I would: 1- Open an RFE to request the specification of CHAR (as opposed to bytes) for variable length fields (VARCHAR/LVARCHAR) 2- Vote for the existing RFE to create a configuration option ($ONCONFIG, SET ENVIRONMENT....) to force the raise of an error if we insert acharacter string that does not fit the column. That RFE is: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33830 There seems to be a duplicate (not yet classified as such): http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=53804 Hopefully if the first one gets implemented, R&D will consider my comments.... I think the original request for a "warning" is not enough. An error is necessary in order to be useful for existing applications. Regarding the first RFE, AFIK it's not inserted yet... but a carefull search would be needed. Implementing it for VARCHAR seems much easier than for CHAR... Regards. On Wed, Oct 22, 2014 at 11:41 AM, Martin Fuerderer <MARTINFU@de.ibm.com> wrote: > Hi, > > you may use function ifx_gl_mb_loc_max() in > an application to determine, how "wide" a > character can be in the current locale. > > Regards, Martin > -- > Martin Fuerderer > IBM Informix Development Munich, Germany > Information Management > > Read about the Informix Warehouse Accelerator: > http://tinyurl.com/the-iwa-blog > > IBM Deutschland Research & Development GmbH > Chairman of the Supervisory Board: Martina Koederitz > Board of Management: Dirk Wittkopp > Corporate Seat: Boeblingen, Germany > Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 > > From: "MATTI JAATINEN" <matti.jaatinen@norelco.fi> > To: ids@iiug.org > Date: 10/22/2014 10:14 > Subject: Re: Informix 12 Unicode [34012] > Sent by: ids-bounces@iiug.org > > Hi, > > http://www.iiug.org/forums/ids/index.cgi/read/34002 > > Char_length return length of the characters in data not the length of > column. Example you have > > column sar1 char(5) and there is row which data is '4ä4'. > Char_length(sar1) > return 3 pecause there is 3 characters in that row. Length(sar1) return 4 > pecause there is 4 bytes in that row. > > The problem is how to determine how many characted can be put into the > column > sar1. Informix reports column size as byte-size so i appears that there is > > enought space for 5 characters, but when we use myltibyte characters eq. > '&#39740;' there can be put only 1-characted into column sar1. > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f7d5e6069310506018ca1

Martin pointed out that by using the function he mentioned, ifx_gl_mb_loc_max() you may get the information you're looking for. But that assumes you either used SQL_LOGICAL_CHAR or you manually calculated the byte size for the specific columns using the same logic. So for example, if you're using a column for names called customer_name, and you want o allow names with 80 characters, you could create a VARCHAR(160) or VARCHAR(240) column (assuming either 2 or 3 bytes per character). Then if you get the byte length using hte usual interface and divide it by the result of ifx_gl_mb_loc_max() you should get the 80 characters... A bit tricky but possible.... Naturally you could wrap this into a frindly developer function.... The function mentioned is from GLS API. I think it's usuable from the engine side (SQL) if we create a C wrapper function.... Would have to test it. Regards. On Wed, Oct 22, 2014 at 11:41 AM, Martin Fuerderer <MARTINFU@de.ibm.com> wrote: > Hi, > > you may use function ifx_gl_mb_loc_max() in > an application to determine, how "wide" a > character can be in the current locale. > > Regards, Martin > -- > Martin Fuerderer > IBM Informix Development Munich, Germany > Information Management > > Read about the Informix Warehouse Accelerator: > http://tinyurl.com/the-iwa-blog > > IBM Deutschland Research & Development GmbH > Chairman of the Supervisory Board: Martina Koederitz > Board of Management: Dirk Wittkopp > Corporate Seat: Boeblingen, Germany > Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 > > From: "MATTI JAATINEN" <matti.jaatinen@norelco.fi> > To: ids@iiug.org > Date: 10/22/2014 10:14 > Subject: Re: Informix 12 Unicode [34012] > Sent by: ids-bounces@iiug.org > > Hi, > > http://www.iiug.org/forums/ids/index.cgi/read/34002 > > Char_length return length of the characters in data not the length of > column. Example you have > > column sar1 char(5) and there is row which data is '4ä4'. > Char_length(sar1) > return 3 pecause there is 3 characters in that row. Length(sar1) return 4 > pecause there is 4 bytes in that row. > > The problem is how to determine how many characted can be put into the > column > sar1. Informix reports column size as byte-size so i appears that there is > > enought space for 5 characters, but when we use myltibyte characters eq. > '&#39740;' there can be put only 1-characted into column sar1. > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7bdc184a0f3bdf050602dedc

Related RFEs are: Character length semantics instead of byte length semantics: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=47787 New National Language Support data type with variable size greater then 255 bytes: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45681 UTF8 should be handled in background instead of setting SQL_LOGICAL_CHAR (duplicate of 34405): http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=34406 UTF8 should be handled in background instead of setting SQL_LOGICAL_CHAR http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=34405 This last one is classified as "Under Consideration" Regards On Wed, Oct 22, 2014 at 12:50 PM, Fernando Nunes <domusonline@gmail.com> wrote: > That is good. But I think the issue the OP is facing (which I think is very > common), is that it's impossible to know how many characters we're able to > store in a column, because we don't guarantee that... > This seems to be a fundamental design structure... Big O for example allows > you to specify: > > CREATE TABLE ... ( col1 VARCHAR2(30 CHAR)) > > This means you'll always be able to fit 30 characters, no matter how many > bytes it takes. There is a disclaimer stating that if you do something > like: > > CREATE TABLE... ( col1 VARCHAR2(4000)) -- 4000 is the "hard limit" of > VARCHAR2. > > Then it's not guaranteed that you'll be able to store 4000 characters... it > will accept up to the maximum bytes (4000) allowed in the datatype). > I think big O is also able to "extend" CHAR fields... but I'm not really > sure. > > It could be easier to implement this (just for VARCHAR) in Informix.... > Since by design (AFAIK) varchar(N) doesn't impose any physical limit on the > row. > My concern is that because our VARCHAR() limit is relatively low (256) that > this could easily lead to columns exceeding the byte limit.... > > And this, in turn, leads to the other possible discussion about this: We > don't raise an error when the user tries to exceed the limit..... > (INSERTing a VARCHAR(200) in a VARCHAR(100) column). We just truncate the > data without warning. > >From my point of view (and I recently had customers complaining about > that) > this is an undesirable behavior (not a bug as it works as design). > There is an RFE for this.... and because we raise an error on ANSI > databases, I'd expect this change to classify as "very possible", but > without seeing the code I can only imagine. > > So.... in short I would: > 1- Open an RFE to request the specification of CHAR (as opposed to bytes) > for variable length fields (VARCHAR/LVARCHAR) > 2- Vote for the existing RFE to create a configuration option ($ONCONFIG, > SET ENVIRONMENT....) to force the raise of an error if we insert a> character string that does not fit the column. That RFE is: > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33830 > > There seems to be a duplicate (not yet classified as such): > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=53804 > > Hopefully if the first one gets implemented, R&D will consider my > comments.... I think the original request for a "warning" is not enough. An > error is necessary in order to be useful for existing applications. > > Regarding the first RFE, AFIK it's not inserted yet... but a carefull > search would be needed. Implementing it for VARCHAR seems much easier than > for CHAR... > Regards. > > On Wed, Oct 22, 2014 at 11:41 AM, Martin Fuerderer <MARTINFU@de.ibm.com> > wrote: > > > Hi, > > > > you may use function ifx_gl_mb_loc_max() in > > an application to determine, how "wide" a > > character can be in the current locale. > > > > Regards, Martin > > -- > > Martin Fuerderer > > IBM Informix Development Munich, Germany > > Information Management > > > > Read about the Informix Warehouse Accelerator: > > http://tinyurl.com/the-iwa-blog > > > > IBM Deutschland Research & Development GmbH > > Chairman of the Supervisory Board: Martina Koederitz > > Board of Management: Dirk Wittkopp > > Corporate Seat: Boeblingen, Germany > > Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 > > > > From: "MATTI JAATINEN" <matti.jaatinen@norelco.fi> > > To: ids@iiug.org > > Date: 10/22/2014 10:14 > > Subject: Re: Informix 12 Unicode [34012] > > Sent by: ids-bounces@iiug.org > > > > Hi, > > > > http://www.iiug.org/forums/ids/index.cgi/read/34002 > > > > Char_length return length of the characters in data not the length of > > column. Example you have > > > > column sar1 char(5) and there is row which data is '4ä4'. > > Char_length(sar1) > > return 3 pecause there is 3 characters in that row. Length(sar1) return 4 > > pecause there is 4 bytes in that row. > > > > The problem is how to determine how many characted can be put into the > > column > > sar1. Informix reports column size as byte-size so i appears that there > is > > > > enought space for 5 characters, but when we use myltibyte characters eq. > > '&#39740;' there can be put only 1-characted into column sar1. > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f7d5e6069310506018ca1 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113ee16eaa6d39050602fdf0

Hi, Unfortunately ifx_gl_mb_loc_max do not help at all, it would be almosts same case as dividing just 3 or 4. I think "SQL_LOGICAL_CHAR ON" return kind of the same results as ifx_gl_mb_loc_max. It do notwork pecause this: SQL_LOGICAL_CHAR OFF "create table eka(sar1 char(15)); " "SELECT '12345' AS test1,* FROM eka; The select return column test1 bytesize 5 and column test1 bytesize 15. If you divide sar1 with ifx_gl_mb_loc_max you get logical character length 5, if divide test1 with ifx_gl_mb_loc_max you get problem.