calling stored procedures and returning data

[hillstax]

We have a system that we are trying to bring up. The cobol was Microfocus and I'm in the process of converting it to Fujitsu. right now I've been running in to a problem with calling stored procedures that do a select and should return data. The stored procedure has been debugged and works when called on the sql server, I have run a trace on the sql server and see the input parms have data. The problem is within the cobol program getting the ouput from the stored procedure. Do you have any examples of cobol calling stored procedures? If you do not, do you know of anyone that uses Fujitsu cobol calling stored procedures.

Thanks for any help

 

[Tromba]

Hi Hillstax

I made some tests few months ago using Powercobol and SQL.
I was using both Sql Server and Ms Access.
I haven't understood if your stored procedures are on the same database file. If yes, you need to declare a table two times: one for data and one for stored procedure using the DbAccess control supplied with Powercobol.
DbAccess control has two options on their properties: Table and Procedure, but if you need to use both, a DbAccess control must be declared twice (Table and Procedure).
It works and you can read the stored procedure and execute your own statements.

Let me know.

Gianni
alias Tromba

 

[hillstax]

Gianni,

Thanks for the response.

The database, tables and stored procedures are on the same sql server 2000. I'm using Fujitsu Cobol V6.1, the enterprise version which does have power cobol with it but I need to eventually wrap this cobol with object oriented code so that is why I'm not using the PowerCobol. With the Fujitsu Cobol 6.1 I did not see a utility called DbAccess control but I will do some more searching. For now what I wanted to show you is a part from the source code working storage, procedure section and then the stored procedure on the sql server that is being called. I know the stored procedure works because I use it in an asp web page that was designed using Front Page and it returns all the rows. I think my stumpling block here is how do I use or get the data rows that are being returned back in to the cobol program?





*** WORKING STORAGE
EXEC SQL BEGIN DECLARE SECTION END-EXEC.

01 TAX-YEAR PIC S9(04).
01 COLLECTOR-NUMBER PIC S9(10).
01 MSG PIC X(255).
01 DESCRIPTION PIC X(30).
01 EXEMPT-AMOUNT PIC 9(11)V9(2).


01 SQLSTATE PIC X(5) VALUE SPACES.
01 SQLCODE PIC S9(9) COMP-5 VALUE 0.
01 SQLMSG PIC X(128) VALUE SPACES.


EXEC SQL END DECLARE SECTION END-EXEC.

*** PROCEDURE DIVISION
PROCEDURE DIVISION.

0000-BALANCING-REPORTS.


EXEC SQL
CONNECT TO DEFAULT
END-EXEC.


EXEC SQL
EXECUTE REEXEMPTIONS( :TAX-YEAR,
:COLLECTOR-NUMBER,
:MSG)
END-EXEC.



*** STORED PROCEDURE

CREATE PROCEDURE reExemptions
@tax_year int,
@collector_number numeric(10,0),
@msg varchar(255) = '' output
AS
SELECT exemption_code.exempt_code description,
exempt_amount
FROM exemption_code,
exemption_value
WHERE exemption_code.exempt_code = exemption_value.exempt_code and
collector_number = @collector_number AND
tax_year = @tax_year
GO

 

[hillstax]

That sql statement in the procedure division should read call reexemptions instead of execute reexemptions. I was trying something out when I copied the code.

 

[Tromba]

Hi Hill

Ok i will test your little source code.
What solution do you prefer? Powercobol or Fujitsu cobol?
In the meantime i will test it using Powercobol and DbAccess controls, storing some data and procedures on a database.

Let me know

Gianni

 

[hillstax]

Gianni,

Thanks for looking in to this. I would eventually like a Fujitsu Cobol solution. Once again thanks for you time.


Andy,

 

[hillstax]

Gianni,

After talking with Fujitsu they said that a stored procedure returning a unkown number of rows can not be done and that I would have to embed the stored procedure sql in to the cobol program and use a cursor and fetch to retrieve each row.

Andy