SQL Server connection
Subiekt GT, Subiekt nexo, Navireo, Comarch ERP Optima and WAPRO Mag keep their data in Microsoft SQL Server. The price checker connects to this server directly over the network and sends one read query per scan. This page covers what these programs have in common; program-specific fields are on each program's page.
Common fields
| Field | What to enter |
|---|---|
| Address | The SQL Server address, with a port or an instance name — formats below. |
| User | A SQL Server login (not a Windows account). Use a dedicated read-only login — how to create it. |
| Password | The password of that login. |
| Database | The name of the company database in SQL Server, e.g. Nexo_MyCompany or CDN_MyCompany. |
| Encrypt connection (SSL) | Checked by default — details. |
| Search by product code (SKU) | Search by the product symbol / code / index. On by default. |
| Search by EAN code | Search by barcode. On by default. |
With both search switches on, the app finds the product both by barcode and by symbol (when the customer scans a shelf label with the symbol). When both are off, the app searches by symbol only — the same as with SKU alone.
Address format
| Entry in the Address field | Meaning |
|---|---|
192.168.1.10 | Default instance, port 1433. |
192.168.1.10:1433 or 192.168.1.10,1433 | The given port. |
192.168.1.10\INSERTGT | Named instance — the port is provided by the SQL Server Browser service (UDP 1434). |
192.168.1.10\INSERTGT,50123 | Named instance with a fixed port — the Browser is not needed. |
You can copy the address the way the program shows it (e.g. SERVER\OPTIMA). It is safer, however, to enter the IP address instead of the computer name: an Android price checker does not always resolve computer names from a Windows network. The server should have a fixed IP address (or a reservation in the router), because after an address change the price checker stops connecting.
When the address contains a port, the app connects straight to that port and does not ask the SQL Server Browser service.
What to check on the server
- The TCP/IP protocol is enabled for the instance (SQL Server Configuration Manager → SQL Server Network Configuration → protocols). In Express editions it is often disabled by default. Restart the SQL Server service after the change.
- The instance port. The default instance listens on 1433. A named instance usually has a dynamic port — the simplest way is to set a fixed port for it (TCP/IP properties → IPAll) and enter it in the address.
- Windows Firewall on the server allows inbound TCP traffic to the SQL Server port (and UDP 1434 if you rely on SQL Server Browser).
- SQL Server authentication is enabled (mixed mode: SQL Server and Windows). The app logs in with a SQL login and password only.
Connection encryption (SSL)
- Checked (default) — the app encrypts the connection if the server supports it; if not, it connects unencrypted. The server certificate is not validated, so the self-signed certificate that SQL Server creates by itself also works.
- Unchecked — the connection is not encrypted.
If the server forces encryption and the box is unchecked, the connection test shows The server closed the connection. If the server requires encryption, check “Encrypt connection (SSL)”.
In the Database field you can add jTDS driver parameters after a semicolon, e.g. Nexo_MyCompany;ssl=require or CDN_MyCompany;loginTimeout=30. A parameter added this way overrides the default (ssl from the checkbox, loginTimeout=15, socketTimeout=15).
How the app keeps the connection
- The connection opens when the app starts and after the settings are saved. During that time the screen shows Loading and scans are ignored.
- If the connection fails, the screen shows No connection to the data source and Ask the store staff for help. Every following scan tries to connect again — no app restart is needed, e.g. when the WiFi came up later than the price checker.
- If a query fails (dropped connection, server restart), the app opens a new connection and repeats the query. Only a second failure shows Data download error on the screen.
- After more than 2 minutes without scans, the connection is reopened before the query, because firewalls and routers often silently drop idle connections.
- The app waits 15 seconds for the connection to open and 15 seconds for the server's response.
Read-only SQL login
The price checker hangs in the shop aisle, and the login and password are stored in its memory. Never enter sa or any other administrator account on it — anyone who gets hold of the device would have full access to the company database. Create a dedicated login that can only read the tables the app needs.
Run the script in SQL Server Management Studio as a server administrator. Replace the database name, the password and the list of tables with the one from your program's page:
USE [master];
CREATE LOGIN [pricechecker] WITH PASSWORD = N'Put-a-long-password-here', CHECK_POLICY = ON, DEFAULT_DATABASE = [CompanyDatabase];
USE [CompanyDatabase];
CREATE USER [pricechecker] FOR LOGIN [pricechecker];
-- only the tables the price checker reads:
GRANT SELECT ON dbo.tw__Towar TO [pricechecker];
GRANT SELECT ON dbo.tw_Cena TO [pricechecker];
GRANT SELECT ON dbo.tw_Parametr TO [pricechecker];
Tables needed by each program:
| Program | SELECT permission on tables |
|---|---|
| Subiekt GT | dbo.tw__Towar, dbo.tw_Cena, dbo.tw_Parametr |
| Navireo | dbo.tw__Towar, dbo.tw_Cena, dbo.tw_Parametr, dbo.tw_KodKreskowy |
| Subiekt nexo | ModelDanychContainer.Asortymenty, ModelDanychContainer.PozycjeCennika, ModelDanychContainer.Cenniki, ModelDanychContainer.JednostkiMiarAsortymentow, ModelDanychContainer.KodyKreskowe |
| Comarch ERP Optima | CDN.Towary, CDN.TwrCeny |
| WAPRO Mag | dbo.ARTYKUL, dbo.CENA_ARTYKULU, dbo.CENA |
Do not grant db_owner or db_datareader — the latter gives read access to the whole database, including customer data. After a major program update, run the connection test; if a permission error appears, run the GRANT statements again.