Modelling an existing SQL Server table

[Anon Anon]

I have successfully imported a few tables from a SQL Server database as well as from and IBM i DDS defined database. The IBM i stuff using the integrator worked pretty smoothly. Tweaked some inheritance and 'Bob's your uncle'. Got format level identifiers spot on.

Now for the SQL Server.

The inheritance wasn't nearly as good but for most data types I was able to tweak this. Therefore I am nearly happy.

I have hit a couple of stumbling blocks:-

How to model NULL and NOT NULL so it is automatically generated.
How to model SQL smalldatetime data type.

Happy to receive any advice. I will continue to read away in the background.

Kind Regards.

Lee.

[Anon Anon]

Okay.

I have moved this on slightly.

I have no issues modelling most of the default SQL Server types using SQL format SYS triple. And with the very latest build (Downloaded for CA Support). I also not have access to the NVarChar types etc which make a huge difference for me and the files I am importing.

Back to my problem.

I have successfully modelled the smalldatetime and bit SQL server types by using the DBMS type NME triple and delcaring a name object as smalldatetime with the literal value set as smalldatetime. This then generates up the correct DDL.

I am yet to have a solution for the NULL and NOT NULL that is on my SQL file definition that I want to import.

An example of the stuff I want to model is

[MAIN_MAKE] [nvarchar](20) NULL. The null is the sticking point.

It might be a case that I don't need it. Any tips.

Lee.

[Dean_Eshleman]

Hi Lee,

I also struggled with the NULL, NOT NULL issue. It appears that if you don't specify anything, SQL Server will default to NULL. In order to generate the NOT NULL, I created a source code that contained "NOT NULL,". I then used the FLD DBMS script SRC triple to assign the script to a field. Then, when I generate the DDL, it adds the NOT NULL, to the DDL. The only problem this causes is if the field is the last one in the file. Then, you end up with 2 commas because for some reason Plex adds another comma on a seperate line after the last field. Then, you need to edit the generated source to remove the extra comma before doing the build.

Dean Eshleman,
MMA, Inc.

[Anon Anon]

Thanks Dean.

All worked fine. I should have spotted that in the help. I am happy to report that I have now successfully fully described my SQL Server entities.

Thanks for your help.

Kind Regards.

Lee.

[Legacy User]

Ideally I think the NULL/NOT NULL should come from the ...optionality sys continuation triple for the relation (NOT NULL gets specified for primary keys), though this doesn't quite fit with the "empty value" approach used for Plex.

Using FLD DBMS Script SRC can be a pain if for instance you attempt to set the field as an identity column with IDENTITY and that entity then owns another where the superkey is not replaced...

I think you only need the NULL/NOT NULL inside of Plex if it isgoing to become the "master source" for the definition of the database and another non Plex app requires the database to have the NULL/NOT NULL definition.

Regards,
Jeremy Hutchinson
Desynit Limited
:: Plex Wiki :: 2E Wiki ::
:: Plex/2E Feed Aggregator ::
:: Edge Forum Archive ::

[Anon Anon]

Fair points Jez.

Lee.

[George Jeffcock]

yep Jez...pain in the you know what..."Using FLD DBMS Script SRC can be a pain if for instance you attempt to set the field as an identity column with IDENTITY and that entity then owns another where the superkey is not replaced..." Only way round was to use the attribute replace as you say...only new development really...and as the IDENTITY field is highly likely to arrive on other entities via owned/refers this is a recurring issue..

 

Have submitted the comma problem to be fixed.