Sybase ASE15 and Import DB schema from ODBC.

[johnraf]

Using: EA v7.0-build 818  

I just tried out "Data Model->Code Engineering->Import DB schema from ODBC source..." with Sybase ASE v15.0, however I encountered a few problems and was wondering if there are workarounds.  

NOTE: The Database I was working with was created with a series of .SQL files which are used to define the schema and these scripts work fine with the "isql -i" option.

Here are the issues I encountered in (order of importance):

(a) Primary and Foreign keys are defined using sp_primarykey and sp_foreignkey and during the import "-- foreign keys for table ..." appear in the "Current Action" list, however when the import is complete there seems to be no evidence of the PKs and FKs in the resulting model. I noticed the following in the build 716 release notes:

   General Improvements - Importing unique indexes and foreign keys from Sybase ASE.  

The indexes are fine, but the PKs and FKs don't appear in the model. Is there a special setting required before the import for the PKs and FKs to work?  

Of course I could create these manually after the import but there are alot of tables, so I would really prefer not to have to do this. Anyway I tried doing this for one table and doing a synchronisation import as an experiment but the PK/FK information was removed from the model by the import.

(b) The Stored Procedure "Import as class operations"option is a neat feature but has problems parsing many SP definitions (this problem appears to be something to do with some comments in the .sql files used to define the SP). Example error:

Error importing Procedure: SL_getDayDefine => There was an error parsing on line 71. Unexpected symbol: select  

(c) We use the following commands to define Table Column defaults before inserting data into the DB:  

create default <default-name> as <value>
sp_bindefault <default-name>, "<table-name>.<column-name>"

However if such commands have been run before the import then EA sets column inital values to "eate default <default-name> as <value>".

This can be seen on the tables in the Data Model diagram or in the following field Column->Properties->General->Initial.

(d) There are alot of custom datatypes defined in the DB, however these are not imported. Does one have to manually define these in EA with the Settings->Database Types dialog?

(e) Is there any way to globally set the Database field of the Tables in a Data Model in EA?  After an import the Database field of each Table in the model is blank and it is painful to have to manually set this field for each table. In anycase surely EA can set this automatically during the import since a "Sybase ASE" ODBC Data Source is used during the import.

[«Midnight»]

John,

Here's my best guess, in no particular order.

(d) Depending on what you mean by "custom:"
If you mean domain definitions, then you are out of luck. EA does not support DBMS domain types, at least not through version 7.0 inclusive. We've often (loudly) requested this. Perhaps you should submit a feature request. See the end of my message for more information.

If you mean data types that are specific to ASE, I think you will need to define your own database data types. I believe there is a 'clone' option, to start with a similar database. See if you can clone ASA or SQL Server and go from there.

(e) I keep thinking there is a default you can use, but I might be wrong. An add-in might help a bit. But - and this is an issue we've often brought up to Sparx - there are no API hooks to the import process.

[There are no hooks to the DDL generation process either. If there were you could make some headway with the rest of your concerns.]

(a)(b)(c) These sound like bugs. Submit bug reports (separately, I think, for reasons below).

So, for the bug reports and feature requests, here's what to do. For each one start a thread, probably in the Suggestions section for feature requests, this section or the General section are fine for the bug reports. Doing this lets the community know what's going on, and allows continued discussion. [You could use this thread, but there's a lot of things you're addressing. It is up to you whether you want to divide it up or not.]

Follow the Report a Bug or Feature Request link (as appropriate) under the Support link at the bottom of any forum page. Fill out the form as appropriate. [You should probably open the link as a new page or tab, so you still have the forum open. Depending on your browser configuration this could save you some real grief in the next steps.] In the text of your report quote the title of the corresponding forum thread. This allows the Sparxians who address your reports to quickly get into the discussion - rather than relying on random 'discovery' during a scan of the forum - and to participate. It also helps Sparx judge the degree of support expressed for feature requests.

Finally, if you hear back from Sparx on any of these - they might propose a workaround, ask for or provide relevant information, or indicate a possible solution date - please post back so the rest of the community knows.

HTH, David

[johnraf]

Thanks for the prompt and clear reply David.

(1) I'll do as you suggest and submit (a)(b)(c) as bugs and reference this posting. I guess there's no way on this site to browse existing bug submissions?. So I can ensure I'm not duplicating issues. I presume there isn't, due to your  advice to start suggestions threads.

(2) Regarding (d) I just mean types based on common types (like varchars, ints etc.). For example:
execute sp_addtype pp_number, "varchar(30)", "not null"
I'll try experimenting with the options you mentioned.

(3) Regarding (e) It's a pity the API is a non runner as that was something else I was thinking of looking at. There is also a problem with the DDL export (seems to be no way to add 'go's). I'm hoping if I export the model to XMI I can convert that into something else that can be used to configure the DB.

[«Midnight»]

John,

Things are not as bad as they seem, based on your reply.

(1) There might have been some submissions based on these, but obviously they have not been picked up. It could be that they were not specific enough, there was not enough information, or they did not make the cut on priorities. Making clear submissions - one for each issue - should help resolve or 'promote' these issues.

(3) You can get the GO statements without too much trouble. On the Generate Package DDL, take a look at the Use [ ] as SQL Terminator entry. Instead of the default ";" enter GO instead, and make sure that you clear the checkbox for on the same line (I think it is cleared by default). This will issue a GO statement after each statement, but that's probably acceptable in the real world. An alternative is to create separate SQL file for each set of entities - tables, views, and such - and string them together with a hand-built file (which you could generate as a stored procedure). Not good.

(2) I think you're in luck, at least to some extent. EA does allow you to model stored procedures, again to some extent. If you are creating your own procedures you can define the parameters. Regardless of whether calling stock procedures or writing your own, you've got to write some code. EA will import procedures, but it is fairly limited in reverse engineering them. Read the documentation carefully, and play around with them a bit.

Note that EA has two methods for creating stored procedures: as single elements, one for each procedure; or as a group of procedures in one element (you can have several of these, grouped by any criteria you choose). However, on reverse engineering, EA seems only to use the single procedure per element mode.

Coda: (3) We keep asking for direct access to the DDL engine, but thus far no go. Yet another suggestion that needs to be made, repeatedly. But...

HTH, David