SQLService

Introduction

There is a Service in AdvanNet that allows sending data from the device to a SQL compatible database.

The information is sent in case of an event or a message (Autonomous vs Sequential modes)

Configuration

The options are:

• • Enabled: self explanatory. if false the service won't work.

  • driverClass: describes the class of the driver to work with the connectionString. recommended values (com.mysql.jdbc.Driver for myql, oracle.jdbc.driver.OracleDriver for oracle db, org.sqlite.JDBC for sql and so on)

  • connectionString: the connection string in a way the driverclass can read it. Put the user and password in the string as well.

  • JSON config: check the following subsection

  • AllowDeletes? Unless specified we don't allow to execute "Delete from" statements

  • EPC encoding: needed to decode the epc_hex value.

JSON config*

This is a JSON List of Maps, each Map with the following elements:

• event: fully qualified name of the event we want to use

• prepared_query: an SQL expression with parametrized values with ? marking a placeholder.

• params: an arraylist of strings with the context variables to put in each placeholder of the prepared_query

• native_query: an alternative to prepared_query, sql query with the context variables that will be replaced with the values and executed directly against the database.

Before entering the JSON config in the device it is recommendable to double check it's correctly formed by using a JSON validator like this one: https://jsonlint.com/

You can find JSON examples in the MYSQL subpage.

Possible events

• TAG_READ events: as generated from inventory operations

• AdvanPay events:

  • TAG_ADPY_PAYMENT

  • TAG_ADPY_RETURN

  • TAG_ADPY_READ

• EAS events

  • TAG_ALARM

  • TAG_ALARM_ANTENNA_1

  • TAG_ALARM_ANTENNA_2

  • TAG_ALARM_ANTENNA_3

  • TAG_ALARM_ANTENNA_4

The available context injected into the body javascript expression depends on the EVENT TYPE

TAG_READ context

Available context variables are

• ctx_devid: device ID

• ctx_devip: device IP

• ctx_devmac: device MAC address expressed as 6b:31:58:87:a2:3c

• ctx_epc: the hexadecinal read EPC

• ctx_sku: the TAG EPC SKU if the tag encoding is known

• ctx_serial: the TAG EPC SKU serial if the tag encoding is known

• ctx_tid: the hexadecimal read TID (may be empty)

• ctx_antenna: the integer antenna port

• ctx_uri: the read URI of the EPC (may be empty)

• ctx_rssi: the integer read RSSI value

• ctx_utc: as the Unix UTC time stamp of the read

• ctx_time: as the Unix time stamp of the read with the local TimeZone offset

• ctx_utc_formatted: as the formatted UTC time of the read expressed as yyyyMMddHHmmss.SSS

• ctx_time_formatted: as the formatted time of the read the local TimeZone offset expressed as yyyyMMddHHmmss.SSS

Other events context

All other events share the same context variables

• ctx_devid: device ID

• ctx_devip: device IP

• ctx_devmac: device MAC address expressed as 6b:31:58:87:a2:3c

• ctx_type: the event type

• ctx_subtype: the event sub-type. For example the alarm type.

• ctx_epc: the event EPC (may be empty)

• ctx_sku: the event EPC SKU in case theEPC encoding is known (may be empty)

• ctx_serial: the event EPC SKU serial in case the EPC encoding is known (may be empty)

• ctx_uri: the event EPC URI (may be empty)