Possible to use a variable for database?

I have a COBOL program and I want to use it with table A or table B, as both have the same structure and similar data, but I don't want to repeat every SQL stmt for both..  Instead of the next two selects, for example, I would like to use one select stmt with the tablename a variable.:

2000-get-record section.

EXEC SQL

Select * from A

where x = z

END-EXEC.

OR

2100-get-record section.

EXEC SQL

Select * from B

where x = z

END-EXEC.

 

.

  • Verified Answer

    One solution could be to define the SQL statements within a copy file.

    Copy file ‘SQLVarTabName.cpy’:

    EXEC SQL

    select * from TabName

     where x = z

    END-EXEC

    Cobol source:

    If recNumber = 2000

      copy SQLVarTabName replacing TabName by ==A==.

    end-if

    If recNumber = 2100

      copy SQLVarTabName replacing TabName by ==B==.

    end-if

    Freundliche Grüsse

    Werner Lanter

  • Verified Answer

    Or you could use Dynamic SQL where instead of hardcoding the statement within EXEC SQL you instead place the content within a variable and then use PREPARE and EXECUTE to run the statement.

    You can find a video that covers the topic of Dynamic SQL here although this covers OpenESQL and I believe you use Pro*COBOL. You should look at the Oracle docs for this if that is the case.

  • Can it also be used to do updates?  I am finding that trickier...

  • Verified Answer

    Yes you should be able to use Dynamic SQL with Update statements. For example, the video that Chris linked to in his response above (at about 1:07) shows an example of an UPDATE statement being coded both as Static and Dynamic SQL. This example was using the Micro Focus OpenESQL Interface. If you are using another (database) vendor's product, that product probably also provides some support for Dynamic SQL - you'll need to check with the vendor's documentation to be sure.

  • Yes, apparently it can be done with Oracle, which we use, but by using host variables rather than ? placeholders.  I need to look into it more.