HI,
I would to alter a table by the structure of another one.

Is there a function to do this or I must create a sql string??

In dbisam 4 I can write:

myTable.FieldDefs.Assign(TableModel.FieldDefs)
myTable.IndexDefs.Assign(TableModel.IndexDefs)

myTable.AlterTable

This just to explain you my needed.

I have written the procedure to create the SQL string but I have some  doubts  when I must change the position of some columns;
The 'ALTER TABLE' sql command adds before all the columns and then change the position??
In this case  must I pass the position's columns from the lowest to the highest or the engine doesn't need this??

or what is the priority of command execution in an "alter table" sql command??

Thank you

Dario

Barbazza

>Is there a function to do this or I must create a sql string??

You have to create an sql string. All DDL type commands (eg alter table, create index etc) in ElevateDB are sql.

>I have written the procedure to create the SQL string but I have some doubts when I must change the position of some columns;
>The 'ALTER TABLE' sql command adds before all the columns and then change the position??
>In this case must I pass the position's columns from the lowest to the highest or the engine doesn't need this??

Altering column positions is always fun. I haven't asked or seen anything about this so I assume its much the same as DBISAM (slightly different syntax).

I find it useful to look in Explorer-SQL History in EDBManager eg

ALTER TABLE "Billing"
ADD COLUMN "_Sequencex" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
ALTER COLUMN "_Sequencex" MOVE TO 38,
ALTER COLUMN "_Sequencex" MOVE TO 37,
ALTER COLUMN "_Sequencex" MOVE TO 36

You can take

ALTER COLUMN "_Sequencex" MOVE TO 38,
ALTER COLUMN "_Sequencex" MOVE TO 37,

out since only the final MOVE matters. However, you need to get the MOVEs in the right sequence. If you MOVE column A to 4 then MOVE column B to 3 (and it was at 10) then column A will be at 5.

Roy Lambert [Team Elevate]

i have this, too.


i have 2 files.

File A is a full table with data  ( old structure ) , File B is a empty
table with new structure ( new field, field moved up/down..,del field )

How i can upgrade structure of A with fields/index... of File B ?

[...]
> However, you need to get the MOVEs in the right sequence. If you MOVE column A to 4 then MOVE column B to 3 (and it was at 10) then column
>A will be at 5.

Yeah this is the problem!! ^^

I thinked so but now I have the confirm..

Thank you very much to explain me and to understand what I have write in my "fun" English ^^

Dario

Mauro

Depends on just what you mean. Should A end up looking the same as B or should A have additional fields added or.....


Roy Lambert [Team Elevate]

> Depends on just what you mean. Should A end up looking the same as B or
> should A have additional fields added or.....

I must update db structure of my clients.

Table A is Table with data of my clients  ( example... : invoice.edbtbl )
Table B is always invoice.edbtbl , with all field of A , but with any new
fields.

original :

Table A         Table B
FIELD_A      FIELD_A
FIELD_B      FIELD_B
FIELD_C      FIELD_B2_NEW
FIELD_D      FIELD_C
FIELD_E      FIELD_D
.....                 ....

i need this :

Table A         Table B
FIELD_A      FIELD_A
FIELD_B      FIELD_B
FIELD_B2_NEW FIELD_B2_NEW
FIELD_C      FIELD_C
FIELD_D      FIELD_D
.....                 ....



With DBISAM   is more more more easy.
(*
myTable.FieldDefs.Assign(TableModel.FieldDefs)
myTable.IndexDefs.Assign(TableModel.IndexDefs)
myTable.AlterTable
*)



How i can update ( ....easy.... ) Update Structure of A , getting all
structure from B ?

Note
*  In Table B , any time , i Move , up and down any fields... without adding
any new fields.

* i can make only one altertable procedure ( i need speed , my db are big )

* ALTER COLUMN "_Sequencex" MOVE TO 37 ....
  This mode is too complex.. another method ?
  ..

Mauro


>With DBISAM is more more more easy.

There are a number of things easier with DBISAM - as Tim will tell you I've moaned about them frequently.

>How i can update ( ....easy.... ) Update Structure of A , getting all
>structure from B ?

There is only way that I can think of to do this which is to query the catalog for both tables, work out the differences and build sql to do the alterations.

>Note
>* In Table B , any time , i Move , up and down any fields... without adding
>any new fields.
>
>* i can make only one altertable procedure ( i need speed , my db are big )
>
>* ALTER COLUMN "_Sequencex" MOVE TO 37 ....
> This mode is too complex.. another method ?

I don't understand what you mean here. How is it to complex?

You could always look at a 3rd party tool eg http://www.contextsoft.com/products/dbdesign/

Roy Lambert [Team Elevate]