OleDB: unable to bind DBTYPE_WSTR parameter - got DB_E_UNSUPPORTEDCONVERSION error

Here is the context, for an OpenSource library :

• I'm calling OleDB library directly from unmanaged code (Delphi);
• I'm binding parameters by creating an IAccessor to a ICommandText OleDB instance;
• I've no problem with simple types like DBTYPE_I8 or DBTYPE_DATE;
• I would like to bind textual parameters as DBTYPE_WSTR type, i.e. always as Unicode.

The issue is that for DBTYPE_WSTR type, I get an OLEDB Error 80040E1D (DB_E_UNSUPPORTEDCONVERSION) error, 'Requested conversion is not supported', when Command.Execute is called.

Of course, I've tried with or without DBTYPE_BYREF data layout (i.e. setting FIELDTYPE2OLEDB[ftUTF8]=DBTYPE_WSTR or DBTYPE_BYREF in code below, and changing the layout): same issue... it works with DBTYPE_WSTR but not with DBTYPE_STR.

But if I change the parameter type from DBTYPE_WSTR into DBTYPE_STR (i.e. setting FIELDTYPE2OLEDB[ftUTF8]=DBTYPE_STR or DBTYPE_BYREF in code below), the command executes without any problem. But I would like to use DBTYPE_WSTR wType instead, to ensure that no character is lost because of the current Ansi Charset.

In fact, I can retrieve any IRowSet data without any problem as DBTYPE_WSTR, but I can't bound a parameter value with DBTYPE_WSTR.

I'm connected to a Microsoft SQL Server 2008 R2 instance. The code works with no parameters in the SQL request, or with int or floating-point parameters, but not with TEXT parameters.

Here开发者_如何学Python is the main sample code:

Query.Execute('select * from Person.Address where AddressLine1 like ?;',true,['% Drive']);

AddressLine1 column is defined as nvarchar(60) in the AdventureWorks2008R2 reference sample database, so it should map with DBTYPE_WSTR, according to official MSDN documentation.

The full source code of the corresponding unit is available from our source code repository. The code is in the TOleDBStatement.Execute method, as such:

const
  PARAMTYPE2OLEDB: array[TSQLDBParamInOutType] of DBPARAMIO = (
    DBPARAMIO_INPUT, DBPARAMIO_OUTPUT, DBPARAMIO_INPUT or DBPARAMIO_OUTPUT);
  FIELDTYPE2OLEDB: array[TSQLDBFieldType] of DBTYPE = (
    DBTYPE_EMPTY, DBTYPE_NULL, DBTYPE_I8, DBTYPE_R8, DBTYPE_CY, DBTYPE_DATE,
    DBTYPE_WSTR or DBTYPE_BYREF, DBTYPE_BYTES or DBTYPE_BYREF);

  (...)
  OleDBCheck((fSession as IDBCreateCommand).
    CreateCommand(nil,IID_ICommandText,ICommand(fCommand)));
  fCommand.SetCommandText(DBGUID_DEFAULT,pointer(Utf8DecodeToRawUnicodeUI(aSQL)));
  P := pointer(fParams);
  SetLength(fParamBindings,fParamCount);
  B := pointer(fParamBindings);
  for i := 1 to fParamCount do begin
    B^.iOrdinal := i; // parameter index (starting at 1)
    B^.eParamIO := PARAMTYPE2OLEDB[P^.VInOut]; // parameter direction
    B^.wType := FIELDTYPE2OLEDB[P^.VType];     // parameter data type
    // set additional fields
    case P^.VType of
    ftInt64, ftDouble, ftCurrency, ftDate: begin
      // those types match the VInt64 binary representation :)
      B^.cbMaxLen := sizeof(Int64);
      B^.dwPart := DBPART_VALUE;
      B^.obValue := PAnsiChar(@P^.VInt64)-pointer(fParams);
    end;
    ftUTF8, ftBlob: begin
      // sent as DBTYPE_BYREF mapping directly the VRawByteString content
      B^.dwPart := DBPART_VALUE or DBPART_LENGTH or DBPART_STATUS;
      B^.obValue := PAnsiChar(@P^.VRawByteString)-pointer(fParams);
      B^.cbMaxLen := sizeof(Pointer);
      Len := length(P^.VRawByteString);
      if P^.VType=ftUTF8 then
        Len := Len shr 1; // expect length in WideChar count, excluding #0 
      P^.VInt64 := Len; // store length in unused VInt64 property
      B^.obLength := PAnsiChar(@P^.VInt64)-pointer(fParams);
      B^.obStatus := B^.obLength+4;
    end;
    end;
    inc(P);
    inc(B);
  end;
  OleDBConnection.OleDBCheck((fCommand as IAccessor).CreateAccessor(
    DBACCESSOR_PARAMETERDATA,fParamCount,Pointer(fParamBindings),0,
    fDBParams.HACCESSOR,nil));
 OleDBConnection.OleDBCheck(fCommand.Execute(
    nil,IID_IRowset,fDBParams,nil,@fRowSet),ParamsStatus);

My question is therefore:

How to bind a B^.wType=DBTYPE_WSTR parameter for a nvarchar() column without having a DB_E_UNSUPPORTEDCONVERSION error in fCommand.Execute.

I suspect there is some property to set to the ISession or ICommand instance, or some flag/option to set, but I was not able to find which one from MSDN documentation. Any help is welcome!

OK... after some more hours, I think I've found out.

The issue was not in my code AFAIK, but in the Microsoft SQL Server OleDB provider from Microsoft. Using DBTYPE_WSTR, as the official documentation states, just doesn't work. I don't say that MS wrote bugs, but I was really confused by the documentation, which clearly stated on the same line: nvarchar DBTYPE_WSTR.

I've just finally tried with DBTYPE_BSTR, using an Ole WideString... and it worked as expected!

I just guess that Microsoft and most OleDB consumers just use DBTYPE_BSTR.

Here is some working code:

    ftUTF8: begin
      // mapping directly the WideString VText content
      B^.wType := DBTYPE_BSTR; // DBTYPE_WSTR just doesn't work :(
      B^.obValue := PAnsiChar(@P^.VText)-pointer(fParams);
      B^.cbMaxLen := sizeof(Pointer);
    end;

What's fine with WideString is that it can be resized by the provider in case of an Output or Input/Output parameter (when callling stored procedures).