#1 2015-11-08 15:04:00

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

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?

Offline

#2 2015-11-08 17:59:41

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,299
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.

Offline

#3 2015-11-08 18:22:51

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

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")
)
;

Offline

#4 2015-11-08 20:25:12

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,299
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.

Offline

#5 2015-11-08 20:53:06

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

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.

Offline

#6 2015-11-09 07:45:13

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,192
Website

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

Use a BATCH instead of a manual transaction.

Offline

#7 2015-11-09 07:58:14

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

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.

Offline

#8 2015-11-11 19:59:57

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

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...

Offline

Board footer

Powered by FluxBB