Problems with longlvarchar

So my database backend for Django works quite well against the testsuite currently and now I decided to try longlvarchar as base data types for text fields (as opposed to clob for long and varchar for shorter fields). This raises the following questions: * CREATE TABLE "auth_group" ("id" serial NOT NULL PRIMARY KEY, "name" lvarchar(1) NOT NULL UNIQUE) results in Total length of columns in constraint is too long. -- which makes me wonder a bit since 1 shouldn't be that long. All I can do is assume that the size specification is ignored or something similar. * Is there any way I can introspect the size limit (and default value) for longlvarchar from sys* tables? Thanks, Florian

Florian: LVARCHAR and LONGLVARCHAR are two different types. The former is a variable length type that defaults to 2048 bytes (plus 2 bytes for the actual string length) if you do not specify a maximum length, but if you set it to lvarchar(1) it takes up just 3 bytes on disk. Regardless of how long the string is defined to be and how long it actually gets LVARCHAR is always stored in table. LONGLVARCHAR is a different animal. It is variable length like LVARCHAR and VARCHAR, however, you do not specify a maximum length. For strings shorter than 2K the strings are saved in table space as an LVARCHAR longer strings are moved into smartblob space and saved as CLOBs. This is automatic and is the same underlying mechanism that is used to store timeseries data and JSON and BSON data. Now as to why your LVARCHAR(1) is reporting that the unique constraint key is too long, I do not know. That should be fine. It does beg the question of WHY you have used an LVARCHAR to store a one character string when it wastes two bytes for the half word that saves the string's length when a CHAR(1) takes up only a single byte, but perhaps this is just an example to show off the problem. Have you opened a PMR about this? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, May 14, 2017 at 6:14 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > So my database backend for Django works quite well against the testsuite > currently and now I decided to try longlvarchar as base data types for text > fields (as opposed to clob for long and varchar for shorter fields). > > This raises the following questions: > > * CREATE TABLE "auth_group" ("id" serial NOT NULL PRIMARY KEY, "name" > lvarchar(1) NOT NULL UNIQUE) results in Total length of columns in > constraint > is too long. -- which makes me wonder a bit since 1 shouldn't be that long. > All I can do is assume that the size specification is ignored or something > similar. > > * Is there any way I can introspect the size limit (and default value) for > longlvarchar from sys* tables? > > Thanks, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Hi Art, > It does beg the question of WHY you have used an LVARCHAR to store a one character string when it wastes two bytes for the half word that saves the string's length when a CHAR(1) takes up only a single byte, but perhaps this is just an example to show off the problem. Yes, I wanted to show it with the minimal possible length to ensure that I do not indeed run over any limits. I'll get into contact with our Informix supplier. Do you have any idea on my second question regarding reading length/default from sys* tables (maybe not just for [LONG]LVARCHAR but for all the types in general) Thanks & Cheers, Florian

Afaik longlvarchar isn't even officially documented, i.e. not meant to be = used in user tables or similar, but rather serves as an internal transport = type for json/bson. The -550 error you're receiving for the unique constraint on the=20 longlvarchar column probably is comparable to the 9816 would you try a=20 unique constraint on a clob column, i.e. you don't really want such=20 constraint on such column, and if you want Informix says 'not allowed' (as = not making much sense). HTH, Andreas From: "FLORIAN APOLLONER" <florian.apolloner@bap.at> To: ids@iiug.org Date: 15.05.2017 00:15 Subject: Problems with longlvarchar [39176] Sent by: ids-bounces@iiug.org So my database backend for Django works quite well against the testsuite=20 currently and now I decided to try longlvarchar as base data types for=20 text=20 fields (as opposed to clob for long and varchar for shorter fields).=20 This raises the following questions:=20 * CREATE TABLE "auth=5Fgroup" ("id" serial NOT NULL PRIMARY KEY, "name"=20 lvarchar(1) NOT NULL UNIQUE) results in Total length of columns in=20 constraint=20 is too long. -- which makes me wonder a bit since 1 shouldn't be that=20 long.=20 All I can do is assume that the size specification is ignored or something = similar.=20 * Is there any way I can introspect the size limit (and default value) for = longlvarchar from sys* tables?=20 Thanks,=20 Florian=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Florian: The length is encoded in the collength column in syscolumns. For most types it's a plain length holding the maximum length for variable length columns. For some types (VARCHAR, DECIMAL/MONEY, DATETIME, & INTERVAL) collength encodes multiple vallues like the min & max legnths for VARCHAR, precision and scale for DECIMAL/MONEY, and starting and ending precision for DATETIME and INTERVAL with one value in the upper byte and the other in the lower byte. Look at the file sqltypes.h in $INFORMIXDIR/incl/esql for details and macros to decode this column. Or you can look at the code that myschema uses in the file print_coltype.ec. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 15, 2017 at 4:13 AM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi Art, > > > It does beg the question of WHY you have used an LVARCHAR to store a one > character string when it wastes two bytes for the half word that saves the > string's length when a CHAR(1) takes up only a single byte, but perhaps > this > is just an example to show off the problem. > > Yes, I wanted to show it with the minimal possible length to ensure that I > do > not indeed run over any limits. I'll get into contact with our Informix > supplier. > > Do you have any idea on my second question regarding reading length/default > from sys* tables (maybe not just for [LONG]LVARCHAR but for all the types > in > general) > > Thanks & Cheers, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thanks, will see what I can dig out there :)

> Afaik longlvarchar isn't even officially documented, i.e. not meant to be used in user tables or similar But it seemed like such a nice data type :D I just found it on my search for a text data type which does not require bending over backwards to use it :/ (ie easily usable in queries)

Florian: You are using LVARCHAR which is documented and fully usable. LONGLVARCHAR is a different animal. It is underdocumented and mostly used internally for the storage of JSON, BSON, and TIMESERIES data. It is not simple to use, although some typecasts do simplify its use in SQL a bit, not so much for host programs. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 15, 2017 at 6:04 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > > Afaik longlvarchar isn't even officially documented, i.e. not meant to be > used in user tables or similar > > But it seemed like such a nice data type :D I just found it on my search > for a > text data type which does not require bending over backwards to use it :/ > (ie > easily usable in queries) > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Hi Art, Sure, LVARCHAR is documented an usable, but has an annoyingly low limit for text fields. Unless I misunderstood most of it I should set SQL_LOGICAL_CHAR to 4 for utf-8 (although most characters in my language would probably fit into two utf-8 encoded bytes), which leaves me with 8k characters left (or 16k if I set it to 2). 8k is roughly somewhere around 2-3 A4 pages of text, which is not that much (although probably enough for quite a few usecases). It is true that at this point I should probably just use TEXT (for efficiency at least maybe), but on the other hand I do not think that the IBM python drivers allow inserting into that :( Cheers, Florian P.S.: Just out of curiosity, lets see if this forum does fine with some chars not in the BMP :þ &#9787; &#9996;

AHH! Sorry your posts were not clear. I was sure that when I asked if you were using longlvarchar or lvarchar that you said lvarchar. That explains the index message. You cannot index a longlvarchar type column no matter how short it is. Some of the support functions that an index would require are not provided. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 16, 2017 at 4:35 AM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi Art, > > Sure, LVARCHAR is documented an usable, but has an annoyingly low limit for > text fields. Unless I misunderstood most of it I should set > SQL_LOGICAL_CHAR> to 4 for utf-8 (although most characters in my language would probably fit > into two utf-8 encoded bytes), which leaves me with 8k characters left (or > 16k > if I set it to 2). 8k is roughly somewhere around 2-3 A4 pages of text, > which > is not that much (although probably enough for quite a few usecases). > > It is true that at this point I should probably just use TEXT (for > efficiency > at least maybe), but on the other hand I do not think that the IBM python > drivers allow inserting into that :( > > Cheers, > Florian > > P.S.: Just out of curiosity, lets see if this forum does fine with some > chars > not in the BMP :ş &#9787; &#9996; > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >