The direct MSSQL path

Audience: developers & AI agents · Scope: BcHelper::getInforDb() and the reporting queries that use it · Last reviewed: 2026-07-20

TL;DR — For anything report-shaped — costing, schedules, saved SQL exports — the site talks straight to Infor's SQL Server database over PDO/sqlsrv, bypassing the IDO layer entirely. The connection is reused within a process, health-checked before reuse, and reconnected with bounded retries on transient errors. It depends on the site-to-site VPN being up.

Implementation: data/customization/system_overrides/BcHelper.php, getInforDb().

Connecting

$dsn = 'sqlsrv:Server=<infor_db_ip_address>\<infor_db_instance_name>[,<infor_db_port>]'
     . ';Database=<infor_db_database_name>'
     . ';TrustServerCertificate=yes;Encrypt=no;LoginTimeout=<wait>';

After connecting, the session is put into the right Infor site context:

SetSiteSp 'BETACALC', NULL

Do not skip that call in new code paths — without it queries run against the wrong site context.

PDO::ATTR_ERRMODE is ERRMODE_EXCEPTION, so failures throw rather than returning false.

TrustServerCertificate=yes + Encrypt=no means the transport is not verified or encrypted at the TLS layer — the confidentiality guarantee comes from the VPN, not from the SQL connection. Worth knowing before anyone moves this off the VPN.

Connection reuse and retry

• The PDO handle is kept in a static (self::$inforDb) and reused for the life of the process.

• Before reuse it is health-checked; if the check throws, the handle is dropped and a reconnect happens. This matters because a long-running CLI script will otherwise hold a connection that the server or the VPN has already dropped.

• Reconnection loops up to $maxAttempts, and only retries transient SQLSTATEs:

  SQLSTATE	Roughly
  08S01, 08S02	communication link failure
  HYT00, HYT01	timeout expired
  40001	deadlock / serialization failure
  40501	service busy / throttled
  40613	database unavailable

• Anything else — bad credentials above all — fails immediately rather than burning the retry budget.

• A post-connect SELECT 1 confirms the new handle before it is cached.

Who uses it

Consumer	What for
models/bccostingsheet.php	Margin Analysis / costing sheet
models/sqlexports.php	the Infor App DB endpoint of the SQL → Google Sheet exports
models/bcproductionschedule.php, bcfabricationschedule.php, bcassemblyschedule.php, bcwarehousekittingdata.php, bckittingpicklist.php	the schedule exports

Most of these run on a schedule — see Scheduled jobs.

Logging & monitoring

Log type	What lands there
custom_infor_pdo	connection attempts, the SQLSTATE and message of each failure, health-check failures and reconnects

There are no CloudWatch metrics on this path, unlike the REST API. A silently failing MSSQL job shows up only in the log and in stale output, so don't assume an alert exists. (The costing sheet is the exception — it emits its own Margin-Analysis-Tool failure metric.)

Gotchas & caveats

• The VPN is a hard dependency. When it drops, every consumer on this path fails while the REST path keeps working — which makes "Infor is down" reports ambiguous. Check which path the feature uses.
• Retries only help with blips. A downed VPN produces the same transient SQLSTATEs, so the retry loop just delays the inevitable failure; don't raise $maxAttempts hoping to ride it out.
• pdo_sqlsrv must be installed. It is a native extension, so a PHP upgrade can silently drop it. The local setup story (MSSQL via PDO, the OpenSSL/root-cert wrinkles) is written up in the off-git notes/Infor DB/ folder rather than here; see also environment.md.
• These are reporting queries against a live ERP. They are read-mostly by convention, not by permission — be deliberate about anything that writes.
• No query timeout is set beyond the login timeout. A pathological query can hang a CLI run.

Related docs

• Infor overview · The IDO REST API
• Google Sheets → consumers — where most of this data ends up
• Scheduled jobs