Automatically Update Database Information

DriveWorks Pro, with all its modules, is an insanely robust and flexible system with what seems to be unlimited potential. One practice commonly used in the application is the ability to connect to outside systems, such as SQL, SAP, Oracle, or almost any other system that you can imagine. Initially getting the data out of those applications and into DriveWorks is relatively simple. But one question that we often hear is, “How can we trigger the database updates to show automatically?”. With some clever setup, we can automatically update data from a database. In this blog, we’ll go through that processes step-by-step to understand how it works.

Understanding What’s Going On

DriveWorks has already created a very helpful knowledge base article KB13103011 “How To: Force a data refresh when data has changed”. That walks you through the processes for refreshing data. Before we walk through it, a key piece of information that you need to understand is that the SQL query must contain a constant or variable inside of it that will act as a trigger for pulling new data in. As long as we drive something to that variable or constant (even if it’s the same value), the SQL query will see that change and be forced to re-run. It also helps to understand that the function “DbExecute” does not return any real “data”, only a confirmation if the action succeeded or a warning message if it did not.

Pass Information with Macros

Let’s walk through the solution to see how it can work! Below you can see my main screen that I’ll be working with. It includes a Text Box to enter a new ID, a Macro Button to push the information to SQL. It also shows a Data Table to show what items exist in the SQL table when we run the automatic update.

This will allow us to input a new record ID, hit the execute button that passes that ID to SQL, then immediately see the updated information in the table below.

This is done by typing a new ID into the “New Record” text box, and then hitting the “Execute” button. The “Execute” button then runs a macro named “Execute”. The “Execute” macro only has one action that drives a constant named “RefreshData”.

The “Drive Constant Value” is running a “DbExecute” command that is adding information into my SQL table.

It’s important to note that when the “DbExecute” command runs successfully, it will return a “1” value. Because we are running this command in a “Drive Constant Value” action, the “1” value will now be imported into the constant “RefreshData”. The value that is pushed is not necessarily relevant so long that something is pushed into the constant. The simple fact that the constant has updated with be the trigger we need to refresh our SQL table information!

Automatically Get Information Back

Next, we’ll look at the table control on our form that reports back what is currently in the SQL database. This is the control that we would like to automatically update immediately after the change has been made. To do so, we will control the items in this table with a “QueryDataValues” function to pull information from SQL.

The syntax to report the values back from SQL is relatively straightforward. See here for the QueryDataValues command, and here for selecting items from a SQL database. Below is the syntax that I used, although you’ll need to add your SQL information.

QueryDataValues(“Server=<<YourServerName>>;”,@”SELECT ID FROM <<YourDatabase>>.dbo.<<YourTable>> WHERE ID <>’@(DWConstantRefreshData)’ ORDER BY ID ASC”,”<<YourSQLUserName>>”,”<<YourSQLPassword>>”,TRUE)

This query is asking SQL to select the ID column from my table where the ID does not equal whatever value is stored in my “RefreshData” constant. I do not have any ID’s in my table that equal “1”, all of the ID’s should report back. The key piece of this query is the “WHERE ID <>’@(DWConstantRefreshData)’ section. Because SQL is looking to select items using a value that I know is irrelevant (the “RefreshData” value of “1”), it doesn’t affect the returning results of the lookup. However; because I am using the constant as part of the SQL query, and each time I hit “Execute” it updates the constant value, forcing the query to update and run again. This in turn re-selects all of the items in SQL and updates the table control on my form!

Summarize

To summarize what is happening, the “Execute” button runs the “Execute” macro. The “Execute” macro starts a “Drive Constant Value” task. The task runs a “DbExecute” function that passes the “New Record” return into the SQL table as a new row. If it runs successfully, it passes the return value of “1” to the constant “Refresh Data”, so the constant is now flagged as being modified. Then, we’re using “QueryDataValues” to read from SQL into the table control on our form, using SQL syntax. Since that SQL query is using the “Refresh Data” constant, which is now flagged as modified, it is triggered to update as well, thus re-running the SQL query and pulling in the new data immediately!