ODBC Connect

Shortcuts

  1. Overview
  2. Getting Started
  3. Preparing Project
  4. Preparing Database Table
  5. Preparing Blueprint
  6. Setting Connection String
  7. Getting Database Manager
  8. ExecuteStatement Function
  9. Getting Data From Database
  10. Getting Affected Rows Count
  11. Statement Tag
  12. More Information

Overview

ODBC Connect is a plugin for unreal engine that allows the developer to connect to Microsoft SQL Server through ODBC to execute statements on the Database

Its recommended to check "More Information" on the left before you follow this guide

Getting started

Important: This guide is not meant for absolute beginners, you must have the basics of blueprints in unreal engine and the basics in sql server


Make sure you have Microsoft SQL Server installed and ODBC Setup.

You should have an entry for your database connection in ODBC like the image below with your database name and information

Media/ODBC System DSN.png

In this guide i will be using "Microsoft SQL Server 2014" and "SQL Server Native Client 11.0" Driver

Preparing Database Table

In your Database make a new table and name it "Accounts" with 3 columns

Column name Datatype Allow Nulls
UID bigint FALSE
Username char(15) FALSE
Password char(255) FALSE

Set the UID "Auto increment" and "Primary Key"

AccountsTable

AccountsTable Design

Add 3 entries to your table

AccountsTable Content

Preparing The Project

Before we start using the plugin we need to enable it after we install it from Unreal Engine Marketplace into our engine

To do that open your project and click "Edit" then "Plugins" in the top left corner

Opening plugins window

Once you click it a windows will popup for plugins, in the search area type "ODBC Connect", search for the plugin and enable it then restart the editor

Enabling the plugin

Preparing your Blueprint

Make 3 new variables in your Blueprint

Variable name Datatype
ConnectionString String
DataObject Object
ColumnValues Array of strings

Blueprint class variables

Set Connection String Value

Set your Connection String variable with your connection string, here are some examples

With Windows authentication DRIVER={SQL Server};Server=ServerName;Database=DatabaseName;Trusted_Connection=yes;

With SQL Server authentication {SQL Server};SERVER=ServerName;DSN=DatabaseName;UID=Username;PWD=Password;

Change "ServerName, DatabaseName, Username and Password" to your info

For example mine will look like this {SQL Server};SERVER=Astral\SQLEXPRESS;DSN=Astral;UID=MyUsername;PWD=MyPassword;


Note: I suggest you to stick with SQL Server authentication unless you know what you're doing

Getting Database Manager

In your blueprint right click in an empty place inside the event graph and type "Get DatabaseManager" and hit enter

Get DatabaseManager

DatabaseManager Reference

ExecuteStatement Function

Pull the pin from DatabaseManager and search for ExecuteStatement and hit enter

GetExecuteStatement

ExecuteStatement function

Connect your Connection String variable to Connection String Pin on the function

Type your statement in Statement String for example SELECT * FROM Accounts;

Bind a custom event to On Complete pin and name your event OnComplete, you can do that by pulling the pin On Complete on the ExecuteStatement function and searching for custom event

You should have something like this

ExecuteStatement pins connected

You are now ready to query the database, now its time to get the data

Note: Sometimes when you enter incorrect ConnectionString the ODBC API keeps trying for about 18 seconds then gives up, So if statement execution doesnt succeed and you didnt get an error right away, this is the reason

Getting Data From Database

First we want to handle errors if there are any

On the OnComplete event we made, Pull the DatabaseObject pin and set your DatabaseObject variable

Make a Branch on the Success pin and on False add a Print String node and connect In String to Error Msg pin on the OnComplete event we made

Custom event call

Now on True

Get your DatabaseObject variable, pull the pin and type GetColumnValuesByColumnName and hit enter and connect it to the True pin in the image above

Getting ColumnValuesByColumnName Function

ColumnValuesByColumnName

We will be getting the values in "Password" column in the database so type Password in GetColumnValuesByColumnName function Column Name pin

On Return Value of the GetColumnValuesByColumnName function set your Column Values variable

After that get the length of your Column Values variable and make a branch on "Length greater than 0"

You should have something like this

ColumnValues

If you have any rows in the table "Accounts" the True pin in the image above will execute, You can do whatever you want with that data!

For testing purposes lets print results like this

Printing ColumnValues

Getting Affected Rows

In case you execute statements like "DELETE" or "UPDATE" you might want to get the total count of rows affected

To get the affected rows count you can use the function GetAffectedRowsCount

GetAffectedRowsCount Function

Printing affected rows count

Statement Tag

Since the statement execution is MultiThreaded its hard to tell which one will finish first, to overcome that we use Statement Tags

Lets say you have 2 statements to execute, the first one with SELECT Statement Tag and the second one with DELETE Statement Tag

MultipleStatements

To know which one of those 2 statements we are getting on OnComplete event we switch based on the tag

Switching On Statemnet Tag

More Information

Version: 1.0


Supported Development Platforms: [Win64]

Supported Target Build Platforms: [Win64]


Testing was done on SQL Server 2014 and 2019 with "SQL Server Native Client 11" driver.


You do not have to manually connect and disconnect from the database, The plugin handles that.


Does not support getting messages printed by the SQL Server.


If you want to get data using Procedures you need to do SELECT inside the Procedure.