Testing Persistent Table Arrays with a MySQL database

MySQL is definitely one of the most popular database systems. It is free and open-source, and comparably easy to install and use.

The all-in-one installation package makes the installation quite simple. This package also includes MySQL workbench for tasks like executing SQL statements and database administration with a nice graphical user interface (GUI).

PersistentTableArray class of the non-commercial .net library finaquant® protos can be connected with a relational database like MySQL (or Microsoft SQL) in order to read or write in-memory tables from/to the database together with their instance information.

You may find some interesting articles in internet if you search for MySQL versus SQL Express.

PersistentTableArray and DataStore classes of finaquant® protos that are responsible for the database connection don’t need many widgets and gadgets of a sophisticated commercial database; they are based on most basic standard (ANSI) SQL statements for reading or writing complete tables. So technically, MySQL database is more than enough for persistent table arrays with the advantage that it doesn’t have the storage space limitations of Microsoft SQL Server Express.

You will also need Microsoft Visual Studio (VS) to run the demo function for persistent table arrays included in the VS project file FinaquantProtosStarter that you can download at the product page of finaquant® protos.

Initiating a persistent table array is about setting the correct database connection string and data provider to a DataStore object:

using FinaquantProtos;
using System.Data.Odbc;
 
// initiate DataStore for database connection
DataStore datstore = new DataStore(Provider: "System.Data.Odbc");
// open database connection
string ConnStr = @"Driver={MySQL ODBC 5.2w Driver};Server=localhost;Database=finaquant;uid=root;pwd=MyPassword";
datstore.OpenConnection(ConnStr);
 
// initiate a persistent table array
var PTblCost = new PersistentTableArray(TableName: "costs", dstore: datstore);

The software ingredients you need for the test

• A computer with a Windows operating system and .Net 4.0 framework (client version)
• Finaquant Protos .net library (release 1.04 or higher)
• Microsoft Visual Studio C# (2010 Express or higher versions)
• MySQL database (all-in-one installation) and ODBC Driver for MySQL (32 bit version)
• Visual Studio project file FinaquantProtosStarter including the related demo function

Steps

Following steps explain all the preparations required for running the related demo function in FinaquantProtosStarter.

1) Install Microsoft Visual Studio C# 2010 Express(which is free) or a higher version.

For downloads visit: Microsoft Visual Studio

Select “install with a database” if you are given the option.

2) Download and install the latest release of finaquant® protos.

3) Download and unzip the MS Visual Studio file FinaquantProtosStarter (2501 downloads)

You need to be registered and logged in at finaquant.com in in order to download this file.

4) Install MySQL all-in-one package (download at: MySQL downloads)

In the installation user interface you can select what components you need. We recommend you to select:

• MySQL Server (all files)
• Applications (all applications)
• Connectors: You need only Connector/ODBC.

IMPORTANT: If you are working on a 64-bit computer you need to download and install 32-bit version of the ODBC connector separately, because even a 64-bit machine may need a 32-bit connector.

Download ODBC Driver for MySQL (32 bit version, mysql-connector-odbc-5.2.4-win32.msi).

5) Using MySQL Workbench create a new connection and a database named finaquant

In the main window you can click on Manage Connection to update or create database connections. You don’t need to change here anything; you can set a password to the default user root if you want.

Select your local connection and click on Open Connection to Start Querying to open the SQL Editor window.

Right-click on an existing database (initially test only) and select Create Schema to create the new database named finaquant.

You have now your new database which is all you need for Persistent Table Array.

A) Find the demo function public static void Persistent_Table_Array() in code file Demo.cs

B) Paste the connection string in the demo function as updated string value for ConnStr2, and save code file Demo.cs

C) Out-comment all other demo functions, un-comment DemoFunctions.Persistent_Table_Array() in the code file Program.cs and press F5 to run the demo function.

This demo function should calculate and display price tables for each instance (country and year) of cost and margin tables.

The table named list_of_data_tables has an entry for each instance of data tables.

A single table of the database stores multiple instances of in-memory cost tables.