John L. Singleton wrote:
>
> Elke,
>
> Oh, wow. Yes, it appears I have misunderstood.
>
> >Now the question: what do you WANT to do? Why do you used this
> (misunderstood) option?
> >
> >
> I'm just documenting it. NO FIXED LENGTH COLUMN is fairly new, so I just
> wanted to get it right. I wasn't trying to DO anything with it, per se.
> If you don't mind, however, I have a few further questions.
>
> >Within the database, the following columns are always stored with its
> maximum length, no matter how 'full' they are filled with data:
> >- the first n-1 primary key-columns
> >- for all non-key-columns specified during CREATE TABLE
> > * char (n) ASCII if n <= 30
> > * char (n) UNICODE if n <= 15
> > * any numeric column
> > * boolean
> > * LONG, DATE, TIME, TIMESTAMP
> >
> >
> Questions:
> 1) Surely LONG is omitted from this? The maximum length of LONG is 2GB.
> Is LONG exempt from this behavior?
The LONG-column has an id needing 9 bytes stored in the row itself and the value
stored with its actual length at another place --> LONG is different to all other
columns.
> 2) So if the first n-1 primary key columns are always stored with their
> maximum length, if I were to define a table like:
>
> (col1 char(20) , col2 char(20) , col3 char(20), PRIMARY KEY (col1, col2,
> col3)) NO FIXED LENGTH COLUMN
>
> That col3 will be stored with a variable length?
Col3 will be stored with variable length in any case, no matter if with
'NO FIXED LENGTH COLUMN' or without
>
> I think the misleading sentence in the docs is:
>
> "If you specify NO FIXED LENGTH COLUMN, all character columns and
> numeric columns are stored with variable lengths."
>
> This is true, but it is misleading. For example, I didn't know that
> values of CHAR(n) n<=30 were all stored as CHAR(30).
Why should you know? That is not true.
The truth is: they are stored as CHAR (n), their maximum definition length.
Elke
SAP Labs Berlin
> Since I didn't know
> about this behavior, this statement lead me to believe something else.
> If it would help at all, I can send you what I write for this
> parameter. You can then edit it to suit your needs.
>
> All best,
> JLS
>
> Zabach, Elke wrote:
>
> >John L. Singleton wrote:
> >
> >
> >>Elke,
> >>
> >>I upgraded to 7.5.00.15 and now NO FIXED LENGTH COLUMN is recognized at
> >>the SQL prompt. However, when I was investigating the part about only
> >>the first n-1 primary keys being exempt from NO FIXED LENGTH COLUMN, I
> >>came across this:
> >>
> >>sqlcli test=> create table nflc_test3 (ID char(5)) NO FIXED LENGTH
> COLUMN
> >>0 rows affected (367 msec)
> >>
> >>sqlcli test=> insert into nflc_test3 values ('12345')
> >>1 row affected (9 msec)
> >>
> >>sqlcli test=> insert into nflc_test3 values ('123456')
> >>* -8004: Constant must be compatible with column type and length
> >>SQLSTATE: 22001
> >>sqlcli test=>
> >>
> >>
> >>As you can see from the above output, NO FIXED LENGTH COLUMN is accepted
> >>as valid syntax, but it does not appear to be enforced. Any thoughts?
> >>
> >>
> >>
> >
> >Oh my goodness, there is a BIG misunderstanding of the NO FIXED LENGTH
> COLUMN-meaning. I have to explain a little bit more:
> >
> >Within the database, the following columns are always stored with its
> maximum length, no matter how 'full' they are filled with data:
> >- the first n-1 primary key-columns
> >- for all non-key-columns specified during CREATE TABLE
> > * char (n) ASCII if n <= 30
> > * char (n) UNICODE if n <= 15
> > * any numeric column
> > * boolean
> > * LONG, DATE, TIME, TIMESTAMP
> >
> >NO FIXED LENGTH COLUMN means that even for those non-key-columns
> mentioned above the variable-length-storing is used, meaning length-
> info+value with its really needed size, not the maximum size according to
> the column-definition.
> >In case your columns usually are filled to its maximum, this variable-
> length-storing needs more space than the usual storing, in case usually
> your data is shorter than the maximum, it uses less space.
> >
> >But NO FIXED LENGTH COLUMN has NOTHING to do with the maximum length
> storable in one column --> no 6 character can be stored in a column with
> the maximum definition of 5 and so on. NO FIXED LENGTH COLUMN does not
> mean that the database is willing to trunc/cut off some data to be able to
> store it or to ignore the MAXIMUM length definition as it is given for
> each column.
> >
> >Now the question: what do you WANT to do? Why do you used this
> (misunderstood) option?
> >
> >Elke
> >SAP Labs Berlin
> >
> >
> >
> >>Thanks again,
> >>JLS
> >>Zabach, Elke wrote:
> >>
> >>
> >>
> >>>John L. Singleton wrote
> >>>
> >>>
> >>>
> >>>
> >>>>Hi Elke,
> >>>>
> >>>>I get the same result in sqlcli and SQL Studio. This output, however,
> is
> >>>>
> >>>>
> >>>>from SQL Studio.
> >>>
> >>>
> >>>>Kernel Version: 7.5.05
> >>>>
> >>>>
> >>>>
> >>>>
> >>>Aaah, here we are. You are using 7.5.00.05, but this feature was
> >>>
> >>>
> >>implemented with 7.5.00.11. With your version it can't do. Please
> upgrade
> >>to a newer one.
> >>
> >>
> >>>Elke
> >>>SAP Labs Berlin
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> >>>>Syntax error or access violation;-3014 POS(46) Invalid end of SQL
> >>>>statement.
> >>>>create table nflc_test (foo int PRIMARY KEY) NO FIXED LENGTH COLUMN
> >>>>
> >>>>To save you time, the POS(46) points to "NO."
> >>>>
> >>>>Thanks,
> >>>>JLS
> >>>>
> >>>>Zabach, Elke wrote:
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>John L. Singleton wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hello all,
> >>>>>>
> >>>>>>Does MaxDB still support the NO FIXED LENGTH COLUMN clause? I've
> tried
> >>>>>>several variations of this statement, but with no luck.
> >>>>>>
> >>>>>>For example:
> >>>>>>create table nflc_test (foo int PRIMARY KEY) NO FIXED LENGTH COLUMN
> >>>>>>-3014: Invalid end of SQL statement SQLSTATE: 42000
> >>>>>>
> >>>>>>I'm sure it's something really simple that I'm missing. Any ideas?
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>No idea, too, as it works fine for me.
> >>>>>Which kernel-version are you using?
> >>>>>Which tool are you using?
> >>>>>Which sqlmode are you using?
> >>>>>Which errorposition is given (position in the statement where the
> error
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>was found)? Maybe a funny char is behind the correct statement? (We
> had
> >>>>this here in private scripts, just an idea)
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>Elke
> >>>>>SAP Labs Berlin
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Cheers,
> >>>>>>JLS
> >>>>>>
> >>>>>>--
> >>>>>>MaxDB Discussion Mailing List
> >>>>>>For list archives: http://lists.mysql.com/maxdb
> >>>>>>To unsubscribe:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>http://lists.mysql.com/[EMAIL PROTECTED]
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>
> >>>>>>
> >>>
> >>>
> >>>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
