No mapping exists from DbType SByte to a known SqlDbType

Relativity Server
1

Hello,

we are receiving this message when executing a fetch from Delphi with a dynamic where clause.
This is working correctly with a custom build server in Delphi, but gives the error in the title when using Relativity Server.
We have tried this with version the latest version 7 and with the trial version of version 8 with the same result.

2

Hello

More information is needed to reproduce the issue:

  1. Database table definition script
  2. Schema where this table is referenced
  3. DynamicWhere expression that is sent to the server

Thanks in advance

3

Hello,

here you go:

Delphi code:
 Screen.Cursor := CrHourglass;
 ClientDataModule.tbl_VwCases.Close;
 ClientDataModule.tbl_VwCases.DynamicWhere.Clear;
 lWhereBuilder := TDAWhereBuilder.Create();
 MasterExpression  :=
 lWhereBuilder.NewBinaryExpression(
 lWhereBuilder.NewField(‘VwCases’, ‘Status’),
 lWhereBuilder.NewConstant(-1), dboNotEqual);

ClientDataModule.tbl_VwCases.DynamicWhere.Expression := MasterExpression;
ClientDataModule.tbl_VwCases.Open;
ClientDataModule.tbl_VwCases.Sort([‘DateLastUpdate’], [sdDescending]);
ClientDataModule.tbl_VwCases.First;
Screen.Cursor := CrDefault;

Database (SQL Server 2012)
It is a view based on the following table, the problem field seems to be the Status field.TestVwCases.zip (66.2 KB)
Fields in the cases table:
    [CaseId] [bigint] IDENTITY(1,1) NOT NULL,
    [Title] nvarchar NOT NULL,
    [Description] nvarchar NOT NULL,
    [Relation] [bigint] NOT NULL,
    [GroupId] [bigint] NOT NULL,
    [NameSubmitter] nvarchar NOT NULL,
    [PhoneSubmitter] nvarchar NOT NULL,
    [EmailSubmitter] nvarchar NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateLastUpdate] [datetime] NOT NULL,
    [Status] [int] NOT NULL,
    [Responsable] [bigint] NULL,
    [IssueId] [bigint] NULL,
    [Solution] nvarchar NULL)

Opening the table without a dynamic where works perfectly.
The name of the view is VwCases:

CREATE VIEW [dbo].[VwCases]
AS
SELECT        dbo.Cases.Title, dbo.Cases.Description, dbo.Relations.RelationName, dbo.Cases.Relation, dbo.Cases.GroupId, dbo.Cases.NameSubmitter,
                         dbo.Cases.PhoneSubmitter, dbo.Cases.EmailSubmitter, dbo.Cases.DateCreated, dbo.Cases.DateLastUpdate, dbo.Cases.Status, dbo.Relations.RelationAPB,
                         dbo.Cases.CaseId, dbo.Cases.Solution, dbo.Cases.Responsable, dbo.Cases.IssueId, dbo.Users.Name + ’ ’ + dbo.Users.FirstName AS ResponsableName,
                         dbo.Issues.IssueNumber
FROM            dbo.Cases LEFT OUTER JOIN
                         dbo.Issues ON dbo.Cases.IssueId = dbo.Issues.IssueId LEFT OUTER JOIN
                         dbo.Users ON dbo.Cases.Responsable = dbo.Users.UserId LEFT OUTER JOIN
                         dbo.Relations ON dbo.Cases.Relation = dbo.Relations.RelationId

4

Hi,
I’ve investigated further.
It is only when providing a negative value (-1) in the where clause on an int or bigint field.

5

This is a minor bug in the ADO.NET drivers for MS SQL Server: SQL query parameters cannot have type ShortInt (AKA SignedByte or SByte).

Note that when you set parameter value to any value positive value it is either sent as Byte or Integer, that are supported by MS SQL as parameter values - this explains why the query does work for values other for -1.

I strongly suggest to use ConstantExpression constructor with explicit constant type parameter to avoid such issues.