Expanding your varchar columns beyond 255 in Clarify is easy with the right tools
We had a support case that came in this week asking if there were issues with expanding a varchar(255) field to a larger value, such as varchar(500). Specifically, this customer was asking about expanding the internal notes field on the notes_log table (table_notes_log.internal). This customer is on an older version of Clarify (version 8 or version 9, IIRC), but they’re using a more current version of Microsoft SQL Server (either 2000 or 2005, I forget exactly which one). These versions of MSSQL can handle much larger varchar lengths. I believe MSSQL 2000 can handle up to varchar(8000). I think the 255 limit is left over from the Sybase and Microsoft SQL Server 6.5 days.
It should work, I thought. One way to find out: let’s try it.
My environment for this test is Clarify 9 with MSSQL 2000.
Clarify Data Dictionary Editor
First up was trying to make the change with Clarify’s DD Editor. I made my schema file change (expanding the internal column to 256 characters), and attempted to apply the change to the database.
Using Clarify’s Data Dictionary Editor and a schema file, I got this very un-helpful (but common) message:
Gee, thanks. Perhaps you could tell me what the problem is? Regardless, no love here. So I moved on.
Clarify ddcomp
I then tried it using Clarify’s ddcomp.exe, and at least this time I got a meaningful error message:
Doing Schema Differences…
Could not read command arguments file ‘clarify.env’; continuing…
Loading database library libdb_d.dll in 2-tier…
Done loading.
Opening database ‘test9’… Done.
Reading data schema from database…
Checking relations…
Optimizing schema…
Done with schema
Schema Differences Error:
>> A problem was found reading line 3068 of data dictionary file. Possibly
the wrong file was supplied.
>> A problem was found reading line 3068 of data dictionary file. Possibly
the wrong file was supplied.
>> Invalid ARRAY_SIZE=256 on line 3068 (must be in range of 0 to 255).*** Schema Differences FAILED! ***
The relevant error being: Invalid ARRAY_SIZE=256 on line 3068 (must be in range of 0 to 255).
So it appears that Clarify schema editing tools (at least on older versions) are hard-coded to a 255 character limit for varchar columns.
Dovetail SchemaEditor
Let’s try the same test using the Dovetail SchemaEditor.
I whipped up a little schema script:
<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
<updateColumn table="notes_log" name="internal">
<length>500</length>
</updateColumn>
</schemaScript>
Then applied it to the database.
The difference report seems to indicate it worked:
Dovetail SchemaEditor Difference Report
———————————————————————————-
Date: 2007-05-15 13:53:39
Action: Apply
User: gary
Database Name: test9
Input File: notes.xml
———————————————————————————-
Changed schema
Changed table "notes_log"
Changed column "internal"
Changed "length" from "255" to "500"
Looking in Enterprise Manager, we can see that it was changed to 500:
And looking at it in BOLT (our online data dictionary viewer) we can also see that it was expanded to 500.
BOLT reads its data from the ADP tables, so now we’re sure that both the table itself and the ADP tables were updated properly.
So there you have it. The internal notes can now handle 500 characters.
I’m sure in some later version of Clarify (11.5? 12?), this is all possible. But there are many customers still on older versions who this post will apply to.
As my Dad likes to say, it’s easy when you have the right tools for the job.
