System administrators, DBAs, and application developers all know about the need for data integration. That’s when you have to pull data from one database, application, or web service to another. Fortunately, this task is a lot less challenging than it used to be because APIs (application programming interfaces) are so popular today. APIs provide a standard way to share the data and services of one system with another, even if the two systems are otherwise incompatible.
The REST API protocol in particular is frequently used for data integration needs. It’s easy to create API calls using RESTful principles and send data back and forth in JSON, a data format similar to XML but more versatile. And when it comes to storing data, Microsoft SQL Server has been widely adopted by enterprises everywhere.
SQL Server stored procedures make it easy to run code on the database when needed. Developers with data integration needs have worked out many ways to do data integration with REST API code and SQL Server stored procedures. Here’s a look at some of the best REST API calls for data integration.
Here's the key things to know about SQL Server Stored Procedures:
Stored procedures are like small programs you can run on a SQL Server database. According to Microsoft, stored procedures can:
Like other small programming languages, stored procedures are perfect for running bits of code on a regular basis. Repetitive and regularly scheduled tasks are handled easily by stored procedures. They’re also handled more efficiently, as each stored procedure is compiled the first time it’s run. This means they run fast and use less overhead on the server.
Most data integration tasks will need to be run more than once. And since you can use code in stored procedures, REST API calls are a perfect fit for stored procedures. Before you get started though, you need to properly configure SQL Server to make HTTP requests from within stored procedures. That’s because RESTful APIs are built upon the HTTP commands that power web services.
To enable HTTP requests, an administrator will need to enable OLE Automation Procedures on the SQL Server instance. An administrator with the appropriate permissions needs to run these commands:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GONow you can make HTTP requests from within stored procedures, enabling you to connect to REST API endpoints.
Once OLE Automation Procedures have been enabled, SQL Server offers a few predefined procedures that can be used for making API calls. First up is the sp_OACreate method.
sp_OACreate is a simple method for creating an instance of an OLE object. The newly created object is destroyed after all SQL statements have been run. In SQL Server terms, this is known as the completion of the Transact-SQL batch.
The format for sp_OACreate looks like this:
sp_OACreate { progid | clsid } , objecttoken OUTPUT [ , context ]
The variable OUTPUT is the returned object. It will identify the newly created OLE object and be used to call other OLE Automation stored procedures.
sp_OAMethod is used to call a method of an OLE object. Its format follows this convention:
sp_OAMethod objecttoken , methodname
[ , returnvalue OUTPUT ]
[ , [ @parametername = ] parameter [ OUTPUT ] [ ...n ] ]Here’s what each part of this code means:
Each OLE object is destroyed at the end of a Transact-SQL batch. The sp_OADestroy procedure allows you to destroy the object at a different point. Here’s how to call this procedure:
sp_OADestroy objecttokenobjecttoken is, of course, the OLE object created earlier by sp_OACreate.
Now that you’re familiar with the OLE predefined procedures, you can put them to use by calling a REST API endpoint in a stored procedure. Here’s an example using the HTTP GET method for retrieving customer data from an API endpoint:
DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/customers/getcustomers';
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
@URL,
'False'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF((Select @ResponseText) <> '')
BEGIN
DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
SELECT *
FROM OPENJSON(@json)
WITH (
customerName NVARCHAR(30) '$.customerName',
Title NVARCHAR(50) '$.title',
Address NVARCHAR(50) '$.address',
City NVARCHAR(50) '$.city',
Region NVARCHAR(50) '$.region',
PostalCode NVARCHAR(50) '$.postalCode',
Country NVARCHAR(50) '$.country',
Phone NVARCHAR(50) '$Phone'
);
END
ELSE
BEGIN
DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
Print @ErroMsg;
END
Exec sp_OADestroy @ObjectThis stored procedure code does the following:
Now that you’ve created a stored procedure to retrieve data via a REST API, the next part of this tutorial is how to do the same with sending data to an API endpoint.
Sending data to an API endpoint using the POST HTTP method is similar to doing the reverse with the GET method. The main difference is that you can issue SQL SELECT statements to retrieve the data to send, or you can include static JSON in your stored procedure. This example uses static JSON to create an OLE object:
DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/customers/updatecustomer';
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) =
'{
"customerId": 1,
"firstName": "John"
"lastName": "Davis",
"title": "Store Manager",
"address": "123 Main Street",
"city": "St. Louis",
"region": "MO",
"postalCode": "63101",
"country": "USA",
"homePhone": "(555) 555-8746"
}'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post',
@URL,
'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'send', null, @body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF CHARINDEX('false',(SELECT @ResponseText)) > 0
BEGIN
SELECT @ResponseText As 'Message'
END
ELSE
BEGIN
SELECT @ResponseText As 'customer Details'
END
EXEC sp_OADestroy @ObjectAnd that’s how you use OLE objects—and their support for HTTP requests—to utilize REST APIs in stored procedures.
For simple data transfer needs, like updating reporting dashboards with data from SQL Server tables, the OLE automation procedures work well. The SQL Server stored procedure language has support for functions that you can use for other API functions, such as parameters, authentication with an API key or OAuth, or advanced HTTP headers.
However, more complex tasks might call for the use of a more advanced scripting language than the stored procedures language. You might consider Python or other languages that have good support for working with Javascript/JSON and SQL server schemas like Java.
Java has strong integration with SQL Server, thanks to the many features of the JDBC driver. On the other hand, Java has a high overhead in terms of system resources—and the time it takes to learn the connection string format of JDBC.
Migrating legacy data integration processes to REST APIs offers a modern approach to data exchange, enhancing scalability, real-time access, and flexibility. Traditional methods like flat file transfers and ETL (Extract, Transform, Load) processes often rely on batch operations, which can introduce latency and complexity in data pipelines. By transitioning to REST API-based integration, you can achieve more efficient and responsive data handling within your SQL Server environment.
1. Assess Current Integration Workflows:
2. Design RESTful Endpoints:
3. Modify SQL Server Stored Procedures:
sp_OACreate, sp_OAMethod) to make HTTP requests, replacing file-based operations with API calls.4. Handle Data Transformation:
5. Automate and Schedule API Calls:
6. Ensure Data Integrity and Security:
7. Test and Validate:
REST API-based integration offers immediate data access, minimizing latency compared to batch processing. It enhances scalability by facilitating seamless integration with cloud services and distributed systems. The consolidation of data integration into API calls simplifies system architecture, reducing maintenance complexity. Additionally, it improves security through modern authentication methods and encrypted communications, surpassing the protection offered by legacy file-based approaches.
An easier and more full-featured way to take care of your SQL Server data integration needs is an API management platform. These comprehensive apps offer several advantages when working with REST APIs and SQL Server.
First, an API management platform is more scalable. Since the platform provides an abstraction layer between SQL Server and web services, the workload on your database is greatly reduced. Management platforms tend to offer more security than opening up your SQL server instance to all the connections required for stored procedures or other hand-coded solutions. And that's just one of many benefits of these platforms.
DreamFactory has excellent data integration capabilities with SQL Server. Generating and documenting a SQL Server API takes less than five minutes. That’s much less than the time it takes to create stored procedures, test them out, and manually document your API. And DreamFactory can handle more than just SQL Server. It’s the complete platform for all your data sources and API management needs. The DreamFactory iPaaS (Internet Platform as a Service) allows you to get up and running right away. You can even get a 14-day free trial to try it out for yourself. Sign up today to see why DreamFactory is the only API management platform you need.