Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How can I use a reserved word in ASE 11.5?

3 posts in Windows NT Last posting was on 1998-04-21 15:50:41.0Z
Zoner Posted on 1998-04-20 23:00:44.0Z
From: "Zoner" <spam@nospam.com>
Subject: How can I use a reserved word in ASE 11.5?
Date: Mon, 20 Apr 1998 16:00:44 -0700
Lines: 30
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <tQJZNfKb9GA.698@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5063
Article PK: 1092433

I am trying to convert a database from Sybase SQL Anywhere 5.5 to use
Adaptive Server 11.5.

I can almost use the structure script created with SQL Anywhere except that
two fields out of the whole database are reserved words.. Connect and
Online..

In the Sybase SQL Anywhere scripts you could overcome this by enclosing the
field name in quotes like so:

CREATE TABLE test
(
field1 varchar(6) not null,
field2 smallint ,
"connect" varchar (2),
"online" varchar(2)
)

This doesn't fly in ASE, and no tech support person will help me until we
buy ASE. (Which we will do if I can show that our app can be used on ASE,
kinda a lame paradox..)

How can I work around this other than have the programmers change the whole
application, come up with an upgrade path, then have all of our customers
move over (all for 2 field names)??

Thanks for any help..
- Brian (brianpa@e-h-s.com)


Bret Halford Posted on 1998-04-21 15:50:41.0Z
Message-ID: <353CC051.78C6@sybase.com>
Date: Tue, 21 Apr 1998 09:50:41 -0600
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
Subject: Re: How can I use a reserved word in ASE 11.5?
References: <tQJZNfKb9GA.698@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 104
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5061
Article PK: 1092425


Zoner wrote:
>
> I am trying to convert a database from Sybase SQL Anywhere 5.5 to use
> Adaptive Server 11.5.
>
> I can almost use the structure script created with SQL Anywhere except that
> two fields out of the whole database are reserved words.. Connect and
> Online..
>
> In the Sybase SQL Anywhere scripts you could overcome this by enclosing the
> field name in quotes like so:
>
> CREATE TABLE test
> (
> field1 varchar(6) not null,
> field2 smallint ,
> "connect" varchar (2),
> "online" varchar(2)
> )
>
> This doesn't fly in ASE, and no tech support person will help me until we
> buy ASE. (Which we will do if I can show that our app can be used on ASE,
> kinda a lame paradox..)

But a resolvable one. Typically, in a pre-sales situation, you should
be able to ask your Sybase salesperson about such technical issues,
and they will either have a presales consultant answer your questions,
or open a courtesy case through Tech Support.




> How can I work around this other than have the programmers change the whole
> application, come up with an upgrade path, then have all of our customers
> move over (all for 2 field names)??
>

As Andrew wrote, you can use the "set quoted_identifier on" command in
your table ddl:

set quoted_identifier on
go
CREATE TABLE test
(
field1 varchar(6) not null,
field2 smallint ,
"connect" varchar(2),
"online" varchar(2)
)
go
set quoted_identifier off
go

insert test values ("col1", 2, "aa", "bb")
go
select * from test
go
1> 2> 1> 2> 3> (1 row affected)
1> 2>
field1 field2 connect online
------ ------ ------- ------
col1 2 aa bb

(1 row affected)

However, there may still be further problems. If quoted_identifier
is left on all the time, you will have trouble with inserts like
the one above because the server will interpret "col1" as a quoted
identifier (ie a column name) rather than a literal and give an error
like this:

-- set quoted_identifier is on
1>insert test values (""col1"", 2, "aa", "bb")
2>go
Msg 102, Level 15, State 1:
Server 'REL_115_SOL_A1_BRET', Line 2:
Incorrect syntax near 'col1'.

If quoted_identifier is off, then "select * from test" will work,
but you will not be able to select specific columns if they
are not normally valid identifiers:

-- set quoted_identifier is off
1> select * from test
2> go
field1 field2 connect online
------ ------ ------- ------
col1 2 aa bb

(1 row affected)
1> select connect from test
2> go
Msg 156, Level 15, State 1:
Server 'REL_115_SOL_A1_BRET', Line 1:
Incorrect syntax near the keyword 'connect'.
1> select "connect" from test
2> go

-------
connect
-- note that this selected a literal "connect", not the
-- value of the column called "connect"

1> set quoted_identifier on
2> go
1> select "connect" from test
2> go
connect
-------
aa

(1 row affected)


As you can see, it will still take some development effort
to get it to work. I think it would be simpler to use that
effort to change the column names in the first place.

Why would all of your customers have to move over? It seems
to me that the ones who want to migrate to an ASE solution
will need to do a fair amount of work anyway. I don't see why
your customers staying on SQL Anywhere would need to do anything
at all.

For future planning, you should look at the full list of reserved
keywords, including those that are not currently reserved words but
may become reserved, such as the "Potential SQL92 Reserved Words" list
in the ASE 11.5 Ref Manual Vol 3, appendix B. If you think you might
port your application to other database systems, such as Informix,
Oracle, Postgres, Gemstone, etc., it is probably worth the time to
seek out their lists of reserved words as well. A little effort in
advance can save you huge conversion headaches in the future.
--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


Andrew Prosser Posted on 1998-04-21 10:08:46.0Z
From: "Andrew Prosser" <andrew@prosser1.demon.co.uk>
References: <tQJZNfKb9GA.698@forums.powersoft.com>
Subject: Re: How can I use a reserved word in ASE 11.5?
Date: Tue, 21 Apr 1998 11:08:46 +0100
Lines: 67
Organization: Disorganised
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <eh33K5Qb9GA.129@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5062
Article PK: 1092426

You can use the following, from Sybooks;

Delimited Identifiers

Delimited identifiers are object names enclosed in double quotes. Using
delimited identifiers allows you to avoid certain restrictions on object
names. You can use double quotes to delimit table, view, and column names;
you cannot use them for other database objects.

Delimited identifiers can be reserved words, can begin with non-alphabetic
characters, and can include characters that would not otherwise be allowed.
They cannot exceed 28 bytes.

Before creating or referencing a delimited identifier, you must execute:

set quoted_identifier on

This option allows SQL Server to recognize delimited identifiers. Each time
you use the quoted identifier in a statement, you must enclose it in double
quotes. For example:

create table "1one"(col1 char(3))

select * from "1one"

create table "include spaces" (col1 int)

Note:Delimited identifiers cannot be used as parameters to system procedures
or with bcp, and may not be supported by all front-end products.

Regards
--
Andrew Prosser

Zoner wrote in message ...
>I am trying to convert a database from Sybase SQL Anywhere 5.5 to use
>Adaptive Server 11.5.
>
>I can almost use the structure script created with SQL Anywhere except that
>two fields out of the whole database are reserved words.. Connect and
>Online..
>
>In the Sybase SQL Anywhere scripts you could overcome this by enclosing the
>field name in quotes like so:
>
>CREATE TABLE test
>(
> field1 varchar(6) not null,
> field2 smallint ,
> "connect" varchar (2),
> "online" varchar(2)
>)
>
>This doesn't fly in ASE, and no tech support person will help me until we
>buy ASE. (Which we will do if I can show that our app can be used on ASE,
>kinda a lame paradox..)
>
>How can I work around this other than have the programmers change the whole
>application, come up with an upgrade path, then have all of our customers
>move over (all for 2 field names)??
>
>Thanks for any help..
> - Brian (brianpa@e-h-s.com)
>
>