Greetings and welcome to another OutSsystems how-to video. My name is Andrew
Duthie and in this video you'll learn how to work with an external Oracle
database if you find this how-to video useful and you'd like to learn more
about working with external databases there's a related course integrating
with external databases that's available in the learning section of our website
you can visit the URL below to find this course
in this how-to video you'll learn the following how to add a connection to
your external Oracle database using service center how to import and adjust
tables using our integration studio tool how to publish your database extension
using integration studio to your OutSystems environment and how to
leverage and consume your new extension as a dependency in your application
using service studio and how to create and scaffold screens and applications
using the entities it contains
we'll start the process of working whether external Oracle database using
service center a management console for my out systems environment I'll click on
the administration link then click on database connections you can see I've
got a couple of database connections already I'm going to click new database
connection to add a connection for my external database I'll set the name to
my Oracle that can be any name you want as long as
it's valid I'm going to choose the DBMS as Oracle I could provide a description
if I want and then I need to provide the information for connecting to the
database so I'm going to paste in my host name
give it my service name and then I need the username and
password then I'll click the test connection button and I see that I get a
valid connection so we'll go ahead and create this connection string so now
we've got our connection string created now we can switch over to integration
studio and build the actual extension
I've now opened integration studio and I've logged into the environment where
I'm working to bring in the tables that I want I can simply click file new and
that will create the extension I need to give this a name other than extension so
I'll call this my Oracle data and to add the tables I can simply right-click the
entities folder and select connect to external table or view this will open a
simple wizard that will make it possible for me to bring in the tables or views
that I want to use so I'll click Next and I'll choose the database connection
that we just setup a minute ago click Next and now you can see I have a number
of different tables here or databases and I'm going to choose the demo
database now I'll click Next and you can see and this is sort of HR
related information so I'm going to choose the employees and departments add
those and click Next again I need to set my logical database
name click Next and it should tell me the entities that we're going to
generate the departments and the employees and now I can click finish
you can see in my Oracle database that my database table names are all caps
maybe I don't like that so I'll change this to be mixed case do that with
departments as well and now we can see if I look at the employee you can see
one of the things that we have here is an attribute called phone number phone
number is actually a discrete data type inside of out systems so instead of
making that a text I can actually make that a phone number and take advantage
of built-in data type validations can do the same thing for the email as well
so I can make the email an actual email data type and again take advantage of
built-in data type validation built-in validations for emails etc when I'm
creating my screens now that I've made the changes that I want to make to how
my external tables map into my out systems entities I need to publish this
to my out systems environment and I can do that simply by clicking the one-click
publish button when I click this it's going to verify that everything looks
good it's going to update the extension
source code it's going to prompt me to save an XF file which is just a file
based copy of my extension so I can move that to other environments if I want
then it's going to save that upload it to my environment and publish that note
that I do have a warning message that I'm missing a configuration step the
configuration step is really important it's basically going to map the
connection string into my extension so I'll go ahead and click configure and
then we can fix this issue this then opens the extension in service
center and you can see the error that I'm receiving here is that it has one
logical database with a missing configuration and all I need to do is
tell it which connection string I'm connecting to which in this case is my
Oracle click apply and now we can use our extension inside of our applications
to demonstrate our extension I've opened service studio and created a new web
application I'm going to go ahead and create the web responsive module and
then to use our extension willing to add it as a dependency for our web
application so I'm going to click manage dependencies
and then in the producers I'm going to search for Oracle and we can see the
extension my Oracle data that we just created you can see the departments and
employees entities that that extension contains you can see that the names are
mixed case just as we change them to be inside the extension click OK
once I've added that it will show up in the data tab under the extension in the
entities folder so you can see this looks just the same as any other out
systems entity would look I see the entity I see the attributes I see the
basic crud operations that I can use to add or remove records from those tables
so I can come in and say
add the employees table as a screen and it scaffolds out the UI for me so you
can see it has the first name the last name email phone number I can just the
same as I would with any other entity I can right click here and since we know
what this shape of this data looks like I can create an employee detail web
screen based on the data that's in that external entity you can see for things
like the manager in the department it actually even includes dropdowns
automatically because it recognizes the relationship between the employees and
departments entities so I'll go ahead and publish this
and when I published this application for those of you who haven't used
service studio I'm basically saving a copy of my application for source
control purposes compiling and generating in asp.net application and
all of the necessary database scripts updating my platform and publishing my
application one other thing I'll want to do for simplicity sake is switch over to
the interface tab I'm going to select each of the screens that I generated and
mark them to be available for anonymous users now I wouldn't do this in a real
application but this makes it a little simpler for me to browse the application
without having to login so again republish my application
and now we can click open and browser to view our application
you can see we load up the list of employees so I can see a list of
employees I can click into a specific employee and we can see for example that
this employee does not have a valid email address so you can see I'm
actually getting validations right out of the box where it says please include
an @ in the email address this particular record is missing the @ so I
didn't have to write any code to do that I'd have to do anything custom and in
fact if I go to try to save this record it's telling me the email address is
invalid the reason for that being that I marked this in my extension as being the
email data type so I get that effectively for free so I change this to
a test com and save it now you can see that my
email is now valid I was able to save my record and I'm good to go
so in this video you've seen how you can add a database connection for your
external Oracle database using service center you've seen how you can import
and modify the definition of tables using integration studio as well as how
to use integration studio to publish your database extension into your
OutSystems environment lastly you saw how to use service studio with the
extension as a dependency to create screens and applications using the
entities from your external database before we wrap up I want to encourage
you to subscribe to the OutSystems youtube channel there you'll find many
videos like this one including how to's and tips on our advocacy how-tos and
tips playlist I hope you enjoyed this video and thank you for watching