Highlighted
Absent Member.
Absent Member.
6339 views

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

Jump to solution

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

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

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

Jump to solution

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.

View solution in original post

0 Likes
5 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

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

Jump to solution

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.

0 Likes
Highlighted
Absent Member.
Absent Member.

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

Jump to solution

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

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

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

Jump to solution

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.

0 Likes
Highlighted
Absent Member.
Absent Member.

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

Jump to solution

Ok Chris, Issue raised - 2601192, Thanks

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

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

Jump to solution

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.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.