Announcement

Collapse
No announcement yet.

Changing Databases dinamically (Multi-company system)

Collapse
This is a sticky topic.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Changing Databases dinamically (Multi-company system)

    If you have a project where you want to change the database in accordance with the logged user, first you should create the database connections in the project. So you must use the "sc_change_connection"(http://downloads.scriptcase.net/down...nge_connection) macro to swap the connection.

    for example:

    databases
    dbcompany_x
    dbcompany_y
    dbcompany_z
    dbdefault contain the user and security tables

    Create these connections in the project.


    Now in the login form we must use the onValidate event to get the company of the user.
    If the login form has the fields: "user", "password" and "company" (select field).
    So we can use:

    $i = {company};
    switch ($i) {
    case 'company_x':
    sc_change_connection('dbdefault','dbcompany_x');
    break;
    case 'company_y':
    sc_change_connection('dbdefault','dbcompany_y);
    break;
    case 'company_z':
    sc_change_connection('dbdefault','dbcompany_z');
    break;
    }


    If you don't have the "company" field in the login form, so you should use "sc_lookup"(http://downloads.scriptcase.net/down....htm#sc_lookup) to get the company of that user.



    Vítor Jamil

  • #2
    Re: Changing Databases dinamically (Multi-company system)

    Very valuable info.

    I wonder if it is possible to read the database connection and database name from a table in the database when logging in.
    What I mean : instead of coding the connection string and database name in de case statement to lookup the info in the users database ?

    Once the info availabe, then switch to the customer his own database.


    ps: I am new to scriptcase (I have been testing 4 other products but sofar this one seems the best). Working with the trial version for 3 days now.

    Thanks,

    R. Thijs

    Comment


    • #3
      Re: Changing Databases dinamically (Multi-company system)

      Hi,

      Yes, you can use sc_lookup macro to get the databases name.

      http://downloads.scriptcase.net/down....htm#sc_lookup

      Regards,

      Vítor Jamil

      Comment


      • #4
        Re: Changing Databases dinamically (Multi-company system)

        That is great information and a great how to as this is something I will need to do. Thanks! :-)

        best -

        David

        Comment


        • #5
          Re: Changing Databases dinamically (Multi-company system)

          Hi Vitor,

          Assuming that I create a project based on Oracle db with 2 schemas, for example

          Databases
          ORCL
          Schemas
          dbsecurity \\ contains users, roles, privileges and others
          dbdefault \\ contains the default schema with tables as orders head, orders details, items, accounts, etc.

          Now, I can have in ORCL more than one schema, structured precisely to dbdefault, and created or deleted dinamically; in other words these schemas are not declared in Scriptcase project.

          With sc_lookup, I can read from dbsecurity the schema associated to the user (if the field is empty dbdefault is used); but, how can force the applications to use this schema ?

          This is a very important question.

          Thank You and Best Regards

          Gaetano

          Thank You and Best Regards
          Gaetano

          Comment


          • #6
            Re: Changing Databases dinamically (Multi-company system)

            Vitor,
            The link seems invalid,

            Can we get the name of the current Database connection using sc_lookup..? How..? please advise


            I want change the Database connection in the Login screen and accross all the application in the project should refer the changed connection.. Is it possible..

            As per the below macro, the documentation says Old and new connection..! what is Old connection..?
            sc_change_connection"old_connection", "new_connection"

            Please clarify

            Regards
            Dhana

            Comment


            • #7
              Re: Changing Databases dinamically (Multi-company system)

              Hi,

              [quote author=dhanam4 link=topic=1419.msg6272#msg6272 date=1286594431]
              Vitor,
              The link seems invalid,

              Can we get the name of the current Database connection using sc_lookup..? How..? please advise


              I want change the Database connection in the Login screen and accross all the application in the project should refer the changed connection.. Is it possible..

              As per the below macro, the documentation says Old and new connection..! what is Old connection..?
              sc_change_connection"old_connection", "new_connection"

              Please clarify

              Regards
              Dhana

              [/quote]

              Here is the new link to sc_lookup macro: http://downloads.scriptcase.net/down....htm#sc_lookup

              If you don't specify any connection in the sc_lookup, it gets the connection used in the application by default.

              The "old connection" is the connection that are being used at that moment. The "new connection" is the connection that you will change to.


              [quote author=nicosia link=topic=1419.msg6262#msg6262 date=1286461953]
              Hi Vitor,

              Assuming that I create a project based on Oracle db with 2 schemas, for example

              Databases
              ORCL
              Schemas
              dbsecurity contains users, roles, privileges and others
              dbdefault contains the default schema with tables as orders head, orders details, items, accounts, etc.

              Now, I can have in ORCL more than one schema, structured precisely to dbdefault, and created or deleted dinamically; in other words these schemas are not declared in Scriptcase project.

              With sc_lookup, I can read from dbsecurity the schema associated to the user (if the field is empty dbdefault is used); but, how can force the applications to use this schema ?

              This is a very important question.

              Thank You and Best Regards

              Gaetano


              [/quote]

              If you want to change a scheme dinamically you can use the option "Variable for table" in the SQL settings of the applications and use a global variable to define the scheme.

              Regards,
              Vítor

              Comment


              • #8
                Re: Changing Databases dinamically (Multi-company system)

                If you want to change a scheme dinamically you can use the option "Variable for table" in the SQL settings of the applications and use a global variable to define the scheme.
                Thank You

                Gaetano
                Thank You and Best Regards
                Gaetano

                Comment


                • #9
                  Re: Changing Databases dinamically (Multi-company system)

                  Vitor,
                  Thanks for your info..! Can i get sample for usage of Variable for Table and Vairable for Field..!

                  Regards
                  Dhana

                  Comment


                  • #10
                    How did make query with 2 different database and 2 different host ?

                    Comment

                    Working...
                    X