Thursday, January 15, 2015

iScala ScaSystemDB

This article discusses iScala ERP 2.3. I am not an iScala specialist but worked on some internals for a while.

There are a lot of things to talk about in iScala; it is an ERP and thus naturally sophisticated. From the business perspective the specialized “functional” modules (e.g. accounting, logistics and others) are what are important but as a software developer I will focus in my blog on technical topics. This post will be an overview and serve as an introduction to other future posts.

I explain some general background information about the nature of the tables and databases representing iScala companies and will also maintain a list of other posts that I write about the subject here.

One can easily explore iScala’s technical internals by using SQL Server Management Studio (SSMS). My assumption here is that the iScala implementation is using SQL Server and not some other DBMS. SQL Server Profiler tracing feature can also help greatly if you want to see how the various iScala screens work and which tables they affect.

There are two kinds of databases within iScala ERP:
  1. Administration database: ScaSystemDB
    • There is only one ScaSystemDB per iScala server.
    • Information about iScala companies and settings, such as company database and server and authentication information, are also stored in ScaSystemDB.
    • This is also where user information and their configuration, such as permissions, are stored.
    • The iScala Administration Console works with ScaSystemDB and the configuration accessed there is stored in ScaSystemDB.
  2. Business databases
    • From the iScala Administration Console, right clicking on the “Companies” node and choosing “New Company…” opens the “Add Company” dialog. There you choose the database name and server and the unique 2-character Company Code. The Company Code is used in table names as I explain below.
    • There is a database for each iScala company and these databases contain the various tables for business information such as finance and logistics. Some of these are year dependent: they change every year and most commonly a new fresh table is created when a new year is set up in iScala. I discuss these in the next section “Business Tables”.
    • Each company database also contains user defined tables that could be made company and year specific.

Business Tables

Business tables are those that store business information such as stock info, logistics, accounting, and other functional information. To a newcomer, table names might be a little confusing as they are not descriptive (for example the purchase orders table is not called PurchaseOrders) but rather a combination of codes. Once you have the naming convention in mind, understanding what a table is for will be much easier.

A table name is 8 characters long and consists of the following:

MODULE + SERIAL + COMPANY + YEAR

To illustrate, “PC” is used to refer to the purchase order module. Let’s say my ‘Flying Rabbits’ company was assigned code FR and the year is 2015. The serial number is just an incrementing number from 01 to 99. The module code and serial number identify the purpose of the table and I call them the “table identifier” but it does not even indicate company or year. The Purchase Order Head table will have the name PC01FR00. The year is 00 because PC is not year dependent. Knowing that this table is not year dependent is important especially if the year in question is 2000 or 2100. 00 here does not indicate those years! An example of a year dependent table would be the General Ledger Transactions table GL06.

Each of these elements is two digits and so most table names are 8 characters. You can use a dictionary that describes the various module tables, their codes and even description of the columns. You can find such a dictionary here.

Table column names are named in a serial nonsensical manner such as PC01001, PC01002, PC01003, etc. The column name is prefixed with the table identifier (the first 4 characters, i.e. Module + Serial). Many tables have their primary key or primary identifying piece of information as the first column. For example a purchase order number is stored in the first column in purchase order related tables (i.e., PC01001 . Column PC01001, PC03001 and PC04001 in all of the tables PC01, PC03 and PC04 represent the order number and the primary key respectively.

Table Identifier = Module + Serial
Column Name = Table Identifier + Column Serial

As for understanding the columns, there is no practical way to know the purpose of the PC0023 column without a dictionary such as the one mentioned above.

Understanding all this helps a lot of you are looking into the database directly. Understanding this is vital for the effectiveness of writing procedures that read or write data from the database. For example it helps one realize the suitability of using dynamic SQL queries when interacting with company tables because you can make the company name and year a parameter and then construct the desired table name dynamically.

In this post I talked about iScala from a partial SQL Server perspective. I just talked about the databases, tables, and columns and only mentioned stored procedures as a possibility. In future posts I’ll elaborate more about how you can write stored procedures that interact with the iScala database both from an administrative and functional points of view.

Sunday, December 28, 2014

QR or Barcodes in iScala Crystal Reports

We use Crystal Reports to accommodate our custom reporting requirements (layout and informationfor iScala ERP 2.3. The Crystal Reports run in the context of the database of the company to which the user is logged in when the report is invoked by the user. 

I should blog more about my iScala experiments, especially when there doesn't seem much talk about it around the developer blogsphere. I will update here when/if I do so. But here is my latest adventure: 

We have a project through which we want to improve our digital experience. This means that we needed to place a scannable barcode (or some other standard symbology (a symbological system)) on one of our reports. The symbology will correspond to the ID of that report (a PO number for a PO report to be specific).

I quickly came to learn that using barcode fonts (the easiest way to render some barcodes on a Crystal report) are not scannable by standard readers as they do not follow standard conventions and features of the various barcode systems. I came to the conclusion that if it is a font, then it won’t be scannable after trying 3 different barcode fonts of differing symbologies including what looks like PostNet and International Article Number aka. IAN or EAN. 

Example of QR code linking to mazinissa.com


In a sad moment, I downloaded a 3rd party’s software and installed it on a test computer. That added some COM DLLs and provided special functions in Crystal Reports that rendered scannable codes of different formats: QR, bars and others. The DLL was comprehensive and worked well. It still used some special fonts to help with rendering, and the unmanaged COM DLL ordered and laid out the text in the correct way. The font consisted of several components that could be composed to render a valid barcode image and it can't be used independent of the 3rd party library. 

We have three issues here: 
  1. We're downloading a 3rd party's components to our server which introduces security and reliability questions. 
  1. The 3rd party component's are not free (free demo version available). 
  1. Just downloading a library that does everything for you is not the most fun way to "develop" solutions. 

So I thought of a couple of alternatives of which only one worked: 
The first option was to Create a web service on one of our servers that would serve a corresponding barcode image depending on a query string parameter sent to it via HTTP GETI can construct a URL for an image depending on a record values in Crystal Reports. A picture object in Crystal Reports can have the "Graphic Location" set as a formula. The web service would still rely on an open source library to do the actual image generation as studying barcode specifications is outside the scope of generating a report with a barcode.

Right click a picture object and choose Format Object. Under the picture tab you can find the graphic location formula.

This would require a deployment of a LIVE web application. This is a big deal in a bureaucratic environment, but I didn't push for it anyways because even though I was able to generate a URL for the image’s source, Crystal Reports failed to actually GET from the web resource. The image URL works with local resources such as a path on the same computer (not sure of a network path). This might be a security configuration issue, but I didn't explore in that direction further. 

The second option was to see if I can directly use that open source barcode library from SQL Server and not through a web service: enter CLR Stored Procedures. This how I came to write my first CLR Stored Proc and that was an adventure by itself. 

SQL Server CLR Integration: Creating CLR Procedures and Functions

SQL Server provides a very powerful feature called CLR Integration that gives database developers the ability to let SQL Server execute .NET code from T-SQL procedures or functions. To begin with, this feature must be enabled on the database: 

sp_configure 'clr enabled', 1;
reconfigure with override;

SQL Server CLR Functions or Procedures are created with reference to methods in a .NET assembly added to SQL Server. That .NET assembly may execute unmanaged code as well (see security note about permission_set below). .NET results can be obtained from out parameters or function return values, and this provides for an infinite toolset extension as one can do anything* in SQL code by calling .NET. I had to use it to draw QR codes on a Crystal Report as I explain in this post.

The first step is to create the .NET assembly containing the method that you want to run. Using SQL Server 2008, only .NET 2.0 assemblies are supported.  Fortunately, I was able to limit my solution to use .NET 2.0 but I had to switch out one 3rd party library. This is a considerable limitationand I hope in future SQL Server versions, assemblies of higher .NET versions would be supported. 

The .NET method that you want to import must be a static one because T-SQL does not have the notion of instantiating a class and calling an object's methodThe inputs and outputs must be compatible with SQL Server and the T-SQL declaration of the procedure must match that of the .NET method as illustrated in the next stepThe method should be annotated with [Microsoft.SqlServer.Server.SqlProcedure] or [SqlFunction] in the same namespace. 

Both attributes exist in the System.Data.dll Framework DLL so I didn't need to add another reference to my application/library.

namespace Example
{
    public class SqlFunctionality
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ClrProc(out SqlInt32 i)
        {
            i = 123;
            Microsoft.SqlServer.Server.SqlContext.Pipe.ExecuteAndSend(new SqlCommand("print 'running sql proc!'"));
        }
 
        [Microsoft.SqlServer.Server.SqlFunction, Microsoft.SqlServer.Server.SqlMethod]
        public static int ClrFunc()
        {
            //You cannot use print in SQL Functions...
            return 57481;
        }
    }
}

Let's say the output assembly is Example.dll.

The second step is then to add the .NET assembly to SQL Server. You set the applicable permission_set in this stage. If your functionality does not need external resources, does not use unmanaged calls, then you can use the SAFE permission_set, which is default (you can just omit the permissions clause). If your code needs to access resources outside SQL Server, then you need the External_Access permission. If you use unmanaged code or use .NET framework libraries that haven't been tested with the SQL Server CLR feature, then you still can add them but with the unsafe permission_set. 

CREATE ASSEMBLY ExampleAssembly FROM 'C:\path\on\sqlserver\machine\example.dll';

SQL Server verifies that the assembly can be added and used with the specified permission_set. To see this in action, try to add the System.Drawing.dll .NET framework DLL.  

CREATE ASSEMBLY [System.Drawing] FROM 'c:\windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll';

NOTE: You don't normally add System.Drawing.DLL to use directly from T-SQL, but rather because another library you are using references this Framework library. 
SQL Server tries to verify that the assembly is safe, but because we use the default SAFE permission_set which isn't applicable to the System.Drawing.dll assembly, the command causes a warning message to be issues then failwith the subsequent message:

Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. 
CREATE ASSEMBLY for assembly 'System.Drawing' failed because assembly 'System.Drawing' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [...omitted...].

To overcome this, you add the System.Drawing.dll with UNSAFE permission_set. But in order to do this, SQL Server requires that the database is trustworthy which is a property that need to be turned on the database. 

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

The user account must have the privilege to ALTER the database: see this TechNet article for details. If this step is not executed, the next command even with the UNSAFE permission_set will fail. SQL Server issues an error message that explains why.

create assembly [System.Drawing] from 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' with permission_set = unsafe

Msg 10327, Level 14, State 1, Line 1 
CREATE ASSEMBLY for assembly 'System.Drawing' failed because assembly 'System.Drawing' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. 

Turning on the TRUSTWORTHY property causes the command to succeed (the warning that System.Drawing is not fully tested with SQL Server is still issued).

After the assembly is created, a CLR stored proc or function is created with an external name as follows: 

CREATE PROCEDURE ClrProc (@i int output) AS EXTERNAL NAME ExampleApp.[Example.SqlFunctionality].ClrProc;
CREATE FUNCTION ClrFunc() RETURNS int AS EXTERNAL NAME ExampleApp.[Example.SqlFunctionality].ClrFunc;
  
Note how the external name is fully qualified. The C# static class and the containing namespace are [grouped with square brackets] because they are a single name from SQL Server's perspective (they do not reflect different SQL Server objects).  
The procedure and function can be invoked just like any other. 

exec ClrProc 1; --prints "running sql proc!" to SQL Server output
select ClrFunc(); --returns 57481

Quiet an adventure. Good to know about this feature.