Highlighted
Absent Member.
Absent Member.
906 views

Automating MF native file conversion to MYSQL

Hi,

Might be reinventing the wheel here (?).... I'm writing a program to generate code to handle MYSQL calls and return data to existing cobol record layouts i.e. so that COBOL doesn't really know the difference when e.g. a 'write myfile-rec' is simply replaced with a 'perform db-myfile-insert'.

I have a question... Some of my records contain binary fields e.g. PIC 9(5)V99 comp. The db equivalent MYSQL field is defined as decimal(7,2). Visual COBOL says I can't use the PIC 9(5)V99 comp. field as a host variable. Is that right or am I doing something wrong? I've worked around it by using an intermediary host field but obv that makes the process more involved and fiddly. 

If anyone's interested, the program I've written (so far, it's a work in progress but basically works) takes in a record layout spec like so:

Table, Field, Type, NonStdField, KeyNo
products, PROD-COMPANY, X, ,1
products, PROD-TYPE, X, ,1
products, PROD-GROUP, X, ,1
products, PROD-CODE, X, ,1
products, PROD-DESC, X, ,
products, PROD-PRICE-TYPE, X, ,
products, PROD-PRICE, 2, Y,
products, PROD-linage-min-price, 2, Y,
products, PROD-box-charge-post, 2, Y,
products, PROD-box-charge-collect, 2, Y,
products, PROD-incl-std-vat-flag, X, ,
vat,vat-start-ccyymmdd, %Y%m%d, ,1desc
vat,vat-rate, 2, ,
 
AND IT CREATES SQL LIKE SO....
 
      db-start section.
           exec sql
               whenever sqlerror perform db-error
           end-exec.
           exit.
 
       db-finish section.
           exit.
 
       db-error section.
           display mfsqlmessagetext, sqlcode.
           exit.
 
      *-----------------------------------------------------------------
       db-products-get-next section.
      *-----------------------------------------------------------------
        if db-products-cursor-closed
         exec sql
          declare db-products-curs cursor for
           select
            prod_company,
            prod_type,
            prod_group,
            prod_code,
            prod_desc,
            prod_price_type,
            prod_price,
            prod_linage_min_price,
            prod_box_charge_post,
            prod_box_charge_collect,
            prod_incl_std_vat_flag
           from products
           order by
            prod_company
            , prod_type
            , prod_group
            , prod_code
         end-exec
         exec sql
          open db-products-curs
         end-exec
         set db-products-cursor-open to true
        end-if.
 
        exec sql
         fetch db-products-curs
          into
           :prod-company,
           :prod-type,
           :prod-group,
           :prod-code,
           :prod-desc,
           :prod-price-type,
           :db-oddNum01,
           :db-oddNum02,
           :db-oddNum03,
           :db-oddNum04,
           :prod-incl-std-vat-flag
        end-exec.
 
        if sqlcode = 100
         exec sql
          close db-products-curs
         end-exec
         set db-products-cursor-closed to true
        else
         move db-oddNum01 to prod-price
         move db-oddNum02 to prod-linage-min-price
         move db-oddNum03 to prod-box-charge-post
         move db-oddNum04 to prod-box-charge-collect
        end-if.
 
      *-----------------------------------------------------------------
       db-products-get-by-key section.
      *-----------------------------------------------------------------
        move prod-price to db-oddNum01.
        move prod-linage-min-price to db-oddNum02.
        move prod-box-charge-post to db-oddNum03.
        move prod-box-charge-collect to db-oddNum04.
        exec sql
         select
          prod_company,
          prod_type,
          prod_group,
          prod_code,
          prod_desc,
          prod_price_type,
          prod_price,
          prod_linage_min_price,
          prod_box_charge_post,
          prod_box_charge_collect,
          prod_incl_std_vat_flag
         into
          :prod-company,
          :prod-type,
          :prod-group,
          :prod-code,
          :prod-desc,
          :prod-price-type,
          :db-oddNum01,
          :db-oddNum02,
          :db-oddNum03,
          :db-oddNum04,
          :prod-incl-std-vat-flag
         from products
         where
          prod_company = :prod-company
          and prod_type = :prod-type
          and prod_group = :prod-group
          and prod_code = :prod-code
        end-exec.
 
        if sqlcode = 100
         set db-products-not-found to true
        else
         set db-products-not-found to true
          move db-oddNum01 to prod-price
          move db-oddNum02 to prod-linage-min-price
          move db-oddNum03 to prod-box-charge-post
          move db-oddNum04 to prod-box-charge-collect
        end-if.
 
      *-----------------------------------------------------------------
       db-products-delete section.
      *-----------------------------------------------------------------
        move prod-price to db-oddNum01.
        move prod-linage-min-price to db-oddNum02.
        move prod-box-charge-post to db-oddNum03.
        move prod-box-charge-collect to db-oddNum04.
        exec sql
         delete from products
         where
          prod_company = :prod-company
          and prod_type = :prod-type
          and prod_group = :prod-group
          and prod_code = :prod-code
        end-exec.
 
      *-----------------------------------------------------------------
       db-products-insert section.
      *-----------------------------------------------------------------
        move prod-price to db-oddNum01.
        move prod-linage-min-price to db-oddNum02.
        move prod-box-charge-post to db-oddNum03.
        move prod-box-charge-collect to db-oddNum04.
        exec sql
         insert into products
         (
          prod_company,
          prod_type,
          prod_group,
          prod_code,
          prod_desc,
          prod_price_type,
          prod_price,
          prod_linage_min_price,
          prod_box_charge_post,
          prod_box_charge_collect,
          prod_incl_std_vat_flag
         ) values (
          :prod-company,
          :prod-type,
          :prod-group,
          :prod-code,
          :prod-desc,
          :prod-price-type,
          :db-oddNum01,
          :db-oddNum02,
          :db-oddNum03,
          :db-oddNum04,
          :prod-incl-std-vat-flag
         )
        end-exec.
 
      *-----------------------------------------------------------------
       db-products-update section.
      *-----------------------------------------------------------------
        move prod-price to db-oddNum01.
        move prod-linage-min-price to db-oddNum02.
        move prod-box-charge-post to db-oddNum03.
        move prod-box-charge-collect to db-oddNum04.
        exec sql
         update products set
          prod_company = :prod-company,
          prod_type = :prod-type,
          prod_group = :prod-group,
          prod_code = :prod-code,
          prod_desc = :prod-desc,
          prod_price_type = :prod-price-type,
          prod_price = :db-oddNum01,
          prod_linage_min_price = :db-oddNum02,
          prod_box_charge_post = :db-oddNum03,
          prod_box_charge_collect = :db-oddNum04,
          prod_incl_std_vat_flag = :prod-incl-std-vat-flag
         where
          prod_company = :prod-company
          and prod_type = :prod-type
          and prod_group = :prod-group
          and prod_code = :prod-code
        end-exec.
 
      *-----------------------------------------------------------------
       db-products-ins-or-upd section.
      *-----------------------------------------------------------------
        perform db-products-insert.
        if sqlcode = -1062
         perform db-products-update
        end-if.
 
      *-----------------------------------------------------------------
       db-vat-get-next section.
      *-----------------------------------------------------------------
        if db-vat-cursor-closed
         exec sql
          declare db-vat-curs cursor for
           select
            vat_start_ccyymmdd,
            vat_rate
           from vat
           order by
            vat_start_ccyymmdd desc
         end-exec
         exec sql
          open db-vat-curs
         end-exec
         set db-vat-cursor-open to true
        end-if.
 
        exec sql
         fetch db-vat-curs
          into
           :db-date01,
           :vat-rate
        end-exec.
 
        if sqlcode = 100
         exec sql
          close db-vat-curs
         end-exec
         set db-vat-cursor-closed to true
        else
         string db-date01(1:4) db-date01(6:2) db-date01(9:2)
          into vat-start-ccyymmdd
        end-if.
 
      *-----------------------------------------------------------------
       db-vat-get-by-key section.
      *-----------------------------------------------------------------
        string vat-start-ccyymmdd(1:4) '-'
               vat-start-ccyymmdd(5:2) '-'
               vat-start-ccyymmdd(7:2) '-'
         into db-date01.
        exec sql
         select
          vat_start_ccyymmdd,
          vat_rate
         into
          :db-date01,
          :vat-rate
         from vat
         where
          vat_start_ccyymmdd = :db-date01
        end-exec.
 
        if sqlcode = 100
         set db-vat-not-found to true
        else
         set db-vat-not-found to true
          string db-date01(1:4) db-date01(6:2) db-date01(9:2)
           into vat-start-ccyymmdd
        end-if.
 
      *-----------------------------------------------------------------
       db-vat-delete section.
      *-----------------------------------------------------------------
        string vat-start-ccyymmdd(1:4) '-'
               vat-start-ccyymmdd(5:2) '-'
               vat-start-ccyymmdd(7:2) '-'
         into db-date01.
        exec sql
         delete from vat
         where
          vat_start_ccyymmdd = :db-date01
        end-exec.
 
      *-----------------------------------------------------------------
       db-vat-insert section.
      *-----------------------------------------------------------------
        string vat-start-ccyymmdd(1:4) '-'
               vat-start-ccyymmdd(5:2) '-'
               vat-start-ccyymmdd(7:2) '-'
         into db-date01.
        exec sql
         insert into vat
         (
          vat_start_ccyymmdd,
          vat_rate
         ) values (
          :db-date01,
          :vat-rate
         )
        end-exec.
 
      *-----------------------------------------------------------------
       db-vat-update section.
      *-----------------------------------------------------------------
        string vat-start-ccyymmdd(1:4) '-'
               vat-start-ccyymmdd(5:2) '-'
               vat-start-ccyymmdd(7:2) '-'
         into db-date01.
        exec sql
         update vat set
          vat_start_ccyymmdd = :db-date01,
          vat_rate = :vat-rate
         where
          vat_start_ccyymmdd = :db-date01
        end-exec.
 
      *-----------------------------------------------------------------
       db-vat-ins-or-upd section.
      *-----------------------------------------------------------------
        perform db-vat-insert.
        if sqlcode = -1062
         perform db-vat-update
        end-if.
 
Linden

 

 

 

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
1 Reply
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Automating MF native file conversion to MYSQL

There is a list of compatible SQL Data types in the product docs here:

Data items with usage COMP are supported but only for integer fields not for decimal.

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.