Expand a text field in file cabinet-1530110624

Hey all,

It seems pretty silly that we can't extend a Text field in DocuWare, that none of the field properties can be changed. I understand not allowing a type change, but extending a string should be legit because SQL Server can handle it with no problem.

Do we really need to remove and add a different field just to expand a Text field, or can I change it in SQL Server and have that work OK? I don't know of any other spot where the data structure is maintained, and the XML contol file doesn't define length, I don't think? Plus, we recently had a field manually added to file cabinet by support to work around an issue we are having with a Task Manager trial expiring. So it seems like the structure defines itself.

Any technical thoughts on this? Can I expand a Text field in the underlying structure and be OK?

Thanks,

Joe Kaufman

Hey all,

After looking more closely, I see the length of fields is stored in dwsystem.DWFileCabinet.settings as well as every XML file alongside stored PDFs in the file system.

So, it looks like changing even something as simple as Text size would break all existing control files for all documents in the cabinet. The structure itself and the settings field are not a big deal, but affecting all existing stored documents is not something I want to do.

If anyone else has ideas, let me know, but I guess we'll have to do the remove-and-add and try to make plenty of room in fields from the start moving forward.

Thanks,

Joe Kaufman

Joe,
You cannot change the length of a text field in the database. DocuWare records the field length in the System settings for the cabinet and in the documents header file. Changing just the database will cause problems.

To avoid having this type of problem DocuWare support a text field length of 2000 characters for 6.12 and up to 255 characters for 6.7 to 6.11.
I would use the max when creating the fields even if today you only want to hold a few characters.

Phil Robson
Senior Director Support Americas

Phil,

Thanks for the response. We probably won't always use max, but will certainly not skimp on it in the future.

Thanks,

Joe Kaufman 

I think 255 is nice figure. Takes no space if not used.

Phil.

True, and unless we know a field is going to be small (like a Yes/No select list, it can't hurt!

Incidentally, we have some a Text field defined as Text 1000 in one of our cabinets (6.11) -- it is structured as nvarchar(1000) in the SQL Server database. So, it sounds like 6.11 can go over 255? It defaults to 255 when you create a new field...

Thanks,

Joe Kaufman

Joe,
I did not check the versions specifically. I thought the 2000 legnth started with 6.12. So it must have been 6.11. Anyhoo, it was a very recent release. Oh, and I thought that the default was 255, but again could not check it when I replied.

Carry on! :)

Phil

Joe,

Against Docuware recommendations, I've done the text field length increase a number of times, even on well used FCs, without changing the document header XML files. Needs to be changed in 3 places for each field: in two places in DWFILECABINET table(dwlength and database length tags, and also in dwdata FC main table to change the actual sql column length.

Never had a problem. The trick I learned is to if not stop all Docuware services completely, at least have Docuware Administaration program closed at the time.

Tread warily. Just because Josef says he has done it his way, does not mean that you can do it and be successful. We will not support such actions if they fail. Caveat Emptor.
 

Phil Robson

Senior Director Support Americas

To everyone reading this thread. Please understand that this is a forum and there can be many different opinions offered. When it comes to DocuWare and its engine, I ask that the DocuWare support departments opinion be carefully weighed against any other. In this post, a DocuWare DSC who I consider to be quite expert with Docuware has offered an opinion. However, he has no mandate to support you. I do. Therefore if you do not follow my advice as the Senior Director of Support for the Americas, and follow the advice of an ADP's Tech, and it goes awry I will have no option but to send you to Professional Services as I will not support Josef's method.

Phil Robson
Senior Director Support Americas

Josef,

What becomes of the XML header files? They just start using the new length? If you tried to re-import the raw documents (e.g. to model recovering from catastrophic database failure without backup), would the documents with the old header files import OK? Not sure how much validation DocuWare does when it would try to recover files...

If we had used the field we want to expand in URL integration or API queries I might go for it. But it won't matter so much to us for this particular field. And only about 25 documents have the field populated, meaning we can easily transfer the index data without much trouble. If the latter were not true, that would be another reason to take this novel (but potentially risky) approach.

Thanks,

Joe Kaufman

If this is something you did not try and do not know how it will effect the system or how to trouble shoot it if it does go badly I would not try it on a live system ever.   The thing is buidling a new field to hold the data, deleting the old one then rebuilding the old field is a really common practice amoung many SQL programmers that know messing with LIVE data on a system can cause issues.  Rememer slow is SMOOTh and SMOOTH is fast.  The neat thing is you do not have to use Auto index to do it any more.....you can use the workflow to update the HEADER file off the database....so you could:

1: Copy the data from the table in question to another database....
2: Delete the field in question
3: UPDATE/APPLY everything....even logging out of configuration etc is a good idea.../
4: Log back in add the field back with the new size. 
5: Copy the data you backed up into the new field. (users can see the data now but you are not backed up)
6: Build a restore that will OVER Write the XML with Database.....
7: Run it.....done....

If you have a really big database or do not have autoindex this should work.

Hey all,

OK, I played around with this, creating a temporary file cabinet, storing documents, then expanding a Text field.

First of all, "expanding the text field" is fairly tricky:

• I did not turn off any services for DocuWare, but I was not performing any administration on the file cabinet in question elsewhere.
• You need to expand the field in the base file cabinet table in the DWDATA database. You may be prevented from doing so due to an option that prevents table changes, so you will need to turn that option off. When I expanded a field, it said it was also modifying the accompanying SECT and VER tables. Not sure why, as they appear to be unchanged.
• You then need to change the "settings" XML field in the DWFileCabinet table (DWSYSTEM database):
• Modifying data in an XML column is a PAIN.
• You need to change the "dwLength" attribute of the UserField in question, and the "length" attribute of the UserField's "DBType" node (sample update queries later).
• All data is preserved; no index values were lost, since this is a same-type field expansion so nothing gets truncated or cast to a different type.

So, that leaves the XML header files stored in the file system. I noticed some interesting things there:

• When I initially store documents to the file cabinet, the length of the Text fields is not defined. The "length" attribute in the XML header file is "-1". (I am not sure if this changes if you have auto-fill indexes or if documents come in via an Import Configuration -- I just brought documents in via a manual Inbox process).
• If you change an index field (any index field) of a document, the user-defined Text fields switch to storing the field length for all of the user-defined index fields. After expanding a Text field, the new (higher) length is stored in the XML header files while all other header files are left alone, either in their initial -1 state or with the previous (lower) field length.

So, older XML header files definitely get out of sync with the newly-expanded structure. I do not know what issues that might cause when trying to recover from some sort of terrible failure where the file system files are all that remain of a file cabinet. But, expanding the field does not seem to be problematic from a DocuWare interface standpoint, and everything seems to work OK after an expansion.

Here is the SQL Server query to change the "dwLength" attribute of the UserField node in question:

UPDATE DWFileCabinet SET
  Settings.modify('replace value of (/FileCabinet/Fields/UserField/@dwLength)[1] with "<expanded value>"')
  WHERE (fid = <key of DWFileCabinet table>) AND (name = <Name of file cabinet, to be doubly sure>)
  AND Settings.value('(/FileCabinet/Fields/UserField/@dbName)[1]', 'varchar(50)') = '<Database name of the expanded field>'

Here is the SQL Server query to change the "length" attribute of the UserField/DBType node in question:

update DWFileCabinet SET
  Settings.modify('replace value of (/FileCabinet/Fields/UserField/DBType/@length)[1] with "<expanded value>"')
  WHERE (fid = <key of DWFileCabinet table>) AND (name = <Name of file cabinet, to be doubly sure>)
  AND Settings.value('(/FileCabinet/Fields/UserField/@dbName)[1]', 'varchar(50)') = '<Database name of the expanded field>'

As Phil says, do this at your own risk! And don't expect to be able to properly restore documents with the now-outdated XML header files should you ever find yourself in such dire straits!

Thanks,
Joe Kaufman
 

Craig,

Another way to make sure the XML header files are all re-written would be to use the Platform API to "touch" each file, like simply updating an index field back with its original value. I believe this would rewrite the XML header files with the new structure. (Would also trigger any modification-based field changes, as it would change last mod. date.time.)

In that way, you could actually keep the old field, add a new field, do a very quick intra-table SQL Update statement to transfer data, delete the old field, and rename the new field back to the previous display name. Then run a quick Platform-based index change (or any workflow job that triggers a refresh of header information) to update XML header files.

The backing database field name will be different, but that probably won't matter to most people who only use the web interface to manipulate documents.

This method removes the requirement to do database backups and pull the data back from the restore. Though, if such things are second nature to you anyway, your way really is the best course of action while keeping the database field of the same name.

Thanks,

Joe Kaufman