KEPServerEX ODBC query with parameter

[nickmalott]

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 - 

[sam_kepware · Accepted solution]

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

[nickmalott]

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: 

• https://youtu.be/udHlrKSm8Rk?list=PLv3EViGH_uVkIWhdkjkf-SPpWnIlO_fmF&t=1168