This Instruction Article explains how to use Query Manager in SENTINEL to manipulate data in SQL database. The example given shows the use of File Detection and Query Manager to manipulate data in SQL database. User can use the method described in this article to create a custom Database Trigger which can be used to start a complex printing process.
Note: The instruction in this article is rather complex. It utilizes Stored Procedure in Microsoft SQL, Mapping design and Query Manager Task in SENTINEL. Before starting, you should have a working knowledge of Microsoft SQL Management Studio.
Sample Drop FIle:
The content of the input file, named Drop5.txt, for triggering the printing process is:
Setup Database and Stored Procedure:
- A table in SENTINEL database called Test-Query
- There are 5 fields in the Test-Query table – Key1, Data1, Data2, Data3, Data4 and Data5. Key1 is the key to the record and is automatically assigned by the system. For the purpose of this article, we will only focus on Data1, Data2, Data3, Data4 and Data5.
- A Stored Procedure called TestQRY. It has 5 input parameters. The purpose of this Stored Procedure is to insert a row into the Test-Query table with values from the 5 input parameters assigned to Data1 to Data5 fields. Here is the script to create the Stored Procedure.
- Create a MAP file, QRY-Map, for SENTINEL to parse the input file (drop5.txt). Note that there are 5 zones defined here – d1, d2, d3, d4 and d5. Each represents one data element from the input file.
- From SENTINEL Manager, select Tools/Query Manager…
- Right-Click on Data source/New datasource/Ms SQL Server
- Fill in the necessary information to connect to the SQL server where SENTINEL database is located. Click on Test Connection to verify that the information entered is correct. When done, click OK then name the connection DB Connect.
- Confirm that the table Test-Query and stored procedure TestQRY are present.
- Right-Click on Tasks and select New. Name the new task QRYMgrTsk.
- On right right pane of the window, select DB Connect as Data Source and TestQRY as Stored Procedure. You should now see the 5 parameters (@param1, @param2, @param3, @param4 and @param5) from the list stored procedure listed.
- In the Alias column, fill in the data you want to assign to the fields data1, data2, data3, data4 and data5. This data will come from the zones in the MAP file (d1, d2, d3, d4 and d5). In this example, we will assign d5 to data1, d4 to data2, d3 to data3, d4 to data4 and d1 to data5.
- Close the Query Manager.
- To tie everything together, create a new sentinel, QM, with the predefined group TC. Select the map file qty-map.map.
- Select Input Tab, select File capture and click Settings…
- Select the location where the drop file will be deposit for processing then click OK.
- Select Process tab, check Database box and click Settings…
- Select the QRYMgrTsk task by checking the box then click OK.
- Clock OK to close the sentinel definition.
- Enable the QM sentinel and activate it.
- Start with an empty table
- The file drop5.txt is on the desktop as is the watch folder.
- Copy (do not drag) the drop5.txt file into the watch folder. If all goes well, the file will disappear very quickly. If you look in the SENTINEL log, you will see the activity generated by QM.
- Checking the database, you should see that 2 rows were added to the table. This is because there was an extra CRLF at the end of the file.
Note on Labeling: You may notice that in the example above, none of the tasks involve any label design file. This means that you can use this technique to perform almost any database related tasks (as long as they are properly defined in stored procedure).