(Received by email)
I've recently come across problems inserting/updating SQL TEXT fields into a SQL Server database. SQL Server does not allow the use of '=' to compare TEXT fields and so WriteOptimisticLockMatch() will fail.
Up until recently, I didn't understand what WriteOptimisticLockMatch() was trying to achieve - it's just making sure it only operates on rows/tuples that haven't changed since local modifications have been made.
My fix to get this working was to use 'LIKE' instead of '=' for string types - there is no distinction within NEO between SQL TEXT and SQL VARCHAR as far as I can tell. For this to work, the values passed to the SQL must be escaped to remove any special characters (this is very SQL Server specific, I imagine). This operation is carried out using an overloaded version of GetParameter() which escapes string values depending upon the value of the escapeValue flag, and a new method called EscapeLikeCharacters().
This fix seems to be working well. I'm sure there's a slight performance overhead using LIKE instead of =, and there may be a few subtle bugs introduced (I think LIKE is case insensitive so text changes of just capitalisation may be ignored).
I've attached my changes which may be of interest.
Many thanks
Simon
Bug
Open
Major
Cannot use SQL TEXT fields
(received by email)
Just a quick update on the SQL TEXT and NTEXT:
I couldn't get the WriteOptimisticLockMatch() to work with text fields. I found the update matched nothing and so updates failed. My solution is to exclude TEXT fields from the optimistic write lock - not ideal, but it works.
Since I could find no obvious way to determine whether a field is of type TEXT or NTEXT, I've resorted to naming such fields as *_ntext and *_text and using two protected methods, IsSqlDbTypeText() and IsSqlDbTypeNText(), to check their type. Again, not ideal but it seems to work. The only alternative I can think of is to query the database and find out the datatypes from the server. This would be extra overhead for each update operation unless some caching was used.
Cheers
Simon