Clovertech

Access database using connection definition

› Clovertech Forums › Cloverleaf › Access database using connection definition

  • Creator
    Topic
  • March 17, 2025 at 11:52 am #121874
    RICK L. PRITCHETT
    Participant

      Hi everyone,

      Is there a way to use a DB connection defined in the “Site preferences” under the “Database Connections”  tab to connect to a DB?

    • Creator
      Topic
    Viewing 2 reply threads
    • Author
      Replies
      • March 17, 2025 at 1:17 pm #121875
        Keith McLeod
        Participant

          Have you looked in Tables and used the Database Options?

          • This reply was modified 2 weeks, 4 days ago by Keith McLeod.
          • March 17, 2025 at 1:59 pm #121877
            RICK L. PRITCHETT
            Participant

              I wanted to use a tcl script to query my external DB and return the results to compare against a lookup table. I know we can do that but wanted not to use the pw in the script.

          • March 17, 2025 at 2:13 pm #121878
            Jim Kosloskey
            Participant

              I suppose you could read the dbconfiguration.ini file in the site directory. It looks like everything you might need is there.

              But how about using the source DB as the lookup and then you will know you have the correct information? I would suggest using a Stored Procedure (written by the owner of the data ideally). I posted a white paper I produced about using a DB as a lookup via a Stored Procedure. There is even a sample SP therein.

              email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

              • March 17, 2025 at 2:21 pm #121879
                RICK L. PRITCHETT
                Participant

                  Hi  Jim,

                  Reading the file for information such as the password and username might work. I see the PW is encrypted in the file. Is there a way to decrypt it, or can it be used as is?

              • March 17, 2025 at 2:30 pm #121880
                Jim Kosloskey
                Participant

                  It all depends on what the DB requires for a Password. If you knew the encryption techniques used I suppose you could try to decrypt it.

                  If you are determined to do a compare and the .ini file does not work, maybe using the DB Protocol to extract the DB information then some Tcl to do a compare to the Table might work.

                  email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

                  • March 17, 2025 at 2:49 pm #121881
                    RICK L. PRITCHETT
                    Participant

                      I know a DB table lookup would normally return one value.  Could a db table lookup be used to return multiple lines if needed

                    • March 17, 2025 at 3:00 pm #121882
                      Jim Kosloskey
                      Participant

                        I was referring to the DB Protocol not DB Lookup. So, you would use the DB Protocol (with either SQL Statements or Stored Procedure – my preference is SP) to extract the elements of interest from the DB; then using Tcl (or perhaps an Xlate) you would do a lookup of each returned element from the Lookup Table doing whatever you want when there is no match or if there is a match.

                        email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

                      • March 17, 2025 at 3:14 pm #121883
                        RICK L. PRITCHETT
                        Participant

                          Can the db lookup handle multiple values returned?

                        • March 17, 2025 at 3:36 pm #121884
                          Jim Kosloskey
                          Participant

                            If you do the Tcl lookup command for DB Lookup table and the proper SQL or Stored Procedure code is issued then you can get multiple values returned. Also, if the Lookup has the proper SQL Statements or Stored Procedure you can get multiple values returned in an Xlate.

                            The values are returned as a list and then can be ITERATEd over (Xlate) but depending on the DB, the list could be quite large.

                            email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

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