Using XLeratorDB with MySQL and other RDBMS’s
Jun
7
Written by:
Charles Flock
6/7/2012 9:01 AM
XLeratorDB can be used with data sources other than SQL Server, such as MySQL and Oracle. This article illustrates how to call an XLeratorDB function against a MySQL database.
Even though XLeratorDB is built for Microsoft SQL Server, it can be used with many other databases and data sources, including MySQL and Oracle. Through the use of SQL Server Linked Servers you can connect SQL Server to diverse data sources via ODBC. If there is an ODBC driver for your data source (as there is with MySQL and Oracle), it is possible to link it to SQL Server and then use XLeratorDB functions against the external data.
For organizations that don’t already employ SQL Server, it is a very economical technology and can be included within the organization’s infrastructure at modest cost.
This SQLServerCentral article discusses the topic of connecting Linked Servers within SQL Server. We use this linked server technique to demonstrate how to use the XLeratorDB PRICE function (which calculates the price of a bond) against 220,000 rows of data in a MySQL database.
To start, we took a MySQL database and created a catalog (database) called “test” and then created a “PRICE” table in the “test” catalog. The PRICE table contains all the data needed for the PRICE function in XLeratorDB, namely:
- Settlement date
- Maturity date
- Rate
- Yield
- Redemption
- Frequency
- Basis
Here is a screen shot of the the MySQL database.
Next we create an ODBC data source to prepare to link MySQL with SQL Server. Using the freely available MySQL ODBC driver we create a DSN named “mySQL-test”.
And configure it to point to the “test” catalog in the local MySQL installation.
Now we can easily link the MySQL database to SQL Server using SQL Server Management Studio (SSMS). We create a linked server named “MYSQL-TEST”. Note that linked servers can be established via script, as mentioned in the article previously referenced, but for the purposes of this blog we will use the SSMS user interface, which essentially performs the same function. (We are using SQL Server 2008 in this example.)
And here we see the linked MYSQL database in SSMS.
Now that the server is linked, we execute a simple test query to ensure that we can actually select data against it. This is done with the use of SQL Server’s OPENQUERY function.
This is all that we need in order to call XLeratorDB functions against external data sources. We can now calculate PRICE on all 220,000 rows of the MySQL data using a derived table in conjunction with OPENQUERY.
MySQL was used in this example, however it would also work equally well with other data sources such as Oracle, Sybase, DB2, PostGRE SQL, and many others. If there is an ODBC driver for it, it will work with XLeratorDB!