We're trying to use the Jira API to retrieve completed issues so that we can show improvements made to different products over time. I now want to display this JSON data using mydbr, if possible.
I was thinking I could retrieve some of the JSON data into a temporary table and then perform a select query against it.
When I get to my finish procedure, or perhaps the import procedure, it tells me that the temporary table doesn't exist. Why can't I access it? Are the prepare, import and finish procedures being run using different connections?
Is it even possible to do what I'm trying to do? Or is there a better way to go about this?
Here is the SQL-code for my reports:
DROP PROCEDURE IF EXISTS sp_DBR_Improvements
CREATE PROCEDURE `sp_DBR_Improvements`()
select 'dbr.import.options', 'format', 'json'; select 'dbr.import.prepare', 'sp_DBR_JiraAPI_prepare';
select 'dbr.import', 'sp_DBR_JiraAPI_do' ,'http://10.73.20.114/?jql=assignee=charles.lindberg+AND+project=MSB+AND+type=improvement', 'now';
select 'dbr.import.finish', 'sp_DBR_JiraAPI_post'; end
$$ DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_prepare
CREATE PROCEDURE `sp_DBR_JiraAPI_prepare`()
drop temporary table if exists mydbr.jira_tmp;
create temporary table mydbr.jira_tmp ( inID varchar(100) );
$$ DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_do
CREATE PROCEDURE `sp_DBR_JiraAPI_do`(inID varchar(100))
insert into mydbr.jira_tmp (inID)
values (inId); end
$$ DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_post
CREATE PROCEDURE `sp_DBR_JiraAPI_post`()
select * from mydbr.jira_tmp;