CSQRY from the .Net API

Introduction

Generally in these blog posts I talk about how to access the Sage 300 ERP data through our various APIs that go through our Business Logic Views. This is the only way to update the data in a supported manner, but often for reporting needs something more is hoped for. We store our data in standard databases which are accessible via various APIs like Entity Framework, ADO.Net or ODBC. The downside is that you need a connection string to establish a connection, which means you need a SQL Server login and password. Often for Sage 300 add-in solutions there can be a lot of resistance from local IT departments on providing these. Basically anywhere these are given out is a possible security risk.

In this article I’ll show how to execute a custom SQL Query through a special Business Logic View to get at data for reporting purposes. This means you can use the full power of SQL Server to extract the exact data you want. This article uses our .Net API which I have blogged on quite a bit with an introductory article here.

The project for the sample program is located in both zip, and folder structure on Google Drive here. It’s the csqrydemo one.

CSQRY

CSQRY is the View CS0120. It basically takes an arbitrary SQL Query as the parameter to its Browse method and then returns the records via calls to Fetch. It also returns the attendant meta-data for the result set using the regular View meta-data APIs (which I blogged on here).

The problem with this is that the meta-data is read and utilized by the .Net API when you first open the View. At this point it build the fields list and various other structures and then you use these in-memory versions afterwards without the .Net API inquiring of the meta-data again from the Views. So when you open CSQRY there is no results set and so it returns that there are zero fields in the View. Then when you execute the Browse statement, it then populates all its meta-data based on the results set returned form the database server, but the .Net API still thinks there are zero fields.

It turns out, that we added a “hack” to the .Net API for situations like this. You can get the .Net API to refresh all the meta-data from the View by calling the terribly intuitive:

view.InternalSet(256);

You might then wonder, what other magic numbers can I pass to the InternalSet method? It turns out this is the only value that InternalSet acts on. But I guess in the future if we need any other sort of “hacks” this would provide a method to put them. Still a more easily understood RefreshMetaData() method would have been helpful.

Sample Program

For the sample program, let’s get the top 6 customers by billing from the AROBL table. Basically summing all the invoices, credit notes and debit notes for a given fiscal year. This is a nested query to get the data, then get the top six by sum. This is the sort of query you might issue to provide, perhaps a dashboard type KPI display.

Here is the routine from the sample program to refresh the list control by issuing the query and displaying the results:

private void refreshList()
{

    // Issue the SQL Query

// TXTTRXTYPE = (1, 2, 3), Document Type (Invoice, Debit Note, Credit Note)

csQry.Browse(@"select top 6 c.IDCUST, c.NAMECUST, sum(c.AMTINVCHC) as 'INVOICED'
    from (select a.IDCUST, b.NAMECUST, a.AMTINVCHC from AROBL a, ARCUS b
    where a.IDCUST = b.IDCUST and a.TRXTYPETXT in (1, 2, 3) and a.FISCYR = 2020) c
    group by c.IDCUST, c.NAMECUST order by INVOICED desc", true);

 // Refresh the View's MetaData
 csQry.InternalSet(256);

 listView1.Clear();
 listView1.View = System.Windows.Forms.View.Details;
 listView1.Columns.Add("Cust ID", 100, HorizontalAlignment.Left);
 listView1.Columns.Add("Customer Name", 200, HorizontalAlignment.Left);
 listView1.Columns.Add("Amount", 100, HorizontalAlignment.Right);

  while (true == csQry.Fetch(false))
  {
     var row = new ListViewItem(new string[]

          { csQry.Fields[0].Value.ToString(),
            csQry.Fields[1].Value.ToString(),
            csQry.Fields[2].Value.ToString() });
     listView1.Items.Add(row);
  }
}

For a bit more information on the structure of the A/R tables used, check out this blog post.

Performance

It may appear that SQL Server can execute this query pretty quickly, for instance in SAMINC in less than a second. But this is still quite an expensive query. Look at the execution plan below:

It might be hard to read but the query needs to do index scans on both AROBL and ARCUS along with a couple of sorts. So we might expect that although this looks good on sample data, it will take quite a bit more time on larger real customer data sets.

Data Types (2016/02/09)

If you return calculated values then we don’t know the data type and might return something unexpected. You can control this by providing some hints in your query to tell us what data type you want. Something like:

SELECT SUM(AMTINVCHC) /* ACCPAC TYPE 1 FT_BCD 10 3 */ …

The format of the comment is /* ACCPAC TYPE fieldnumber type length precision */ where the precision is only needed for type FT_BCD. The type can be FT_BCD, FT_BTYE, FT_DATE, FT_LONG, FT_TIME, FT_BOOL, FT_CHAR, FT_INT, FT_REAL.

As another example:

SELECT UPPER(SHPTOSTTE) /* ACCPAC TYPE 1 FT_CHAR 30  */ as PROV from ARIBH

Summary

CSQRY gives a fairly easy to use way to execute general SQL statements through the Sage 300 API. This way you don’t need additional database credentials and you don’t need to use an alternate API like ODBC. At the same time beware that doing general SQL statements isn’t a solution to all performance problems.