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. 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.
View full tip
