Length of VARCHAR in Instantsql RM 12.17.

Hi Humberto,

Set the COBOL data field size to the maximum length size defined in MySQL.

We use varchar(2500) for our comment fields.

We do not use JSON data, however you could parse it using the XSLT processor included with RM-COBOL XML Extensions.

John 

John,

I developed the JSON parsing with XSLT technique, but have since started using a small PHP script to create an importable XML document.  I have attached it to this message.

Usage is:

CALL "SYSTEM" "json2xml.php <jsonfile >xmlfile"

Then, import using normal XSLT technique.

The XSLT parser suffers from limitations on recursion depth, plus some issues with JSON escape sequences.  The PHP script puts all that on thePHP library rooutine.

Hi, John

I've changed data for Varchar (2500) i DB table and get error 88  Sqe-Datatruncation Value 2001. If I change 2500 by 255 it works fine.

My scenario is Mysql 9.3, RM/Cobol 12.17 with Instantsql tool.

       Working-Storage Section.
       01 Ws-Data                                     Pic X(2500). *>X(255).
       
       Sql-Prepare-Query-Td.
           Initialize                                      Sql-Message-Error.  *> Ws-Json-Data.
           Sql Prepare Query Ws-Insertqueryhandle Sql-Connectionhandle
              " Insert into fidesktd(codemp_td, hotel_td, year_td, typrec_td, dpto_td, concep_td, data_td) values ( ?, ?, ?, ?, ?, ?, ? )
      -       "on duplicate key update data_td = data_td". *>see sql-notes.
           If Sql-Ok
              Sql Bind Parameter Ws-Insertqueryhandle
                 1 Sql-Smallint, Sql-Param-Input Codemp-Tc Omitted
                 2 Sql-Smallint, Sql-Param-Input Hotel-Tc Omitted
                 3 Sql-Smallint, Sql-Param-Input Year-Tc Omitted
                 4 Sql-Smallint, Sql-Param-Input Typrec-Tc Omitted
                 5 Sql-Smallint, Sql-Param-Input Dpto-Td Omitted
                 6 Sql-Smallint, Sql-Param-Input Concep-Td Omitted
                 7 Sql-Varchar, Sql-Param-Input Ws-Data Omitted
                 Sql Start Query Ws-Insertqueryhandle
              Evaluate True
              When Sql-Ok
                 Set Ok-Records                            To True
              When Other
                 Perform Display-Error-Info
                 Set Ok-Records                            To False
              End-Evaluate
           Else
              Perform Display-Error-Info             
           End-If.

Hi, John

I've changed data for Varchar (2500) i DB table and get error 88  Sqe-Datatruncation Value 2001. If I change 2500 by 255 it works fine.

My scenario is Mysql 9.3, RM/Cobol 12.17 with Instantsql tool.

       Working-Storage Section.
       01 Ws-Data                                     Pic X(2500). *>X(255).
       
       Sql-Prepare-Query-Td.
           Initialize                                      Sql-Message-Error.  *> Ws-Json-Data.
           Sql Prepare Query Ws-Insertqueryhandle Sql-Connectionhandle
              " Insert into fidesktd(codemp_td, hotel_td, year_td, typrec_td, dpto_td, concep_td, data_td) values ( ?, ?, ?, ?, ?, ?, ? )
      -       "on duplicate key update data_td = data_td". *>see sql-notes.
           If Sql-Ok
              Sql Bind Parameter Ws-Insertqueryhandle
                 1 Sql-Smallint, Sql-Param-Input Codemp-Tc Omitted
                 2 Sql-Smallint, Sql-Param-Input Hotel-Tc Omitted
                 3 Sql-Smallint, Sql-Param-Input Year-Tc Omitted
                 4 Sql-Smallint, Sql-Param-Input Typrec-Tc Omitted
                 5 Sql-Smallint, Sql-Param-Input Dpto-Td Omitted
                 6 Sql-Smallint, Sql-Param-Input Concep-Td Omitted
                 7 Sql-Varchar, Sql-Param-Input Ws-Data Omitted
                 Sql Start Query Ws-Insertqueryhandle
              Evaluate True
              When Sql-Ok
                 Set Ok-Records                            To True
              When Other
                 Perform Display-Error-Info
                 Set Ok-Records                            To False
              End-Evaluate
           Else
              Perform Display-Error-Info             
           End-If.

I'll appreciate any help.

Thaks.

Humberto Betancur

------------------------------
Humberto Betancur
Rocket Forum Shared Account
------------------------------

Hi Humberto,

The default length of Varchar is defined as 4096.

On parameter 7 change the OMITTED to a variable. For example use sql-ParamLength.
MOVE 2500 to sql-ParamLength.

You can use sql-ParamLength or use your own ws-paramLength PIC S9(10).

From documentation:

For parameters with the data type sql-LongVarChar, the length of the COBOL data item specified by DataItemN is used to establish the maximum length of the data that can be transferred to or from the data source for the parameter.  Otherwise, the maximum length for the data type is established by the configuration option LongVarCharDataSize.

LongVarCharDataSize.  This option value specifies the size of the largest LongVarChar data item that is to be supported.  Larger result values will be truncated when an SQL FETCH ROW statement is executed.  This value can be overridden for a particular LongVarChar parameter by using the SQL BIND PARAMETER statement to bind a larger COBOL data item to the query parameter.  The default value is "4096".

John