1. 

   SteveD, Member

   We are using myDBR to provide web based reports for an existing software solution within the company, as the existing software is fully developed (without any web side unfortunately) it is a lot easier for us to use the existing stored procedures within the database rather than create all new queries. My questions are:

   1. Is it possible for myDBR to only display select fields from a pre existing stored procedure? One of the stored procedure that we are referencing returns 61 fields, of which we may only want to display 20 or so in myDBR.

   2. Is it possible to apply any degree of formatting to a report that calls a pre existing procedure? I know that calls such as select 'dbr.pageview'; will work, but ideally I am looking for more control than that, using commands such as select 'dbr.keepwithnext'; for example?
2. 

   SteveD, Member

   Just discovered the answer to Question 1. The select 'dbr.hidecolumns' command will work perfectly for that, and info on question 2 would be helpful though.
3. 

   myDBR Team, Key Master

   The easiest way to utilize existing procedures is to use them inside myDBR reports via call / exec. If the existing procedures return only one result set per procdure, you will have full control how the data is treated within myDBR. So the answer is yes for both questions. If the existing procedures return more than one result set, myDBR commands will apply to the first one:

   Example (MySQL syntax):

   select 'dbr.hidecolumns', 'extracolumn';
   
   select 'dbr.pageview';
   
   call sp_LegacyProcedure(1, 2);
   
   

   --
   myDBR Team
4. 

   SteveD, Member

   Thanks for that, but do you have an example of how you would use something like dbr.keepwithnext with a result set form a stored procedure, or is that not possible?
5. 

   myDBR Team, Key Master

   That would work similar way. myDBR works with result sets. Whether the result set comes from a called stored procedure or from a direct SQL query is all the same for myDBR. You are free to include calls to other procedures, use functions or any feature your database engine is capable of.

   As an example:

   select 'dbr.keepwithnext';
   
   call sp_LegacyProcedure(1, 2);
   
   select 'dbr.keepwithnext';
   
   call sp_LegacyProcedure2( vParameter );
   
   select col1, col2...
   
   from mytable;
   
   

   Will produce a report where the three result sets are shown side by side.

   --
   myDBR Team
6. 

   SteveD, Member

   I appear to be struggling to get my question across properly, so let me try again.

   Can dbr.keepwithnext (or any of the other formatting commands) be used to format a result set from only one stored procedure, ideally while only calling the procedure once?

   As an example lets say I have a stored procedure that returns a result set consisting of First Name, Surname, Address and I wanted myDBR to format the output along the lines of:

   First Name Surname
   Address

   First Name Surname
   Address

   First Name Surname
   Address

   Can that be done when calling a legacy procedure?
7. 

   myDBR Team, Key Master

   You can apply the dbr.pageview to a stored procedure. How much you can process the columns coming from stored procedure depends on the database you are using. For example with SQL Server you can put the result of a stored procedure into temp table and handle it as your own. In MySQL you would need to edit the procedure itself to use temp table.

   When to utilize the existing legacy procedure and when to copy/rewrite them depends on the case. Unless your procedures needs to be shared with multiple applications and they contain complex logic, it may be better to reuse the existing codebase but not be tied into it.

   --
   myDBR Team

Reply

You must log in to post.