Increasing the Character Limit for ALI Studio in SQL Server

By now you’re aware of our stated problem – we need to increase the size of text fields in Aqualogic Studio. We’ve already found the file used for the JavaScript, and used a new Cool Tool (HxD) to assist in the recompile, but there’s still a problem: when Studio creates new forms, it also creates a separate table in MS SQL Server. And the size of text fields in those tables that already exist is still 1,000. So if we just update the Javascript, our existing forms aren’t going to have the proper error checking, because our JavaScript is preventing field sizes of, say, 10,000 characters, but at the database layer, the size of those fields is still 1,000 characters. Even worse, if you try to increase the size of the table in the code to >4,000 characters, SQL Server will reject it because the regular nvarchar data type doesn’t go over that limit:

2011-04-21 14:54:54,819 ERROR [rocessor25] AppDesignerHandler: Error processing wizard form post
Error creating new user database ‘Survey Database’.
– [XNRC39]The size (10000) given to the column ‘test_10000’ exceeds the maximum allowed for any data type (8000).
at com.plumtree. studio.model.data. access.TableDAOSQLServer.create (TableDAOSQLServer.java:220)
at com.plumtree. studio.model.app. Table._create (Table.java:585)
at com.plumtree. studio.model.app. Table.save (Table.java:618)

So, we have two problems: first, we need to tell Studio to create these fields with a type of NVARCHAR(MAX) rather than NVARCHAR(10000), and second, we need to update all existing tables.

The first problems is pretty straightforward – we just need to update the TableDAOSQLServer.java file (or, if you’re on Oracle, TableDAOOracle.java). Change:

      sqlBuffer.append(this.mUserColumnType).append("(").append(this.mUserColumnWidth).append(") ");

…to:

      sqlBuffer.append(this.mUserColumnType).append("(MAX) ");

… and recompile as mentioned in the last post.

The second problem requires some SQL Server voodoo – we need to write a SQL Script that generates a SQL Script. So, if you run this script as your studiodbuser:

SELECT 'ALTER TABLE ' +  syo.name 
    + ' ALTER COLUMN ' + syc.name + ' NVARCHAR(MAX);'
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'nvarchar' 
    and syo.xtype='U'

… it will produce a SQL Script that looks like this:

ALTER TABLE PTU_SHPR2_Progress_Revi ALTER COLUMN Additional_Comments NVARCHAR(MAX);
ALTER TABLE PTU_SHPR2_Progress_Revi ALTER COLUMN U__Is_the_work_proceeding_i NVARCHAR(MAX);
ALTER TABLE PTU_SHPR2_Progress_Revi ALTER COLUMN U__Is_progress_towards_the_ NVARCHAR(MAX);

So basically, you’re using s script to find all the existing text fields, and creating a new one to actually increase the size limits on those fields.

Cool? Cool.

Tags: , , , ,

Leave a Reply