No internet connection
  1. Home
  2. General

Lookup From a Database Connection string builder too restrictive

By @matty
    2022-09-22 19:36:03.909Z

    In TA5 The connection string builder requires that I choose a hard-coded provider. In TA4 this was less restrictive. For example a connection string that works fine in TA4 can not be built in TA5 due to the requirement that I choose the "DB2" drop down option. This prints the parameter as "Provider=DB2" in the connection string however the correct parameter in my organization is IBMDADB2.DB2COPY1

    Failed With Error: Failed to open database using connection: provider=DB2;Provider=IBMDADB2.DB2COPY1;Password=;Persist Security Info=True;User ID=;Data Source=;Location=* Connection parameter 'Provider' is duplicated. Parameter name: Provider

    Perhaps you could allow a 'Custom' option in the drop down that would allow free form typing in the provider section?

    • 7 replies
    1. D
      Daniel Horton @daniel.horton
        2022-10-06 15:35:45.212Z

        Hi Matty,

        The aspect you're seeing of "provider=DB2;" in the error isn't actually used in the connection string. That is just an internal element of ThinkAutomation being presented within the error. You should be able to pick any provider and paste into the Connection String a valid value for your connection and this would super-seed anything taken from the menu options.

        We will check to see if there is any reason this would not work with IBM DB2 and if there is a problem we will resolve it.

        1. S
          In reply tomatty:
          Stephen Parker @stephenparker
            2022-10-07 07:00:36.458Z2022-10-07 07:08:06.739Z

            Hi Matty,

            You wouldnt be able to paste a TA4 OLEDB connection string into TA5. TA4 used OLEDB to connect to different DB's. TA5 uses native connections for the DB Types listed. You can still use OLEDB but you need to select that DB type rather than DB2.

            For your DB2 connection you would select the DB2 Database type and then specify Server, database name, username, password. Or paste a connection string - but this must be a valid DB2 connection string (not an OLEDB connection string).

            If you want to paste an OLEDB connection string from TA4 into TA5 (when using OLEDB as the database type) then you must change the 'Provider=xxx' to 'OLE DB Provider=xxx' in the pasted connection string (we will make a changed to Studio to do this automatically in future).

            Thanks
            Stephen

            1. M@matty
                2022-10-07 20:53:01.500Z

                Thanks @stephenparker I was able to successfully connect to the OLEDB and return a row from the database using the "Test" button. However when the automation runs it stops at this particular action and does not proceed to the next action? As soon as I switch this back to ODBC it works...I need OLEDB to lookup a CLOB field. As mentioned everything works when using the "TEST" button from within the db lookup action when using OLEDB. SEE HERE: https://recordit.co/aHwIknFncj

                OLEDB (it ENDS processing here)

                Whereas with ODBC

              • S
                In reply tomatty:
                Stephen Parker @stephenparker
                  2022-10-08 07:25:05.040Z

                  If it works in the Studio Test button but not when processing messages then this is usually a permissions issue. The Studio is running under the logged in user context. The Message Processor is running as a service under the SYSTEM context (by default). You can configure the Message Processor service to run under a different user (see the Help - Running The Message Processor Service Under A Different User).

                  I will do a test here using OLEDB to check.

                  Thanks

                  1. M@matty
                      2022-10-11 18:53:16.673Z

                      Thanks Stephen, in my case though ODBC works, but OLEDB does not, if the user was not set up correctly wouldn't ODBC fail to connect?

                      Also in the logs when trying with OLEDB it just ends processing at this action instead of printing an error message. See line 69 above it sets the Database parameter and that is it, then it just stops processing.

                      1. In reply tostephenparker:
                        M@matty
                          2022-10-14 00:05:00.250Z

                          Any updates/assistance on this?

                          1. In reply tostephenparker:
                            M@matty
                              2022-10-24 16:37:13.028Z

                              Hi @stephenparker any updates/assistance on this?