MES Objects

NV SQL Adapter

Details
Category: Name Value Configuration

With the SQL Adapter NV you are able to write or read to/from an external SQL Database.  It is executed at the moment the Step is signed off or when the Step has begun Processing with the Track In option.

Example Overview

Lets begin with brief example overview.  We have an external database called 'EZ_SQLAdapter_Sample'.  We want to pull the data from the 'Description' column into EZ-MES based on the Unique Part Record ID (UID).

SQLSampleDB-Adapter

This is what the final result would like like on the Traveler of a properly configured SQL Adapter Name-Value.

SQLAdapterNV_Traveler

 

Configuration for Pulling Values from an External Database

You will need to have at least 1 additional Name-Value aside from the SQL Adapter NV for proper configuration.  Our examle will use a Text NV as the entry we are writing to.

Name-Value [Text]: Data from outside SQL DB

This is just a basic Text NV.  Our SQL Adapter NV will be writing the external data to here.

SQLAdapter-TextNV

 

Name-Value [SQL Adapter]: Data from outside SQL DB

This SQL Adapter NV has five main points of configuration.  

You can optionally HIDE this NV since its only function is to pull data and place it into another NV.  If it is not hidden the millisecond timestamp will be displayed. 

 

1. CONNECTION STRING

An example of a connection string:

  • Server=ServerName.local;Database=master; User ID=sa;Password=XXXXX;Trusted_Connection=False;Connect Timeout=600;Pooling=true;
  •  
  •  

This consists of the following parts:

  • Server: The server where the database is located.
  • Database: The name for the database you want to address
  • User ID: Your username for the database
  • Password: The password to logon the user to the database

 

2. SQL STATEMENT

Depending on the circumstances, you can use either a SELECT or UPDATE SQL Statement.  Our particular demonstration will be pulling data from a remote SQL Database into EZ-MES so a SELECT Statement is necessary.  If we were using EZ-MES to populate data in a remote SQL Database, then we would be using a UPDATE SQL Statement.  You can see a sample of the UPDATE SQL Statement at the bottom of this help document.

Available variables that can be used in the WHERE clause.

  • 'UID' or 'ID' - Contains Part Record Unique ID
  • 'sJob' - Contains Part Record Internal Job number
  • 'user' - User who initiated the button action.
  • 'date' - Current date/time button action is initiated.

 

3. Variable NV

Choose the NV on the current Step that will be populated by the remote SQL Database.  

 

4. Column Name

This is the Column from your SQL Statement of which your data will be returned.  The return result will populate the given NV chosen in the Variable NV.

 

5. Added Results (variables)

This listbox will show all the variables added from the 'Variable NV' and 'Column Name' combinations given.

 

SQLAdapterNV

 

SQL Adapter Use

Once the Traveler gets to the Step containing the SQL Adapter, you will then see it polulate as it was configured.  The image below demonstrates this.

Sample

 

 

Sample UPDATE SQL Statement

This SQL statement writes the internal job ID of the Part Record to a table called ‘MoveIn’ it writes an additional numeric value of 1 to the second column.

In the external database you will need a table with the following:

  • Name of the table: tbl_MoveIn
  • Name of the first column: sPR (nvarchar(50), not null)
  • Name of the second column: MoveIn (int, not null)

If this is defined, you can use the following SQL statement:

UPDATE tbl_MoveIn SET MoveIn=1 WHERE sPR=''

IF @@ROWCOUNT = 0 INSERT INTO tbl_MoveIn VALUES ('', 1)

SELECT 'AAA' AS AAA

This consists of the following:

UPDATE tbl_MoveIn SET MoveIn=1 WHERE sPR=''

We will first try to update the table. This is to take care with the case that the PR record is already in the table:

  • Tbl_MoveIn: The name of the table you are updating:
  • SET MoveIn=1: Writes the value 1 to the MoveIn column.
  • WHERE sPR=’’
  1. Will write to the row where the sPR value equals to the current sJob value for the Part Record
  2. : Will be replaced by the Part Record internal Job ID (e.g.: sPR12-000000987)

IF @@ROWCOUNT = 0 INSERT INTO tbl_MoveIn VALUES ('', 1)

The IF statement checks if the update was not successful (no rows are updated). This means that the Part Record was not part of the table. If this is the case it does the following:

  • INSERT INTO tbl_MoveIn VALUES ('', 1)
  • This inserts a new Row into the table with the following values:
  • i.      sPR: The internal Part Record ID (e.g.: sPR12-000000987)
  • ii.      MoveIn: 1

SELECT 'AAA' AS AAA

Statement to make sure the total SQL statement returns something (otherwise it will crash) So this does not have any meaning.