EAS SQL Alarm

SQL alarm configuration

AdvanNet can be configured to consult a database to see if a product has been paid or not and trigger an alarm. In this example, we useMySQL.

Any SQL database with an existing Java driver should work. Amongst the known working SQL engines are: PostgeSQL, MicrosoftSQL, HyperSQL, Oracle, etc.

This Configuration has been tested with AdvanNet-2.1.4 and AdvanReader-100. It also works with AdvanReader-150.

Requirements

- Install MySQL.
- Create a new Schema, for example, alarms.
- Once the schema is created, there has to be one table named sale_info with at least two columns: EPC and paid (0: Not paid, 1: Paid)
- It should have, at least, one entry, for example:

INSERT INTO `advannet`.`sale_info` (`epc`, `paid`) VALUES ('14270e4714270e4714270e47', 1);

- Add the MySQL connector to the $ADVANNET/lib folder, download it from here.

AdvanNet configuration

- Change to a Alarm mode configuration.
- Go to the RF & Antenna Configuration tab.
- Set Read mode to SQL_EAS_ALARM.
- Java driver class:
  - MySQL: com.mysql.jdbc.Driver
  - MSSQL: net.sourceforge.jtds.jdbc.Driver
  - PostgreSQL: org.postgresql.Driver
- Connection string:
  - MySQL: jdbc:mysql://<ip_or_hostname>/<Schema>
    - e.g. jdbc:mysql://localhost/alarms
  - MSSQL: jdbc:jtds:sqlserver://<ip_or_hostname>:1433/<Schema>
  - PostgreSQL: jdbc:postgresql://<ip_or_hostname>:5432/<Schema>
- DB username: The username that was configured in the connection on the SQL database.
- DB password: The password that was configured for the connection to the SQL database.
- Alarm query: select paid from sale_info where (epc=${epc} and paid=0)
  - It depends on the columns created and the table name.
  - The ${epc} is the epc read by AdvanReader.
  - An EPC will trigger an alarm if AdvanNet receives 1 response from the Alarm query, if it receives 0 or more than 1, it will not trigger the alarm.
- Apply the configuration by clicking the Apply Config button.

- Go to the Events & Actions tab.
- Configure an event for a Tag Alarm by clicking on the Update action button.
  - The alarm can be a buzzer action:

- The alarm can be also a GPO action:

SQL libraries

For AdvanNet to work with any SQL database, it needs a java library, please install the following upgrade package:

https://www.dropbox.com/s/dbw75kqgse8490d/installDbLibraries_signed.jar?dl=0

following the instructions on Advannet Updater page

If your database of choice uses a driver not installed in the package above, please follow the instructions below to upload the desired library to the device:

Upload library into the Keonn reader/system

To be able to upload a new library to a Keonn reader/system, SSH credentials are needed. For that, please follow this wiki section.

Once the SSH credentials are obtained: