Skip to Content
Jun 02 at 10:51 AM

Querying for Available Database Columns in the Service Layer

29 Views Last edit Jun 02 at 11:06 AM 2 rev

I write an ad-hoc reporting solution for SAP Business One over both SQL Server and Hana. I'm extending it to run over the Service Layer using the SQLQueries functionality but running into a blocker.

At startup, we query the database for the list of columns in the tables we operate over in order to automatically determine which columns can be queried in the reports. This also has the ability to automatically discover any user defined columns. This works fine in both SQL Server and Hana using ADO.Net but obviously won't work for the Service Layer.

I had hoped to use the $metadata endpoint (https://sid-hdb:55000/b1s/v1/$metadata) in the Service Layer to retrieve this information but it returns the OData metadata instead and the entity names don't match the database column names. For example, the ChartOfAccounts entity has Code and Name as properties but the underlying columns are AcctCode and AcctName.

Is there an endpoint that we can call that allows us to retrieve the underlying column information? For each column, I would need it to return the table name, column name and data type.