DDEX in VS2008: How to filter column type from WHERE clause in generated SQL for update

Question

I'm creating a DDEX provider for our database, based on the SDK VSPackage sample, and I've got it mostly working.
I can see all the tables/keys/views/etc... I can even create tables and retrieve data.
Now, our database does not support searching on LOBs (LONG VARCHAR, aka TEXT). So "SELECT * FROM FOO WHERE LONG_COL" = :1 is invalid.
No matter what I do, Visual Studio insists on putting the LOB in the WHERE clause when I try to update a record in the query designer results pane, which results in a error being thrown.
From what I've read here: http://social.msdn.microsoft.com/Forums/en/vsx/thread/becb142e-6eae-467f-9fad-4b63c4588637, I need to make sure I have an ObjectSupport entry for IsPrimary. I believe I have that set up, but perhaps it's not.
XML:
MetaData.xml:

          <xs:element name="DataTypes" msdata:MinimumCapacity="3">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="TypeName" type="xs:string" minOccurs="0" />
                <xs:element name="ProviderDbType" type="xs:string" minOccurs="0" />
                <xs:element name="ColumnSize" type="xs:string" minOccurs="0" />
                <xs:element name="CreateFormat" type="xs:string" minOccurs="0" />
                <xs:element name="CreateParameters" type="xs:string" minOccurs="0" />
                <xs:element name="DataType" type="xs:string" minOccurs="0" />
                <xs:element name="IsAutoincrementable" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsBestMatch" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsCaseSensitive" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsFixedLength" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsFixedPrecisionScale" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsLong" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsNullable" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsSearchable" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsSearchableWithLike" type="xs:boolean" minOccurs="0" />
                <xs:element name="IsUnsigned" type="xs:boolean" minOccurs="0" />
                <xs:element name="MaximumScale" type="xs:string" minOccurs="0" />
                <xs:element name="MinimumScale" type="xs:string" minOccurs="0" />
                <xs:element name="IsConcurrencyType" type="xs:string" minOccurs="0" />
                <xs:element name="IsLiteralSupported" type="xs:string" minOccurs="0" />
                <xs:element name="LiteralPrefix" type="xs:string" minOccurs="0" />
                <xs:element name="LiteralSuffix" type="xs:string" minOccurs="0" />
                <xs:element name="NativeDataType" type="xs:string" minOccurs="0" />
              </xs:sequence>
  <DataTypes>
    <TypeName>LONGVARCHAR</TypeName>
    <ProviderDbType>Provider.DataTypes.LONGVARCHAR</ProviderDbType>
    <DataType>System.String</DataType>
    <IsLong>true</IsLong>
    <IsSearchable>false</IsSearchable>
    <IsSearchableWithLike>false</IsSearchableWithLike>
    <IsConcurrencyType>false</IsConcurrencyType>
  </DataTypes>

ObjectSupport.xml:

    <MappedType name="TableUniqueKey" underlyingType="Index">
<Selection restrictions="{Catalog},{Schema},{Name},null,{Table}" filter="IsUnique = true" />
      <Identifier>
        <Part name="Catalog" underlyingMember="INDEX_CATALOG" />
        <Part name="Schema" underlyingMember="INDEX_SCHEMA" />
        <Part name="Name" underlyingMember="INDEX_NAME" />
        <Part name="Table" underlyingMember="TABLE_NAME" />
      </Identifier>
      <Properties>
        <Property name="Name" isIdentifierPart="true" />
        <Property name="IsPrimary" underlyingMember="PRIMARY_KEY" />
        <Property name="IsUnique" underlyingMember="UNIQUE" />
      </Properties>
    </MappedType>


    <Type name="Index" preferredOrdering="UNIQUE, TYPE, INDEX_CATALOG, INDEX_SCHEMA, INDEX_NAME, ORDINAL_POSITION">
      <Identifier>
        <Part name="INDEX_CATALOG" />
        <Part name="INDEX_SCHEMA" />
        <Part name="INDEX_NAME" />
        <Part name="TYPE" />
        <Part name="TABLE_NAME" />
      </Identifier>
      <Properties>
        <Property name="INDEX_NAME" isIdentifierPart="true" />
        <Property name="TABLE_NAME" isIdentifierPart="true" />
        <Property name="COLUMN_NAME" />
        <Property name="UNIQUE" type="System.Boolean" />
        <Property name="PRIMARY_KEY" type="System.Boolean" />
        <Property name="TYPE" type="System.UInt32" isIdentifierPart="true" />
        <Property name="ORDINAL_POSITION" type="System.UInt32" />
      </Properties>
    </Type>

Then in the code that populates the Index schema rowset, the PRIMARY_KEY and UNIQUE columns are set to System.Boolean and the values are true or false accordingly

I figure I'm missing something, I just don't know what.

I have 3 ways I can solve this, in order:

1. Fix the problem so long columns aren't put in the where clause
2. Make changes so ROWID's are used instead (but I have no idea on what I need to do for that).
3. Just make the data type read-only in the DDEX provider.

I'm on Windows 7 RTM with VS2008 SP1.

If I missed anything, please let me know.

Thanks for any help you can provide!

Answer 1

Hi Phoenix84118,

Thank you for your question.

I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

Thank you for your understanding and support.

---

Lucy Liu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Phoenix84118,

Unfortunately, this doesn't look like something we can readily resolve via forum support.

My recommendation would be to have you open a paid support incident with Microsoft support, and see if we can't get a repro they can debug. You may have a few options for opening said support incident.

Alliance and Premier VSIP membership includes a complimentary MSDN subscription, which includes 4 professional support incidents. These can be used to initialize a support request with Microsoft's Customer Support Services. Some versions of Visual Studio include a number of free support incidents as well. See the "Technical Support Incidents" topic for details.

Microsoft Customer Support Services, has a small team of support staff (including myself) dedicated to assisting customers with problems related to Visual Studio package development, Visual Studio Addin development, or just plain automating the IDE via the exposed COM interfaces. Internally, we're known as the "Visual Studio Extensibility Support Team". This team is primarily responsible for supporting the VS SDK, and fielding questions/problems related to extending or integrating with the VS .Net IDE.

The VS SDK documentation has a topic entitled "Support and Other Resources" that can provide additional details on how to contact and open support incidents with the VS Extensibility Support Team.

Sincerely,

---

Ed Dore