cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Have a PTC product question you need answered fast? Chances are someone has asked it before. Learn about the community search. X

Working with External Databases Part 2

100% helpful (1/1)

 

 

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.

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.

  1. In the DatabaseController entity, go to the Services tab.

    ext9.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
commandStringTrue

5. Add the following code to your new service:

<<command>>

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

ext10.png

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.

  1. In the Services tab of the DatabaseController entity, create a new service of type JavaScript.

  2. Name the service JavaScriptInsert_PersonsTable.

  3. Set the Output as InfoTable, but do not set the DataShape for the InfoTable.

  4. 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
keyStringTrue
name_firstStringTrue
name_lastStringTrue
company_nameStringTrue
company_positionStringTrue
addr1_line1StringTrue
addr1_line2StringTrue
addr1_line3StringTrue
addr1_cityStringTrue
addr1_stateStringTrue
addr1_postal_codeStringTrue
addr1_country_codeStringTrue
addr1_phone_numberStringTrue
addr1_fax_numberStringTrue
created_byStringTrue
updated_byStringTrue
created_dateStringTrue
updated_dateStringTrue

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).

    ext11.png

     

  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
keyStringTrue
name_firstStringTrue
name_lastStringTrue
company_nameStringTrue
company_positionStringTrue
addr1_line1StringTrue
addr1_line2StringTrue
addr1_line3StringTrue
addr1_cityStringTrue
addr1_stateStringTrue
addr1_postal_codeStringTrue
addr1_country_codeStringTrue
addr1_phone_numberStringTrue
addr1_fax_numberStringTrue
created_byStringTrue
updated_byStringTrue
created_dateStringTrue
updated_dateStringTrue

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 singular 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.

    1. In the DatabaseController entity, go to the Services tab.

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

    3. Add the following parameter:

 Name                      Base Type        Required
student_keyStringTrue
course_keyStringTrue

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:

ext12.png

 

    1. In the DatabaseController entity, go to the Services tab.

    2. Create a new service of type SQL (Query) called GetStudentCoursesStoredProcedure.

    3. Set the Output as InfoTable, but do not set the DataShape for the InfoTable.

    4. Add the following parameter:

 Name                    Base Type        Required
course_keyStringTrue

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.



Step 6: Next Steps

Congratulations! You've successfully completed the guide for External Database, and learned how to use the ThingWorx Platform to connect to database, query for data, and write new data.

Learn More

We recommend the following resources to continue your learning experience:

 Capability       Guide
BuildDesign Your Data Model
BuildConfigure Permissions

Additional Resources

If you have questions, issues, or need additional information, refer to:

 Resource           Link
CommunityDeveloper Community Forum
Version history
Last update:
‎Nov 01, 2022 10:19 AM
Updated by:
Labels (2)
Contributors