Connecting External Databases and Model Part 2

Yes

Step 4: Write Data to External Database
Step 5: Executing Stored Procedures

Step 4: Write Data to External Database

You’ve connected to the database, you’re able to query the database. Now let’s handle inserting new data into the database. The update statements and data shown below are based on the table scripts provided in the download. Examples of how the ThingWorx entity should look can be seen in the SQLServerDatabaseController and OracleDatabaseController entities that you've downloaded

Running an Insert

Follow the steps below to set up a helper service to perform queries for the database. While other services might generate the query to be used, this helper service will be your shared execution service.

In the DatabaseController entity, go to the Services tab.

sql_query_service_init (2).png

2. Create a new service of type SQL (Command) called RunDatabaseCommand.

3. Keep the Output as Integer.

4. Add the following parameter:

Name	Base Type	Required
command	String	True

5. Add the following code to your new service:

<<command>>

6. Click Save and Continue. Your service signature should look like the below example.

You now have a service that can run commands to the database. Run your service with a simple insert.

There are two ways to go from here. You can either query the database using services that call this service, or you can create more SQL Command services that query the database directly. Let’s go over each method next, starting with a service to call the helper.

In the Services tab of the DatabaseController entity, create a new service of type JavaScript.
Name the service JavaScriptInsert_PersonsTable.
Set the Output as InfoTable, but do not set the DataShape for the InfoTable.
Add the following code to your new service:

try {
var command = "INSERT INTO Persons (person_key, person_name_first, person_name_last, person_email, person_company_name, " +
"person_company_position, person_addr1_line1, person_addr1_line2, person_addr1_line3, person_addr1_city, person_addr1_state, " +
"person_addr1_postal_code, person_addr1_country_code, person_addr1_phone_number, person_addr1_fax_number, person_created_by, " +
"person_updated_by, person_created_date, person_updated_date) VALUES ('" +
key + "', '" + name_first + "', '" + name_last  + "', '" + 
email  + "', '" + company_name  + "', '" + company_position  + "', '" + 
addr1_line1  + "', '" + addr1_line2  + "', '" + addr1_line3  + "', '" + 
addr1_city  + "', '" + addr1_state + "', '" + addr1_postal_code  + "', '" + 
addr1_country_code + "', '" + addr1_phone_number  + "', '" + addr1_fax_number  + "', '" + 
created_by + "', '" + updated_by  + "', '" + created_date + "', '" + updated_date  + "')";

logger.debug("DatabaseController.JavaScriptInsert_PersonsTable(): Query - " + command);
    
    var result = me.RunDatabaseCommand({command: command});
} catch(error) {
    logger.error("DatabaseController.JavaScriptInsert_PersonsTable(): Error - " + error.message);
}

5. Add the following parameter:

Name	Base Type	Required
key	String	True
name_first	String	True
name_last	String	True
company_name	String	True
company_position	String	True
addr1_line1	String	True
addr1_line2	String	True
addr1_line3	String	True
addr1_city	String	True
addr1_state	String	True
addr1_postal_code	String	True
addr1_country_code	String	True
addr1_phone_number	String	True
addr1_fax_number	String	True
created_by	String	True
updated_by	String	True
created_date	String	True
updated_date	String	True

6. Click Save and Continue.

Any parameter, especially those that were entered by users, that is being passed into a SQL Statement using the Database Connectors should be fully validated and sanitized before executing the statement! Failure to do so could result in the service becoming an SQL Injection vector.

Now, let’s utilize a second method to create a query directly to the database. You can use open and close brackets for parameters for the insert. You can also use <> as a method to mark a value that will need to be replaced. As you build your insert statement, use [[Parameter Name]] for parameters/variables substitution and <<string replacement >> for string substitution.

1. In the Services tab of the DatabaseController entity, create a new service of type SQL (Command).

2. Name the service SQLInsert_PersonsTable.

3. Add the following code to your new service:

INSERT INTO Persons
           (person_key
           ,person_name_first
           ,person_name_last
           ,person_email
           ,person_company_name
           ,person_company_position
           ,person_addr1_line1
           ,person_addr1_line2
           ,person_addr1_line3
           ,person_addr1_city
           ,person_addr1_state
           ,person_addr1_postal_code
           ,person_addr1_country_code
           ,person_addr1_phone_number
           ,person_addr1_fax_number
           ,person_created_by
           ,person_updated_by
           ,person_created_date
           ,person_updated_date)
     VALUES
           ([[key]]
           ,[[name_first]]
           ,[[name_last]]
           ,[[email]]
           ,[[company_name]]
           ,[[company_position]]
           ,[[addr1_line1]]
           ,[[addr1_line2]]
           ,[[addr1_line3]]
           ,[[addr1_city]]]]
           ,[[addr1_state]]
           ,[[addr1_postal_code]]
           ,[[addr1_country_code]]
           ,[[addr1_phone_number]]
           ,[[addr1_fax_number]]
           ,[[created_by]]
           ,[[updated_by]]
           ,[[created_date]]
           ,[[updated_date]]);

4. Add the following parameter:

Name	Base Type	Required
key	String	True
name_first	String	True
name_last	String	True
company_name	String	True
company_position	String	True
addr1_line1	String	True
addr1_line2	String	True
addr1_line3	String	True
addr1_city	String	True
addr1_state	String	True
addr1_postal_code	String	True
addr1_country_code	String	True
addr1_phone_number	String	True
addr1_fax_number	String	True
created_by	String	True
updated_by	String	True
created_date	String	True
updated_date	String	True

5. Click Save and Continue.

Examples of insert services can be seen in the provided downloads.

Step 5: Executing Stored Procedures

There will be times when a singluar query will not be enough to get the job done. This is when you'll need to incorporate stored procedures into your database design.

ThingWorx is able to use the same SQL Command when executing a stored procedure with no data return and a SQL query when executing a stored procedure with an expected result set. Before executing these services or stored procedures, ensure they exist in your database. They can be found in the example file provided.

Execute Stored Procedure

Now, let's create the service to handle calling/executing a stored procedure.

If you are expecting data from this stored procedure, use EXEC to execute the stored procedure. If you only need to execute the stored procedure and do not expect a result set, then using the EXECUTE statement is good enough. You're also able to use the string substitution similar to what we've shown you in the earlier steps.

In the DatabaseController entity, go to the Services tab.
Create a new service of type SQL (Command) called RunAssignStudentStoredProcedure.
Add the following parameter:

Name	Base Type	Required
student_key	String	True
course_key	String	True

4. Add the following code to your new service:

EXECUTE AddStudentsToCourse @person_key = N'<<person_key>>', @course_key = N'<<course_key>>';

You can also perform this execute in a service based on JavaScript using the following code:

try {
    var command = "EXECUTE AddStudentsToCourse " +
    " @student_key = N'" + student_key + "', " +
    " @course_key = N'" + course_key + "'";

    logger.debug("DatabaseController.RunAssignStudentStoredProcedure(): Command - " + command);

    var result = me.RunDatabaseCommand({command:command});
} catch(error) {
    logger.error("DatabaseController.RunAssignStudentStoredProcedure(): Error - " + error.message);
}

5. Click Save and Continue.

Execute Stored Procedure for Data

Let's create the entity you will use for both methods. This can be seen in the example below:

In the DatabaseController entity, go to the Services tab.
Create a new service of type SQL (Query) called GetStudentCoursesStoredProcedure.
Set the Output as InfoTable, but do not set the DataShape for the InfoTable.
Add the following parameter:

Name	Base Type	Required
course_key	String	True

5. Add the following code to your new service:

EXEC GetStudentsInCourse @course_key = N'<<course_key>>'

You can also perform this execute in a service based on JavaScript using the following code:

try {
    var query = "EXEC GetStudentsInCourse " +
    " @course_key = N'" + course_key + "'";

    logger.debug("DatabaseController.GetStudentCoursesStoredProcedure(): Query - " + query);

    var result = me.RunDatabaseQuery({query:query});
} catch(error) {
    logger.error("DatabaseController.GetStudentCoursesStoredProcedure(): Error - " + error.message);
}

6. Click Save and Continue.

You've now created your first set of services used to call stored procedures for data. Of course, these stored procedures will need to be in the database before they can successfully run.

Click here to view Part 3 of this guide.