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

Hi,

Can anyone tell me the appropriate cobol picture clause to use on a host variable to access a VARCHAR(MAX) column please ?

Currently we have this defined as PIC X(32000) but when interrogating the esql statement generated in Sql profiler this appears to

get converted into a TEXT data type which leads to an SQL error as we wish to use RTRIM.

We need to be moving away from using TEXT data types as Microsoft are indicating they intend to withdraw support.

Any advice would be much, appreciated. Thanks

  • 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

  • Verified Answer

    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.