Microsoft SQL Server Integration

Connecting SQL Server to Retool

1. Get Database Connection Details & Whitelist Retool IP Address

You can get your database connection details from whoever set up the database. You'll also need to get them to whitelist the Retool IP address.

2. Add to Retool

Create a new resource in Retool, and select "Microsoft SQL" as the type.

Enter your database connection details.

3. Create Queries

You can now select your newly-created SQL Server resource from the Resource dropdown when creating queries in your Retool apps. You can toggle between SQL mode for raw SQL statements or the GUI mode for structured writes.

You can display the results of SQL Server queries as with any other query in Retool:

Displaying images in Retool with MSSQL

Images in MSSQL are stored as a varbinary. To display them in Retool, you'll need to convert them to Base64 first. Here's an example of a stored procedure from one of our community members that can do the trick:

CREATE PROCEDURE [dbo].[spImageData_Put]
(
–
@Base64String as varchar(MAX),
@TableID as integer
)
AS
BEGIN
SET NOCOUNT ON
update [dbo].[testtable] set
base64imagestring = @Base64String,
imagefield = CAST(N’’ AS xml).value(‘xs:base64Binary(sql:variable("@Base64String"))’, ‘varbinary(max)’)
where TestTableID = @TableID
END
GO

CREATE function [dbo].[fnConvertBinaryToBase64] (@BinaryImage varbinary(max))
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @result varchar(MAX)
set @result = cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryImage"))', 'varchar(max)')
RETURN @result;
END
GO

Did this page help you?