SQL Server varchar(MAX) datatype in delphi using RemObjects

Question

Got a request to change comment field max size in application. Before had it set to varchar(500), so after reading documentation i have decided to change data type of the field from varchar(500) to varchar(max). Database accepted changes without any problems (using Microsoft SQL Server Management Studio 2005 and Microsoft SQL Server Management Studio 2008 for database management). Then i went on changing the software. Software is written in Delphi with RemObjects to communication with database. So I changed the TDASchema for the server, it mapped my new varchar(max) field as String(65536) data type (got me a little worried there about such an explicit static size, but I went on). Then I Retrieved DataTable Schema for my TDAMemDataTable object, which updated all the fields.

I started the application and decided to see whether my database will accept changes on this specific changed field. I have edited one of the records and clicked the button to synchronize the DataSet with server and got such a fail message:

The data types varchar(max) and text are incompatible in the equal to operator

I interpret it as that my server object (the one that maps database fields with RemObjects objects) have mapped field data types to wrong data types in RemObjects.

How can this be resolved? What are the alternatives?

P.S. In this release Build .1267 logs from RemObjects it clearly states that:

fixed: DataSnap: fails to post updates to MSSQL 2005 VARCHAR(MAX)

I am using build version .1067. Wonder if update will fix the problem

P.P.S. After update to the latest version of RemObjects, the problem persists.

Vardon

Answer 1

As you suspected, I think the root of your problems is that the fields haven't come into the TDASchema as the correct types. I've just tried it here and varchar(max) and nvarchar(max) fields come through to my schema as Memo and WideMemo respectively, not String(65536).

I'm using Delphi XE6 and SQL Server 2008 R2 via FireDAC.

This suggests an issue retrieving the metadata from the database. What database driver are you using? Can you try FireDAC (if available) or another driver to see if the problem persists?

Skillet

Answer 2

This error message usually happens when trying to compare a varchar(n) and text using an equality operator (usually in a where clause in sql but possible elsewhere). there was an article on MSDN which covered a few points which might relate to this.

when you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

You mentioned that the TDASchema had mapped your new field as String(65536) which, although never having used RemObjects before, i would assume somewhere in it's own code (or yours) is trying to do a comparison of some kind hence the error message.

Try using VARCHAR(8000) instead of MAX and see if that fixes the issue.

The other option if you can find where in the code it is doing this equality check, is to try doing a cast()

Safranine

I agree with yours proposed cause for the error, comparison happens somewhere in the library (maybe when some trigger compares old entry of a cell with the new one where one of them is text and another is varchar) The problem with using VARCHAR(8000) is that it will be allocated enormous amount of space in the database, which i do not want to happen, where MAX property allocates exactly what is needed to store particular varchar. – Vardon

Yeah, that is a problem. If you can find out what kind of comments will be going into the field you can at least have a guess and trim the size down to a respectable level. – Safranine

Or maybe some kind of exception handler that will catch the exception and manually cast the values? Having never used remobjects before that is about the only ideas i have. – Safranine

Thank you for your ideas, but i would rather wait for someone who have used RemObjects and maybe had similar problem or can point me some direction if I am doing something wrong – Vardon

Answer 3

Resolution for Delphi 7 and MS SQL Server 2008 R2 (SP2)

Delphi:

    with TADOStoredProc.Create(Self) do
    try
      Connection := AConnection;
      ProcedureName := ASPName;
      Parameters.Refresh;
      Parameters.ParamByName('@XML').Value := AXML;
      try
        ExecProc;
...

MS SQL Server:

ALTER PROCEDURE dbo.StoredProcName
    @XML        NVARCHAR(MAX)
   ,@ErrMsgOut  NVARCHAR(MAX) = NULL OUT
AS BEGIN
SET NOCOUNT ON
DECLARE @RETURN INT = 0
       ,@idoc   INT

BEGIN TRY
    -- Prepare XML
    DECLARE @XML_TEXT VARCHAR(MAX)
    SET @XML_TEXT = CONVERT(VARCHAR(MAX), @XML) 
    EXEC sp_xml_preparedocument @idoc OUTPUT, @XML_TEXT

    -- Open XML
    SELECT  *
    FROM    OPENXML (@idoc, '/ServicesList/ServicesItem', 2)
    WITH
    (
        YourFields AndTypes
    )
...

Kappa