Stored Procedure Data Not Returned in BIRT

I call stored procedures a lot in my BIRT reports and they've always worked quite nicely. This stored procedure, however, isn't going well. When I call the stored procedure in MySQL Workbench, I see the correct data rows returned in the results window. When I call this stored procedure in BIRT, I do not see data rows in either a table that references the data set calling the stored procedure, nor do I see any data rows in the "Preview Results" window for the data set.

(This code has been modified to keep our table names anonymous.)

DROP procedure IF EXISTS ch_dw.p_closest5rxproviders;
DELIMITER //
CREATE PROCEDURE ch_dw.p_closest5rxproviders(i_participantid INT(11))
BEGIN
/*************************************************************************************
Create Date: 21-AUG-2012
Author: Megan Ewers
Purpose: SQL to get the 5 closest providers for each rx brand dosage id in savings.
Frequency: Run as needed
Revision History: Dev Track #? -
*************************************************************************************/
DECLARE v_participantid INT(11);

SET v_participantid = i_participantid;

SELECT *
FROM (SELECT s1.rx_brand_dosage_id rx_1,
COUNT(s1.rx_brand_dosage_id) rx_count
FROM chc_etl.TABLE_S s1
JOIN chc_etl.TABLE_ST st on s1.saving_type_id = st.id
JOIN chc_etl.TABLE_SP sp on s1.id = sp.saving_id
JOIN chc_etl.TABLE_MP mp on sp.medical_provider_id = mp.id
LEFT JOIN chc_etl.TABLE_PSC psc on s1.provider_service_code_id = psc.id
LEFT JOIN chc_etl.TABLE_PG pg on s1.provider_grouping_id = pg.id
WHERE rx_brand_dosage_id IS NOT NULL
AND s1.state = 'new'
AND s1.participant_id = v_participantid
GROUP BY 1
HAVING COUNT(s1.rx_brand_dosage_id) > 5) a,
(SELECT q1.medical_provider_id,
q1.rx_brand_dosage_id,
q1.distance,
q1.address1,
q1.address2,
q1.city,
q1.state,
q1.zipcode,
IF(@prev <> q1.rx_brand_dosage_id, @rownum := 1, @rownum := @rownum + 1) AS rank,
@prev := q1.rx_brand_dosage_id rx_3
FROM (SELECT DISTINCT s.rx_brand_dosage_id,
pa.medical_provider_id,
pa.address_type,
pa.address1,
pa.address2,
pa.city,
pa.state,
pa.zipcode,
mp.latitude,
mp.longitude,
round((((acos(sin((p.latitude*pi()/180)) *
sin((mp.latitude*pi()/180))+
cos((p.latitude*pi()/180)) *
cos((mp.latitude*pi()/180)) *
cos(((p.longitude-
mp.longitude)*pi()/180))))*180/pi())*60*1.1515)) distance
FROM chc_etl.TABLE_U u,
chc_etl.TABLE_P p,
chc_etl.TABLE_S s,
chc_etl.TABLE_ST st,
chc_etl.TABLE_SP sp,
chc_etl.TABLE_MP mp,
chc_etl.TABLE_PA pa
WHERE s.user_id = u.id
AND u.id = p.user_id
AND s.state = 'new'
AND s.id = sp.saving_id
AND s.saving_type_id = st.id
AND sp.medical_provider_id = mp.id
AND mp.id = pa.medical_provider_id
AND pa.address_type = 'Primary'
AND s.participant_id = v_participantid
ORDER BY rx_brand_dosage_id, distance asc) q1) b
WHERE a.rx_1 = b.rx_brand_dosage_id
AND b.rank <= 5
ORDER BY a.rx_1, b.rank asc
;

END //
DELIMITER ;

We even tried to "fake out" BIRT by changing this procedure to create a table called "provider_distances" to insert the rows into and then did this:

DROP procedure IF EXISTS ch_dw.p_birt_test;
DELIMITER //
CREATE PROCEDURE ch_dw.p_birt_test(i_participant INT(11))
BEGIN

DROP TABLE IF EXISTS ch_dw.provider_distances;

call p_closest5rxproviders(513624);

SELECT rx_1,
rx_count,
medical_provider_id,
rx_brand_dosage_id,
distance,
address1,
address2,
city,
state,
zipcode,
rank,
rx_3
FROM ch_dw.provider_distances;

DROP TABLE IF EXISTS ch_dw.provider_distances;

END //
DELIMITER ;

It still returned no data rows.

If we ran this: call p_closest5rxproviders(513624);
and then did this:

DROP procedure IF EXISTS ch_dw.p_birt_test;
DELIMITER //
CREATE PROCEDURE ch_dw.p_birt_test(i_participant INT(11))
BEGIN

#DROP TABLE IF EXISTS ch_dw.provider_distances;

#call p_closest5rxproviders(i_participant);

SELECT rx_1,
rx_count,
medical_provider_id,
rx_brand_dosage_id,
distance,
address1,
address2,
city,
state,
zipcode,
rank,
rx_3
FROM ch_dw.provider_distances;

DROP TABLE IF EXISTS ch_dw.provider_distances;

END //
DELIMITER ;

then we got the data to appear in BIRT. So what we determined is BIRT *really* hates something in the p_closest5rxproviders stored procedure. My gut is telling me that one of these two lines is the culprit because I've never used anything like this in the other stored procedures I've called in BIRT:

IF(@prev <> q1.rx_brand_dosage_id, @rownum := 1, @rownum := @rownum + 1) AS rank,
@prev := q1.rx_brand_dosage_id rx_3

Ideas for how to do our little ranking magic somehow that won't tick BIRT off?

Find more posts tagged with

Comments

mwilliams

Do you get any errors? Is there anything in the log file?

Meg E.

Hi, Michael,

There is no error showing up in the BIRT application itself, but I did find some things in the .log file. I've put what I think are the relevant log entries in the attached file for you to look over.

Meg

mwilliams

I talked to one of the ODA experts and they wanted to make sure you were choosing the stored procedure dataSet because the error you're getting seems to suggest you're using a SQL Select Query dataSet, instead of the SQL Stored Procedure Query.

Meg E.

Hi, Michael,

It turns out I was *not* using a stored procedure data set (nor have I ever used that for any of my stored procedures which is making me cringe now). When I attempted to create one, this is the error I got:

set type (org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet).
[{call ch_dw.p_closest5rxproviders(513624)} ]
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement.
SQL error #1:User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
;
java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
!SESSION 2012-08-28 15:23:11.939

eclipse.buildId=M20110210-1200
java.version=1.7.0_03
java.vendor=Oracle Corporation
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Command-line arguments: -os win32 -ws win32 -arch x86

!ENTRY org.eclipse.core.resources 2 10035 2012-08-28 15:23:14.001
!MESSAGE The workspace exited with unsaved changes in the previous session; refreshing workspace to recover changes.

Do I need to go spend some quality time with our DBA?

Meg

mwilliams

Have you tried the noAccessToProcedureBodies=true, just to see what happens?

Meg E.

Funny you ask, because I just went hunting for that property setting and couldn't find it. If you'll tell me where to look, I'll be happy to see if setting to "True" works. It's not like I can break things any more than they already are ;-)

mwilliams

I believe you set it in your database connections, like in the JDBC URL or something, as a parameter.

mwilliams

Take a look at this page. It might help.

http://sql.dzone.com/tips/quick-tip-grating-access-meta-

Meg E.

The DBA and I looked over the dzone link you sent. After talking through the issue I've run into, he went with the first solution suggested and granted the BIRT reporting account the necessary permissions on proc, which took care of the error I was getting when I tried to preview the results of my stored procedure. However...still no data coming through from the stored procedure itself. So I basically just took a step sideways here.

A couple of the dev guys have taken an interest in this and think maybe it's time to rewrite the procedure in such a way that we're not using those lines with temp variables in them anymore. There was talk of cursors, which I had hoped to avoid, but sometimes you gotta do whatcha gotta go.

If the folks on your end think of anything, I'd love to hear it. But it sounds like we'll be coming up with a new stored procedure solution.

tanvi_openText

Hi friends,

When I call the stored procedure in MySQL Workbench, I see the correct data rows returned in the results window. But when I call this stored procedure in BIRT I am getting the error as -
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
The parameter is going NULL.

Could you please help me?

My stored procedure is -

DELIMITER ;;
CREATE PROCEDURE DownloadProfiles(IN mnoid VARCHAR(255))
BEGIN
set @mnoid = mnoid;
@group_concat_max_len = 10000;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(IF(COALESCE(profile_type.display_name,profile_type.subscription_type) = ''',COALESCE(profile_type.display_name,profile_type.subscription_type),''', 1, 0)) AS''',COALESCE(profile_type.display_name,profile_type.subscription_type),''''
)
) INTO @sql FROM profile_type where mno_id = @mnoid;
SET @sql = CONCAT('SELECT COALESCE(DATE(r.end_download_date), DATE(r.last_update)) AS DatRange, ', @sql, ' FROM rsp_session r
inner join subscription s on s.iccid = r.profile_iccid
inner join profile_type on s.profile_type = profile_type.id
where r.transaction_status in (''COMPLETED'',''TERMINATED'') and
s.status in (''INSTALLED'',''ENABLED'',''DELETED'')
group by COALESCE(DATE(r.end_download_date), DATE(r.last_update))');

prepare stmt from @sql;
execute stmt;

END;;
DELIMITER ;

Thank you in advance!
Tanvi.