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.

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 the query produces records from the database,

      • However, if the Invert query result option is checked, the system will trigger the alarm only if no records are received

  • 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/5xh41xhm925jbz8/installDbLibrariesMySQL8.zip?dl=1

following the instructions on Install Update Package Procedure 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:

• 1. Use an SFTP program like FileZilla, WinSCP or other and connect to the device.

  2. Upload the desired library into the folder: /home/keonn/kernel/module/app.AdvanNet/lib

  3. Reboot the device.

  4. After the reboot, AdvanNet will have loaded the new library and will be ready to use it.

Downloads