Stored Procedures, MYDBR, different databases, different hosts QUESTIONS

(2 posts) (2 voices)


  1. I am getting familiar with mydbr and would like to branch out to different data bases and even different hosts to obtain informaiont.

    Since stored procedures are associated with databases, should they be stored with the database which contains the information (I think) or with the mydbr database. In either case, how does one tell the system which database is being requested?
    Is it only through the specification of the database name in the from portion of the select command?
    I am not familiar with extending this syntax to host.dbname.table. Is this possible or do I need to have different mydbr installations for different hosts/databases.

    Sorry for my confusion, I am really new to the whole concept of stored procedures and the things that can be done with them.

    Thanks for the help.

  2. myDBR Team, Key Master

    myDBR uses by default a separate database for reporting objects. This includes the internal tables of myDBR and the created reports (stored procedures). The reason for endorsing the separate database is that with this setup, there is no danger that objects created conflict with the user database objects. Also, in many cases reporting is done on existing (perhaps a 3rd party) database which the vendors tends like to keep to themselves.

    Having said that, there is no reason why myDBR objects could not be installed directly to user database. myDBR works just fine with this setup. myDBR tables and procedures are named with 'mydbr' in them and, by default, the reports start with 'sp_DBR' so the database objects are not likely to conflict.

    Fetching data from different database differs slightly on database servers:

    - On MySQL the syntax is "database.table". In order to make life bit simpler, you can use aliases (like t1 in example) in query to make reference to the tables bit shorter:

    select t1.Name, sum(t2.value)
    from database1.table1 d1, database2.table2 t2
    group by t1.Name;

    In order to select data in MySQL from another server, you can utilize the Federated storage engine. This is an extra option, so you might want to take a look at the documentation first:

    - On MS SQL Server the full table syntax is "server.database.owner.table". Here too, you can use aliases in query. You also often omit the owner-part in table reference:

    select t1.Name, sum(t2.value)
    from server1.database1..table1 d1, server2.database2..table2 t2
    group by t1.Name;

    Hope this helps,
    myDBR Team


You must log in to post.