cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

KEPServerEX ODBC query with parameter

nickmalott
5-Regular Member

KEPServerEX ODBC query with parameter

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 - 

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

Kepware's Sam Elsner discusses the features and functionality available in the Advanced Tags Plug-In for KEPServerEX, as well as practical use cases for the various tag types. The presentation includes a live product demonstration and Q&A. Learn more about the KEPServerEX connectivity platform: ...
Kepware's Sam Elsner describes how KEPServerEX's drivers and plug-ins can be used to access databases and build highly-functional automation systems. The presentation includes a live product demonstration and Q&A. Learn more about the KEPServerEX connectivity platform: http://ptc.co/ysUx30o2EdW
2 REPLIES 2

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

Kepware's Sam Elsner discusses the features and functionality available in the Advanced Tags Plug-In for KEPServerEX, as well as practical use cases for the various tag types. The presentation includes a live product demonstration and Q&A. Learn more about the KEPServerEX connectivity platform: ...
Kepware's Sam Elsner describes how KEPServerEX's drivers and plug-ins can be used to access databases and build highly-functional automation systems. The presentation includes a live product demonstration and Q&A. Learn more about the KEPServerEX connectivity platform: http://ptc.co/ysUx30o2EdW
nickmalott
5-Regular Member
(To:sam_kepware)

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: 

Kepware's Sam Elsner describes how KEPServerEX's drivers and plug-ins can be used to access databases and build highly-functional automation systems. The presentation includes a live product demonstration and Q&A. Learn more about the KEPServerEX connectivity platform: http://ptc.co/ysUx30o2EdW
Top Tags