+ Reply to Thread
Results 1 to 8 of 8

converting COBOL to SQL?

  1. converting COBOL to SQL?

    I have just been given the source code to a COBOL (Net Express) program
    that includes many SQL statements and have been asked to convert it to a
    SQL stored procedure. It includes many SQL cursors that FETCH into
    variables, and then ultimately writes this data out to a series of text
    files. It's highly structured programming from an earlier generation of
    programming, relying on what are essentially calls to reusable
    subroutines that are followed by something like a RETURN.

    Are there any general rules I can rely on to convert structured
    programming like this to a SQL stored procedure?

  2. Re: converting COBOL to SQL?

    On Oct 30, 4:25*pm, Rick wrote:
    > I have just been given the source code to a COBOL (Net Express) program
    > that includes many SQL statements and have been asked to convert it to a
    > SQL stored procedure. *It includes many SQL cursors that FETCH into
    > variables, and then ultimately writes this data out to a series of text
    > files. *It's highly structured programming from an earlier generation of
    > programming, relying on what are essentially calls to reusable
    > subroutines that are followed by something like a RETURN.
    >
    > Are there any general rules I can rely on to convert structured
    > programming like this to a SQL stored procedure?


    Geez!! Is this a halloween trick? Nightmare on SQL Street????

    If this is for real, you don't have a whole lot of choices.

    1. You can try to define the data in and out by analyzing the COBOL
    program. Then throw the program away and try to write set oriented
    SQL to accomplish the same task. Not easy.
    2. You can rewrite/port the COBOL program to SQL as is - cursors,
    warts and all. This for sure ain't pretty and, under any
    circumstances I can envision, it would be my last choice.
    3. You can use an ETL tool, such as SSIS, to recreate the extract and
    export.

    My choice, based on nothing but intuition, is some combination of 1
    and 3 - mostly 3. Stored procs aren't really designed to write flat
    files (although they can) and cursors can _almost_ always be replaced
    with set oriented logic.

    Good luck. This does not sound like fun.

    Payson

  3. Re: converting COBOL to SQL?


    "Rick" wrote in message
    news:MPG.2373e247b8e713829899f1@msnews.microsoft.com...
    >I have just been given the source code to a COBOL (Net Express) program
    > that includes many SQL statements and have been asked to convert it to a
    > SQL stored procedure. It includes many SQL cursors that FETCH into
    > variables, and then ultimately writes this data out to a series of text
    > files. It's highly structured programming from an earlier generation of
    > programming, relying on what are essentially calls to reusable
    > subroutines that are followed by something like a RETURN.
    >
    > Are there any general rules I can rely on to convert structured
    > programming like this to a SQL stored procedure?


    Hi

    I would expect the code to be very in-efficient if converted directly to SQL
    as it is far quicker to use set based statements rather than cursors, so it
    may be worth starting again.

    For the actual output to a file, I suggest that you look at making it part
    of a SSIS package if that is not possible BCP may be an option.

    John


  4. Re: converting COBOL to SQL?

    In article <1e12a5b3-99e6-40d2-8be5-54ce47bfbce0
    @m74g2000hsh.googlegroups.com>, payson_b@hotmail.com says...>
    > On Oct 30, 4:25*pm, Rick wrote:
    > > I have just been given the source code to a COBOL (Net Express) program
    > > that includes many SQL statements and have been asked to convert it to a
    > > SQL stored procedure. *It includes many SQL cursors that FETCH into
    > > variables, and then ultimately writes this data out to a series of text
    > > files. *It's highly structured programming from an earlier generationof
    > > programming, relying on what are essentially calls to reusable
    > > subroutines that are followed by something like a RETURN.
    > >
    > > Are there any general rules I can rely on to convert structured
    > > programming like this to a SQL stored procedure?

    >
    > Geez!! Is this a halloween trick? Nightmare on SQL Street????
    >
    > If this is for real, you don't have a whole lot of choices.
    >
    > 1. You can try to define the data in and out by analyzing the COBOL
    > program. Then throw the program away and try to write set oriented
    > SQL to accomplish the same task. Not easy.
    > 2. You can rewrite/port the COBOL program to SQL as is - cursors,
    > warts and all. This for sure ain't pretty and, under any
    > circumstances I can envision, it would be my last choice.
    > 3. You can use an ETL tool, such as SSIS, to recreate the extract and
    > export.
    >
    > My choice, based on nothing but intuition, is some combination of 1
    > and 3 - mostly 3. Stored procs aren't really designed to write flat
    > files (although they can) and cursors can _almost_ always be replaced
    > with set oriented logic.
    >
    > Good luck. This does not sound like fun.
    >
    > Payson


    Right--"Nightmare on SQL Street" is right.

    I actually don't know what it means to replace cursors with set-oriented
    logic. Rather than fetch table data into variables via a cursor, how
    else would I do it? Thanks for any help; this is new territory for me.

  5. Re: converting COBOL to SQL?

    > I actually don't know what it means to replace cursors with set-oriented
    > logic. *Rather than fetch table data into variables via a cursor, how
    > else would I do it? *Thanks for any help; this is new territory for me.


    You need more than I can give you in a newsgroup posting. You might
    read one of the introductory SQL texts.

    At its most basic, set oriented code operates on an entire set of data
    at once. Whereas cursor and fetch logic operates on one row at a
    time, the following code might insert thousands of rows in one swell
    foop:

    INSERT INTO Foo (alpha, beta, gamma)
    SELECT alpha, beta, gamma
    FROM Bar b
    INNER JOIN Framitz z ON b.alpha = z.alpha -- multiple input tables,
    maybe
    WHERE ... -- filter
    criteria if appropriate

    Good luck again.

    Payson

  6. Re: converting COBOL to SQL?

    In article 6cf6b23c232b@b31g2000prf.googlegroups.com>,
    payson_b@hotmail.com says...
    > > I actually don't know what it means to replace cursors with set-oriented
    > > logic. *Rather than fetch table data into variables via a cursor, how
    > > else would I do it? *Thanks for any help; this is new territory for me.

    >
    > You need more than I can give you in a newsgroup posting. You might
    > read one of the introductory SQL texts.
    >
    > At its most basic, set oriented code operates on an entire set of data
    > at once. Whereas cursor and fetch logic operates on one row at a
    > time, the following code might insert thousands of rows in one swell
    > foop:
    >
    > INSERT INTO Foo (alpha, beta, gamma)
    > SELECT alpha, beta, gamma
    > FROM Bar b
    > INNER JOIN Framitz z ON b.alpha = z.alpha -- multiple input tables,
    > maybe
    > WHERE ... -- filter
    > criteria if appropriate
    >
    > Good luck again.
    >
    > Payson
    >

    Right--thanks. The COBOL program with its SQL cursor
    actually uses the same basic logic that many of the
    stored procedures in our company use when they need to
    write to a text file. It's this logic that I want to
    challenge. They declare a cursor that selects from
    several different tables, fetch selected column values
    into variables, and one by one (one row at a time) do an
    INSERT INTO a *temporary table*. Finally, at the end,
    do a SELECT from this temporary table.

    The proc is then executed by OSQL using its -O (output)
    switch, so the result set of the SELECT statement gets
    written to a text file.

    It's this idea of using a cursor to grab values that
    meet certain criteria and then inserting *one row at a
    time* into the temporary table that I think violates the
    idea of set-oriented logic. But we've done it this way
    using cursors for years and we're not sure how to do it
    differently. Thanks for any help in how to do this
    without a cursor.

  7. Re: converting COBOL to SQL?

    > They declare a cursor that selects from
    > several different tables, fetch selected column values
    > into variables, and one by one (one row at a time) do an
    > INSERT INTO a *temporary table*. *Finally, at the end,
    > do a SELECT from this temporary table.
    >
    > The proc is then executed by OSQL using its -O (output)
    > switch, so the result set of the SELECT statement gets
    > written to a text file.
    >


    You are beyond me. I haven't used OSQL in years. I did some quick
    testing and on my servers (2000 and 2005), the -O switch does not
    cause a text file to be output. Are you sure you aren't redirecting
    the output with ">" ? See the osql -? below. The precise OSQL
    command line could be useful.

    To go any farther with this, I need DDL (CREATE statements for the
    input tables), sample data (INSERT statements) and sample output. A
    sample showing the logic of one of your current stored procedures
    would help me understand what your current process is.

    Payson

    usage: osql [-U login id] [-P password]
    [-S server] [-H hostname] [-E trusted
    connection]
    [-d use database name] [-l login timeout] [-t query timeout]
    [-h headers] [-s colseparator] [-w columnwidth]
    [-a packetsize] [-e echo input] [-I Enable Quoted
    Identifiers]
    [-L list servers] [-c cmdend] [-D ODBC DSN name]
    [-q "cmdline query"] [-Q "cmdline query" and exit]
    [-n remove numbering] [-m errorlevel]
    [-r msgs to stderr] [-V severitylevel]
    [-i inputfile] [-o outputfile]
    [-p print statistics] [-b On error batch abort]
    [-X[1] disable commands [and exit with warning]]
    [-O use Old ISQL behavior disables the following]
    batch processing
    Auto console width scaling
    Wide messages
    default errorlevel is -1 vs 1
    [-? show syntax summary]

  8. Re: converting COBOL to SQL?


    "Rick C." wrote in message
    news:MPG.2378bec98d0365a098990a@msnews.microsoft.com...
    In article 6cf6b23c232b@b31g2000prf.googlegroups.com>,
    payson_b@hotmail.com says...
    > > I actually don't know what it means to replace cursors with set-oriented
    > > logic. Rather than fetch table data into variables via a cursor, how
    > > else would I do it? Thanks for any help; this is new territory for me.

    >
    > You need more than I can give you in a newsgroup posting. You might
    > read one of the introductory SQL texts.
    >
    > At its most basic, set oriented code operates on an entire set of data
    > at once. Whereas cursor and fetch logic operates on one row at a
    > time, the following code might insert thousands of rows in one swell
    > foop:
    >
    > INSERT INTO Foo (alpha, beta, gamma)
    > SELECT alpha, beta, gamma
    > FROM Bar b
    > INNER JOIN Framitz z ON b.alpha = z.alpha -- multiple input tables,
    > maybe
    > WHERE ... -- filter
    > criteria if appropriate
    >
    > Good luck again.
    >
    > Payson
    >

    Right--thanks. The COBOL program with its SQL cursor
    actually uses the same basic logic that many of the
    stored procedures in our company use when they need to
    write to a text file. It's this logic that I want to
    challenge. They declare a cursor that selects from
    several different tables, fetch selected column values
    into variables, and one by one (one row at a time) do an
    INSERT INTO a *temporary table*. Finally, at the end,
    do a SELECT from this temporary table.

    The proc is then executed by OSQL using its -O (output)
    switch, so the result set of the SELECT statement gets
    written to a text file.

    It's this idea of using a cursor to grab values that
    meet certain criteria and then inserting *one row at a
    time* into the temporary table that I think violates the
    idea of set-oriented logic. But we've done it this way
    using cursors for years and we're not sure how to do it
    differently. Thanks for any help in how to do this
    without a cursor.

    Hi

    The only way to be sure of you need will require the code itself, but just
    because it currently processes a row at a time does not mean it can't be set
    based. You may want to try and forget the way it works currently works and
    just look how to get the result and what they represent (if that makes
    sense?)

    You can still use osql or sqlcmd to obtain the output and will only produce
    basic text file output, SSIS is specifically designed to do ETL tasks and
    can output into other databases/formats alot more easily, for instance if
    you want to write it to excel without having to have the user import it.

    John


+ Reply to Thread