mORMot Open Source

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

I don't know what is wrong, but if I set a string len to 40 I can't retrieve it back from DB.

Test code:

uses
  SysUtils,
  Classes,
  mORMot,
  mORMotSQLite3,
  mORMotDB,
  SynDB,
  SynDBODBC,
  SynSQLite3Static,
  SynCommons;

type
  TSQLTestRec = class(TSQLRecord)
  public
    FCustomID: TID;
    FTestField1: RawUTF8;
    FTestField2: RawUTF8;
  published
    property CustomID: TID read FCustomID write FCustomID;
    property TestField1: RawUTF8 read FTestField1 write FTestField1;
    property TestField2: RawUTF8 read FTestField2 write FTestField2;
  end;

procedure DoTest;
const
  cDNS = 'TEST_MSSQL';
var
  I: Integer;
  VId: TID;
  FModel: TSQLModel;
  FClientDB: TSQLRestClientDB;
  FProps: TSQLDBConnectionProperties;
  VRec: TSQLTestRec;
  VTestStr: string;
begin
  FModel := TSQLModel.Create([TSQLTestRec]);
  try
    FProps := TODBCConnectionProperties.Create(cDNS, '', '', '');
    try

      for I := 0 to High(FModel.Tables) do begin
        if not VirtualTableExternalRegister(FModel, FModel.Tables[i], FProps, '') then begin
          raise Exception.Create('VirtualTableExternalRegister failed');
        end;
      end;

      FClientDB := TSQLRestClientDB.Create(FModel, nil, ':memory:', TSQLRestServerDB);
      try
        FClientDB.Server.AcquireExecutionMode[execORMWrite] := amBackgroundThread;
        FClientDB.Server.AcquireExecutionMode[execORMGet] := amBackgroundThread;

        FClientDB.Server.CreateMissingTables;

        SetLength(VTestStr, 40); // <-- 40 is FAIL, but 30 is OK
        for I := 1 to Length(VTestStr) do begin
          VTestStr[i] := 'z';
        end;

        VRec := TSQLTestRec.Create;
        try
          VRec.CustomID := 2;
          VRec.FTestField1 := StringToUTF8(VTestStr);
          VRec.FTestField2 := StringToUTF8(VTestStr);

          for I := 0 to 1 do begin
            FClientDB.TransactionBegin(TSQLTestRec, 1000);
            try
              VId := FClientDB.Add(VRec, True);
              Assert(VId > 0);
              FClientDB.Commit(1000, True);
            except
              FClientDB.RollBack(1000);
              raise;
            end;

            if FClientDB.Retrieve(VId, VRec) then begin
              if not SameText(UTF8ToString(VRec.FTestField2), VTestStr) then begin
                Assert(False, 'SameText failed');
              end;
            end else begin
              Assert(False, 'Retrieve failed');
            end;
          end;
        finally
          VRec.Free;
        end;

      finally
        FClientDB.Free;
      end;
    finally
      FProps.Free;
    end;
  finally
    FModel.Free;
  end;
end;

Function FClientDB.Retrieve(VId, VRec) returns False, and this is wrong.

I found this error in log:

TODBCStatement(00B28C50).00586274 SynDBODBC.TODBCStatement.ExecutePrepared (1494) 
00000000CA203659  # SQL   	TODBCStatement(00B28C50) select ID,CustomID,TestField1,TestField2 from dbo.TestRec where ID=3
00000000D89BEDBE  #  -    00.081.010
00000000D8A3553A  # info  [01004] [Microsoft][SQL Server Native Client 10.0]String data, right truncation (0)  
00000000D8A3C689  # info  [01004] [Microsoft][SQL Server Native Client 10.0]String data, right truncation (0)
00000000DC5192A8  # EXC   EODBCException .... "TODBCStatement - TODBCLib error: [07009] [Microsoft][SQL Server Native Client 10.0]Invalid Descriptor Index (0)\r\n"...

If I set string len to 30 then all works fine.

WTF?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

It seams column dbo.TestRec.TestField1 size is < 40.
Alter table and increase column size.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

Text fields is nvarchar(max):

CREATE TABLE "TestRec" (
	"ID" BIGINT NOT NULL,
	"CustomID" BIGINT NULL DEFAULT NULL,
	"TestField1" NVARCHAR(max) NULL DEFAULT NULL,
	"TestField2" NVARCHAR(max) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

Try to use TOleDBMSSQLConnectionProperties instead of TODBCConnectionProperties. We use it in production w/o problems. In fact this is a "native" interface for SQL Native Client.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

Yes, I can try it, but, if it's possible, I would like to use TODBCConnectionProperties.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

Use a BATCH instead of a manual transaction.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

Error occurred when I'm trying to read an existing record from DB, not to insert it!
If DB already has records with strings length more than 40 chars, you can make test without transaction and Add operation. It will be failure in any case.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL 2008 via ODBC raise error "Invalid Descriptor Index"

If I'm manually change fields definition to nvarchar(2000), then all works fine:

CREATE TABLE "TestRec" (
	"ID" BIGINT NOT NULL,
	"CustomID" BIGINT NULL DEFAULT NULL,
	"TestField1" NVARCHAR(2000) NULL DEFAULT NULL,
	"TestField2" NVARCHAR(2000) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

So, I think that here is a problem with nvarchar(max) type in SynDBODBC...