4 Replies Latest reply on Feb 10, 2014 11:59 PM by George Lester

    Running SQL queries vs using the EPDM API

    George Lester

      I'm writing an add-in for the EPDM client, and I've noticed that it can be quite difficult to get the information you're after via the API. Looking at the vault database, the information is plain as day, and I could get what I want through a simple SQL query.

       

      Correct me if I'm wrong, but EPDM is structured like this:

      Client -> Archive Server -> SQL Server.

      meaning that the client does not directly access the vault's SQL Server database.

       

      From browsing the forums, it seems that the solution involves storing SQL Server login credentials in a vault dictionary and then connecting the client straight to the database.

       

      Anyway, I have a few questions:

      • Is this considered best practice? Or is it preferred that developers try and use the API to retrieve information?
      • Is it reasonable to expect that IT departments will expose the SQL Server database to EPDM clients?
      • Is there a hacky way to run SQL against the vault database via the archive server instead of the client connecting straight to the database?

       

      Many thanks,

      George

        • Re: Running SQL queries vs using the EPDM API
          Lee CS Young

          For your questions;

           

          • Unless you really know what you're doing with the query, stick with the API. The reason being is that configurations and latest versions can play havok on your queries. That's not always the case, of course. If you must connect to the db, I would suggest that you create a read-only user and store those credentials in a vault dictionary. (Don't use 'sa'!)
          • If you're attached to the vault, you should be able to see the db. However, while there are a few instances where you may not be able to retrieve information via the API, it's rare.
          • Yes, but it's total overkill and not required. (You could use a web service, middleware, etc.)
          • Re: Running SQL queries vs using the EPDM API
            Charley Saint

            My take on it:

             

            • I know the db very well, so I pretty much always pull info from the database, I just never SET anything directly to the database. The API is nicer though if you can use it because it imposes things like folder and state permissions that you are almost certainly not going to include in your queries.
            • Absolutely, if the client can't talk to the SQL server it doesn't work. The SQL traffic doesn't flow through the Archive Server it goes straight to the client, you can use wireshark or procmon to see for yourself, or run a profiler trace with the hostname column enabled
            • I don't think you should worry about this, all clients can talk to the SQL server
            • Re: Running SQL queries vs using the EPDM API
              Tim Webb

              API - cleaner and less intrusive into your SQL server.

               

              Less risky from a business management perspective and far less likely to require elevated permissions from IT depending on the size of the company you work for.

               

              Tim

              • Re: Running SQL queries vs using the EPDM API
                George Lester

                Thank you all for your responses, they are very helpful! I will prefer to use the API, provided it's simple enough to get what I'm after.