When you set Transfer to use a table that has an ntext or text column on SQL Server 2000 (and perhaps later versions) and also a many to many relationship you may receive the following error when you attempt to retreive the collection for the many to many:

view plain print about
1[Macromedia][SQLServer JDBC Driver][SQLServer]Only
2text pointers are
3allowed in work tables, never text, ntext, or image
4columns. The query
5processor produced a query plan that required a
6text, ntext, or image
7column in a work table.

For example I have the following tables set up

view plain print about
1Table Name:
2Table1
3id        char(35)    primary key
4title        
5varchar(255)    
6description    ntext
7
8Table Name: Table2
9id        
10char(35)    primary key
11description    
12varchar(255)
13
14Table Name: Table1_Table2
15table1_id    char(35)    
16composite key
17table2_id    char(35)    composite
18key

And the following transfer configuration:

view plain print about
1<transfer>
2    <objectDefinitions>
3        
4<object name="Table1" table="table1">
5            <id
6name="id" type="UUID" generate="true" />
7            
8<property name="title" type="string" nullable="false" />
9            
10<property name="description" type="string" nullable="true" />
11            
12<manytomany name="Relation" table="Table1_Table2" lazy="true">
13                
14<link to="Table1" column="table1_id" />
15                
16<link to="Table2" column="table2_id" />
17                
18<collection type="array">
19                    <order
20property="description" order="asc" />
21                
22</collection>
23            </manytomany>    
24        
25</object>
26        <object name="Table2" table="Table2">
27            
28<id name="id" type="UUID" generate="true" />
29            
30<property name="description" type="string" nullable="false" />
31        
32</object>
33    </objectDefinitions>
34</transfer>

So when you call getRelationArray() on a Table1 object. You will receive the error. To correct this I took the following steps:

Create a for table 1 with the ntext column casted to varchar:

select id,title,cast(description as varchar(8000)) as description from table1

Save this as vwTable1.

Change your transfer config to point to the view instead of table one so

view plain print about
1<object
2name="Table1" table="table1">

becomes

view plain print about
1<object
2name="Table1" table="vwTable1">

Now, you wont be able to run it yet because the view is not updatable since you used the casted field. To overcome this we are going to use an INSTEAD OF trigger.

You need to create 2 triggers. One will be called whenever UPDATE is called on the vwTable1 and the other will be called when INSERT INTO is called on vwTable1

You can create the triggers as follows, obviously change it to suit your table's needs:

view plain print about
1CREATE trigger io_trigger_insert_vwTable1 on
2vwTable1
3instead of insert
4as
5begin
6    set nocount
7on
8    if (not exists (select t.[id] from table1 t, inserted i where
9t.[id] = i.[id]))
10        insert into table1
11        
12select
13            [id],
14            
15[title],
16            [description]
17        from
18
19            inserted
20end

view plain print about
1create
2trigger io_trigger_update_vwAd on vwAd
3instead of update
4as
5
6begin
7set nocount on
8if (exists (select t.[id] from table1 t,
9inserted i where t.[id] = i.[id]))
10    update
11        table1
12
13    set
14        title = i.title,
15        
16[description] = i.[description]
17    from
18        table1
19t, inserted i
20    where
21        t.[id] =
22i.[id]
23end

Once you have created these triggers you should be able to use Transfer as you normally would. Using the casted column in the view will allow you to call the getRelationArray(). The SQL that Transfer creates will no longer be invalid.

You may run into a couple of other items though. First, your ntext field is now limited to 8000 characters since you are casting it to varchar. You may be able to increase this, but I haven't tried. Your mileage may vary, but be sure before the value is passed to transfer that it is validated as less than 8000 characters (perhaps in your decorator). Next, if you have any non-null, defaulted columns in your base table (a created date perhaps) you HAVE to provide a value when you call insert into vwTable1. Since Transfer is creating this SQL you have to ensure that Transfer includes that column. This means that you cannot use the ignore-insert="true" option in the transfer configuration. SQL Server will still respect your default value, as long as you provide that column in the sql statement pointed at the view.

You can find more information about INSTEAD OF INSERT and INSTEAD OF UPDATE triggers on the MSDN site.

I have only used this in a simple application with two base tables and one relationship table, so if your setup is more complex it may not work out for you.

This entry was posted on February 17, 2009 at 5:43 AM and has received 1778 views. There are currently 0 comments. Print this entry.