How to Use SQL in Ignition (SCADA Tutorial)

LeMaster Tech
183 followers
4 Video Views·Apr 20, 2025
Ignition is an incredibly powerful and modern SCADA Software, and one of the things that makes it so powerful is the ability to easily interface with standard database tools, like the easy-to-use Microsoft SQL Server!
Consider checking out the sponsor of today's video, PCBWay!
https://pcbway.com/g/2Zlh7L
Thank you for watching, please leave a like on the video and don't forget to subscribe and let me know in the comments what you want to see next!
Love the channel? Consider becoming a super supporter here: https://www.patreon.com/c/LeMasterTech
hello and welcome back to the Master
Tech and in today's video I am going to
show you how to connect ignition to SQL
and then also how to read and write to
and from SQL from an ignition display so
first off sorry if I sound a little
under the weather in this video I'm
definitely kind of stuffed up from the
holidays but I'm really excited to
continue the ignition tutorial series
we've been doing on the channel if you
have any super basic questions like what
is ignition uh what is it used for where
can I download this like maker version
of it um how to get started with it I
can uh recommend you check out the
introduction to ignition tutorial series
on the channel this is not going to be
specifically on how to download and
install uh Microsoft SQL you can get SQL
Server Express at any time for free I've
already downloaded and installed that um
and that's kind of the prerequisite I
want you to do before following this
tutorial because this is not on
downloading and installing SQL but you
want to make sure you do both pie pieces
of the SQL install which is downloading
the SQL Server uh itself and then also
the SQL Server management Studio which
is a software tool that looks kind of
like this um and when you download your
software Tool uh if you do SQL Express
which is what I did um you'll have
something that kind of looks like this
it'll say the name of your computer and
then back SL SQL Express uh I just made
a new database called IT lamaster Tech
tutorials here and then I made a new
table and called it ignition tutorial uh
this is all just the setup that I did
for this lesson this is not actually to
do with ignition yet this is just
downloading and installing SQL and uh
tables there's lots of setup videos on
how to do that out there so I made a
table uh that just has four columns it
has a name field a timestamp a counter
and then uh comments so the part that I
want to stress before you even get to
ignition is you need to make sure from
the SQL Server configuration manager
that you have SQL Server running and SQL
server browser running or your uh
Gateway instance will not be able to
connect to it uh and then the only other
thing I think is worth discussing here
ignition is a web hosted tcpip base uh
tool when it connects to uh databases so
you want to make sure again from the SQL
Server configuration manager that the
TCP IP uh service is enabled that is all
like PRX before we do any piece from
ignition so uh let's go ahead and um pop
into the gateway now so I have a nice
test stand where I'll show both reading
and writing and talk about how to set
that up in just a moment but for now
let's uh show actually getting started
with database connections so to do this
to do it fair uh I'll go ahead and I'll
just delete the database that I already
have in there which is this connection
so I'll go ahead and go to config and
then databases connections and I'm going
to delete the one that I already have uh
and I'm going to so right now when you
log into your gateway you should be able
to get here uh if you're if you're not
able to get here then you need to follow
the previous tutorials on the channel
because I'm assuming you know how to get
to your gateway configuration page now
let's go to databases it says there are
no database connections defined let's go
ahead and create a new one okay and here
what's cool you'll see all the options
that ignition gives you natively for
connecting to databases the tool that I
download and installed is Microsoft SQL
Server I'm going to call it LT Express
and I'm just going to say that this is a
test database for tutorials um now the
jdbc driver is going to be whatever you
selected on the previous menu by default
so we told it it was Microsoft SQL
Server which is why that's filled in um
now the important part that I kind of
alluded to before is getting your uh
connect URL uh right so when you have
SQL and ignition installed on the same
computer like I have uh you can leave
this as Local Host if you want that just
tells it to point directly to um where
you're currently looking or you can put
in the computer name in there so like my
computer name is LT engineering one it's
trying to autofill which is fine um but
like so my computer name is LT
engineering 1 back/ SQL Express uh and
you can see this if you go in the SQL
management Studio you go in this tool
you can see in the top uh your computer
name and then the instance of SQL you
can do it that way
um but if you have a computer like if
SQL lives on a server on your network
that the two computers can communicate
over um but but uh you you need to enter
the IP address here you can't leave it
as Local Host okay so let's go ahead and
uh do this now username and password if
we went to LT tutorials and we went to
um permissions and we view server
permissions if you then look at these
accounts you'll see one ignition I made
an account specifically for this
tutorial I called it ignition I made a
password just for ignition to use and I
granted it access to all of the tools So
reading and writing and this is where
you can also restrict ignition to only
write to tables that you want it to
write to just by creating restrictions
on whatever account you let it log in
with okay so the username is ignition
I'll go ahead and pop the password that
I made for that account in um and then
the database name
s is also important so we want to
connect to the
ltor tutorials database so we say
database name equals and then ltor
tutorials just like that and we create a
new database connection uh and it
faulted I'm guessing because I mistyped
the password it felt wrong when I typed
it in so let's go ahead and type it in
again oh I have caps lock on that'll do
it okay paying attention to keyboard
board this time and let's try again save
reconnecting valid there we go so this
is what you want um if you try to
reconnect and it does not say valid if
it says faulted then what you probably
want to start by doing go to the
database status page so no longer in
config over here on the left you're in
status you go to databases and if this
is faulted then this will be a little
red like exclamation mark icon and it'll
say some of the details about why it's
faulted some the most common stuff is
that your computer is actually getting
blocked from accessing SQL because of
like a firewall restriction um some of
the other most common stuff is you
actually don't have the SQL server
browser running um you could definitely
have an account where you typed in the
credentials wrong just like just like I
did uh and then another possibility is
you got the host name or the database
name wrong so there's a lot of common
errors that you could run into here um
but the important thing hopefully that
this page shows you is that there's not
actually all that much going on so most
issues are likely in a tool or a network
or firewall policy that you need to go
fix outside of ignition okay so that's
how to connect basically to Microsoft
SQL Server I'm sure there will be
questions about that as well cuz this
isn't to catch all so let me know about
those in the comments below but let's
move on to great we're connected now
what can we actually do with that I want
to pause right here and talk to you
quickly about the sponsor of of today's
video PCB way PCB way is a long-term
supporter of the lamaster tech Channel
and I love working with them because
their products and services are all
about empowering makers as their name
suggests the core of their business is
manufacturing pcbs or printed circuit
boards this is such a cool way for
hobbyists and makers to turn their
electronic designs into Super
highquality and professional grade
circuit boards beyond that they also
have the ability to support all your
prototyping needs with highquality 3D
printing and metal fabric ification as
well be sure to check them out at the
description below this video and thank
you so much to PCB way for sponsoring
the video all right now back to the
tutorial so let's look at the ignition
designer again I have a decent like
walkr test already here that I'll um
show you guys in a second but the
easiest way to verify that you can see
and do what you want to see and do in uh
SQL from ignition is you go to tools in
the uh top bar of the ignition designer
you go to tools and then you go to
database query browser and then uh you
should over here you should see all of
the databases that your gateway is
connected to so uh I have LT Express but
here I can see ignition tutorial and if
I were to go into SQL and make a second
table in that database I'd be able to
see that here as well this database
query browser is actually super useful
you can write uh inst structured query
language SQL right here uh in the
browser so select all from ignition to
tutorial let's just do that uh you can
do that right here and make sure that
your connection is good so then uh the
data pulls in and you're doing all of
this in ignition you don't need the
management Studio or anything else to
view this uh and that's kind of nice
because then when you get a query that's
working you can put that in a specific
ignition tag called a query tag you
don't have to use Query tags um you
could just make like a memory tag and
you could populate it uh with a named
query there are so many different ways
that we could go about this check out
some of ignition inductive automations
documentation on named queries if you're
looking to do a lot of SQL uh you
probably want to learn about that but
for now let's look at a query tag so uh
I made a query tag so uh the data type
is going to be a data set if you're
pulling in a whole bunch of results from
SQL if you're just like pulling in the
max value in a table this could be an
integer or a float or a string or any
other form obviously now the execution
mode is up to you event driven usually
means like it'll be listening for
changes I like to do fixed rate for
execution rates because I can slow down
less important things but for this it's
like just a quick little list of names
and comments so I'm going to check for
changes every second and that'll uh look
good on the HMI as well when we're
entering new data and I'm going to use
that same query select all from ignition
tutorial select everything from ignition
tutorial it will uh pull in data every
uh 1 second you tell it what data set to
use again just like in that database
query Tool uh and you can tell it
specifically if it's a select or an
update but um I'm just going to let it
autodetect okay so now I have a tag
called query results uh that is going to
update every second and I got it on the
screen in front of you that you can
already see by just dragging that tag
onto the screen and telling it to be a
table oh the rest of what's going on on
the screen is really simple I just have
a little button to trigger a SQL right
and I'll show the script that we're
executing in just a second and then I
have a name and comments field where uh
they're text Fields so we can enter
whatever data we want in there and then
I have this counter variable that's
going to count up every time I send new
data to SQL so this is so that we can
test both writes and reads uh in this
tutorial so those are just memory tags
now let's go ahead and um take a look at
the script when I click on send entry
when I tell that tag uh to go from zero
to one cuz it's a Boolean let's take a
look at the script that's actually going
to do the SQL writing okay and um if you
want you can screenshot this or pause
this and make sure that the code makes
sense to you but basically this is now
python scripting you write python
scripting to do a SQL insert statement
here if you don't want to do uh another
query tag or if you don't want to do
named queries which again future concept
this video would be really long if we
did that um but the whole scope of this
code that you see in here with a little
bit of like logging troubleshooting
flare is go grab the values of the name
counter and comments fields that we want
to write to SQL grab the current time
when I click this button uh and then
insert into that table those values and
the last thing I do is after I store it
into SQL I just add one to the counter I
reset my trigger tag to false and then I
clear out the name in the comments field
so it's ready for more data so again I'm
sure there will be questions ask away
but I'm trying to not let this get too
long um let's go ahead and enter some
data so that you guys can see this thing
working so I'm back on the web page I'm
viewing this uh in real time let's say
our name is Pete lamaster Tech and
comments will be this is L Master Tech
all right and let's go ahead and send
the entry okay and you can see that
table on the bottom almost immediately
updated as these tags cleared out and
while that seems cool it's actually even
cooler when you think about where that
data just went so we just triggered a
tag that wrote a SQL insert statement if
we were to go right to our SQL server
and select the top thousand now you see
that data that we just wrote with the
timestamps and everything else then our
query tag in ignition is pulling that
data back in So if we did another one
and we said Caitlyn and we said this is
Mrs L Master Tech and then we wrote that
one uh we send that away and then we
have a new entry in the table again
which is pretty cool we can do Dale we
can say l Master text first ever
patreon and then we send that away
clears out we'll say
Phillip second L Master Tech patreon
supporter that's right and that's why
you should support me on patreon cuz
you'll get in these tutorials
um but you can see there seven entries
really quick and easy so whatever you're
monitoring and controlling when you have
a set of data that's important enough to
store in a long-term database that's
when you want these kinds of tools and
then if you're trying to show historical
performance and pull back in from a
database that's when you want these sort
of tables and query tags so I know we
packed a lot into this tutorial but I
think it's all super useful and super
impactful stuff I hope you ask any
questions that you may have had in the
comments below and let me know what
you'd like to see next on the ignition
tutorial series as well as the channel
overall thank you so much for watching
good luck with your projects we'll see
you next time thanks bye
