Clovertech

MS SQL query

› Clovertech Forums › Cloverleaf › MS SQL query

  • Creator
    Topic
  • July 10, 2019 at 11:34 am #110508
    Keith McLeod
    Participant

      Running on Cloverleaf 6.1.2.0, On RHEL 6.5.  Need to obtain information from an MS SQL database on another server.  Can someone provide me with the requirements to accomplish this?  Is this a license or a tcl package?  Was able to do this against SQLite db’s locally.  Just looking for some guidance.  Looking to use HL7 data to build query and populate discrete value OBX segments.  Currently exploring my options.

    • Creator
      Topic
    Viewing 2 reply threads
    • Author
      Replies
      • July 10, 2019 at 2:16 pm #110512
        Jim Kosloskey
        Participant

          Keith,

          Perhaps you could use the Cloverleaf Java based DB protocol.  I don’t know how usable or stable it is in 6.1.2 someone else could perhaps comment on that. I don’t think that needs any licensing. However, it was pretty flaky in 6.0 when I tried to use it.

          You can also License the Progress Software Data Direct drivers Infor has as an Add-on (Data Integrator??). That will cost some money. I have used that successfully. It is really fast.

          My general recommendation in either case is to actually use a Stored Procedure (ideally written by whoever owns the data you are querying) leaving most of the Sql work in the Stored Procedure. In my experience this can alleviate a lot of finger pointing (especially during on call issues) as well as the penchant for some DBAs to only want a particular method used to access their DBs and other ‘religious issues. I just have them write the Stored Procedure. We agree on the interchange between the SP and Cloverleaf (what arguments are passed, acknowledgment, error indications, etc.).

          email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

          • July 16, 2019 at 5:39 pm #110730
            JP Trosclair
            Participant

              You can build database lookup tables using the lookup table configurator tool in the IDE. You can specify an input value as a parameter to a query. This works for fairly simple stuff. To get started:

              • Go to Options -> Site Preferences and then the Database Configurations tab in the IDE. You can add a connection here to the SQL server using the JDBC MSSQL driver which is included.
              • After that’s done, open the lookup table configurator and you can build a “Basic” or an “Advanced” database lookup per your needs.
              • From there you can use the dblookup TCL command in your TCL procs to query data.

              We use this setup for querying data from Clarity (Epic) on MS SQL. There was a bug with the keyed lists returned 6.1.2, it was resolved in 6.2.

               

          • July 22, 2019 at 11:48 am #110870
            Devika Agarwal
            Participant

              If the database is unavailable at the time dblookup query, is there a way to handle this within Cloverleaf to prevent the messages from failing? We recently did a test where we took the database offline and the messages using dblookup failed. Ideally, we would like to stop the thread and send alerts to notify user.

            • July 22, 2019 at 10:42 pm #110903
              Charlie Bursell
              Participant

                Perhaps a preproc that issues a very simple and fast query.  If error returned send mail and shutdown.

                FWIW, I never shut down a thread from a script.  It depends on what the process is doing.  The shutdown may timeout and not happen.  You will get really weird results.  I prefer to shutdown the process even if that means that thread in a single process.  Using hcienginestop means the the process, and the thread, will be shutdown even if hung. hcienginestop will eventually kick it to death 🙂

            • Author
              Replies
            Viewing 2 reply threads
            • You must be logged in to reply to this topic.