Embedded SQL in COBOL program

I am trying to compare the same file that exists in two distinct environments on AS400. The file may or may not be the same, that is the purpose of my program. I have created an SQL statement that preforms the task. However when i try to impletment this statement in a program i am having problems. In my program i take 2 values entered by the user, the two environments that i want to do the file comparison between. In my Declare cursor, i have a valid 'SELECT', but the 'FROM' part of my statement is going trouble, i cannot include the user entered environment here, it seems it has to be hard-coded. Can someone please give me a possible solution to this or to somewhere on the net with an example how to do this. Kind Regards, ITnavan

Answer Wiki

Thanks. We'll let you know when a new response is added.

I believe you are right about the inability to specify a library on the embedded SQL statement. A couple of ideas occur to me (I haven’t checked these so caveat emptor):

You could take a look at the ALIAS command for SQL – this might allow the softcoding of the library via a different route. It could be a temporary thing that got removed when you are done.

Alternatively you could break the process down. e.g.

A CL that goes through three steps:
1) Get the user input via a HLL program.
2) Do any overrides for libraries / members whatever SQL can’t handle.
3) Use another HLL program to run the SQL and present the results.

Hope there’s something of use in this lot.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • JacekK
    Hi, In general your task is 'dynamic', so it can be accomplished by ... dynamic SQL. It has some restrictions comparing with 'static' but in some situations it's really valuable feature. Here is an outline of possible solution: 0004.00 DATA DIVISION. 0005.00 WORKING-STORAGE SECTION. 0006.00 01 SQLSTMT PIC X(500). SQL EXEC SQL INCLUDE SQLCA END-EXEC. 0008.00 PROCEDURE DIVISION. 0009.00 RUNSQL. SQL EXEC SQL SET TRANSACTION ISOLATION LEVEL NO COMMIT END-EXEC. 0011.00 MOVE "insert into f select count(*) from tmp1pf" to SQLSTMT SQL EXEC SQL EXECUTE IMMEDIATE :SQLSTMT END-EXEC. 0013.00 STOP RUN. At first sight it has nothing common with your question. Mainly because this is my first COBOL programm :-) The clue is the 0012 line (...EXECUTE IMMEDIATE...). It allows you to execute SQL statement (not every) which is dynamically got from string variable. Yes, I don't know how to concatenate two strings in COBOL, but you can form this statement using library/file names provided by user (ACCEPT?). As you can see I 'redirect' output from this select statement into some simple file - that's because you can't use host variables in dynamic SQL. But it shouldn't be problem to process such temporary file in COBOL - I hope. I'm not sure but as I remember your task can be accomplished even without this temp file using some advanced dynamic SQL techniques (PREPARE + descriptors ?). IBM SQL manuals have some informations about this flavour of SQL. Hope this help. -- Regards, Jakobr
    0 pointsBadges:
  • Lriger
    Is all the data on the same database server or is it on different database servers on separate environments? You say that the queries work independently from the program. what is different? I would expect that you connect to different servers. I don't mean only different physical hardware , but also database servers. When COBOL is loaded on an as400 system it is probably setup to point to the database server on the same box. You may be able to point to another database server by using the IP address of the other box, but it would be better if the database server is named differently from each other. The network could then determine which database you want then you could connect to each server separately. I suspect that the database servers are named the same. You will probably need to see if you can hardcode the IP address not the dns name. Otherwise you will have to see if you can configure the COBOL installation. I have not worked with the AS400, but I have seen it on Unix and mainframes. sincerely Rich Gerber
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: