======TransSECS Devices Application Examples with Databases====== ====Recipe Management==== Recipe Management is most likely the most common use case for using TransSECS Devices scripts for Databases. If the recipes are stored locally (file based recipes) then you might want to consider the automatic file-based recipe handling of TransSECS (by setting transsecs.recipemanager=1 in the ErgoTechConfiguration.properties of your project deployment). See [[Recipe Management]]. These examples use the PLC Database Tool (PLCDatabaseTool.tsx) example project, which uses SQLite for the database ("RecipeTest.sqlite". "PLCDatabaseTool" is an extension of PLCTool which is an extension of GEMTool. Note: Just as other Devices tool projects, databases can only be tested in deployment. You will need to ensure that the JDBC driver (jar) referenced by the database device configuration is added to the deployed project. This can be done automatically when the project is built by placing this jar in the project's "addons" directory. For SQLite databases, which is a file based database, also put the database file (i.e., RecipeTest.sqlite) into the "addons" directory. Otherwise copy the SQLite database file into the deployment directory after the project is built. ** Setup ** The example project contains the necessary recipe handling message templates and their replies. These are S7F3, S7F4, S7F5, S7F6, S7F17, S7F18, S7F19, and S7F20. This project adds a Database Device to the "Devices" node. Several database components are used by the scripts for the recipe messages. {{:pasted:20200709-003817.png?400}} Right Click on the Recipe_DB node to add: Database Read called "CheckSinglePPIDExists" (used by S7F3 and S7F5) Database Read called "GetRecipe" (used by S7F5) Database Write called "AddRecipe" (used by S7F3) Database Write called "DeleteRecipes" (used by S7F17) Database Read called "CheckRecipeListToDelete"(used by S7F17) Database Read called "GetPPIDList" (used by S7F19) Most of these will have the sql statement set by the scripts, so do not configure anything for this property. The only component which the SQL Statement can be configured is the "GetPPIDList". The SQL Statement for this is "SELECT ppid from recipes;". ** The Recipe Database Table Schema ** One table is required in the database which contains the PPID (recipe name) and the PPBody (recipe data) of the recipe. If your database table contains individual columns for the recipe data you will need to combine these columns to create the PPBody for the S7F3 and parse the incoming S7F5 data as needed to update or insert individual values into your table columns for the PPID. In the following examples we treat the PPBody as a single column of data, PPID as a unique id, both as Text. CREATE TABLE "recipes" ( "PPID" TEXT NOT NULL UNIQUE, "PPBODY" TEXT ) **S7F19 Handling Example** This is an example of a script for S7F19 to send the S7F20 reply with recipe list. As with all messages in your TransSECS project, the script will be called when the message is received from the host. var TransSecsController = Java.type("com.ergotech.transsecs.secs.TransSecsController"); print("S7F19 received, getting PPID list from database"); try { ppidList = /Devices/RecipeDB_Servers/RecipeList->retrieveValueObject(); // <--- SQLite Database Manger renamed "RecipeDB" in the project, "RecipeList" is a Database Column //ppidList is an array value object //print(ppidList); //generate response message S7F20 PLCDatabaseTool=TransSecsController.findController("PLCDatabaseTool"); reply = PLCDatabaseTool.getMessageBean("S7F20"); //<--- Message called "S7F20" must be defined in messages list reply.setRecipeList(ppidList); reply.sendMessage(); } catch (e) { print("Error in S7F19 handler script: \n"+e.stack); print(e); } **S7F3 Handling Example** The SF73 message is sent from the host to add a recipe to the database. If the PPID already exists, send a message to the host rejecting the recipe. Alternatively, if the tool allows replacing an existing recipe, just update the PPBody without an error. In this example we check if the recipe already exists and send an error reply if it does. //Recieved an S7F3 //Host has sent a recipe to be stored var TransSecsController = Java.type("com.ergotech.transsecs.secs.TransSecsController"); var ArrayValueObject = Java.type("com.ergotech.vib.valueobjects.ArrayValueObject"); var StringValueObject = Java.type("com.ergotech.vib.valueobjects.StringValueObject"); var SecsFormat20 = Java.type("com.ergotech.secs.SecsFormat20"); var SecsFormat00 = Java.type("com.ergotech.secs.SecsFormat00") try { print("S7F3 received"); tool=TransSecsController.findController("PLCDatabaseTool"); msg=tool.getMessageBean("S7F3"); ppid=msg.getPPID(); ppbody=msg.getPPBody(); //check that this recipe does not already exist /Devices/RecipeDB_Servers/CheckSinglePPIDExists->setSQLStatement("select count(*) from recipes where ppid='"+ppid+"';"); exists=/Devices/RecipeDB_Servers/CheckSinglePPIDExists->retrieveValueObject(); if (exists!=0) { //send S7F4 with ACKC7=1, recipe already exists (we do not allow updates, only inserts) reply=tool.getMessageBean("S7F4"); reply.setACKC7(Java.to([1],"byte[]")); reply.sendMessage(); } else { //store recipe sqlStatement="INSERT into recipes (ppid,ppbody) values ('"+ ppid +"','" + ppbody + "');"; /Devices/RecipeDB_Servers/AddRecipe->setSQLStatement(sqlStatement); /Devices/RecipeDB_Servers/AddRecipe->executeStatement(); //send reply reply=tool.getMessageBean("S7F4"); reply.setACKC7(Java.to([0],"byte[]")); reply.sendMessage(); } } catch (e) { print("Error in S7F3 handler script: \n"+e.stack); print(e); } **S7F5 Handling Example** The SF75 message is sent from the host to request a recipe. If the PPID does not exist, send a message to the host rejecting the request. //a recipe is requested var TransSecsController = Java.type("com.ergotech.transsecs.secs.TransSecsController"); var ArrayValueObject = Java.type("com.ergotech.vib.valueobjects.ArrayValueObject"); var StringValueObject = Java.type("com.ergotech.vib.valueobjects.StringValueObject"); var SecsFormat20 = Java.type("com.ergotech.secs.SecsFormat20"); var SecsFormat00 = Java.type("com.ergotech.secs.SecsFormat00"); try { tool=TransSecsController.findController("PLCDatabaseTool"); msg=tool.getMessageBean("S7F5"); ppid=msg.getPPID(); /Devices/RecipeDB_Servers/CheckSinglePPIDExists->setSQLStatement("select count(*) from recipes where ppid='"+ppid+"';"); exists=/Devices/RecipeDB_Servers/CheckSinglePPIDExists->retrieveValueObject(); if (exists==1) {//send recipe to host //get the ppbody /Devices/RecipeDB_Servers/GetRecipe->setSQLStatement("SELECT PPBody from recipes where PPID='"+ppid+"';"); ppbody=/Devices/RecipeDB_Servers/GetRecipe->retrieveValueObject(); reply=tool.getMessageBean("S7F6"); //get the list and add the ppid and ppbody items //make a new empty list to initialize the message recipeData = new SecsFormat00(); recipeData.addElement(new SecsFormat20(ppid)); recipeData.addElement(new SecsFormat20(ppbody)); reply.setrecipeDataFormat(recipeData); reply.sendMessage(); } if (exists==0) { //send reply with empty list, the ppid does not exist reply = tool.getMessageBean("S7F6"); recipeData = new SecsFormat00(); //empty list reply.setrecipeDataFormat(recipeData); reply.sendMessage(); } } catch (e) { print("Error in S7F5 handler script: \n"+e.stack); print(e); } **S7F17 Handling Example** The SF717 message is sent from the host to delete one or more PPIDs. If the PPID does not exist, send a message to the host rejecting the request, otherwise delete the list of recipes. //Recieved an S7F17 //Host has sent a list of one or more recipes to delete. An empty list will delete all recipes. var TransSecsController = Java.type("com.ergotech.transsecs.secs.TransSecsController"); var ArrayValueObject = Java.type("com.ergotech.vib.valueobjects.ArrayValueObject"); var StringValueObject = Java.type("com.ergotech.vib.valueobjects.StringValueObject"); var SecsFormat20 = Java.type("com.ergotech.secs.SecsFormat20"); var SecsFormat00 = Java.type("com.ergotech.secs.SecsFormat00"); var String = Java.type("java.lang.String"); try { print("----------------------------------"); print("S7F17 received"); tool=TransSecsController.findController("PLCDatabaseTool"); msg=tool.getMessageBean("S7F17"); deleteList=msg.getRecipeListToDelete(); size=deleteList.size(); print("toDelete size:"+size); if (size==0) {//delete all recipes //print("deleting all recipes"); /Devices/RecipeDB_Servers/DeleteRecipes->setSQLStatement("DELETE from recipes;"); /Devices/RecipeDB_Servers/DeleteRecipes->executeStatement(); //get the result for the execute result = /Devices/RecipeDB_Servers/DeleteRecipes->getLongValue(); //result=0 no rows changed, >0 number of rows changed (deleted) print(result+" recipes deleted"); //send reply reply=tool.getMessageBean("S7F18"); reply.setACKC7(Java.to([0],"byte[]")); reply.sendMessage(); } else { //deleting one or more recipeList=deleteList.toString().replace('[','').replace(']',''); sqlStatementRecipeList=recipeList.split(",").map(function(ppid) { return "'"+ppid.trim()+"'";}).join(","); //print("sql list: "+sqlStatementRecipeList); //check that all recipes exist, otherwise send an error message sqlStatement="SELECT count(*) from recipes where ppid in ("+sqlStatementRecipeList+");"; /Devices/RecipeDB_Servers/CheckRecipeListToDelete->setSQLStatement(sqlStatement); count=/Devices/RecipeDB_Servers/CheckRecipeListToDelete->retrieveValueObject(); // print("count is:"+count+", expected "+size); if (count!=size) { //one or more recipes was not found //print("one or more of the recipes was not found in the database"); reply=tool.getMessageBean("S7F18"); reply.setACKC7(Java.to([4],"byte[]")); //ppid not found reply.sendMessage(); } else { //everything checks out OK, so delete the list //print("deleting the list"); sqlStatementDeleteList=recipeList.split(",").map(function(ppid) { return "ppid='"+ppid.trim()+"'";}).join(" or "); sqlStatement="DELETE from recipes WHERE ("+sqlStatementDeleteList+");"; /Devices/RecipeDB_Servers/DeleteRecipes->setSQLStatement(sqlStatement); /Devices/RecipeDB_Servers/DeleteRecipes->executeStatement(); //get the result for the execute result = /Devices/RecipeDB_Servers/DeleteRecipes->getLongValue(); //result=0 no rows changed, >0 number of rows changed (deleted) print(result+" recipes deleted"); reply=tool.getMessageBean("S7F18"); reply.setACKC7(Java.to([0],"byte[]")); reply.sendMessage(); } } } catch (e) { //if the code throws here, there was a database error and delete command was not executed //suggestion: trigger an alarm in the tool if the database status needs to be checked print("Error in Delete Recipe List script: \n"+e.stack); //send the reply with an error reply=tool.getMessageBean("S7F18"); reply.setACKC7(Java.to([6],"byte[]")); //other error (database) reply.sendMessage(); } ---- ====Host Recipe Lookup==== ===Setup=== In this example the host receives an event from the tool which prompts a database lookup based on a Lot ID. This example uses a SQLite database called "LotRecipes.sqlite" which has two columns, lotid and ppid. Start with the GEMHost and add a SQLite database device server: {{:pasted:20200708-153247.png?300}} Rename the "SQLite Database Connection" to the simpler "RecipeDB". Configure the device server to use the file "LotRecipes.sqlite" {{:pasted:20200708-153753.png?600}} To prepare for this example, create and populate the database with few test records: CREATE TABLE IF NOT EXISTS "recipes" ( "LOTID" TEXT UNIQUE, "PPID" TEXT ); INSERT INTO "recipes" ("LOTID","PPID") VALUES ('XXX','Recipe1'); INSERT INTO "recipes" ("LOTID","PPID") VALUES ('YYY','Recipe2'); COMMIT; The last task on the setup is to add a "Database Read" component to the RecipeDB Device. Right click on the RecipeDB and select "Database Read". Add this and rename it to the simpler "DatabaseRead". {{:pasted:20200708-155549.png?200}} ===Lookup Data on Event=== To get the PPID for LotID when Event "LOADED" is received, edit the script for the "LOADED" event in TransSECS. The LotID needs to be passed into the script or use the VID "LOTID" for this value. Additional examples for obtaining the LotId are at the end of this document. For example, getting the value from an [[devices_databases#host_-_additional_report_database_examples|incoming report]], or from the VID value [[devices_databases#host_-_getting_the_value_of_any_vid|VID value]] try{ lotid="XXX"; //get this from the VIDs LOTID or from elsewhere //should check that this lot id is in the database //set the SQL Statement /Devices/RecipeDB_Servers/DatabaseRead->setSQLStatement("SELECT PPID from recipes where LOTID="+lotid+";"); //trigger the lookup ppid=/Devices/RecipeDB_Servers/DatabaseRead->retrieveValueObject(); //use this ppid and the lotid in the PPSELECT host command and send the message var TransSecsController = Java.type("com.ergotech.transsecs.secs.TransSecsController"); host=TransSecsController.findController("GEMHost"); ppselect = host.getMessageBean("HostCommandPPSELECT"); ppselect.setCP2Value(lotid); ppselect.setCP1Value(ppid); reply=host.getMessageBean("HostCommandReply"); reply=ppselect.sendMessageAndWait(); } catch (e) { print("Error in LOADED Event handler script: \n"+e.stack); print(e); } //finished ===Test=== Test this script by running the GEMTool. Let the host establish communications and let the tool go to remote/online. Send the LOADED event from the tool. The host should send the PP-SELECT host command to the tool with the lotid "XXX" and the ppid "Recipe1". ====Adding Drivers for Databases==== TransSECS uses the JDBC standard for database connectivity. There is JDBC support for just about every database - Microsoft SQL Server, Oracle, MySQL, MariaDB, SQLite, PostgresSQL and many more. Each database manufacturer provides a jar - usually a free download - with JDBC support. The JDBC jar should be placed in the "resource" folder in the TransSECS Builder directory. For example, for Microsoft SQL Server, the driver can be downloaded from here: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 The driver jar - currently mssql-jdbc-8.4.1.jre8.jar - must be extracted from the zip that is downloaded and placed in the folder C:\Users\Public\ErgoTech\TransSECSDevicesTrial\MIStudioSuite\TransSECS\Builder\resources (if you did not install in the default location, choose the path to your installation). Be sure to extract the jre8 jar, not a later one (jre11 or jre14). The jar will automatically be copied to the deployment folder of your project and will be used when the project is run. In the DatabaseConnectionManager the configuration for this driver is in the associated documentation. You will need to know the name of your database as well as provide credentials (username and password) for the database and tables you plan to use. The database class name is com.microsoft.sqlserver.jdbc.SQLServerDriver The database url is jdbc:sqlserver://hostname:1433;databaseName=mydatabasename for example to access the database "testdb" on the server "192.168.5.157" you would use this URL jdbc:sqlserver://192.168.5.157:1433;databaseName=testdb If you're having trouble with either finding the JDBC jar or knowing the format of the parameters to used to access the database, please contact us for help. ====Host - Saving Reports to a Database==== Saving SECS/GEM data can be a challenge. Each tool produces different data and the data can change as process engineers decide what must be collected. TransSECS provides some very easy options for storing data from your tool. The most useful structure to store is a "Report". Regardless of how these are received, in Event Messages or polled the structure is the same and can easily be mapped to an SQL database. In TransSECS, after adding a database device, you right-click and add an "Historical" server. {{:pasted:20200920-153350.png}} Configure this with the name of the table to which to store the report data. If you also set "Create Table" to true, TransSECS will manage the database schema, automatically adding columns to the table as you add data. (If you remove a data point, you must remove the column manually - doing so automatically could result in a loss of data). Creating reports is easy in TransSECS. You can then just link the report to the "Historical" server. That's it. The data from the report will be saved to the database whenever the report is received. {{:pasted:20200920-153129.png}} As shown in the example, you can also add some data that will be stored with the report. Here we add the operation id ("OP_ID") to the data so that it's saved with the report. ====Host - Additional Report/Database Examples ==== It's common to have a "Loaded" event of some kind provide a LotID that can be used to retreive a recipe. For example, a report like this (available with the StandAloneGEMTool simulator): {{:pasted:20210907-183154.png}} In the script on that report, it's easy to get the elements of the report: lotId = incomingValue.getProperty("MAP").get("LOTID"); // the name must match exactly. It is case sensitive waferCount = incomingValue.getProperty("MAP").get("WaferCount"); This applies to all elements of a received report. We can use these values for different purposes in the script. First, lets store them to a database. lotId = incomingValue.getProperty("MAP").get("LOTID"); // the name must match exactly. It is case sensitive waferCount = incomingValue.getProperty("MAP").get("WaferCount"); // This assumes that you have a Database Write called "LoadedEventWrite" created un the "SQLServer" node of your project. // set the SQLStatement /Devices/SQLServer_Servers/LoadedEventWrite->setSQLStatement("insert into loadedevent (wafercount, lotid) values( " + waferCount + ",'" + lotId + "')"); // execute the statement try { /Devices/SQLServer_Servers/LoadedEventWrite->executeStatement(); // update the table. } catch ( e ) { print (e); } An alternate approach that uses a fixed SQL satatement. This approach is a little more efficient which may be important if the report is being received frequently. var ArrayValueObject = Java.type("com.ergotech.vib.valueobjects.ArrayValueObject"); var StringValueObject = Java.type("com.ergotech.vib.valueobjects.StringValueObject"); var LongValueObject = Java.type("com.ergotech.vib.valueobjects.LongValueObject"); lotId = incomingValue.getProperty("MAP").get("LOTID"); // the name must match exactly. It is case sensitive waferCount = incomingValue.getProperty("MAP").get("WaferCount"); avo = new ArrayValueObject(); avo.add(new LongValueObject(waferCount)); avo.add(new StringValueObject(lotID)) // execute the statement // The SQL statment must be set in the LoadedEventWrite server and should be something like: // insert into loadedevent (wafercount, lotid) values(?,?) // the values in the ArrayValueObject will be substitued in order try { /Devices/SQLServer_Servers/LoadedEventWrite->setValueObject(avo); // update the table. } catch ( e ) { print (e); } Now let's look up our recipe. Imagine a simple lookup: select recipe from recipetable where lotid = ? Create a Database Read server, under you database node (here SQLServer) and call it "RecipeLookup" var StringValueObject = Java.type("com.ergotech.vib.valueobjects.StringValueObject"); lotId = incomingValue.getProperty("MAP").get("LOTID"); // the name must match exactly. It is case sensitive // execute the statement try { /Devices/SQLServer_Servers/RecipeLookup->setStringValue(lotId); } catch ( e ) { print (e); } print ("Recipe : " + /Devices/SQLServer_Servers/RecipeLookup->getStringValue()); This gets you the recipe to use in, for example a PP-SELECT command (see [[devices_databases#lookup_data_on_event|PP-Select Example]] ====Host - Getting the value of any VID ==== You can get the value of any VID in any script. In report and event scripts the VIDs are always updated before the script is called and so will have the value received in that message Here's an example of getting the LotId. var TransSecsController = Java.type("com.ergotech.transsecs.secs.TransSecsController"); // get the interface. Replace "GEMHost" with the name of your host. var secsInterface=TransSecsController.findController("GEMHost"); // use the name of the TransSECS Project gemHost=secsInterface.getGemHostModel(); lotidServer = gemHost.getVID("LOTID"); /// get the value from the VID var lotID = lotidServer.getCurrentValue().getStringValue(); ====Convert CLOCK VID to SQL (ISO) ==== The time on semiconductor tools is often not accurate and so TransSECS timestamps all received events and values with the system clock. When you add an "Historical" server to save the values of reports, it's this timestamp that is saved. However, it can be useful to save the tool time, which is provided in the GEM variable "CLOCK". Most database won't willingly parse the SECS/GEM "CLOCK" format (yyyyMMddHHmmssSSS - eg ) and so that needs to be converted to something SQL friendly. Here's a conversion to ISO format: CLOCK = incomingValue.getProperty("MAP").get("CLOCK"); var SimpleDateFormat = Java.type("java.text.SimpleDateFormat"); sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS"); // yyMMddHHmmss for 12 byte format clockValue = sdf.parse(CLOCK.getStringValue()); isoSdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ"); inIso = isoSdf.format(clockValue); print ("ISO " + inIso); The value is something like this: 2021-09-09T18:24:16.099-0400 ====Configuring the Database Servers Node ==== The Database Server Node can be configured from within a script. The node itself is available as /Devices/{NAME} in this example in this document that would be: /Devices/RecipeDB You can call methods on this node in the usual way. Properties available correspond to the properties in the configuration sheet, for example: setUsername setPassword setUrl setDriverClassName For example, you could set the username with this call: /Devices/RecipeDB->setUsername("myUserName");