What is the best way to detect changes in the  structure (definition) of the database?

I would like to programmatically respond to these changes, recreating files that are based on the definition of data types, tables, columns, etc.

Geziel


>What is the best way to detect changes in the structure (definition) of the database?
>
>I would like to programmatically respond to these changes, recreating files that are based on the definition of data types, tables, columns, etc.

I'm sure that others will understand what you're trying to achieve but I'm baffled.

Are users allowed to change the table structures or are you asking how you can alter the database and then roll out the changes to your users?

Roy Lambert [Team Elevate]

Geziel,

<< What is the best way to detect changes in the  structure (definition) of
the database? >>

You can use this statement to generate a diff between a source and target
database:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=COMPARE_DATABASE

Tim Young
Elevate Software
www.elevatesoft.com

Users will alter the structure with DDL statements.  After that, I need to recreate some data that should reflect these changes.

"Tim Young [Elevate Software]" wrote:

You can use this statement to generate a diff between a source and target
database:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=COMPARE_DATABASE

Tim Young
Elevate Software
www.elevatesoft.com



Thank you, very helpful.  But as I said earlier: "Users will alter the structure with DDL statements.  After that, I need to recreate some data that should reflect these changes."

It would be nice if I could detect an object (table, column, etc.) has changed. Is this possible?

Geziel

>Users will alter the structure with DDL statements. After that, I need to recreate some data that should reflect these changes.

Ouch! The best way would be to keep a base version database in the catalog then as Tim suggests you can use COMPARE DATABASE otherwise you're going to have to maintain a set of tables yourself so that you can compare.

Another thought would be to simply capture the DDL statements and then you could simply analyse those.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

<<Ouch! The best way would be to keep a base version database in the catalog then as Tim suggests you can use COMPARE DATABASE otherwise you're going to have to maintain a set of tables yourself so that you can compare.

Another thought would be to simply capture the DDL statements and then you could simply analyse those.>>



Thanks Roy,

But I couldn't to analyze the entire database looking for changes. What I look for is a way to know when the change occurs and then act specifically on this object (like a event or trigger).

If at least I know that a table was changed, then I could punctually analyze the DDL.

I bought the source code, then I consider even a implementation at this level. It is not possible to monitor instructions such as "ALTER TABLE" or "CREATE TABLE"?

Any way to know immediately that an object has been modified by a DDL statement would be useful to me.

Geziel


>But I couldn't to analyze the entire database looking for changes. What I look for is a way to know when the change occurs and then act specifically on this object (like a event or trigger).

>If at least I know that a table was changed, then I could punctually analyze the DDL.
>
>I bought the source code, then I consider even a implementation at this level. It is not possible to monitor instructions such as "ALTER TABLE" or "CREATE TABLE"?
>
>Any way to know immediately that an object has been modified by a DDL statement would be useful to me.

I wasn't suggesting that you should monitor the entire database but I don't know how your users interact with the data and I'm assuming that somewhere there's a form they type their SQL into - monitor that for any instructions containing DLL code.

Can you give us more information?

Roy Lambert [Team Elevate]

Roy Lambert wrote:

<<I wasn't suggesting that you should monitor the entire database but I don't know how your users interact with the data and I'm assuming that somewhere there's a form they type their SQL into - monitor that for any instructions containing DLL code.

Can you give us more information?>>



Sure Roy,

Some users can interact by ElevateDB manager and even by programatically API. Just a few especial users can do it, but they can. Because of this i guess that what I need is some solution that can be implemented in the my custom server.

What you think?

Geziel


>Some users can interact by ElevateDB manager and even by programatically API. Just a few especial users can do it, but they can. Because of this i guess that what I need is some solution that can be implemented in the my custom server.
>
>What you think?

The first thing I think is that no way would I allow users access to EDBManager

If the ONLY way the users can access the database is through your server then what you suggest is the way to go. Unfortunately, if they are on a LAN then, with EDBManager, there's nothing to stop them using local access and then you have a problem.

You may be better off modifying EDBManager but that depends on what you mean by "programatically API". If its your program then alter that as well.

I would give serious thought to Tim's suggestion. Maintain a separate copy of the database (structure only - no data) and then you can use the COMPARE DATABASE capability. If you need to track changes from last monitoring point then simply capture the changes, store that then bring the copy to the same state as the live database.

My preferred approach would be to move all of the DDE capabilities inside the app where you can control it. Since you have the source of EDBManager you can steal chunks of it (Tim won't mind - honest) to save having to write them yourself. Do it that way and you can monitor / control things.

Roy Lambert [Team Elevate]