HELP: Creating your own Visual Database Tools

HELP: Creating your own Visual Database Tools

Post by Gian U. U » Wed, 05 Jan 2000 04:00:00



Hi everyone!

I would like to ask what is the best solution for this problem:

I am developing an application that could create & modify table definition
in SQL Server. It is like the Data Environment in the Enterprise Manager,
Visual Basic, & the other Visual Development tools under Visual Studio Ent
Ed.

It is quite complicated if I implement Transact-SQL statements that would
modify individual columns without redefining the entire table. It would take
me time, divulging into the ALTER COLUMN statements and using System Stored
Procedures for the Check and Default constraints.  SQL-DMO is out of the
question for the product is to be independent from the Lisencing schemes
from SQL Server.

Is this the correct way?

1. Create a new Table with a temporary name assigned to it.
2. Copy the data into the temporary table.
3. Purge the Old Table
4. Rename the temporary table.

Hope you guys could help me...

Thanks,

Gian U. Uy
*sphere Systems Inc.

 
 
 

HELP: Creating your own Visual Database Tools

Post by David Rairig » Wed, 05 Jan 2000 04:00:00


There is no "easy" way to do it...however, TSQL is probably the best way...
TSQL isn't really complicated, but keeping your end-user from doing damage
is...

You will simply have to include a substantial amount of logic...or a bold
disclaimer...



Quote:> Hi everyone!

> I would like to ask what is the best solution for this problem:

> I am developing an application that could create & modify table definition
> in SQL Server. It is like the Data Environment in the Enterprise Manager,
> Visual Basic, & the other Visual Development tools under Visual Studio Ent
> Ed.

> It is quite complicated if I implement Transact-SQL statements that would
> modify individual columns without redefining the entire table. It would
take
> me time, divulging into the ALTER COLUMN statements and using System
Stored
> Procedures for the Check and Default constraints.  SQL-DMO is out of the
> question for the product is to be independent from the Lisencing schemes
> from SQL Server.

> Is this the correct way?

> 1. Create a new Table with a temporary name assigned to it.
> 2. Copy the data into the temporary table.
> 3. Purge the Old Table
> 4. Rename the temporary table.

> Hope you guys could help me...

> Thanks,

> Gian U. Uy
> *sphere Systems Inc.


 
 
 

HELP: Creating your own Visual Database Tools

Post by Gian U. U » Wed, 05 Jan 2000 04:00:00


Hi Dave,

    Thanks for the advice but what way should I do it? Should I go for the
1>ALTER COLUMN statements (Also aditional logic for column constraints &
defaults) or 2>simply redefining a new table, transfer the data then purging
the old one of which all of this are done thru Transact SQL using the ADO
Command Object. I would also like to know if  The Visual Database tools that
are used within the Visual Studio Suite are using what type of technique in
modifying an existing table in the database.

Thanks!

Gian U. Uy
*sphere Systems Inc


Quote:> There is no "easy" way to do it...however, TSQL is probably the best
way...
> TSQL isn't really complicated, but keeping your end-user from doing damage
> is...

> You will simply have to include a substantial amount of logic...or a bold
> disclaimer...

(...)
 
 
 

HELP: Creating your own Visual Database Tools

Post by David Rairig » Wed, 05 Jan 2000 04:00:00


I do not know how the VD tools do it, but consider this:
Your end-user needs to change the field length of a varchar fields to 30
chars...in your code, you copy the table, make the changes, and rename.
What happens if the table is big...I mean really big?

I have actually written something very much like what you are describing...I
use sql script for everything.
Admittedly, however, I do have not added any easy method for changing column
info...I just added a scripting box that allowed the user to enter his/her
own sql statement and run it.  I simply pass it on to SQL server, and return
any errors along the way.



> Hi Dave,

>     Thanks for the advice but what way should I do it? Should I go for the
> 1>ALTER COLUMN statements (Also aditional logic for column constraints &
> defaults) or 2>simply redefining a new table, transfer the data then
purging
> the old one of which all of this are done thru Transact SQL using the ADO
> Command Object. I would also like to know if  The Visual Database tools
that
> are used within the Visual Studio Suite are using what type of technique
in
> modifying an existing table in the database.

> Thanks!

> Gian U. Uy
> *sphere Systems Inc



> > There is no "easy" way to do it...however, TSQL is probably the best
> way...
> > TSQL isn't really complicated, but keeping your end-user from doing
damage
> > is...

> > You will simply have to include a substantial amount of logic...or a
bold
> > disclaimer...

> (...)

 
 
 

1. HELP: Creating your own Visual Database Tools

Hi everyone!

I would like to ask what is the best solution for this problem:

I am developing an application that could create & modify table definition
in SQL Server. It is like the Data Environment in the Enterprise Manager,
Visual Basic, & the other Visual Development tools under Visual Studio Ent
Ed.

It is quite complicated if I implement Transact-SQL statements that would
modify individual columns without redefining the entire table. It would take
me time, divulging into the ALTER COLUMN statements and using System Stored
Procedures for the Check and Default constraints.  SQL-DMO is out of the
question for the product is to be independent from the Lisencing schemes
from SQL Server.

Is this the correct way?

1. Create a new Table with a temporary name assigned to it.
2. Copy the data into the temporary table.
3. Purge the Old Table
4. Rename the temporary table.

OR

1. Reiterate thru the columns and use the ALTER COLUMN statement
2. If constraints are available check them if it is a DEFAULT , CHECK, etc..
3. Use sp_helpconstraints and reiterate thru the result sets to distinguish
the different constraints
...

The later is only applicable to SQL Server 7.0 for the ALTER COLUMN
statement is only available in 7.0 thru the ALTER TABLE statement. Also the
later technique was not fully finished in this post but I guess you guys
could now get the entire picture.

Hope you guys could help me...

Thanks,

Gian U. Uy
Cybersphere Systems Inc.

2. Paper Design / SQL

3. ADODB.Recordset bug ?

4. Visual Database Tools / Visual Studio 97

5. Installing multiple Oracle Packs (Diagnostics, Tuning, etc.)

6. HELP: Microsoft Visual Database Tools??

7. Temporary sorting space

8. how to create my own database in java2

9. How to create your own database engine

10. A Database That Creates Its Own Web-Pages

11. Help please creating own class Snapshot...

12. Creating MY own database with C++ and of course WinAPI