Skip to main content

Convert programs from ORACLE-sql to MS-SQL server sql

  • November 6, 2013
  • 7 replies
  • 0 views
D

I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
 

1) For ORACLE we had a translation for the codes :

      *    ORACLE sqlcodes.
       01  C01-SQL.
           03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
           03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
           03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
           03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
           03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

I have to convert these values to the MS SQL-server values.
OK is very simple to translate.
 
Has anyone of the forum a translation for me.

2) Are there more specific aspects, for example the length or other, to pay attention for  ?

3)

I also get a message 0000000001.
There is not written in the INTO column :

EXEC SQL
SELECT MAX(log.DT_MUT)
INTO :S01-LOG-MUT
FROM LOG

S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

What is wrong (see attachment)

    7 replies

    Chris Glazier
    Forum|alt.badge.img+3

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    There are many differences in behavior and supported syntax between Pro*COBOL and OpenESQL and the difficulty in conversion depends on how many of these Oracle extensions your application is using.

    OpenESQL is based on the ANSI SQL standards and Pro*COBOL contains many Oracle extensions to the standard.

    The best way to find out the problem areas is to compile your Pro*COBOL apps using the Pro*COBOL FIPS flag so that it will flag code that does not adhere to the ANSI standard.

    In OpenESQL error conditions are returned in sqlcode and sqlstate fields as well as the error message text returned in the sqlerrmc field although it will be truncated to 70 characters

    If you add the following data item to your SQL programs then it will automatically be populated with the sql error message texts and can be > 70 characters:

    01 MFSQLMESSAGETEXT  PIC X(250).

    For a list of sqlcode and sqlstate values that can be returned by OESQL in NX/SX and in Visual COBOL see

    documentation.microfocus.com/.../index.jsp

    Warning conditions are indicated by setting sqlcode=1 and then setting the appropriate SQLWARN flags of the SQLCA structure to a "W"

    For a list of SQLWARN flags that can be set in NX/SX and in Visual COBOL see:

    documentation.microfocus.com/.../index.jsp

    For the conditions that you list in your question:

    Condition                Oracle    OpenESQL

    row-not-found          -1403          100
    row-already-exists         -1            -1
    row-found-with-nulls -1405            0 must check null indicator
    If a null indicator is present and a null is returned then sqlcode will = 0 and then the null indicator will be set to -1
    if a null indicator is not used and a null is returned
       if ANSI92ENTRY directive is on then -19425 is returned
       if NOANSI92ENTRY directive then sqlcode=1 and SQLWARN2=W

    multiple-rows-returned -2112    -811 must set checksingleton directive
        if nochecksingleton then sqlcode=1 and SQLWARN4=W

    Some other differences between Pro*COBOL and OpenESQL that have been found:

    Pro*COBOL doesn't require ":" before host variable name and OESQL does
    Pro*COBOL integer host variables can be pic s9(n) comp where n is 4 thru 9 and OESQL requires either pic s9(4) comp or pic s9(9) comp
    Pro*COBOL allows SELECT data into PIC x fields of any length where OESQL requires PIC X(10) for date or PIC X(29) for TIMESTAMP
    Oracle Dates are actually timestamp fields and are formatted differently than OpenESQL

    In Visual COBOL we have added a number of directives to aid in the conversion of date, time and timestamp fields.
    These are not available in NX or SX:

    TIME
    TIMEDELIM
    DATEDELIM
    TSTAMPSEP
    If using DBMAN=ADO then you can also set OPTION=PROCOB and DATE=EXTERNAL to allow you to set the date format

    D

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    Thanks.

    We have changed the programs. 

    We compiled and build them in NX 5.1 and it works for debugging.

    The Project directives are :

    %FILENAME  list() COBIDY(%TARGETDIR) WB3 WB CSI ANIM EDITOR(MF2) ENSUITE(3)  SQL(DBMAN=ODBC) ;

    Now I have to recompile them for Acceptation and Production (Server for .NET (cobol))

    For ORACLE I used :

    1) first step

    " PROCOB %1.pco INCLUDE=%COBOLORACLE% INCLUDE=%COBOLINC% LNAME=%COBOLLST%\\%1.LSP PICX=VARCHAR2 "

    2) second step

    " COBOL %COBOLPCO%\\%VEERTIEN%.cbl OMF"OBJ" OBJ"%VEERTIEN%" LIST"%COBOLLST%\\%VEERTIEN%.LST"; "

    3) Third step

    cbllink /BSFD /M%1 /O%RUNTME%\\COBOL\\%1.exe %1 %2 %3 %4 %5 %6 %7 %8 %9 %TIEN% %ELF% %TWAALF% ļæ½RTIEN% %VEERTIEN%

    The EXEcutable (%1)  was ready for using on the server.

    Now I have to make EXE's from the sources with SQL-server .

    My question  is :

    - Is there a "PROCOB" for MS SQL-server ?

    Chris Glazier
    Forum|alt.badge.img+3

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    If by "Is there a "PROCOB" for MS SQL-server ?", you mean is there a SQL precompiler for SQL Server then the answer is yes and this is what the SQL(DBMAN=ODBC) directive turns on.

    Technically, OpenESQL is not a "SQL Server" precompiler but is instead a precompiler which will parse your sources for exec sql statements and will generate code that will communicate with ODBC.

    This is an integrated precompiler which means that there is no separate step required.

    If you remove the procob step from your build script and then add the SQL(DBMAN=ODBC) directive to the cbllink then you should be OK.

    " COBOL %COBOLPCO%\\%VEERTIEN%.cbl OMF"OBJ" SQL(DBMAN=ODBC) OBJ"%VEERTIEN%" LIST"%COBOLLST%\\%VEERTIEN%.LST"; "

    Also, note that Procob took in as input, a file with a .pco extension and generated a .cbl file that was then input to the cobol command.

    Since you will be removing the procob step your original sources would still have the .pco extension and this is what you should specify in the cobol command instead of .cbl unless you have gone through the process of renamig all of your .pco files to .cbl manually already?

    D

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    I have a batch-file, who compiles and build the sources. 

    In the batch-file I rename the PCO to CBL so I can use the other batch-files.

    The compile (COBOL) and link (cbllink) are without errors.

    Now I want to execute the program.
    I get the error "The program cannot be started, because odbcrw32.dll is missing"

    I had to put the path of  \\Micro Focus\\Net Express 5.1\\Base\\Bin also to the PATH of windows.

    We use also the product 'Server for .NET' .
    Will the program run there without changes.

    D

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    I have a batch-file, who compiles and build the sources. 

    In the batch-file I rename the PCO to CBL so I can use the other batch-files.

    The compile (COBOL) and link (cbllink) are without errors.

    Now I want to execute the program.
    I get the error "The program cannot be started, because odbcrw32.dll is missing"

    I had to put the path of  \\Micro Focus\\Net Express 5.1\\Base\\Bin also to the PATH of windows.

    We use also the product 'Server for .NET' .
    Will the program run there without changes.

    Chris Glazier
    Forum|alt.badge.img+3
    • Chris GlazierModerator
    • Moderator
    • November 11, 2013

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    odbcrw32.dll is the OpenESQL run-time system for ODBC access and it is present in the folder C:\\Program Files (x86)\\Micro Focus\\Server 5.1\\Bin by defdault (for 5.1 version anyway).

    This must be available to your program at run-time, either by including the folder location in your PATH environment variable or by linking your application dynamically.

    You can link dynamically by including the cbllink option -rS for single-threaded or -rM for multi-threaded applications.

    When you link dynamically the program will look in the computer's registry to find the install location of the Server product and will then be able to locate the required run-time files without the need to set the PATH.

    The Server for .NET product should be fine as long as it is at the same version level as the version of Net Express that you are using to compile your application.

    You will also need to have an appropriate license installed in the license database.

    You can run the apptrack.exe program from this same bin folder in order to install or view installed licenses.

    Thanks.

    D

    I  have to rebuild  COBOL embedded programs from ORACLE sql to MS SQL-server SQL.
     

    1) For ORACLE we had a translation for the codes :

          *    ORACLE sqlcodes.
           01  C01-SQL.
               03  OK                           VALUE  ZERO         PIC S9(04) BINARY.
               03  NOT-FOUND        VALUE 1403         PIC S9(04) BINARY.
               03  ALREADY-EXIST  VALUE -0001         PIC S9(04) BINARY.
               03  MORE-ROWS       VALUE -2112         PIC S9(04) BINARY.
               03  NULL-FOUND      VALUE -1405         PIC S9(04) BINARY.

    I have to convert these values to the MS SQL-server values.
    OK is very simple to translate.
     
    Has anyone of the forum a translation for me.

    2) Are there more specific aspects, for example the length or other, to pay attention for  ?

    3)

    I also get a message 0000000001.
    There is not written in the INTO column :

    EXEC SQL
    SELECT MAX(log.DT_MUT)
    INTO :S01-LOG-MUT
    FROM LOG

    S01-LOG-MUT is pic X(23) and put in the EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    What is wrong (see attachment)

    Chris.

    We have succeeded in converting ORACLE sql-commands to SQL server commands and make our EXE's running in Acceptation-environment.

    The next step is Production. 

    Thanks for your help.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement