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!
Edited by: John Hailey on Jul 4, 2008 12:14 PM
Edited by: John Hailey on Jul 4, 2008 12:21 PM