Usign dbo.import with a temporary table

(12 posts) (2 voices)

Started by Charles
Latest reply from myDBR Team

Tags:

Charles, Member

Hello,

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?

Thanks.

Here is the SQL-code for my reports:



 DROP PROCEDURE IF EXISTS sp_DBR_Improvements

$$

CREATE PROCEDURE `sp_DBR_Improvements`()

begin

  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`()

begin

  drop temporary table if exists mydbr.jira_tmp;

  create temporary table mydbr.jira_tmp ( inID varchar(100) );

end

$$

DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_do

$$

CREATE PROCEDURE `sp_DBR_JiraAPI_do`(inID varchar(100))

begin

  insert into mydbr.jira_tmp (inID)

  values (inId);

end

$$

DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_post

$$

CREATE PROCEDURE `sp_DBR_JiraAPI_post`()

begin

  select * from mydbr.jira_tmp;

end

$$

myDBR Team, Key Master

Prepare and the actual import are done in same process. The finish is done after the import with another process (hence the temp table is no longer available). Just use a normal table with additional fields identifying the import.

--
myDBR Team
Charles, Member

Is it only possible to do insert statements inside the import procedure? I started by trying to create a temporary table inside that procedure, but it wasn't wokrking. It would be nice to not have to handle the identifying of reports logging different information. I really don't need to access the information after a report is shown either, so a temporary table seems perfect in this case.

I also saw this post https://mydbr.com/forums/topic.php?id=311#post-1298 where someone from the myDBR Team answered:

You also might want to use normal table instead of temporary table since the temporary table is removed when the connection is closed (if you do not process the rows in sp_DBR_Import_Reloads_post).

The sp_DBR_Import_Reloads_post in his case is the finish procedure. So from reading that I figured it would be possible.
myDBR Team, Key Master
Charles,
you can use any valid SQL in the import (and any other) procedures.

By default, myDBR uses different DB connections for separate queries, so temporary tables created in one connection are cleared by the database server when the connection closes (in MS SQL Server & Sybase, temporary tables live just for the procedure execution).

If you use a normal table for the import, you can identify the import by adding extra parameters to the import (for example user_id + timestamp). You can then clear the rows at the end of finish procedure using the same parameters.

If you really want to use temporary tables in import, you can set myDBR to use pooled connections. If you enable the option, you just need to be more careful when creating temporary tables (drop when you are done / use drop if exists when creating). To enable the pooled connections add:
```
$mydbr_defaults['db_connection']['pooled_connections'] = true;
```
into the user/defaults.php.

--
myDBR Team

Charles, Member

Hello again,

I just tried adding that row to the defaults.php and the error message about the temporary table not existing just disappeared. But, why trying to select from the temporary table, in the finish procedure, I can't see anything. If I move the select statement to the main report, it still says that the temporary table does not exist.

Here is my report (the URL simply returns a string as of now):



DROP PROCEDURE IF EXISTS sp_DBR_MSBImprovements

$$

CREATE PROCEDURE `sp_DBR_MSBImprovements`()

begin

  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

$$

Here is my prepare procedure



USE mydbr

$$

DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_prepare

$$

CREATE PROCEDURE `sp_DBR_JiraAPI_prepare`()

begin

  drop temporary table if exists mydbr.jira_tmp;

  create temporary table mydbr.jira_tmp ( inID varchar(100) );

end

$$

Here is the import procedure



USE mydbr

$$

DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_do

$$

CREATE PROCEDURE `sp_DBR_JiraAPI_do`(inID varchar(100))

begin

  insert into mydbr.jira_tmp (inID)

  values ('test123');

end

$$

As you can see, I insert a hard coded string just to see if I can select it later. I can not.

Here is the finish procedure



USE mydbr

$$

DROP PROCEDURE IF EXISTS sp_DBR_JiraAPI_post

$$

CREATE PROCEDURE `sp_DBR_JiraAPI_post`()

begin

  select * from mydbr.jira_tmp;

end

$$

All I see when I run the main report is a box with the file import status:
File import, total number of rows: 1, errors: 0.

If I perform a select statement, maybe select 'test123';, inside the import procedure, the file import status box changes to errors: 1, rownumber 1, error: 'test123'.

Can you see something that i'm doing wrong here? Thanks!

myDBR Team, Key Master

Charles,
move the dbr.import.finish-command before the dbr.import as it is a parameter to the dbr.import-command. Now your import thinks that you do not have any finish procedure.

You should not put additional select-statements into the dbr.import-procedure as there is no-one listening those rows. The import procedure should just handle the incoming data.

Also, the select in main process will not work as the main procedure is executed in different SQL connection, simultaneuosly with the main query when using 'now' option. If you do not use the 'now'-option, the main report gets executed before user clicks the import-button hence would be no data to be shown.

--
myDBR Team
Charles, Member

Thank you very much for your help. I thought I would see a "real" myDBR-table with the sorting of the columns and the filters and all that. Now I only see a finish column in the "import status box".

Is there a way to show the data as any other report would display it, instead of up in the "import status box"?
myDBR Team, Key Master

Just import data into a real table and use real myDBR reports to show the data. Then you have every myDBR feature in yout disposal. If you wish to delete the data after viewing you can do that in the report.

The import finish procedure is usually used to batch process the imported rows, so it is not a "real myDBR report" even though it can show simple result sets.

--
myDBR Team
Charles, Member

Allright. I have to give up this temporary table idea. I figured it would be nice since we're probably going to import different data from the API from time to time, depending on the report.

Maybe it's something you can look into in the future.

Thanks again.
Charles, Member

Ok - so a quick follow up. Since calling the import is run simultaneuosly with the main query, I can't see the latest imported data. I'm always one insert behind, if you know what I mean. The first time i ran the report, I got nothing displayed - even though I can see it inserted a row into the table.

How would you solve this in the best way?
Charles, Member

Any thoughts on my follow up regarding the timing of the two queries?
myDBR Team, Key Master

Sorry about missing your followup question. Just put the processing into the dbr.import.finish procedure which is executed after the import.

The main query is actually fully run prior any import is done (database processes the stored procedure call and writes the output into the buffer where myDBR reads it from).

So, myDBR executes the main stored procedure DB processes it and puts the output to buffer. myDBR reads is and once it receives the import command from the buffer it does the import. The finish command is run after the import.

--
myDBR Team

Reply

You must log in to post.