This is an example of a minimum viable design for sending a text message from DeltaV. It can made more complex to suit needs. By understanding each piece of this, it's easy to add the additional details as you go along.
1.) DeltaV Setup
2.) Outside Provider Setup
3.) Integration
To get deltaV to send a text message, we need to determine what we should be sending a text message based on. A very straightforward appropach is to use the alarm and event database as a driver for sending a HTTP-GET request with a string and some routing information to a server that can send the text message.
From Chat GPT, here is a strategy push data out of DeltaV alarm and event database for use by the text message / alerting server:
Step 1: Create a Trigger to Check New Records
This trigger will detect newly inserted records and check for the text string [TEXT]. When the condition is met, it will call a stored procedure to perform the HTTP GET request.
```
CREATE TRIGGER trg_CheckNewRecord
ON YourTable
AFTER INSERT
AS
BEGIN
DECLARE @TextColumn NVARCHAR(MAX)
DECLARE @ID INT -- assuming there's an ID column or another primary key
-- Get the inserted row's ID and text column
SELECT @ID = ID, @TextColumn = TextColumn
FROM INSERTED
-- Check if the text column contains '[TEXT]'
IF CHARINDEX('[TEXT]', @TextColumn) > 0
BEGIN
-- Call the stored procedure to send the HTTP GET request
EXEC dbo.SendHttpRequest @TextColumn
END
END
```
Replace YourTable with your table name, and TextColumn with the actual column name you want to check.
Step 2: Create a CLR Stored Procedure to Send HTTP GET Request
SQL Server 2014 Standard Edition doesn’t natively support making HTTP requests directly within T-SQL. However, you can use a CLR procedure to make the request from SQL Server.
Enable CLR Integration: Run the following to enable CLR integration in SQL Server:
```
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Create the CLR Function: You’ll need to use Visual Studio or a similar tool to create a CLR function in C# to make the HTTP request. Here’s an outline of what the C# code would look like:
csharp
Copy code
using System;
using System.Data.SqlTypes;
using System.Net.Http;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendHttpRequest(SqlString textToSend)
{
using (var client = new HttpClient())
{
// Replace with the appropriate URL
var url =
quot;http://your-flask-server/endpoint?text={textToSend}";
var response = client.GetAsync(url).Result;
// Optional: Handle response if necessary
}
}
}
```
Deploy the CLR Procedure to SQL Server: After building the CLR DLL, you’ll need to deploy it to SQL Server.
```
CREATE ASSEMBLY [HttpRequestAssembly]
FROM 'path_to_your_compiled_dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
CREATE PROCEDURE dbo.SendHttpRequest
@textToSend NVARCHAR(MAX)
AS EXTERNAL NAME [HttpRequestAssembly].[StoredProcedures].[SendHttpRequest];
```
Execute the Trigger: Now, when a new record is added to YourTable containing [TEXT], the trigger will fire, calling the SendHttpRequest stored procedure and sending the HTTP GET request to your Flask server.
This setup should accomplish your goal, but be cautious: implementing HTTP requests within SQL triggers can introduce performance overhead.
END of chat gpt response.
Next, we need a provider.
[[Twilio Messaging Setup]]
After we have a provider:
[[Python Flask Endpoint to text messaging interface]]