Skip to main content
N
nickmalott5-Regular Member
5-Regular Member
January 11, 2019
Solved

KEPServerEX ODBC query with parameter

  • January 11, 2019
  • 1 reply
  • 8247 views

Hi - 

 

I'm working on a solution to transfer data between a PLC and MSSQL Database using KEPServerEX. I was able to implement the push (data from PLC being transferred to the MSSQL DB ) using the ODBC client and writing to a stored procedure.

 

However, I'm having issues with pulling the data to KEPServerEX with a parameter (i.e. ID). I would like to call a stored procedure or function with the parameter 'id' to limit the result set to a single row from the SQL Server.

 

I've attempted the data pull on a test database with a stored procedure (returning no results), a function (also returning no results), a query that selects from the function (which returns results, but no parameter), and several other attempts only to reach a dead end.

 

Wondering if anyone has experience with the ODBC client driver that may have found a solution to this. The simplest way would be to have an input tag used in the query and do something like:

  • SELECT * FROM ufn_GetDevices( @id )

 

Thanks in advance - 

    Best answer by sam_kepware

    Hello! I manage Kepware's Applications Engineering team and would be happy to field this question. Please note that you can reach my team directly by emailing us at presales.support@kepware.com.

     

    With regard to your question, unfortunately we cannot use "dynamic" parameters like a tag name variable as "ID" in a Query table access method. As an aside, I have pushed for this feature quite often but so far we have not had enough external customer interest to add the feature.

     

    Anyway, there are a couple of options:

     

    1- Continue to use a Query table access method and hard-code the ID field in each query. NOTE that you can create up to 256 parallel Queries within a single instance of KEPServerEX, and we can support many hundreds more queries if you don't mind then run sequentially.

     

    2- Switch to using a Stored Procedure access method, and you can do the following:

    ---> you would create an Input variable in your Stored Procedure to retrieve an ID field

    ---> you would use Output variables to combine / create the response value(s) that you're looking for; the Return variables (as you might know) are just for success or failure decimal values.

    ---> you might need to use our Advanced Tags plugin in order to automatically place values in Input variables and have the procedure automatically executed.

     

    Here are a couple of webinars where I provide a detailed overview and example of functionality similar to what you're looking for:

     

    Database Interaction:

    https://www.youtube.com/watch?v=udHlrKSm8Rk&list=PLv3EViGH_uVkIWhdkjkf-SPpWnIlO_fmF&index=11&t=182s

     

    Advanced Tags (probably less relevant):

    https://www.youtube.com/watch?v=LtfaViH0W7k&list=PLv3EViGH_uVkIWhdkjkf-SPpWnIlO_fmF&index=16&t=2090s

     

    It might best to discuss this via phone and/or remote support session; this is heavier engineering work that often requires engagement with my team. In other words, what you're looking to do - if I've understood correctly - is possible but it will take a combination of particular configuration of the ODBC Client driver and the use of QuoteAdvanced Tags.

     

    Thanks, and look forward to hearing from you.

     

    Sam

    1 reply

    S
    sam_kepware12-AmethystAnswer
    12-Amethyst
    January 11, 2019

    Hello! I manage Kepware's Applications Engineering team and would be happy to field this question. Please note that you can reach my team directly by emailing us at presales.support@kepware.com.

     

    With regard to your question, unfortunately we cannot use "dynamic" parameters like a tag name variable as "ID" in a Query table access method. As an aside, I have pushed for this feature quite often but so far we have not had enough external customer interest to add the feature.

     

    Anyway, there are a couple of options:

     

    1- Continue to use a Query table access method and hard-code the ID field in each query. NOTE that you can create up to 256 parallel Queries within a single instance of KEPServerEX, and we can support many hundreds more queries if you don't mind then run sequentially.

     

    2- Switch to using a Stored Procedure access method, and you can do the following:

    ---> you would create an Input variable in your Stored Procedure to retrieve an ID field

    ---> you would use Output variables to combine / create the response value(s) that you're looking for; the Return variables (as you might know) are just for success or failure decimal values.

    ---> you might need to use our Advanced Tags plugin in order to automatically place values in Input variables and have the procedure automatically executed.

     

    Here are a couple of webinars where I provide a detailed overview and example of functionality similar to what you're looking for:

     

    Database Interaction:

    https://www.youtube.com/watch?v=udHlrKSm8Rk&list=PLv3EViGH_uVkIWhdkjkf-SPpWnIlO_fmF&index=11&t=182s

     

    Advanced Tags (probably less relevant):

    https://www.youtube.com/watch?v=LtfaViH0W7k&list=PLv3EViGH_uVkIWhdkjkf-SPpWnIlO_fmF&index=16&t=2090s

     

    It might best to discuss this via phone and/or remote support session; this is heavier engineering work that often requires engagement with my team. In other words, what you're looking to do - if I've understood correctly - is possible but it will take a combination of particular configuration of the ODBC Client driver and the use of QuoteAdvanced Tags.

     

    Thanks, and look forward to hearing from you.

     

    Sam

      N
      nickmalott5-Regular MemberAuthor
      5-Regular Member
      January 14, 2019

      Sam - thanks for your assistance. I was able to resolve the problem by defining output parameters within the SQL stored procedures.

       

      The instructional video was helpful (once I found the right part, around 19min in). For future reference: 

        Terms & ConditionsAccessibility statement