Data types in schema

[Thomas Mercer-Hursh]

Looking at an EAP file in Access, I see that the character fields are a mixture of Text and Memo datatypes, i.e., 255 or 64K limits.  In an OpenEdge repository ... should I ever get that working ... everything is set up as type Character which has a 32K limit.  For most purposes, this isn't an issue, but there are a dozen instances in t_UMLPATTERN which are over 32K and don't load.  And, I know that there are instances in the code we will be working with where a single code block would exceed 32K (yes, I know ... it's legacy code).

So, might it be possible to change the schema to make selected fields into CLOB, i.e., something which would come through the SQL as LVARCHAR?  Would EA be likely to accept that?

[«Midnight»]

First, make sure you've checked the Jet 4.0 option (Tools | Options main dialog) for every EA user.

Consider what I said in your earlier thread about tagged values. You can set these to memo type, which should allow you to store a CLOB.

[Thomas Mercer-Hursh]

I think I know what I can do if I leave the database as Jet ... and, in any case it is easy enough to make a copy and just try it, but I have strong reasons why I want to move it to OpenEdge to enable other programming, so I am wondering what will happen there.

[«Midnight»]

You're stuck with Jet as the back end for EAP files regardless. What I meant was to switch EA to Jet 4.0 - the default is Jet 3.51, which has the limitation you describe.

Once you make the switch - you must exit and restart EA for it to take effect - EA will remember this setting.

You might also want to download the Jet 4.0 EABase file from the Sparx site - you will need to look around a bit to find it - and use this in place of the one that installs with the product.

David

[Thomas Mercer-Hursh]

You're stuck with Jet as the back end for EAP files regardless.

? There are instructions for using a number of different databases as a repository.

[«Midnight»]

Yes, but then you are not using an EAP file. The model now resides in the repository.

[Actually, you can save the connection string in a file with an EAP extension, and then open the DBMS repository from that file, but that is not what we're talking about here.]

When you originally posted about a 64K field size limit in Access, you were scoping to the Jet (i.e. Access) engine. Check the sample SQL scripts for other DBMS engines, and you'll note that this limit is removed. The same should be true for Jet 4.0. At least it is for tagged values of memo type, which is what I referred to both here and in your other thread.

[Thomas Mercer-Hursh]

Seems to me there is a little confusion about what I am asking ... so let me rephrase.  The plan is to use an OpenEdge database for the repository for a variety of reasons, not the least of which the project is focused on ABL and we will be able to use ABL to create some things directly in the repository and this is a skill set available at the customer.

In Access, we have two character type fields, Text and Memo.  When we create an OpenEdge repository, both of these get mapped to the OpenEdge datatype of Character.  Character has a 32KB size limit, i.e., much bigger than text, but smaller than Memo.  OpenEdge also supports CLOB which maps to a SQL LVARCHAR with a 2GB size limit.

So, the question is, if I move selected fields in the repository schema from Character to CLOB, how likely is it that EA will be happy about it?

[«Midnight»]

My guess is that: if you map text fields to character, and if you keep the entries in OpenEdge to 255 characters or less you will be OK.

Remember that EA is Unicode based, so you might be able to get away with twice that many bytes. Even so, you'll be in trouble if you go back to EA since it will likely convert the string to Unicode and then take the first 255 characters of the result - not what you'd likely want.

EDIT: You are probably OK with the Access-side memo fields. Since you cannot exceed the current EA limit on the size of these. Just be careful when first porting to OpenEdge so you don't truncate something that is already in the model.

[Thomas Mercer-Hursh]

In the Sparx supplied schema, Text is mapped to Character and I understand the need to limit the size in anything I do external to EA.  I don't think this is much of a concern or problem.  The real issue is the Memo fields.  I have already seen one issue there, which is that t_UMLPATTERN.PATTERNXML contains entries in excess of 32K.  My other concern is the T_OPERATION.CODE ... not that the base will have existing contents, but that there are known instances in the legacy code which will be over 32K.

OpenEdge will support the Unicode just fine.

I guess the conclusion is that it might work and I just need to try it once I get past not being able to complete the copy and sequences issue.

[«Midnight»]

This sounds like an OpenEdge limit - as in field size limit, not a limitation of the engine - that you might be able to work around.

Perhaps use the CLOB for the appropriate fields on the OpenEdge side. Now all you have to be careful of is porting a model back to an EAP file or some other DBMS back end.

I'll give up on the suggestion of using a memo-type tagged value.

[KP]

Seems to me there is a little confusion about what I am asking ... so let me rephrase.  The plan is to use an OpenEdge database for the repository for a variety of reasons, not the least of which the project is focused on ABL and we will be able to use ABL to create some things directly in the repository and this is a skill set available at the customer.

In Access, we have two character type fields, Text and Memo.  When we create an OpenEdge repository, both of these get mapped to the OpenEdge datatype of Character.  Character has a 32KB size limit, i.e., much bigger than text, but smaller than Memo.  OpenEdge also supports CLOB which maps to a SQL LVARCHAR with a 2GB size limit.

So, the question is, if I move selected fields in the repository schema from Character to CLOB, how likely is it that EA will be happy about it?

You may come across problems using patterns and profiles which can both exceed the OpenEdge field size limit. With patterns, you can edit the exported XML, delete the preview image (which is what usually pushes them over the limit) and then import them and use them. With profiles you can embed them in an MDG Technology and "point to them" (using the "Settings > MDG Technologies" command) rather than import them into your database. There may be other problems that I don't know about, in which case hopefully one of my colleagues will reply...

[hd]

OpenEdge also supports CLOB which maps to a SQL LVARCHAR with a 2GB size limit.

EA inserts data directly into the relevant fields. OpenEdge won't allow this (SQL Server, Oracle, MySQL, PostgreSQL all do), which is why there are no CLOB fields in the OpenEdge repository.

There is also a limit on the size of a row where varchars are used...

Maximum length for VARCHAR

• The maximum length can be as large as 31,995. The sum of all the column lengths

of a table row must not exceed 31,960.
[/list]

(from OpenEdge SQL Reference)

[Thomas Mercer-Hursh]

I know about the limit for Character which maps to VARCHAR, but my understanding was that CLOB mapped to LVARCHAR and so one could update and query those fields.

What do you mean by <I> EA inserts data directly into the relevant fields. OpenEdge won't allow this</i>?  Aren't they all using SQL, albeit in different flavors?

[hd]

Using ADO...