Creating oracle database tables from acu4gl Vision Files

I am trying to create oracle database tables from acu4gl vision files using cobol and .xfd files

How do I setup the naming conventions to differentiate the vision file from the database table name.  Do I need to create 'another' set of .xfd files?

  • Verified Answer

    It sounds like you are converting your application’s data files to Oracle Tables. If so, you’ll use a COBOL program that opens the Vision file input, opens the Oracle table output, reads each Vision record and writes them to the table. To do that you’ll define two files in your program with two separate SELECTs and FDs, and probably the easiest way is to rename the Vision file itself - in this example I just added a V to it. Don’t forget to change the name of the index also. After the conversion you can restore the name of the file if you need to. So if your original description is:

    SELECT ACCOUNTS ASSIGN TO “ACCOUNTS”…

    FD ACCOUNTS.
    01 ACC-REC…

    Then your conversion program will have something like:

    SELECT INFILE ASSIGN TO “ACCOUNTSV”…

    SELECT ACCOUNTS ASSIGN TO “ACCOUNTS”…

    FD INFILE.
    01 IN-ACC-REC…

    FD ACCOUNTS.
    01 ACC-REC…

    Compile that with -Fx and you’ll get two XFDs, ACCOUNTSV.xfd, which won’t be used, and ACCOUNTS.xfd, which will be used by Acu4GL to access the ACCOUNTS table.

    Lastly, in your Runtime configuration file you’ll have:

    ACCOUNTSV_HOST Vision
    DEFAULT_HOST Oracle
    (and the other Acu4GL configurations for connecting to Oracle)
  • Since 1992 acucobol have a generic program (CONVERT that you can find on the support page under Example and Utilities) that convert
    files from a VISION format to other formats such as ORACLE INFORMIX C-ISAM etc. and vice versa, I used it to convert all or at least 90 percent
    of my data given by VISION to ORACLE.
    If you do not find the program let me know that I can send you mine in which I made some small changes
  • I have used the CONVERT program many times to convert a whole database of vision files. A couple of extra tips/points:
    1. You need to also download a program call menubar for this.
    2. What I have found is that you are best to create the empty database versions of the files before running the conversion on your files. We use a program written in COBOL called ALLFILE that creates an empty database. Doing this sets the file structure up within the receiving database.
    3. Configuration variables for CONVERT are:
    A_MSSQL_DATABASE OSLIVE
    A_MSSQL_LOCK_DB live_lockdb
    A_MSSQL_LOGIN optim (This is an MS SQL user)
    A_MSSQL_PASSWD password_here
    A_MSSQL_DEFAULT_CONNECTION MS SQL server here
    A_MSSQL_NO_TABLE_LOCKS 1
    A_MSSQL_NO_RECORD_LOCKS ON

    XFD_DIRECTORY d:\optim\live\xfds

    # make the default file type to be VISION
    DEFAULT_HOST VISION

    # Create WORKFILEs as vision files to use in standard reports
    WORKFILE_HOST VISION
    WORKFILE0_HOST VISION
    WORKFILE1_HOST VISION
    WORKFILE2_HOST VISION
    WORKFILE3_HOST VISION
    WORKFILE4_HOST VISION
    WORKFILE5_HOST VISION
    WORKFILE6_HOST VISION
    WORKFILE7_HOST VISION
    WORKFILE8_HOST VISION
    WORKFILE9_HOST VISION

    # fsaudt has a field > 255 char which is not allowed under MSSQL
    # so make it a vision file
    FSAUDT_HOST VISION
    FSHELP_HOST VISION
    FSLANG_HOST VISION
    FSMENU_HOST VISION
    FSLICN_HOST VISION
    FSPSWD_HOST VISION
    WSALOC_HOST VISION
    WSSRV2_HOST VISION

    OSAUDT_HOST VISION
    OSHELP_HOST VISION
    OSLANG_HOST VISION
    OSMENU_HOST VISION
    OSLICN_HOST VISION
    OSPSWD_HOST VISION
    OSALOC_HOST VISION
    OSSRV2_HOST VISION


    # details below to run convert program
    SRC_HOST VISION
    TRG_HOST MSSQL

    Obviously the above is to convert to MS SQL but just replace the MS SQL statements with ORACLE statements.

    To actually run your new system here is an example configuration file:
    A_MSSQL_DATABASE OSLIVE
    A_MSSQL_LOCK_DB live_lockdb
    A_MSSQL_LOGIN optim (This is an MS SQL user)
    A_MSSQL_PASSWD password_here
    A_MSSQL_DEFAULT_CONNECTION MS SQL server here
    A_MSSQL_EXTRA_PROC 1
    A_MSSQL_NO_TABLE_LOCKS 1
    A_MSSQL_ROWCOUNT 20

    XFD_DIRECTORY d:\optim\live\xfds

    # make the default file type to be MSSQL
    DEFAULT_HOST MSSQL

    # Create WORKFILEs as vision files to use in standard reports
    WORKFILE_HOST VISION
    WORKFILE0_HOST VISION
    WORKFILE1_HOST VISION
    WORKFILE2_HOST VISION
    WORKFILE3_HOST VISION
    WORKFILE4_HOST VISION
    WORKFILE5_HOST VISION
    WORKFILE6_HOST VISION
    WORKFILE7_HOST VISION
    WORKFILE8_HOST VISION
    WORKFILE9_HOST VISION

    # fsaudt has a field > 255 char which is not allowed under MSSQL
    # so make it a vision file
    FSAUDT_HOST VISION
    FSHELP_HOST VISION
    FSLANG_HOST VISION
    FSMENU_HOST VISION
    FSLICN_HOST VISION
    FSPSWD_HOST VISION
    WSALOC_HOST VISION
    WSCSC1_HOST VISION
    WSSRV2_HOST VISION
    CSELNK_HOST VISION

    OSAUDT_HOST VISION
    OSHELP_HOST VISION
    OSLANG_HOST VISION
    OSMENU_HOST VISION
    OSLICN_HOST VISION
    OSPSWD_HOST VISION
    OSALOC_HOST VISION
    OSCSC1_HOST VISION
    OSSRV2_HOST VISION
    OSELNK_HOST VISION

    The major difference is that the 2 variables for the conversion program are not required and the default host is set to MSSQL instead of vision.

    I hope this helps you with your coversions
    Regards
    Brian
  • Sorry forgot to mention how to run the conversion programs. Create a batch file with the following command lines:

    "C:\Program Files (x86)\Acucorp\Acucbl813\AcuGT\bin\wrun32.exe" -le d:\optim\cnv_list_errors\CSCPTR.txt -c sqlconlive.cfg CONVERT d:\optim\cnv_list_files\CSCPTR.txt
    "C:\Program Files (x86)\Acucorp\Acucbl813\AcuGT\bin\wrun32.exe" -le d:\optim\cnv_list_errors\CSELNK.txt -c sqlconlive.cfg CONVERT d:\optim\cnv_list_files\CSELNK.txt
    "C:\Program Files (x86)\Acucorp\Acucbl813\AcuGT\bin\wrun32.exe" -le d:\optim\cnv_list_errors\FS0001D.txt -c sqlconlive.cfg CONVERT d:\optim\cnv_list_files\FS0001D.txt
    "C:\Program Files (x86)\Acucorp\Acucbl813\AcuGT\bin\wrun32.exe" -le d:\optim\cnv_list_errors\FS0001T.txt -c sqlconlive.cfg CONVERT d:\optim\cnv_list_files\FS0001T.txt
    "C:\Program Files (x86)\Acucorp\Acucbl813\AcuGT\bin\wrun32.exe" -le d:\optim\cnv_list_errors\FS0002D.txt -c sqlconlive.cfg CONVERT d:\optim\cnv_list_files\FS0002D.txt

    The cnv_list_files have one line entries for each file as follows:

    d:\optim\vision\files\FS0001D FS0001D

    Within the configuration files, CODE_PREFIX and FILE_PREFIX looks like this:
    CODE-PREFIX d:\optim\live\obj\extra;d:\optim\live\obj\std
    FILE-PREFIX d:\optim\vision\files

    I hope this also helps.
    Regards
    Brian