Host variable Cobol picture for Sql VARCHAR(MAX) column ?

When I use OpenESQL Assistant to generate the host variables for a table which includes a varchar(max) field it generates it as the following:

      EXEC SQL DECLARE mytable TABLE
         ( ...
          , myvarchar             varchar(2147483647)
         ) END-EXEC.
      *> COBOL HOST VARIABLES FOR TABLE mytable
...
           03 mytable-myvarchar               PIC X(64000).
I don't understand about the error that you decribe.
What error is it that you are experiencing?
I can use RTRIM with this variable without a problem as shown below:
   EXEC SQL
             SELECT
               A.myname
              ,A.mycompany
              ,RTRIM(A.myvarchar)
             INTO
               :mytable-myname:mytable-myname-NULL
              ,:mytable-mycompany:mytable-mycompany-NULL
              ,:mytable-myvarchar:mytable-myvarchar-NULL
            FROM mytable A
            where A.myname = 'chris'
          END-EXEC
The OpenESQL run-time needs to get values returned as text in the situation where there is no comparable COBOL data item that matches the SQL
column type specified.

Can you elaborate on your requirement to not use text columns?

Thanks.

Thanks for the reply Chris, I believe the problem is with dynamic sql (which we need to be using).

In this simple example, VTEXT_FIELD is a VARCHAR(MAX) column, WS-TTEXT is defined as

PIC X(64000).

INITIALIZE H-STATEMENT.                                    

STRING "SELECT KEY_FIELD "        DELIMITED BY SIZE        

      "FROM PCD_TEST "           DELIMITED BY SIZE        

      "WHERE VTEXT_FIELD = " P1    DELIMITED BY SIZE      

      INTO H-STATEMENT                                    

END-STRING                                                  

EXEC SQL DECLARE CUR_READ CURSOR FOR STMT_READ END-EXEC.    

EXEC SQL                                                    

   PREPARE STMT_READ FROM :H-STATEMENT                    

END-EXEC.                                                  

IF ( SQLSTATE NOT = "00000" )                              

   GO DISCON                                              

END-IF.                                                    

MOVE "MICROFOCUS" TO WS-TTEXT.                              

EXEC SQL                                                    

   OPEN CUR_READ  USING :WS-TTEXT                          

END-EXEC.

The open cursor fails with 37000 "[Microsoft][ODBC SQL Server Driver][SQL Server]The data types varchar(max) and text are incompatible in the equal to operator.  "  

If I define WS-TTEXT as PIC X(8000) then this statement works, anything larger than that fails,

this is without any RTRIM.

If an RTRIM is attempted, :-

STRING "SELECT KEY_FIELD "        DELIMITED BY SIZE    

      "FROM PCD_TEST "           DELIMITED BY SIZE    

      "WHERE VTEXT_FIELD = RTRIM(" P1 DELIMITED BY SIZE

      ")"                        DELIMITED BY SIZE    

      INTO H-STATEMENT                                

END-STRING                                            

Then this fails as well because Openesql is using a TEXT data type :-

"[Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type text is invalid for argument 1 of rtrim function."    

So my question is how should I be defining WS-TTEXT to prevent this error while still being able

to use values in excess of 8000 characters which we need to do on equivalent insert/update statements ?. We are using Netexpress 5.106.0079.

Hope this makes things clearer & you can provide some advice, thanks again,

Paul

Thanks Paul, I have now been able to reproduce this.

Can you please open up a Support Incident for this so that I may raise an RPI against it?

Please put in description "assign to Chris Glazier" and when you get the incident number back could you post it here?

This problem occurs with ODBC in native code but does not occur when using ADO with Managed code.

Thanks.

Ok Chris, Issue raised - 2601192, Thanks

This problem has now been fixed and the fix will be available in the next wrappack release for Net Express 5.1, wrappack 8.

Thanks.