Hi, Ariel. Sorry, I'm still confused. Why would any back-end want to
treat the specified VARCHAR length as both maximum (makes sense) and
minimum (seems to defeat the purpose, which I thought was to allow entry
of the longest expected value without requiring all records to use that
much storage)? With the interpretation of an exact length for VARCHAR,
how would it differ from CHAR? What would the "VAR" allow to vary?
HSQLDB seems to act as I would expect, even with the
sql.enforce_strict_size property set TRUE (at least, I think it was!).
More comments below.
Thanks for your help on this, I'd really like to understand.
Ariel Constenla-Haile wrote:
Hi Barbara, John, *,
Barbara Duprey escribió:
Ariel Constenla-Haile wrote:
Hi *,
UPDATE 'your_table' SET 'your_field' = RTRIM('your_field')
depending on the back-end, this statement may have any effect at
all (or
at least not the desired by John); because in some back-ends, where
the
field size is strictly respected, whenever you insert/update a
field of
type char, if the string is shorter than the size specified on the
table
definition, blank spaces are added.
Now can't recall what HSQLDB does (though I recall of some property
sql.enforce_strict_size - but neither recall if this is accessible to
the OOo Base user, or just in the db properties file [it¡s worth a RFE
to make all HSQLDB properties accessible to OOo users]).
His fields are of type longvarchar, and I know HSQLDB handles
regular varchar fields without enforcing strict field length with
this command
this depends on the property I quoted in my mail
(sql.enforce_strict_size)
(I've used it on several occasions myself to remove trailing blanks
that were interfering with comparisons). So unless longvarchar
fields work differently, using this command from Tools > SQL ought
to be successful. Isn't the whole point of the varchar (and
presumably longvarchar) definition that the field length is allowed
to vary based on content?
well, not really if you specify a constraint in the table definition,
like
CREATE TABLE user (
...
user_name VARCHAR (15) NOT NULL
...
);
That would seem to allow, say, any length string from one to 15 -- still
varying based on content, but with an enforced minimum length of one.
here the field has a max. lenght of 15 chars. In some back-ends, if you
insert/update a record with 10 chars., you may end up with 5 blank
spaces at the end.
Now reading the HSQL guide[1], sql.enforce_strict_size defaults to false.
JT- I created my string in two ways:
1: "UPDATE TestMedData SET TestMedData = RTRIM(Major/Common Side
Effects)"
<without the quotes at the ends of the string> and received this
response:
"4: Table not found in statement [UPDATE TestMedData]"
2."UPDATE 'TestMedData' SET 'TestMedData' = RTRIM('Major/Common Side
Effects')" and received this response: "5: Unexpected token:
TestMedData in
statement [UPDATE 'TestMedData']"
Now this feels like I simply am missing the proper syntax for issuing
this
SQL command, but I created the test table and pasted the name from
the table
into the template you wrote for the SQL string the difference
between the
two was the quotes surrounding the name of the table "TestMedData".
'single quotes' are for string literals. For identifiers, like table
names and field names, you have to use in HSQLDB double quotes
"TableName", "FieldName":
UPDATE "TableName" SET "FieldName" = RTRIM("FieldName");
Oops! Used an example I found in a message in another thread, thanks for
the correction.
2."UPDATE 'TestMedData' SET 'TestMedData' = RTRIM('Major/Common Side
Effects')"
did you create a field named "Major/Common Side Effects"? with a / in
the name?
Regards
Ariel.
[1] some quotes:
"The qualifiers are still ignored unless you set a database property.
SET PROPERTY "sql.enforce_strict_size" TRUE will enforce sizes for
CHARACTER or VARCHAR columns and pad any strings when inserting or
updating a CHARACTER column."
Note that this specifies both CHARACTER and VARCHAR in the first part,
about size being enforced, but only CHARACTER in the second part, about
padding. To me, this indicates that an exception would be generated for
either type if the string is too long, but it does not say VARCHAR
columns would be padded. In fact, I created a test embedded HSQLDB
database and set this property (or at least the command above was
reported as successfully completed), and it acted as I expected when I
then created a table containing a VARCHAR field, and created a record
with the field set to "ABC " (without the quotes, of course). I'm not
sure the property really makes a difference for VARCHAR fields, though,
because I was not able to add an entry that was too long regardless of
whether I was in this database or one with the default of FALSE. In any
case, using the syntax I started with, but corrected to have double
quotes, the trailing blanks were removed as I expected; no padding was
added.
"sql.enforce_strict_size false size enforcement and padding
string columns
Conforms to SQL standards for size and precision of data types. When
true, all CHARACTER, VARCHAR, NUMERIC and DECIMAL values that are in a
row affected by an INSERT INTO or UPDATE statement are checked against
the size specified in the SQL table definition. An exception is thrown
if the value is too long. Also all CHARACTER values that are shorter
than the specified size are padded with spaces. TIMESTAMP(0) and
Here again, CHARACTER and VARCHAR are both explicitly identified for the
maximum, but only CHARACTER for the padding. I'm a little confused by
the use of CHARACTER rather then CHAR here, though, and it says nothing
about LONGVARCHAR.
TIMESTAMP(6) are also allowed in order to specify the subsecond
resolution of the values. When false (default), stores the exact string
that is inserted. (SET PROPERTY)"
"HSQLDB databases are initially created in a legacy mode that does not
enforce column size and precision. You can set the property:
sql.enforce_strict_size=true to enable this feature. When this property
has been set, Any supplied column size and precision for numeric and
character types (CHARACTER and VARCHAR) are enforced. Use the command,
SET PROPERTY "sql.enforce_strict_size" TRUE once before defining the
tables."
"By default specified precision and scale for the column is simply
ignored by the engine. Instead, the values for the corresponding Java
types are always used, which in the case of DECIMAL is an unlimited
precision and scale. If a size is specified, it is stored in the
database definition but is not enforeced by default. Once you have
created the database (before adding data), you can add a database
property value to enforce the sizes:
SET PROPERTY "sql.enforce_strict_size" true
This will enforce the specified size and pad CHAR fields with spaces to
fill the size. This complies with SQL standards by throwing an exception
Again, only CHAR is specified for the padding, whereas immediately
below, all are explicitly identified for the sorting order. I'd
interpret this as indicating VARCHAR and LONGVARCHAR fields would not be
padded. Now if it said "character fields" (a general description) rather
than "CHAR fields" (a reference to a specific field type), it would
clearly apply to all of them. If it said "CHARACTER fields" here, I'd
flip a coin!
if an attempt is made to insert a string longer than the maximum size.
It also results in all DECIMAL values conforming to the specified
precision and scale.
CHAR and VARCHAR and LONGVARCHAR columns are by default compared and
sorted according to POSIX standards. See the SET DATABASE COLLATION[2]
section above to modify this behavior. The property
sql.compare_in_locale is no longer supported. Instead, you can define a
collation to be used for all character comparisons."
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
