Topics

1

Automation Interface, Add-Ins and Tools / How to add PK to columns of tables via DML operations?

« on: November 20, 2024, 02:48:53 pm »

I reverse engineer several database types by querying their metadata objects, exporting to Excel and using Office MDG to import into EA (16.1).  Works well except that primary key columns are imported as <<pk>> stereotype but in EA it is treated as any stereotype and the PK -  NN boxes in the Properties window remain unchecked: EA does not recognize it as a primary key.  So I have to manually check the PK box for every column with a <<pk>> stereotype.
I would like to do that via a DML script like (pseudocode:) UPDATE t_attribute SET somethingsomething = 'pk' WHERE stereotype = 'pk' .  But In Inside EA book I could not find what table column needs to be updated and how.  If anybody knows please help!  Thanks!
ps sorry no time to learn VB or scripting languages... my only option is to run DML on the project database

2

Automation Interface, Add-Ins and Tools / Can I use EA as frontend to run SQL queries on SQL server database?

« on: October 20, 2023, 02:49:14 pm »

Hello, EA 16 professional, *.qea project on my PC.  I'm reverse engineering a SQL Server database; the ODBC connection worked fine.  However I need to query the database system views to extract the DDL of views and procedures and view/procedure attributes (it was not retrieved with the reverse engineering).  I've done this before using DBeaver, but I don't have JDBC connection to this database.  Does EA have a SQL terminal window that I can open and run my queries on a non-project database?  Can the results be exported as csv.  I really don't want to install SSM or Azure studio.  Thanks!

3

General Board / Help I forgot how to set up Oracle DB reverse engineering in my Windows 10

« on: August 02, 2023, 11:30:49 pm »

I've been reverse engineering PowerBI and PostgreSQL databases a lot in past few years, but it's been a long time since I worked on an Oracle one (this one is 19C 64 bit).  I remember it worked well when I did it.
The oracle help pages and various forums tell you how to install a client to query but not how to connect with EA.  Could anyone help me remember?  Are these steps correct:
1. Download instant client basic lite and instant client ODBC for windows 10 64 bit
2. Unzip instant client and ODBC to C:\ORACLE_HOME directory; the unzip adds C:\ORACLE_HOME\network and C:\ORACLE_HOME\network\admin. 
3. Unzip the odbc driver so that the odbc_install.exe file is in C:\ORACLE_HOME\ directory
4. Get the tsnames.ora file and paste it in C:\ORACLE_HOME\network\admin
5. Right click on odbc_install.exe and run as administrator
6. Open Windows 10 ODBC Data Sources (64-bit) and in System DSN tab, fill in the driver configuration.   However I'm not  getting the tsnames.ora data filled in in the ODBC driver configuration tab like I should
7. Open Windows environment variables and add a user variable Path with value C:\ORACLE_HOME.  Save.
8. Open EA 16, Settings / ODBC Data Sources, make sure the new data source is listed
9.  In EA, Develop / Import and reverse engineer.

**PHEW**

I'm curious if the whole oracle client and tsnames.ora are needed or can I just download the driver file and run odbc_install.exe?  Then add the TSA file parameters directly in the Windows ODBC manager window.

I don't yet have a login to try so would appreciate if anyone can share experience thanks!


[EDIT: found the problem!  I needed to include system variable TNS_ADMIN with value C:\ORACLE_HOME\network\admin in my environment variables.  Works now.]

4

Automation Interface, Add-Ins and Tools / My feature request for office integration MDG - please submit similar requests

« on: April 14, 2023, 02:13:50 pm »

I submitted below requests, please submit similar ones to support it!
(Note: I use the MDG to reverse engineer several databases for which we have no CASE tool - in my case HANA and Databricks.  Usually 500K to 1 M rows extracted from RDBMS metadata and imported into EA)

1. Add precision to the Additional properties > Attributes profile.  Length is already there so by adding precision we can import full datatype details
2. Add the ability to import connectors details by source/target Alias in the connector import profile.  Currently you are forcing the user to import objects, then export so we can know the object and connector eaguid, and finally import connectors: this is very cumbersome for large models.  We can assign a suitable alias to objects and connectors when we import, then we can use the same alias to import the connector details right away without needing to export the model just to get the eaguids.  Import connectors by Alias is already available in the object import profile so the connector detail profile should have the same capability.

Clean up the MDG code so imports are much faster. I routinely import from 500K rows of Excel and it takes several hours.
In the current Office MDG it's impossible to set primary/Foreign key constraint on import of classifiers of type <<table>>.  This is a pity because the MDG allows us to reverse engineer any database using the DBMS object metadata.  My suggestion is:
3. Add capability to assign primary key constraint to any column to the classifier import profile (keep in mind composite keys)
4. Add capability to assign primary and foreign key constraint to any column to the connector detail profile (keep in mind composite foreign keys and primary key that include a FK)

Today the options of connector detail profile are not available in the classifier import profile for Excel.  This forces us to break down imports into at least 2 steps.  Having all options in 1 profile means we can create one Excel sheet, import it and be done (instead of creating one Excel, import, save the synchronization Excel, update it with connector details, re-import).  I don't see the benefit of having 2 different profiles for classifier and connector details

5

General Board / Help - how to import tables, columns and PKs from Excel

« on: April 11, 2023, 09:13:52 pm »

(cross post - I emailed Sparx support about this but I'm on deadline so also asking here)

I use both Sparx Office Integration MDG and the Bellekens excel importer to import thousands of database views and tables into EA to make ERD diagrams etc.

I can import everything I need except primary keys. I import the columns and if they are a PK, the import includes a Method of 'PK' stereotype owned by the <<table>> classifier: this would result in the column having PK properties in EA, *except* I cannot find a way to assign a column to the method during the import (I can import method parameters but the PK assignment is not a parameter).

After the import, if I manually assign the right column to the method using the table 'features' menu, everything works fine.  Does anyone know how to assign during the import (or after via a SQL UPDATE in the EA model database...).  Thanks!

6

Automation Interface, Add-Ins and Tools / How to change the Search Category menu in searches

« on: March 23, 2023, 03:14:49 pm »

For EA search (CTRL-F) I want to break down my custom queries into multiple categories instead of have them all bunched in 'My searches' menu.  And I want to hide or delete some other categories to reduce clutter.  Any way to do this?  Maybe there's some text or XMI file I can edit (I'd like this t be available in all my Sparx projects and be retained when I upgrade to new version of EA).  Thanks!

7

Automation Interface, Add-Ins and Tools / How to share custom database setup (datatypes and ddl generation templates)

« on: March 13, 2023, 02:27:41 am »

Hello I set up my own settings in project datatypes and in ddl templates to round trip engineer from/to SAP HANA.  But they only show in the one project file (*.qea on my hard disk) .  How can export them to other projects?  Without using MDG because I don't have time to muck around with it.  I remembered there was a metadata export menu but cannot find it.  And I don't think it will work for ddl templates.  Thanks for any advice!

8

Uml Process / What is a Sparx interaction point? It seems not to be in the UML spec

« on: February 20, 2023, 11:31:08 pm »

After googling and consulting the UML reference manual I cannot find this definition.  Seems to overlap UML  parts concept.  If it's not in the UML spec would it be better to remove from EA?

9

Automation Interface, Add-Ins and Tools / Bellekens importer error 'Name cannot be blank'

« on: February 02, 2023, 08:10:09 pm »

I have been using Geert's importer for a couple of years, but since I updated to EA version 16.1 (or maybe since I got a Windows update?  Can't tell) I get the following error when I import attributes into classes:

Run-Time error '-2147221504 (80040000)'
Name cannot be blank

I have been trying different things but always get the same error, does anyone have any experience on how to solve?  Thanks!

ps Importer version 5.0

10

Automation Interface, Add-Ins and Tools / Help me fix my EA 16 query it crashes when run in EA (new problem!!)

« on: December 29, 2022, 02:05:29 am »

For the life of me, I cannot understand why this code crashes EA 16.1 on my Win 10 machine (32GB RAM). 
It's a custom query (My searches) to generate a DDL template for HANA views.  My repository is standard *.qea file on my local hard disk.
EA shuts down every time I run it.  If I run from DBeaver (accessing the *.qea file and using the package GUID) it runs fine.

Bug?  My EA instance is possessed?  Any ideas (I need to use the #Branch# statement)

Code: [Select]

SELECT
'CREATE VIEW  "_SYS_BIC"."' || t_package.Name || '/' || t_object.Name || '" AS' || CHAR(10) || COALESCE(vdef.notes, '')
FROM t_object
LEFT OUTER JOIN t_package ON t_object.Package_ID = t_package.Package_ID
LEFT OUTER JOIN t_objectproperties vdef ON t_object.Object_ID = vdef.Object_ID AND vdef.Property = 'viewdef'
WHERE t_object.Package_ID IN (#Branch#) AND LOWER(t_object.Stereotype) LIKE('%view%')

This runs OK in EA:

Code: [Select]

SELECT t_package.Name || t_object.Name  || COALESCE(vdef.notes, '')
FROM t_object
LEFT OUTER JOIN t_package ON t_object.Package_ID = t_package.Package_ID
LEFT OUTER JOIN t_objectproperties vdef ON t_object.Object_ID = vdef.Object_ID AND vdef.Property = 'viewdef'
WHERE t_object.Package_ID IN (#Branch#) AND LOWER(t_object.Stereotype) LIKE('%view%')


11

General Board / HELP editing DDL templates: add a prefix on the DDL name of all objects

« on: December 24, 2022, 02:22:47 am »

EA 16.1

I'm editing the PostgreSQL templates to generate DDL for SAP HANA from a ERD.

INCLUDE_OWNER  seem to be doing nothing even though I created a tag named Owner with value = the schema name and also populated the Owner field in the table's Properties.
 I set owner to 'True' in the DDL generation options.  Not sure why INCLUDE_OWNER is failing, or maybe the Owner value is not populated in my new database type / template.  Any ideas?   

Even better, I would like to prefix the object name (table, view name) with the name of the EA package, so I can create a package for each schema in my EA model and then forward engineer the whole thing, resulting in

CREATE TABLE "packagename"."tablename" ( etc. etc.)

Anyone know how to change the SQL template to do this?  I think I would need something like (row 19 on the PostgreSQL 'DDL Name' template)

$name= "_SYS_BIC." + [package name here] + %ddlTableName%

Also, how to escape the double quote character so the object name has double quotes before and after the  .  With
$name= "_SYS_BIC." + %ddlTableName% the result is:   CREATE TABLE "_SYS_BIC.Customer" instead of CREATE TABLE "_SYS_BIC"."Customer"

Thanks and season's greetings!

12

General Board / Why no 'Find in diagrams' menu for packages?

« on: October 31, 2022, 12:45:09 am »

Today I watched a couple of reruns of breaking bad, so read this in Jesse Pinkman's voice

YO why can't I find which packages appear in which diagrams YO.  That's messed up YO.  Package diagrams are a thing, you know?  Like drawings with packages and dependency lines and all that science stuff.  YO.  Byiaaches need to know if a package is showing up in a diagram or three, y'know whatImsayyin'?  YO.  Give us a 'Find in all diagrams' menu for packages already.  Geeez.  YO.

13

General Board / I'm getting a few crashes in EA 16 that never occurred in EA 13

« on: September 24, 2022, 01:39:28 am »

EA 16 on *qea file, Wiundows 10 32GB RAM

Just wondering is this is something normal, something due to the interface between Sparx and SQLite, or some bug in EA16.

On EA 13 with *feap files it was rock stable, never had any issues.

I can't keep a good log of crashes on EA16 but one occurred when I mistakenly ran an old Firebird query on the query window in EA; another when I was dragging a class from one diagram to another and clicking 'paste as link'.  Another time my fingers slipped on the keyboard while I was writing a query on the query manager, don;t know what keys I pressed.

Is anyone else experiencing similar issues?

14

General Board / EA16 what's the setting for double click = open properties?

« on: September 17, 2022, 12:37:00 pm »

In EA 13, when I double clicked on an object in a diagram or in the browser, the properties window would open.  In EA 16 the only way to open the properties window is to right click and use the context menu.  Is there a setting somewhere in EA16 to set double click as open properties?  I looked and could not find it.

15

General Board / EA16 bug? Project browser does not auto scroll

« on: September 17, 2022, 12:33:52 pm »

Hello in EA13 if an element name was too long to be displayed in the browser window, the browser would auto scroll to the left when hovering/clicking on the object, so you could always see the beginning of the object name, click on the expand triangle and move up/down its parents and children.  This is not happening in EA16: I have to use the browser's scroll bar manually.  Is there a setting to enable browser auto scroll, or is it a bug in EA 16?