Long SQL Filters and Statements in Déjà Vu X2

One of the features I liked in the old Déjà Vu X (DVX) was SQL Filters and Statements which could be used to filter, update or modify terms and segments in project files, translation memories (TM) and termbases (TB). I used to store my favourite SQL commands in a Word file but now I can store and reuse them in the new Déjà Vu X2 (DVX2).

SQL Filters and Statements are stored in the Settings.dvset file (located at C:\Documents and Settings\All Users\Application Data\ATRIL\Déjà Vu X2 folder in Windows XP).

Although I am only a beginner, I can modify (and sometimes write) some long SQL commands with the help of SQL gurus at DejaVu-l. Long SQL commands? Like this one:

UPDATE Pairs SET Bookmark_XXXX = -1 WHERE (Source <> '' AND ASC(Source) between 65 and 90) AND (Target_XXXX <> '' AND ASC(Target_XXXX) between 97 and 122) OR (Source <> '' AND ASC(Source) between 97 and 122) AND (Target_XXXX <> '' AND ASC(Target_XXXX) between 65 and 90)

It is a statement to add bookmarks to all segment pairs in which source segments start with an uppercase character but target segments with a lowercase or vice versa. If I copy-paste this statement into Execute SQL window it works like a charm but I cannot store it in DVX2. Why? Well, I was not aware of this problem until Jenny Zonneveld reported it. Counted in MS Word this statement has '270 characters with spaces' but maximum 255 characters are allowed for SQL field name in the Settings.dvset file.

The Settings file is created possibly by MS Access (or at least it can be opened and edited with Access). When opened with Access we can see two SQL related tables: SQLFilters and SQLStatements. The above statement is stored in the SQLStatements table. 

 

In this table, statements are stored in the SQL column and its Data Type is Text, in other words it has a limited Field Size of 255 characters.

Solution? Sure, Atril will fix it in one of the next builds but until then there is a manual solution for this problem. If we change the Data Type as Memo (and then save the Settings file), longer SQL filters and statements (exceeding 255 characters) can be stored in DVX2.

Needless to say, for longer SQL Filters, same change should be made in the SQLFilters table as well.


CAUTION: Settings file stores critical information, e.g. lists of Clients and Subjects, Delimiters, Filter Locations and SQL Filters & Statements. Always have a backup of this file and do NOT edit it with external applications (MS Access or OpenOffice or LibreOffice Base) if you are not comfortable with these tools.

NOTE: How can I edit/delete my SQL Filters and Statements?
No need to use MS Access or similar programs for it. In DVX2 go to Tools > Options > SQL to edit/delete your SQL commands.