Highlighted
Super Contributor.
Super Contributor.
1667 views

Possible to use a variable for database?

Jump to solution

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.

 

.

0 Likes
3 Solutions

Accepted Solutions
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: Possible to use a variable for database?

Jump to solution

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

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Possible to use a variable for database?

Jump to solution

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.

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Possible to use a variable for database?

Jump to solution

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.

Blair McDonald

View solution in original post

0 Likes
5 Replies
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: Possible to use a variable for database?

Jump to solution

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

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Possible to use a variable for database?

Jump to solution

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.

View solution in original post

0 Likes
Highlighted
Super Contributor.
Super Contributor.

RE: Possible to use a variable for database?

Jump to solution

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

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Possible to use a variable for database?

Jump to solution

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.

Blair McDonald

View solution in original post

0 Likes
Highlighted
Super Contributor.
Super Contributor.

RE: Possible to use a variable for database?

Jump to solution

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.  

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.