Board index » delphi » Reverse engineering with ERWIN

Reverse engineering with ERWIN


2007-09-20 09:34:48 PM
delphi254
I am trying to reverse engineer a ODS 10.1 sql-dialect 1 database, running
on IB2007, Windows 2003 Server using the datadirect ODBC Interbase driver. #
This does not work: I receive the following error: "ODBC Interbase driver
dynamic SQL error (error code -104): Invalid command, Data type unknown".
The ODBC DSN entry is configured with 1 for the database dialect. If i turn
off the option to retrieve information about indexes, reverse engineering
works.
Does someone have expieriences with erwin and interbase? Any hints to solve
this problem? What do you use to reverse engenier / model your database?
Regards, Michael
 
 

Re:Reverse engineering with ERWIN

Extract the metadata using isql. Simple example:
isql yourdatabase -user sysdba -pass masterkey -x -o somefile.sql
After running this, somefile.sql has all the sql meta data statements to
create the database.
Michael Danninger writes:
Quote
I am trying to reverse engineer a ODS 10.1 sql-dialect 1 database,
running on IB2007, Windows 2003 Server using the datadirect ODBC
Interbase driver. #

This does not work: I receive the following error: "ODBC Interbase
driver dynamic SQL error (error code -104): Invalid command, Data type
unknown". The ODBC DSN entry is configured with 1 for the database
dialect. If i turn off the option to retrieve information about indexes,
reverse engineering works.

Does someone have expieriences with erwin and interbase? Any hints to
solve this problem? What do you use to reverse engenier / model your
database?

Regards, Michael
 

Re:Reverse engineering with ERWIN

Michael Danninger writes:
Quote
I am trying to reverse engineer a ODS 10.1 sql-dialect 1 database,
running on IB2007, Windows 2003 Server using the datadirect ODBC
Interbase driver. #

This does not work: I receive the following error: "ODBC Interbase
driver dynamic SQL error (error code -104): Invalid command, Data type
unknown". The ODBC DSN entry is configured with 1 for the database
dialect. If i turn off the option to retrieve information about indexes,
reverse engineering works.

Does someone have expieriences with erwin and interbase? Any hints to
solve this problem? What do you use to reverse engenier / model your
database?

Regards, Michael
I don't know if you can still download a functional copy of CaseStudio
Version 2.25, but it seems to reverse engineer a Interbase DB just find.
www.casestudio.com/enu/default.aspx
Unfortunately the have been bought out by Toad -- they attach no
importance to the Interbase community and the product now concentrates
on Micro$oft and Oracle.
--
Will R
PMC Consulting
 

Re:Reverse engineering with ERWIN

Quote
>Does someone have expieriences with erwin and interbase? Any hints to
>solve this problem? What do you use to reverse engenier / model your
>database?
Fortunately, InterBase Development Studio has a built-in ER diagram designer
which was initially made as an InterBase-oriented replacement for ERWin.
Actually the "virtual" database programming style, offered by IBStudio's
Database Designer makes your development efficiency far far ahead from
ERWin. Check this link for more details:
www.sqlly.com/dbdesigner.en.html
--
Best regards.
Pavel Kutakov
SQLLY Development
 

Re:Reverse engineering with ERWIN

Quinn Wildman (CodeGear Developer Support) writes:
Quote
Extract the metadata using isql. Simple example:
That doesn't do what ERwin does. Amongst other things, ERwin will
create an ERD, do schema comparisons with other DBs (potentially on
different DB servers), and automatically create procs and triggers for
common patterns.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
blogs.teamb.com/craigstuntz/category/21.aspx
 

Re:Reverse engineering with ERWIN

Quote
>Extract the metadata using isql. Simple example:

That doesn't do what ERwin does. Amongst other things, ERwin will
create an ERD, do schema comparisons with other DBs (potentially on
different DB servers), and automatically create procs and triggers for
common patterns.

Yes, these are the features I'd like to make use of,
Thanks, Michael
 

Re:Reverse engineering with ERWIN

Thanks to both of you, i will have a look at interbase development studio
and casestudio,
Michael
 

Re:Reverse engineering with ERWIN

Pavel Kutakov writes:
Quote
>>Does someone have expieriences with erwin and interbase? Any hints to
>>solve this problem? What do you use to reverse engenier / model your
>>database?


Fortunately, InterBase Development Studio has a built-in ER diagram designer
which was initially made as an InterBase-oriented replacement for ERWin.
Actually the "virtual" database programming style, offered by IBStudio's
Database Designer makes your development efficiency far far ahead from
ERWin. Check this link for more details:
www.sqlly.com/dbdesigner.en.html

Sounds interesting -- does it have automatic update trigger generation
and self referencing tables?
How does it Compare to Case Studio?
There is not a lot of information on your web site!
--
Will R
PMC Consulting
 

Re:Reverse engineering with ERWIN

Quote
Sounds interesting -- does it have automatic update trigger generation and
self referencing tables?
It has no automatic update triggers. Self referencing tables are supported.
Quote

How does it Compare to Case Studio?
I'll try to highlight differences. Case Studio is aimed for ER diagrams
design. It has logical and physical view and it is able to generate script
for different SQL servers from one diagram. IBStudio's Database Designer is
able to work with InterBase only and ER diagram represents only physical
level. Actually, logical level can be simulated by changing view style, e.g.
you can view fields descriptions instead on real names.
The main advantage of Database Designer is an ability to compare model with
real database and generate "update script". This script consists of the
commans needed to carefully change database structure. There is no "DROP
TABLE.... CREATE TABLE" sequences, it generates "ALTER TABLE ADD..."
instead. So you will never lost existing data.
Another advantage is the approach used to edit database objects. Yes, in the
Case Studio you can create stored procedures and triggers. But this is a
supplementary feature - the editor window is modal and you can edit only one
object at a time. Database Designer is able to edit several different
objects at once. Moreover, the ER diagrams is treated as one of the object
type. So Database Designer is very good for creating procedures especially
with support of Refactoring and intellectual syntax highlighting(check the
following links:www.sqlly.com/intellectualcompletion.en.html ,
www.sqlly.com/articles/Active_Editor.en.html ). Refactoring allows to
rename a field in the table and all views,procedures and triggers will be
updated automatically.
In the Database Designer you can create any number of ER diagrams, this is
equal to submodels feature of CaseStudio.
Database Designer has a built-in version control system and ER diagrams are
stored in it too. Surely, version control system allows several developers
to work on the same project.
Other interesting features are:
- Autoincrement fields (with automatic creation of generator and trigger or
stored procedure)
- Audit tables and triggers generator
- HTML documentation
- Project code validation (performed before each attempt to update physical
database).
--
Best regards.
Pavel Kutakov
SQLLY Development
 

Re:Reverse engineering with ERWIN

Pavel Kutakov writes:
Quote
>Sounds interesting -- does it have automatic update trigger generation and
>self referencing tables?

It has no automatic update triggers. Self referencing tables are supported.

Too bad about the update triggers. I have about 360 tables in the
current design for a GIS system. The trigger generation is quite
mechanical, but, considering that there are several events per table
that require a trigger, the feature is quite handy -- on insert, on
delete (cascades etc), on update... So that work alone is of
considerable value. In this case at 3 X 360 -->well you get the idea
Some options that can be set during generation are to create a trigger
for the cascade operations or to use declarative style for restrict, set
null and set default. You should consider adding those features to make
a better product.
Quote
>How does it Compare to Case Studio?

I'll try to highlight differences. Case Studio is aimed for ER diagrams
design. It has logical and physical view and it is able to generate script
for different SQL servers from one diagram. IBStudio's Database Designer is
able to work with InterBase only and ER diagram represents only physical
level. Actually, logical level can be simulated by changing view style, e.g.
you can view fields descriptions instead on real names.
I will look at this.
Quote
The main advantage of Database Designer is an ability to compare model with
real database and generate "update script". This script consists of the
commans needed to carefully change database structure. There is no "DROP
TABLE.... CREATE TABLE" sequences, it generates "ALTER TABLE ADD..."
instead. So you will never lost existing data.
CaseStudio does something similar -- I will look at this.
Quote
Another advantage is the approach used to edit database objects. Yes, in the
Case Studio you can create stored procedures and triggers. But this is a
supplementary feature - the editor window is modal and you can edit only one
object at a time.
True -- but many triggers are "automatic". Their manual procedure
creation is poor -- true. As I recall Embarcadero could automatically
create update and and insert procedures etc. --- That was very handy.
Quote
Database Designer is able to edit several different
objects at once. Moreover, the ER diagrams is treated as one of the object
type. So Database Designer is very good for creating procedures especially
with support of Refactoring and intellectual syntax highlighting(check the
following links:www.sqlly.com/intellectualcompletion.en.html ,
www.sqlly.com/articles/Active_Editor.en.html ). Refactoring allows to
rename a field in the table and all views,procedures and triggers will be
updated automatically.
This looks very interesting!
Quote
In the Database Designer you can create any number of ER diagrams, this is
equal to submodels feature of CaseStudio.

Database Designer has a built-in version control system and ER diagrams are
stored in it too. Surely, version control system allows several developers
to work on the same project.
Despite the project size -- I am the only person working on the big
project! :-) -- But I do use version control! -- Absolutely necessary!
Quote
Other interesting features are:
- Autoincrement fields (with automatic creation of generator and trigger or
stored procedure)
Good Idea! I suggested it to CaseStudio -- it is really irritating to
create all those generators by hand and then reference them in Delphi! :-)
Quote
- Audit tables and triggers generator
- HTML documentation
- Project code validation (performed before each attempt to update physical
database).

Sounds pretty interesting -- I will definitely review the software.
--
Will R
PMC Consulting
 

Re:Reverse engineering with ERWIN

Quote
Too bad about the update triggers. I have about 360 tables in the current
design for a GIS system. The trigger generation is quite mechanical, but,
considering that there are several events per table that require a
trigger, the feature is quite handy -- on insert, on delete (cascades
etc), on update... So that work alone is of considerable value. In this
case at 3 X 360 -->well you get the idea
But.... what is the reason to use triggers that do only cascading delete ?
If you need to restrict deletion, the foreing key does it for you. As well
as you can specify "ON DELETE CASCADE" and it will do cascading delete.
Surely, if you need to do some specific checking in these triggers you have
to create them, but in this case you have to write your code and no tool can
help you.
Quote
Some options that can be set during generation are to create a trigger for
the cascade operations or to use declarative style for restrict, set null
and set default. You should consider adding those features to make a
better product.
Everything is available in InterBase on SQL declaration level - foreign key
has CASCASE, SET NULL and SET DEFAULT options for update and delete
operations.
--
Best regards.
Pavel Kutakov
SQLLY Development
www.sqlly.com
 

Re:Reverse engineering with ERWIN

Pavel Kutakov writes:
Quote
>Too bad about the update triggers. I have about 360 tables in the current
>design for a GIS system. The trigger generation is quite mechanical, but,
>considering that there are several events per table that require a
>trigger, the feature is quite handy -- on insert, on delete (cascades
>etc), on update... So that work alone is of considerable value. In this
>case at 3 X 360 -->well you get the idea

But.... what is the reason to use triggers that do only cascading delete ?
If you need to restrict deletion, the foreing key does it for you.
Declarative vs triggers... I found some early problems with declarative
logic -- can not recall why now -- but now I have settled on triggers to
do the cascading deletes and I have no problems.SO I let CaseStudio
generate the triggers automatically and the system works as desired.
The business rules for addition and deletion of child records and parent
records are quite involved -- so I am using every feature I can to get
things to work correctly.
Quote
As well
as you can specify "ON DELETE CASCADE" and it will do cascading delete.
Surely, if you need to do some specific checking in these triggers you have
to create them, but in this case you have to write your code and no tool can
help you.
Actually CaseStudio does do what I need and does it well. :-)
Quote
>Some options that can be set during generation are to create a trigger for
>the cascade operations or to use declarative style for restrict, set null
>and set default. You should consider adding those features to make a
>better product.
Everything is available in InterBase on SQL declaration level - foreign key
has CASCASE, SET NULL and SET DEFAULT options for update and delete
operations.
I realize that!
*************************
However...
The Development Studio is definitely a good tool! Congratulations on a
fine product!
I have installed and started testing. It has some nice features. Some
things I can see are better than Case Studio -- others are not as good.
It has shown me some things in my model that look "strange" to me -- a
different perspective is always good. :-) Now I have to investigate. :-(
I need sub-models or a restricted view. So I have to learn how to do
that. The model is just too large to use one Diagram/View.
For a model of 360+ tables it may be the wrong tool. (And Case Studio
was barely adequate -- they added some features that I suggested!) But
then I can not afford Embarcadero either! :-)
--
Will R
PMC Consulting
 

Re:Reverse engineering with ERWIN

WillR writes:
Quote
Declarative vs triggers... I found some early problems with
declarative logic -- can not recall why now -- but now I have settled
on triggers to do the cascading deletes and I have no problems.
I don't know of any issues with declarative constraints other than
potentially useless indices, but I do know of an issue with simulating
it with triggers:
1) There is a master record and a detail record A.
2) User 1 starts a transaction and inserts detail record B with the
same master. The statement succeeds.
3) User 2 deletes detail A and the master. Since user 1's transaction
hasn't been committed yet this works.
4) User 1 commits. Oops. No master now.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues
 

Re:Reverse engineering with ERWIN

Quote
It has shown me some things in my model that look "strange" to me -- a
different perspective is always good. :-) Now I have to investigate. :-(
Try to run "Validate project". It may show you interesting places in your
code.
Quote
I need sub-models or a restricted view. So I have to learn how to do that.
The model is just too large to use one Diagram/View.
As I indicated before, you may create as many number of diagrams as you want
and place only desired objects to them - this is the same as submodels in
Case Studio. Diagrams is just another sort of objects, just click "New
object" button..
Quote
For a model of 360+ tables it may be the wrong tool. (And Case Studio was
barely adequate -- they added some features that I suggested!) But then I
can't afford Embarcadero either! :-)
360+ is not a problem :-) You just need to put things on their places...
P.S. I think we'd better to continue this discussion by email.
--
Best regards.
Pavel Kutakov
SQLLY Development
www.sqlly.com
 

Re:Reverse engineering with ERWIN

Craig Stuntz [TeamB] writes:
Quote
WillR writes:

>Declarative vs triggers... I found some early problems with
>declarative logic -- can not recall why now -- but now I have settled
>on triggers to do the cascading deletes and I have no problems.

I don't know of any issues with declarative constraints other than
potentially useless indices, but I do know of an issue with simulating
it with triggers:

1) There is a master record and a detail record A.
2) User 1 starts a transaction and inserts detail record B with the
same master. The statement succeeds.
3) User 2 deletes detail A and the master. Since user 1's transaction
hasn't been committed yet this works.
4) User 1 commits. Oops. No master now.

Thanks for pointing that out Craig.
It shouldn't happen in this particular database because most (100% or
close to?) of the places the issue would show up are reference tables
(with cross connects)- despite the fact that they are probably 100's of
thousands of items. They are under the control of the administrator --
and typically batch entered. We just need to update or delete all
references at once if/when the tables are altered. Then only one user --
the Chief Geologist and system administrator -- should be altering this
master data as it could potentially alter completely the meaning of some
results and data sets.
_However_, now that you have drawn this to my attention I will review
all the Master-Detail relationships in the sampling and business records
to see what the odds are of a conflict! Usually though I "cancel" and
time-stamp a record on this side (even a detail item) -- it is never
deleted completely as the audit trail would be destroyed as mistakes and
corrections must be clearly visible. Not a good idea to destroy the
audit trail when potentially the SEC -- or it is equivalent in Canada or
Europe -- could demand a complete audit of a sample set and the
calculations, or payments made to various governments. :-)
My recollection is that because of the complexity of the relationships
the tool generated code that caused IB to hiccup on generation of the DB
when I used _only_ declarative referential integrity. So I went through
the DB and made some choices as to where I thought the declarative or
the trigger model was most appropriate. The generation problems
disappeared. Like other incidents of this type I assumed I was inept and
did not fully document the case. Perhaps it was a bug in IB -- perhaps I
was inept. I just don't know now. Probably the version control in
CaseStudio has a record of the case where the problems occurred. If so I
may try re-generating the DB under the new "IB" 7.5.1 (service packs
installed) and see if I still have the problem.
--
Will R
PMC Consulting