Sparx Systems Forum

Enterprise Architect => General Board => Topic started by: rupertkiwi on April 28, 2021, 12:50:24 pm

Title: SQL ODBC Import element creation
Post by: rupertkiwi on April 28, 2021, 12:50:24 pm

Hi there,

I have connected to a SQL Server database using the odbc connection in Database Builder and was wondering if it is possible to import the data from a view and create elements in EA using the view data?

Thanks,
Rupert

Title: Re: SQL ODBC Import element creation
Post by: Geert Bellekens on April 28, 2021, 02:03:06 pm

You can import a view, but it's probably going to be a bit disappointing. EA creates a view element that contains the SQL select query, but that's it.

Geert

Title: Re: SQL ODBC Import element creation
Post by: Paolo F Cantoni on April 28, 2021, 03:33:12 pm

Quote from: Geert Bellekens on April 28, 2021, 02:03:06 pm

You can import a view, but it's probably going to be a bit disappointing. EA creates a view element that contains the SQL select query, but that's it.

Geert

As Geert says, if you import ONLY the view, you'll get only the textual SQL query.  However, if you have already imported the views and tables on which the view depends, I believe you'll get a dependency relationship between the view item and the independent item.

HTH,
Paolo

Title: Re: SQL ODBC Import element creation
Post by: Geert Bellekens on April 28, 2021, 03:53:25 pm

Quote from: Paolo F Cantoni on April 28, 2021, 03:33:12 pm

Quote from: Geert Bellekens on April 28, 2021, 02:03:06 pm

You can import a view, but it's probably going to be a bit disappointing. EA creates a view element that contains the SQL select query, but that's it.

Geert

As Geert says, if you import ONLY the view, you'll get only the textual SQL query.  However, if you have already imported the views and tables on which the view depends, I believe you'll get a dependency relationship between the view item and the independent item.

HTH,
Paolo

True, but I'm not sure the dependencies can be trusted upon to be complete.

Because the way views are imported was not convenient for us (we needed to map the columns of the views to our Logical Data Model) we actually made a script to convert views into tables on a dummy database, and then we imported the view as tables in EA.

Geert

Title: Re: SQL ODBC Import element creation
Post by: rupertkiwi on April 29, 2021, 10:52:33 am

Thanks Geert.

Does anyone have an example of how they compare what they have in their model versus the live database using the Database Compare module in the Database Builder?

Thanks,
Rupert

Title: Re: SQL ODBC Import element creation
Post by: Paolo F Cantoni on April 29, 2021, 11:04:59 am

Quote from: rupertkiwi on April 29, 2021, 10:52:33 am

Thanks Geert.

Does anyone have an example of how they compare what they have in their model versus the live database using the Database Compare module in the Database Builder?

Thanks,
Rupert

Hi Rupert,

"Just DO IT!" (as the ad says).  You may need to run the comparison multiple times to remove some false positives.  But otherwise, our use has been as expected.  Do you have any specific questions?

HTH,
Paolo

Title: Re: SQL ODBC Import element creation
Post by: rupertkiwi on April 29, 2021, 11:28:17 am

Hi Paolo,

I would like to select a list of elements in the EA model and compare them to a list from a view in the live database.

Is that possible?

Thanks,
Rupert

Title: Re: SQL ODBC Import element creation
Post by: Paolo F Cantoni on April 29, 2021, 12:26:08 pm

Quote from: rupertkiwi on April 29, 2021, 11:28:17 am

Hi Paolo,

I would like to select a list of elements in the EA model and compare them to a list from a view in the live database.

Is that possible?

Thanks,
Rupert

Hi Rupert,

I don't think that's what the compare option in the Database Builder is about.  It's only for comparing the LIVE database structure with the model previously reversed.  "this table has a new column, this view has changed the SQL definition, this is a new procedure, etc."

My understanding of what you are trying to do is NOT that.  You're trying to compare an arbitrary set of elements with the live DB (or have I misunderstood).

Paolo

Title: Re: SQL ODBC Import element creation
Post by: rupertkiwi on April 29, 2021, 12:50:24 pm

Hi Paolo,

Yes that's right. I have a bunch of elements contained in my EA model and I want to compare them to a list of data that is contained in the view in the live database.

I don't think it is possible but thought I would try

Thanks,
Rupert