Skip to Content
0
Former Member
Jul 04, 2008 at 11:00 AM

Problem importing a DB2 stored procedure into a Crystal Report

81 Views

Hi

I'm using Crystal Reports 11.5 and connecting to a DB2 database on an AS/400 using ODBC.

I have used iSeries navigator's "Run SQL scripts" create a DB2 stored procedure which looks like this:

CREATE PROCEDURE schema_name.proc_name (IN in_param1 VARCHAR(7), IN in_param2  NUMERIC(3))
RESULT SETS 1
LANGUAGE SQL 

BEGIN

  DECLARE numRecords INT;
  INSERT INTO schema_name.table_name (column1, column2) VALUES (in_param1, in_param1);
  SELECT count(column1) INTO numRecords FROM schema_name.table_name;

END;

I'm able to create the procedure and when I call it within iSeries it operates as expected. When i add the stored procedure to a new

Crystal Report using the Report Creation Wizard I'm asked to supply the two parameters which is fine, thats what i expect to happen.

However after supplying the parameters I then get an error stating "Database Connector Error: Cannot obtain error message from server".

The stored procedure is not added to the selected tables part of the wizard. However before Crystal fails to add it to the report it DOES manage

to call the procedure. I know this as I can see a new record (which matches whatever parameters i have entered) appears in the table each

time this rather unhelpful error message appears.

Now I am aware Crystal always requires I do a SELECT statement in a stored procedure otherwise there is no data for the report to pull from it.

However I cannot write a DB2 stored procedure which has a SELECT statement which does not include an INTO clause. If i remove the INTO

clause i get the following error from iSeries navigator when trying to create the procedure:

SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, or trigger proc_name in schema_name not created. Cause 
. . . . . :   SQL procedure, function, or trigger proc_name in schema_namewas not created. The compile was not 
successful. SQL creates an SQL procedure, function, or trigger as a C program that contains embedded SQL.  Errors not found during 
the initial parsing of the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement can be found during the precompile. 
Recovery  . . . :   If a compile error occurred, see the appropriate listing in QSYSPRT. If the SQL precompile failed, there is 
always a listing with the error. If the C compile failed, the listing is only created if requested. Specify SET OPTION OUTPUT=*PRINT 
prior to the routine body in the CREATE statement if listings are required.

I have a feeling my problem is being caused by an issue with the SELECT statement, but cannot be certain.

Any help would be most appreciated!

Thanks

John

Edited by: John Hailey on Jul 4, 2008 12:14 PM

Edited by: John Hailey on Jul 4, 2008 12:21 PM