SQL Anywhere Feedback - one more request for create statement enhancement

Author

one more request for create statement enhancement

Russell Fleming

2005-08-26, 9:23 am

Why can't we get "create or replace" similar syntax support. This usually
starts an anti-oracle rant from sybase guys but how hard can it be to add
this.

Dmitri

2005-08-26, 9:23 am

Russell Fleming wrote:

> Why can't we get "create or replace" similar syntax support.

Why should Sybase copy all Oracle/MS/... gimmicks?

Dmitri.

Greg Fenton

2005-08-27, 8:23 pm

Russell Fleming wrote:
> Why can't we get "create or replace" similar syntax support. This usually
> starts an anti-oracle rant from sybase guys but how hard can it be to add
> this.

There has been some talk about this in recent weeks. But for the
record, could you explain a bit about the behaviour of "create or
replace" and how you see it being used?

We have an idea of what people want from such a feature, but it would be
better to hear directly from those who are asking for the feature.

Thanks,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-08-28, 1:23 pm

On 26 Aug 2005 06:51:40 -0700, Dmitri <NOdimSPAM@mail15.com> wrote:

>Why should Sybase copy all Oracle/MS/... gimmicks?

No, just the very useful ones, like CREATE OR REPLACE. Some DBAs use
Sybase Central for everything, but others write scripts that can be
run repeatedly to create or REcreate everything... that is very useful
in a development environment when things are changing rapidly AND when
multiple databases are being created (test, QA, production, etc).

FWIW, in Oracle that syntax does not apply to tables, just some
objects like procedures. In SQL Anywhere, it should apply to all
objects that can be created. It will eliminate the need to write IF
THEN ... DROP logic which can get rather complex for rows in SYSSYNC
(which represent three different kinds of CREATEd objects).

This request has been repeated several times over the years. The
sa_make_object procedure is kinda kludgy (sorry, guys!) and it doesn't
work for tables either... CREATE OR REPLACE is the way to go :)

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-08-29, 3:23 am

Breck Carter [TeamSybase] wrote:
>
> This request has been repeated several times over the years. The
> sa_make_object procedure is kinda kludgy (sorry, guys!) and it doesn't
> work for tables either... CREATE OR REPLACE is the way to go :)
>

So the question I've heard batted around in engineering about *how*
CREATE OR REPLACE should work if the object already exists. I've been
pushing customers to give an answer without putting words in there
mouths...so I ask here too:

If I have a table FOO defined as:

CREATE TABLE FOO (
id varchar(20) NOT NULL PRIMARY KEY,
value varchar(100) );

which has 1000 rows and someone issues:

CREATE OR REPLACE FOO (
id varchar(20) NOT NULL PRIMARY KEY,
value varchar(100),
value2 varchar(100) );

then what should happen? What about the existing 1000 rows?

What about a second case of:

CREATE OR REPLACE FOO (
id integer DEFAULT AUTOINCREMENT PRIMARY KEY,
value varchar(100) );

Thanks in advance,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Dmitri

2005-08-29, 3:23 am

Greg Fenton wrote:

> If I have a table FOO defined as:
>
> CREATE TABLE FOO (
> id varchar(20) NOT NULL PRIMARY KEY,
> value varchar(100) );
>
> which has 1000 rows and someone issues:
>
> CREATE OR REPLACE FOO (
> id varchar(20) NOT NULL PRIMARY KEY,
> value varchar(100),
> value2 varchar(100) );
>
> then what should happen?

DROP TABLE FOO;

CREATE TABLE FOO (
id varchar(20) NOT NULL PRIMARY KEY,
value varchar(100),
value2 varchar(100) );

> What about a second case of:
>
> CREATE OR REPLACE FOO (
> id integer DEFAULT AUTOINCREMENT PRIMARY KEY,
> value varchar(100) );

The same.

Dmitri.

Paul Horan[TeamSybase]

2005-08-29, 9:23 am

Modifying a "live" table, and retaining all the data, foreign keys, indexes, constraints, triggers, permissions... If
you go with the DROP/CREATE route, then you've got to remember to recreate all the dependent objects as well.

--
Paul Horan[TeamSybase]

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message news:43110436$1@foru
ms-1-dub...
> Russell Fleming wrote:
>
>
> There has been some talk about this in recent weeks. But for the record, could you explain a bit about the behaviour
> of "create or replace" and how you see it being used?
>
> We have an idea of what people want from such a feature, but it would be better to hear directly from those who are
> asking for the feature.
>
> Thanks,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Robert Kratschmann

2005-08-29, 11:23 am

"Dmitri" <NOdimSPAM@mail15.com> schrieb im Newsbeitrag
news:4312bae4@forums
-1-dub...
> Greg Fenton wrote:
>
>
> DROP TABLE FOO;
>
> CREATE TABLE FOO (
> id varchar(20) NOT NULL PRIMARY KEY,
> value varchar(100),
> value2 varchar(100) );
>

If the table contains data an error should appear, if you want to ignore the
error code it like

CREATE OR REPLACE FOO (
id varchar(20) NOT NULL PRIMARY KEY,
value varchar(100),
value2 varchar(100) ) WITH OVERRIDE;

So there should never be a problem if you dont know all details.

Robert

Russell Fleming

2005-08-29, 11:23 am

I wouldn't expect or want a database utility or SQL command to save me from
myself. As Breck mentioned I have scripts to create the full database
schema. If I issue a 'drop table' today I don't get warnings or messages so
'create or replace' should be quite straight forward.

"Paul Horan[TeamSybase]" < paulhATvcisolutionsD
OTcom> wrote in message
news:431312f4$1@foru
ms-2-dub...
> Modifying a "live" table, and retaining all the data, foreign keys,
indexes, constraints, triggers, permissions... If
> you go with the DROP/CREATE route, then you've got to remember to recreate
all the dependent objects as well.
>
> --
> Paul Horan[TeamSybase]
>
> "Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:43110436$1@foru
ms-1-dub...
usually[color=darkre
d]
add[color=darkred]
record, could you explain a bit about the behaviour[color=dark
red]
better to hear directly from those who are[color=darkred]
>
>

Breck Carter [TeamSybase]

2005-08-29, 1:23 pm

No, I agree with Dmitri. It should just do a DROP, no questions asked.
This is functionality for DBAs, not casual users. If someone needs
sophisticated error checking, they can write IF logic themselves...
but in all my years of writing drop-and-recreate scripts that's never
been an issue.

Clearly, CREATE OR REPLACE is not a command that will be often used on
an established production database containing millions of rows, it's
just too spooky. It will be more often used for new development, and
for deployment of new databases to multiple destinations. It will also
be used inside stored procedures to (re)create objects on the fly. And
it WILL be used.

FWIW, CREATE OR REPLACE VARIABLE should be included in the list... IF
VAREXISTS works but it's just extra clutter :)

Breck

On 29 Aug 2005 07:43:06 -0700, "Robert Kratschmann"
<rkratsch@sybase.com> wrote:

>"Dmitri" <NOdimSPAM@mail15.com> schrieb im Newsbeitrag
> news:4312bae4@forums
-1-dub...
>
>If the table contains data an error should appear, if you want to ignore the
>error code it like
>
>CREATE OR REPLACE FOO (
> id varchar(20) NOT NULL PRIMARY KEY,
> value varchar(100),
> value2 varchar(100) ) WITH OVERRIDE;
>
>So there should never be a problem if you dont know all details.
>
>Robert
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links