2
votes

I get that exception when OrmLite make the following call :

return db.Select<T>(x => x.Name == name && x.PuId == puId).FirstOrDefault();

Exception :"System.Data.SqlClient.SqlException (0x80131904): The text, ntext, and image data types cannot be compared or sorted, except when usingIS NULL or LIKE operator.

The name is a String and puid is an Int. The type is mapped to a SQL Table which has no columns of type Text, NText or image at all.

When I look at the LastSQLStatement and executes it from SQL Server, it works. When I replace the call with the following, it works fine too

return db.SqlList<T>("SELECT Event_Id, Event_Num, Entry_On, Timestamp, Applied_Product, Source_Event, Event_Status, Confirmed, User_Id, Extended_Info, Comment_Id, PU_Id FROM Events WHERE ((Event_Num = @Event_Num) AND (PU_Id = @PU_Id))",new {Event_Num= "16J2730", PU_Id=91}).FirstOrDefault();

An old version of my service works fine with the same code. Using the latest version of servicestack and ormlite, I am now getting that weird issue...

Is the latest version of OrmLite has issues with old version of SQL Server? We are still on a 2000 version. I used both SQLServer Dialect without luck.

Anyone have an idea?

Here is what Mythz requested

        public ProficyEvent TestGetByName(string name, int puId, bool withDetails = false)
    {
        using (IDbConnection db = OpenDBConnection())
        {
            try
            {                   
                return db.Select<ProficyEvent>(x => x.Name == name && x.PuId == puId).FirstOrDefault();             
            }
            catch (Exception ex)
            {
                log.ErrorFormat("Error querying database: {0}", ex.ToString());
                throw;
            }
        }           
    }

[Alias("Events")]
public class ProficyEvent:IProficyPuEntity
{       
    [AutoIncrement]
    [Alias("Event_Id")]
    public int Id { get; set; }     
    [Ignore]
    public string Code { get; set; }
    [Ignore]
    public string Desc { get; set; }
    [Alias("Event_Num")]
    public string Name { get; set; }
    [Alias("Entry_On")]
    public DateTime? LastModified { get; set; }
    [Ignore]
    public string LastModifiedBy { get; set; }      
    public DateTime? Timestamp { get; set; }
    [Alias("Applied_Product")]
    public int? AppliedProductId { get; set; }
    [Ignore]
    public string AppliedProductName { get; set; }
    [Ignore]
    public int OriginalProductId { get; set; }
    [Ignore]
    public string OriginalProductName { get; set; }
    [Alias("Source_Event")]
    public int? SourceEvent { get; set; }
    [Alias("Event_Status")]
    public int? EventStatus { get; set; }
    [Ignore]
    public string EventStatusName { get; set; }
    public int Confirmed { get; set; }
    [Alias("User_Id")]
    public int UserId { get; set; }
    [Alias("Extended_Info")]
    public string ExtendedInfo { get; set; }
    [Ignore]
    public string Comment { get; set; }
    [Alias("Comment_Id")]
    public int? CommentId { get; set; }     
    [Ignore]
    public IEnumerable<ProficyTest> TestResults { get; set; }
    [Alias("PU_Id")]
    public int PuId { get; set; }
    [Ignore]
    public string UnitName { get; set; }
    [Ignore]
    public string LineName { get; set; }
}

CREATE TABLE [dbo].[Events](
[Event_Id] [int] IDENTITY(1,1) NOT NULL,
[Event_Num] [Varchar_Event_Number] NOT NULL,
[PU_Id] [int] NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[Applied_Product] [int] NULL,
[Source_Event] [int] NULL,
[Event_Status] [tinyint] NULL,
[Confirmed] [bit] NOT NULL DEFAULT (0),
[User_Id] [int] NULL,
[Comment_Id] [int] NULL,
[Entry_On] [datetime] NULL,
[Testing_Status] [int] NULL DEFAULT (1),
[Event_Subtype_Id] [int] NULL,
[Start_Time] [Datetime_ComX] NULL,
[Extended_Info] [varchar](255) NULL,
[Converted_Timestamp] [datetime] NULL,
[Orientation_X] [float] NULL,
[Orientation_Y] [float] NULL,
[Orientation_Z] [float] NULL,
[Final_Dimension_Z] [real] NULL,
[Final_Dimension_A] [real] NULL,
[Initial_Dimension_A] [real] NULL,
[Final_Dimension_X] [real] NULL,
[Final_Dimension_Y] [real] NULL,
[Initial_Dimension_Y] [real] NULL,
[Initial_Dimension_Z] [real] NULL,
[Initial_Dimension_X] [real] NULL,
[Conformance] [tinyint] NULL,
[Testing_Prct_Complete] [tinyint] NULL)

CREATE TYPE [dbo].[Varchar_Event_Number] FROM [varchar](25) NOT NULL
CREATE TYPE [dbo].[Datetime_ComX] FROM [datetime] NOT NULL
1
We'd need to be able to repro the issue to identify what it is, can you submit the CREATE TABLE statement of your existing table, the class definition of your table and the code you're using that has the issue (please use real type name instead of generic 'T' arg) - mythz
This query runs without issues for me using the latest version of OrmLite, maybe this exception is for a different query? Otherwise you can try using the latest version of OrmLite. - mythz
Thanks Mythz. I use the latest version. In fact, it works fine where I am not using the latest version. Is it possible for you to test it on a SQL Server 2000 SP4? Works also fine (not the same table and class though) for me on server with a higher version of SQL Server. Could it be something not compatible anymore with older version of SQL Server? - jbrabant
You're creating your own user defined types which may be the issue, but I don't have access to an SQL Server 2000 instance to test this on. You can try excluding use of UDTs in a test clone table to see if that's the cause. The only thing I can think that's changed in OrmLite that would affect this is the change from in-line SQL params to DB Params, in which case you'll need to use the legacy SelectFmt APIs which still use inline SQL, otherwise use Custom SQL. - mythz
Thanks Mythz. I am pretty sure the issue is with the Db params flag in OrmLite since it works fine with other tables with UDT. For that specific type, I now use custom SQL. - jbrabant

1 Answers

1
votes

To answer this question, this runs without issue on a recent version of SQL Server.

The major change to OrmLite that would have likely affected this behavior was the change to use Parameterized SQL Expressions from in-line SQL params.

You can use in-line SQL Params using OrmLite's legacy APIs or by dropping down to use Custom SQL APIs.