I have created a Windows Search string custom property that shows up fine as a column in Windows Explorer. In C# I issue the following SQL query:
SELECT System.ItemPathDisplay, System.ItemName, My.Custom.Property FROM SystemIndex WHERE CONTAINS(My.CustomProperty, 'blah')
The resulting OleDbDataReader correctly returns a list of the files which contain the word "blah" in My.Custom.Property. So I try to find out the full value of the property, like this:
reader.GetValue(2);
The return value has a type System.DBNull. But how can this be, as the query was issued only for those files for which My.Custom.Property CONTAINS a value?
After more investigation, I have found that the above select statement will return the full value of the property if the value is "abc". It will also work with "abc def". It will work for "ABCDEFGHIJKLMNOPQRSTUVWXYZ" and for "ABCDEFGHIJKLMNOPQRSTUVWXYZ QWERTYUIOPASDFGHJKLZXCVBNM".
But if the value is "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ QWERTYUIOPASDFGHJKLZXCVBNMQWERTYUIOPASDFGHJKLZXCVBNM", then the original problem resurfaces: the SQL query succeeds (that is, the files with the matching values are found), but the returned property value is returned as System.DBNull for that column. If I add the the property as a column in Windows Explorer, the entire value is shown.
After even further investigation, I have discovered that the maximum value in a column that will be returned in a SQL query is 63. Hmmm... the value is not surprising---but why such a small limit!?? Why a limit at all? Where is this documented? How do I get around it (as Windows Explorer is apparently doing)?