At 06:00 PM 5/23/2001 -0700, Jason Wharton wrote:

>I was wondering how the new SQL Dialect 3 identifiers are being handled
>where significant trailing spaces are being used in the names of the
>objects.

>Are names with leading or trailing spaces even allowed?

From a little experimentation, it appears that trailing blanks are
not significant in table names, and are not allowed in column name
references, though they are allowed in column name definitions.

In this example, the table name was created with trailing blanks and
referenced successfully without them.

SQL> create table "4 Trailing Blanks "
CON> (" 3 Leading Blanks" integer);
SQL> insert into "4 Trailing Blanks" values (1);
SQL> select * from "4 Trailing Blanks";

3 Leading Blanks
===================

1

Leading blanks in a column name appear significant.

SQL> select "3 Leading Blanks" from "4 Trailing Blanks";
Statement failed, SQLCODE = -206

Dynamic SQL Error
-SQL error code = -206
-Column unknown
-3 Leading Blanks

SQL> select " 3 Leading Blanks" from "4 Trailing Blanks";

3 Leading Blanks
===================

1

Adding a trailing blank to a column defined without one seems not
to work:

SQL> select " 3 Leading Blanks " from "4 Trailing Blanks ";
Statement failed, SQLCODE = -206

Dynamic SQL Error
-SQL error code = -206
-Column unknown
- 3 Leading Blanks

This alter sequence struck me as odd, because after I added a column,
I could not store into it, even after a commit. When I printed with
a SELECT *, the new column appeared.

SQL> alter table "4 Trailing Blanks" add "2 Trailing Blanks "
integer;

SQL> insert into "4 Trailing Blanks " values (1, 2);
Statement failed, SQLCODE = -804

Dynamic SQL Error
-SQL error code = -804
-Count of columns does not equal count of values

SQL> select * from "4 Trailing Blanks";

3 Leading Blanks 2 Trailing Blanks
=================== =================

1 <null>


Notice also that the formatting does not include the trailing
blanks.

After a disconnect and reconnect the new column became more
reliably visible. However, referencing the column with trailing
blanks exactly as it was defined still fails

SQL> select "2 Trailing Blanks " from "4 Trailing Blanks";
Statement failed, SQLCODE = -206

Dynamic SQL Error
-SQL error code = -206
-Column unknown
-2 Trailing Blanks

Remove the trailing blanks from the column name reference and it
works:

SQL> select "2 Trailing Blanks" from "4 Trailing Blanks";

2 Trailing Blanks
=================

<null>

I was also surprised to realize that even delimited identifiers
are restricted to 32 characters. All very odd.


Regards,

Ann
www.ibphoenix.com
We have answers.