Greetings and welcome to another OutSystems How-to Video. My name is Andrew
Duthie and in this video you're going to learn how to work with an external MySQL
database
as a quick note there's a related course in our learning section entitled
integrating with external databases if you want to know more about this course
or get a deeper understanding of this topic please visit the URL below
in this video you'll learn the following how to add a connection to your external
my sequel database using our service center management console how to import
and adjust your tables including data types names etc using integration studio
how to use integration studio to publish your database extension to your out
systems environment and lastly how to consume your extension as a dependency
using service studio and create and scaffold screens and applications using
the entities it contains let's get started
we'll start the process of working with an external database in service center
our management console for the specific environment that I'm working in I've
opened service center I'll click the administration link and then click
database connections you'll see I already have an existing connection but
I want to create a new database connection so I'll click create database
connection I'll give this a name test MySQL and I can provide a description if
I want to connect to MySQL I'll select in the drop down and list MySQL and then
I need to provide the data on the connection itself so I'll plug in the
server name the schema name and the user name and password
then I'm going to click the test connection button to make sure that
these values are working I get a successful result so now I'll
click create to create the database connection
next we'll move to integration studio and create the extension itself
I'm now in integration studio and I've logged into the OutSystems environment
where I want to publish my extension I'll go ahead and click file new to
create a new extension and I'm going to name it
external my SQL to add tables from my external database I'll simply
right-click the entities folder select connect to external table or view and
that will start a simple wizard that will allow me to import the tables that
I want from my external database when I click Next I can see I have the test my
sequel database connection available so I'll select that and click Next now I
can see the available databases I want the employees database I'll click
Next here again and I can see that I have a number of
tables that are available here and I can take for example the employees table and
go ahead and add that to my tables that I want to use and I could add say
departments as well I can double click I can also grab the depth M there's a
mapping table between these two and I'll go ahead and click Next and the logical
database is the name of the database in service center
and then the final step shows me the entities that are going to be created I
can look at a report or I can just simply click finish now you can see that
I've added the entities to my entities folder I double click one of these you
can now see the mappings from the original name and original data types in
the external database to the corresponding out systems data types
instead that will appear inside of out systems so I could do things like maybe
I want uppercase names so I can change the name of the entity on the out system
side to be employers with an uppercase name
I could change things like the empty employee number etc I can change the
data types I were to go to the department's you can see I've got the
department number in the department name and for the department amp you can see
its mapping the employee number to the department number and has that mapping
with a from date two to date so we can easily change which departments an
employee as as part of to go ahead and make this available in my out systems
environment I simply need to click the one click publish button and that's
going to take care of all of the steps necessary to compile and publish this to
my system I can also as part of this process save an EXIF file that's the
kind of file based definition of my extension
and then last but not least there's one configuration step that I need to take
so you can see that right now I'm getting a warning message that says then
in order to use this this configuration I actually have to have the logical
connection set inside of service center so if I click the configure button we go
ahead and open the record for the extension that we just published and
we'll see that we have a status error that says that it has one logical
database with a missing configuration so to fix that we'll switch to the
operation tab and select the logical database and click apply now we can see
our status message goes away and we're ready to use our extension so we'll
switch over service studio next
now I've opened service studio and I've created an application called MySQL demo
and I'll go ahead and create a web responsive module and we'll get started
using our extension to use the custom database extension that we just created
we'll open the manage dependencies window and add this as a dependency
and you can see I have external my SQL I'll select that and select all of the
entities that I've exposed and you can see the changed name of the employees
with the uppercase e that we made in integration studio
once I add that as a dependency it shows up under the entities folder inside the
data tab then I can simply go ahead and leverage the same functionality that
you'd expect in terms of scaffolding and modifying data just simply by
grabbing and dragging dragging and dropping and creating a screen so you
can see now we'll get a data band table of the employees just the same as we
would if we were using a native entity created inside the platform database
and just as I would in any other application I can do things like linking
to the employee detail web screen and it's going to go ahead and based on
knowing exactly what the shape of the data is it can create that detail screen
for me as well so let's go ahead and publish this for those of you who may be
new to out systems when we publish our application using the one published one
click publish button we're taking a snapshot of the application for source
control purposes compiling and generating compiling and optimized
asp.net application updating the underlying database model and then
making that application available for browsing
next we'll click the open and browser button and see how this looks
and now we can see that we've got the employee information we can search for
something like math we can see that we get we have a lot of employees
apparently with the last name Matheson and we can reset we can do all the
things that we'd expect to be able to do paging and sorting all of that works I
can also click into the record for a given employee and I can change the
first name there and if I search for that first name you
can see the change record is indeed there so you can see without a lot of
effort we were able to work with an external MySQL database we were able to
bring the data from that database into a noun systems application modify the data
and treat it essentially the same as we would any other entity inside of a noun
systems application
so to summarize what you just saw is how we add a database connection to your
external MySQL database using a service center you saw how we imported and
adjusted tables using integration studio you saw how we can publish our database
extension to our environment again using integration studio and you saw how you
can use service center to consume your new extension as a dependency and create
and scaffold screens and applications using the entities it contains if you
liked this video I'd encourage you to subscribe to our OutSystems youtube
channel you can find many more videos like this on our advocacy how to's and
tips playlist and thanks for watching