MS-SQL Integration for UE4

The MS SQL Integration Plugin brings the power of .Net in Unreal Engine. With this plugin you can store and retrieve data and images from your SQL Server. Build your own queries and select and update data to your database directly from Blueprints. No restrictions in query formation. Any query working inside SQL Client , works here, which means you can select and update multiple data at once , use joins and sub-queries, as well as create and drop tables via Blueprints..

If you have worked with SQL, you must be very familiar to Select, Update, Insert and Delete SQL queries, joins and sub-queries. If you know C#.Net, and worked with ADO.Net, you must be knowing how to set Connection properties for your Server, and how to write and execute your SQL Queries within your .NET applications.

But now you can use Blueprints in Unreal Engine 4 to do your job. You are still writing the same queries that you are familiar with, but here you are writing it within Blueprints, which gives you flexibility to store and retrieve data as well as Images from your MySQL Server to your UE4 projects.

Get It Here

Features

Asynchronous Connection

Asynchronously connect your Server via Windows Authentication System as well as SQL Server Authentication System.

Easy Query Formation

Form your own query and pass as string input , directly within your Blueprint or C++ class.

Selection and Insertion of Images

Easily Save and Retrieve Images from your Database

Custom ConnectionString

Create custom connection string format with different protocols and other connection parameters, as required by your server.

Getting Started

To get started, first ensure that the plugin is installed, by visiting the Plugin Window, as shown below:

MSSQLPluginWindow

If you are not familiar with SQL Server at all, you will get plenty of online resources to get you started, and you can definitely test it out by downloading the free version, SQL Server Express. SQL Server Express is an entry level database server having a limitation of 10 GB size, which is perfect from small scale applications.

Working with SQL Server Express

Assuming that you have your database server up and running, either locally or remotely, to get started working with this plugin, you first need to create a database and a table in your server, where you would like to store all your in-game data. If you are unsure regarding how to work with SQL Express, let me quickly walk you through the basic steps.

Setting Up Database Connection in UE4

Before we begin writing our SQL queries and executing them in our database, we first need to set up a connection within UE4. This is a onetime setup, and is similar to how we manually connect to our DB Server using Windows or SQL Server Authentication, as shown in the above steps.

Authentication Modes

To set up a DB Connection, we either need to use Windows Authentication mode or SQL Authentication mode.

  • Windows Authentication mode authenticates users to use the Database server via Windows Login ID and Password.

    Function Name Description
    Get Current User This function returns the current Windows User Name.
    Get Current Domain This function returns the current PC Domain.
    Authenticate User This function takes the current user name, domain and password for your Windows account as input and returns the authentication result as a Boolean variable. Please note that this function will not work if the current Windows user does not require any password to login. So you may want to avoid using this function unless absolutely necessary.

  • SQL Server Authentication mode uses logins that are created and stored in SQL Server and are not based on Windows user accounts. Here you need to explicitly enter both your SQL Server ID and password, while setting up the connection property. To learn about Authentication modes in detail, you can refer here : https://msdn.microsoft.com/en-us/library/ms144284.aspx

SQL Server Connection

Connection to the SQL Server happens asynchronously. To connect to your server, first you need to call the Set Connection Properties function, and then add a custom tick event that you need to listen to at every tick to validate the current status of your connection

  • The function Set Connection Properties takes the necessary parameters that your server is going to need , and request for the connection to be established.

    Inputs Description
    Server This parameter takes your server address.
    DBName This parameter takes the name of your database.
    UserID This parameter takes your login ID.
    Password This parameter takes your login Password.
    Extra Param This parameter takes any extra setting that you might want to add. All individual settings should be seperated by semicolon and passed as a single string.
    Is Trusted If you are using Windows Authentication Mode , you can check Is Trusted, and you do not need to enter your User ID and password in that case. Otherwise, uncheck this paramter.

    SetConnectionProperties

    A connection string typically looks like:

    Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

    So if your connectionstring consists of extra parameters , apart from the usual parameters included in the Set Connection Properties function(Server , DBName , UserID , Password , IsTrusted) , you can enter those extra parameters in the Extra Param input, seperated by a semicolon. For example , if your connectionstring is something like :

    Server=.\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;

    Then you need to enter , in your Extra Param input :

    AttachDbFilename=C:\MyFolder\MyDataFile.mdf;

    It must be noted here that you can obviously enter the extra parameters in other inputs as well, like in your Server input , you can also write like this :

    .\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;

    But it is recommended to use Extra Param input for this purpose, just to avoid confusion.

    The Server property is not limited to any particular SQL Server type and can accept different string formats, as shown below:

    Server Type String Format
    Standard Security , TCP/IP hostname
    tcp:hostname
    tcp:hostname, portNumber
    Named Pipes np:hostname , np:\\hostname\pipe\pipeName

    To learn about various types of Connection String and forming the one that best suits your requirement, you can refer here : https://www.connectionstrings.com/sql-server/

  • Once your connection has been established, you need to periodically listen to the function Check Connection State to find out the current state of your connection. An useful way to do that in Blueprints is using Gates, which is called in Tick function , but is opened after the funciton Set Connection Properties is called.

    Connection State Description
    Broken The connection to the data source is broken.
    Closed The connection is closed.
    Connecting The connection object is connecting to the data source.
    Executing The connection object is executing a command.
    Fetching The connection object is retrieving data.
    Open The connection is open.

    CheckConnectionState

    As long as the current state shows Connecting, you can execute other operations, and let the connection happen in the background. When you get current state as Open, this means connection is successfully established , and now you can proceed with executing queries in your database.

Writing SQL queries within UE4 Blueprints

Once the connection property has been set, you can now start forming your SQL queries. Forming queries can be categorized in two types, queries that modify data to the database (INSERT, UPDATE, DELETE), or the queries that fetch data from the database (SELECT). We will discuss these two categories one by one.

Update Queries

To asynchronously modify data in our table , we will use the function Update data from Query Async.

UpdateQueryAsync

After calling the Update function , you need to periodically listen to the function Check Query Execution State to find out the current state of your function. Here also you can use Gate within the Tick function, which should be opened after the function Update data from Query Async is called.

UpdateQueryExecution

Query Execution State Description
Executing The query is still being executed.
Success The query has been successfully executed.
Failed The query execution has failed. This generates error message as string output in the Check Query Execution State function.

If you would like to update data synchronously, you can call the function Update data from Query which takes a string input where we have to enter our query and executes the query in the database.

UpdateQuery

Select Queries

  • To select data asynchronously, you need to first call the function Select Data from Query Async which takes a string input where we have to enter our query and fetches the result from the database.

    MySQLSelectQueryAsync

  • Once you called Select Data from Query Async, you need to periodically listen to the function Check Query Execution State to find out the current state of your query execution.

  • To select the data once the query execution is successful, call the function Get Selected Table.

    MSSQLSelectedQueryAsync1

    Output Name Description
    Result By Column

    This will return an array of structured variable called MSSQLDataTable. This represents a column in the table and contains two properties, ColumnName and ColumnData.

    ColumnName

    ColumnName contains the header name of the column of the current instance of our initial array that we get by executing our select query. If you want to display data of a specific column, you can use this variable to match the column name.

    ColumnData

    ColumnData is an array of string consisting of all the elements of a given column. So you can easily set up a for-loop here to get all the elements of a particular column iteratively.
    Result By Row

    This contains an array of structured variable called MSSQLDataRow. This structure contains an array of string that represents values of an entire row. So the number of elements in this string array equals number of columns, and number of elements in the Result by Row array equals number of rows in the output of the Select query.

Below images display an example of how we can use Result By Row and Result By Column structures to store and display selected data retrieved from the server to our UE4 UI, as part of UMG widget.

ResultByColumn

ResultByRow

If you would like to select data synchronously, you can call the function Select data from Query which takes a string input where we have to enter our query , executes the query in the database. and returns the selected data as Result By Column and Result By Row format.

SelectDatafromQuery

Update Images

To save an image to our table, we first need to browse for an image from our desktop. As of now, you cannot directly save UE4 Textures to your database. Instead, you can directly browse for an image of common types via Blueprints, by calling the function called Browse File.

BrowseFile

This function will bring up a File Browser dialogue. On selecting the image file and clicking OK, the dialogue box will close and the function returns the file path. You may have your separate ways of retrieving image path from your hard drive, so this function is pretty much optional.

Once we have our image file path, we need to fire the function called Update Image from Query that will update the image to the server.

UpdateImagefromQuery

Inputs Description
Query

Here we enter our query. So if we want to insert an image in our Picture column, we need to enter it like this:

INSERT INTO ImageData( Picture) VALUES (@img)

If we want to insert values in other columns, we can do that as well.

INSERT INTO ImageData( ID , Picture) VALUES (0, @img)
Update Parameter

This will contain the variable name which should be replaced by our image. In the query above, we are using the word @img. This is a common way of inserting dynamic data in SQL server, and you must be familiar with it if you have hands on experience in ADO.NET.. So this input will take the same parameter as input, without the @. symbol, to help the program determine what variable is being used to represent image variable. So here we need to enter img., as shown in the picture above.

Image Path

Here we are providing the image file path that we have received from the previous section.

Select Images

To select an image from SQL Server, we simply need to call the function called Select Image from Query and enter our selection query here.

You need to remember that you can select only one image at a time, so make sure your selection query only fetches a single image and nothing else. For example, when your table has a multiple rows, you cannot write Select Picture from ImageData as that will fetch multiple values. Instead, you need to write something like:

Select Picture from ImageData Where ID=3

SelectImageFromQuery

This function returns a Texture2D format, which you can use to display the image in your game.

SelectImageExample

The End

Thank you for your time. I hope you find this plugin useful. For technical difficulties, feedback, suggestions, queries, kindly drop me a mail at sameek4@gmail.com. I would love to hear from you.