Or: “How to call a web server from a Microsoft SQL Server Stored Procedure”.
Customer has got a VoIP software PBX (Swyx). It logs incoming calls (the CDR) in a MS SQL Server database. The CDR structure is straightforward: a single table where each row is a call, indexed by CallId (transferred calls get, eventually, a new row and a “child CallId”).
I needed to process the CDR within these specs/restrictions:
- Each row has to be processed as soon as it is INSERTed
- Rows must be filtered (depending on the called number)
- Filtered rows must be “mirrored” to a MySQL DB
- MS SQL machine is heavily loaded and mission critical; the row-copy mechanism must be light and fast
The first and second specs imply the use of triggers/stored procedures.
I originally thought that the “DB link”-kind of functionality could be achieved natively on MS SQL. In theory it can, via Linked Servers (bound to ODBC Data Sources). There’s a catch though: you can SELECT stuff on linked servers at will, but as soon as you try to INSERT, you’ll hit error 7391. MS SQL, can’t really blame it, would like to be able to rollback any change made, even on the linked MySQL. It needs to start a (implicit, distributed) transaction on MySQL, but that’s not supported and the write fails. This workaround (forcibly switch off implicit transactions) didn’t work for me. Apparently, the Oracle OLEDB Provider is able to ignore/disable distributing transactions when the parameter DistribTX=0 is in the provider string. MySQL’s ODBC driver doesn’t provide a similar toggle.
The easiest way to push data “out” of MS SQL is (arguably) through HTTP. The DB GETs a full URL, passing key/value parameters to a Web Service that outputs to MySQL.
On with the code, starting with the “Web Service”. What follow is a mere Perl script, useful for testing. Depending on the expected load, you may want to use a proper application server, providing MySQL DB connection pooling. What you should really do, is serve the script through HTTPS and password protect it. Without SSL, a malicious user could sniff the cleartext requests sent by the source DB, forge similar ones and litter/DOS the MySQL instance. Of course, the Web Service could output to just any supported DB, not only to MySQL.
#!/usr/bin/perl
use DBI
;
use CGI
;
use strict
;
my $DEBUG = 0;
my @FIELDS = qw(
CallId
OriginationNumber
CalledNumber
DestinationNumber
StartTime
ScriptConnectTime
DeliveredTime
ConnectTime
TransferTime
EndTime
DisconnectReason
TransferredToCallId
);
my $q = new CGI
;
print $q->header(-type
=> 'text/plain', -charset
=> 'ISO-8859-1', -expires
=> '-1d');
# checks
my $checkresult = 1;
my $checkmessage = '';
sub setcheck
($$$$) {
my ($rrc, $rc, $rrs, $rs) = @_;
$$rrc = $rc;
$$rrs = $rs;
}
sub isnumber
{ return 1 if $_[0] =~ /^[0-9]*$/i; return 0; }
sub issane
{ return 1 if $_[0] =~ /^[a-z0-9%:\- ]*$/i; return 0; }
setcheck
(\$checkresult,0,\$checkmessage,'NULL CallId') if $checkresult and not $q->param('CallId');
setcheck
(\$checkresult,0,\$checkmessage,'CallId must be a number') if $checkresult and not isnumber
($q->param('CallId'));
foreach (@FIELDS) {
setcheck
(\$checkresult,0,\$checkmessage,"$_ value contains invalid characters")
if $checkresult and not issane
($q->param($_));
}
if ($checkresult) {
my $dbh = DBI
->connect('DBI:mysql:database=dbname','dbuser','password') or ((print "KO: Error $DBI::err - $DBI::errstr\n"), exit);
my $values = join ',', ( map { $dbh->quote( $q->param($_) ? $q->param($_) : '') } @FIELDS );
my $sth = $dbh->prepare("INSERT INTO callslog VALUES ($values)") or ((print "KO: Error $DBI::err - $DBI::errstr\n"), exit);
$sth->execute or ((print "KO: Error $DBI::err - $DBI::errstr\n"), exit);
if ($DEBUG) {
print $_.': '.$q->param($_)."\n" for @FIELDS;
}
print "OK\n";
} else {
print "KO: $checkmessage\n";
}
exit; Next, the trigger code. It acts after each INSERT on the IpPbxCDR table. If a called number ends with the given digits, calls the Stored Procedure spLogCall, passing it the fields we’re interested in. I use the (commented) raiserror call for debugging purposes.
USE [ippbxlog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_ProcessCall]
ON [dbo].[IpPbxCDR]
AFTER INSERT
AS
BEGIN
DECLARE
@RightMatch nvarchar (10),
@CallId INT,
@OriginationNumber nvarchar(50),
@CalledNumber nvarchar(50),
@DestinationNumber nvarchar(50),
@StartTime datetime,
@ScriptConnectTime datetime,
@DeliveredTime datetime,
@ConnectTime datetime,
@TransferTime datetime,
@EndTime datetime,
@DisconnectReason nvarchar(50),
@TransferredToCallId INT
SET @RightMatch = '12345678'
SELECT
@CallId = CallId,
@OriginationNumber = OriginationNumber,
@CalledNumber = CalledNumber,
@DestinationNumber = DestinationNumber,
@StartTime = StartTime,
@ScriptConnectTime = ScriptConnectTime,
@DeliveredTime = DeliveredTime,
@ConnectTime = ConnectTime,
@TransferTime = TransferTime,
@EndTime = EndTime,
@DisconnectReason = DisconnectReason,
@TransferredToCallId = TransferredToCallId
FROM INSERTED
IF (RIGHT(@DestinationNumber,LEN(@RightMatch)) = @RightMatch) OR (RIGHT(@CalledNumber,LEN(@RightMatch)) = @RightMatch)
BEGIN
--raiserror('%s',16,1, @DestinationNumber)
EXEC spLogCall
@CallId,
@OriginationNumber,
@CalledNumber,
@DestinationNumber,
@StartTime,
@ScriptConnectTime,
@DeliveredTime,
@ConnectTime,
@TransferTime,
@EndTime,
@DisconnectReason,
@TransferredToCallId
END
END
Lastly, the Web Service contacting Stored Procedure. I use sp_OACreate to create an OLE object of class MSXML2.ServerXMLHTTP passing it the contructed GET URL (address + parameters). Depending on MS SQL’s version, you may have to explicitly enable in-database OLE automation, this way:
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go
Timeouts for various operations are set to reasonably low values, we don’t want the DB to “block” for too long. And again: use HTTPS. Get your certificates right (on MS SQL’s server, install the root certificate for the CA who issued the cert you’re using on the web/application server) and use HTTPS.
USE [ippbxlog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spLogCall]
@CallId INT,
@OriginationNumber nvarchar(50),
@CalledNumber nvarchar(50),
@DestinationNumber nvarchar(50),
@StartTime datetime,
@ScriptConnectTime datetime,
@DeliveredTime datetime,
@ConnectTime datetime,
@TransferTime datetime,
@EndTime datetime,
@DisconnectReason nvarchar(50),
@TransferredToCallId INT
AS
BEGIN
DECLARE
@Object INT,
@hr INT,
@openparams nvarchar(2048),
@responsetext VARCHAR(8000);
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT
IF @hr = 0
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
SET @openparams = 'open("GET", "http://10.1.1.123/ws/CDR.pl?' +
'CallId=' + CAST(@CallId AS VARCHAR) + '&' +
'OriginationNumber=' + CAST(@OriginationNumber AS VARCHAR) + '&' +
'CalledNumber=' + CAST(@CalledNumber AS VARCHAR) + '&' +
'DestinationNumber=' + CAST(@DestinationNumber AS VARCHAR) + '&' +
'StartTime=' + CONVERT(VARCHAR, @StartTime, 120) + '&' +
'ScriptConnectTime=' + CONVERT(VARCHAR, @ScriptConnectTime, 120) + '&' +
'DeliveredTime=' + CONVERT(VARCHAR, @DeliveredTime, 120) + '&' +
'ConnectTime=' + CONVERT(VARCHAR, @ConnectTime, 120) + '&' +
'TransferTime=' + CONVERT(VARCHAR, @TransferTime, 120) + '&' +
'EndTime=' + CONVERT(VARCHAR, @EndTime, 120) + '&' +
'DisconnectReason=' + CAST(@DisconnectReason AS VARCHAR) + '&' +
'TransferredToCallId=' + CAST(@TransferredToCallId AS VARCHAR) +
'", False)'
EXEC @hr = sp_OAMethod @Object, 'setTimeouts(3000,3000,3000,3000)'
EXEC @hr = sp_OAMethod @Object, @openparams
EXEC @hr = sp_OAMethod @Object, 'Send'
EXEC @hr = sp_OAGetProperty @Object, 'responseText', @responseText OUT
END
END
That’s it, the method performs and scales quite well. I think I’ll find other uses for it soon…
