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
...
);
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");
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."
"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
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
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."
--
Ariel Constenla-Haile
La Plata, Argentina
"Aus der Kriegsschule des Lebens
- Was mich nicht umbringt,
macht mich härter."
Nietzsche Götzendämmerung, Sprüche und Pfeile, 8.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
