Accessing SQL Server Databases

Issue

Can GoldSim access data from Microsoft SQL Server?

Resolution

Some GoldSim data entry elements can be linked directly to databases. These GoldSim data entry elements include the following element types (also see Figure 1):

1. Data elements;
2. 1-D and 2-D Lookup Table elements; and
3. Stochastic elements.

Of these data entry elements, only Data elements can be linked directly to a SQL Server database and the SQL Server database needs to have a particular format for successful linkage to occur.

Figure 1: GoldSim data entry elements

ODBC (Open Database Connectivity) is a standard middleware, application programming interface (API) for accessing database systems. It is independent of the database system or software because it uses a driver (an ODBC driver) as a translation layer between the database system and the application which is using the ODBC driver. An application that can use ODBC is referred to as "ODBC-compliant", and GoldSim is an ODBC-compliant application. Any ODBC-compliant application can access any database system for which an ODBC driver is installed and configured. Drivers exist for all major database systems, including Microsoft SQL Server, and for many other data sources like address book systems, Microsoft Excel files, and CSV (Comma Separated Value) files.

Microsoft SQL Server is a relational database management system developed by Microsoft. It is a software product whose primary function is to store and retrieve data as requested by other software applications. There are a number of different editions of Microsoft SQL Server aimed at different audiences and different workload magnitudes. Its primary query languages are the Structured Query Language (SQL) variants T-SQL and ANSI SQL.

GoldSim is an ODBC-compliant application and ODBC drivers are available for Microsoft SQL Server. As a result, GoldSim data entry elements can be linked to a Microsoft SQL Server database. There are two requirements for a successful linkage:

1. Formatting, or configuration, of the tables in the Microsoft SQL Server database so that GoldSim can access data from these tables according to the format or configuration that GoldSim expects; and, 
2. Configuration of the Microsoft SQL Server database (or table in a database) as an ODBC data source.

GoldSim Database Formats

GoldSim is designed for linkage to three different database formats: 

1. Generic Database Format;
2. Simple GoldSim Database Format; and 
3. Yucca Mountain Database Format.

Each of these formats expects a different collection of tables with different field and data type configurations. These formats were developed for implementation in the Microsoft Access database system. As a result, the GoldSim database functionality assumes database field structure based on implementation of these formats in Access. These expected formats may then be hard-coded into the internal software implementation of SQL statements which pull data and information from the linked database.  A brief description of the three GoldSim database formats is given below and more complete description is available in the GoldSim User's Guide (see Appendix E and Chapter 10).

• Generic Database: Generic databases have a very simple format, consisting of a single table. They can download only to scalar, vector and matrix Data elements.
• Simple GoldSim Database: Simple GoldSim databases are more structured than Generic databases. As such they can not only download to scalar, vector and matrix Data elements, but they can also download to Stochastic elements. The database is structured to allow you to store different versions of the same datum (e.g., associated with different dates). You specify from within the database which version is to be used when linked to GoldSim.
• Yucca Mountain Database: The Yucca Mountain database format is the most complex of the three formats. They can download to scalar, vector and matrix Data elements, Stochastics, and 1-D and 2-D Table elements. The database is structured to allow you to store different versions of the same datum (e.g., associated with different dates). You can specify from within GoldSim which date is to be used when linked to GoldSim.

SQL Server Database Formatting

As mentioned above, each of the three GoldSim database formats is really identified with Microsoft Access data types for field formats. This occurs because Microsoft Access databases were used for the original GoldSim database design and because various field type expectations are hard-coded into the internal SQL statements in GoldSim. ODBC compliance ensures that GoldSim will attach or access a database which has an available ODBC driver but does not ensure that a specific SQL syntax implementation or data type will be recognized by a particular type of database. Each database management system may recognize slightly different SQL syntax variants and may not contain or work with equivalent data types.

An example of this type of hard-coding where the SQL syntax is not correct for all databases which have an available ODBC driver is that the "Current" field in the tbl_Parameter table of the Simple Database format expects a “Yes/No” logical field and the SQL statement syntax in GoldSim uses a WHERE clause with “Current = Yes”. Microsoft Access supports a “Yes/No” data type which can be displayed as “Yes/No”, “True/False”, or “On/Off” but does not support NULL (w3schools.com, 2014). Additionally, the Microsoft Access “True/False” pair is represented numerically as -1/0. Microsoft SQL Server on the other hand uses a bit type which allows 0, 1, or NULL values and does not support a “Yes/No” data type (w3schools.com, 2014). As a result, the SQL statement used by GoldSim to access the Simple Database format will work with an appropriately formatted Microsoft Access database but will not work with a Microsoft SQL Server database because the “Yes” data type is not recognized by SQL Server. Similar issues occur with the Yucca Mountain Database format because the “DTN_Prelim” and “DTN_Qualied” fields in the GS_Parameter_Value table are “Yes/No” Microsoft Access data types.

The Generic Database format will however allow GoldSim Data elements to extract data directly from an appropriately configured Microsoft SQL Server database. Details of the appropriate database configuration are available in the GoldSim User’s Manual Chapter 10 and Appendix E. A SQL Server database with the desired/appropriate format can be created using Microsoft SQL Server Management Studio (Microsoft, 2014a). Or, the SQL Server Migration Assistant for Access  (Microsoft, 2014b) can be used to transform the example Generic_DB.accdb Access database which comes with GoldSim (see "C:\Program Files (x86)\GTG\GoldSim 11.1\General Examples\Database\Generic_DB.accdb" ) into a Microsoft SQL Server database.

GoldSim Data entry elements can be linked to any database for which an ODBC driver is configured on the system. Microsoft SQL Server has an available ODBC driver which permits configuration so that GoldSim can attach to a SQL Server database. GoldSim does require certain data type formatting so that it can extract the desired information from an attached database. At this time, only the Generic Database format is compatible with the data types available in SQL Server and with the SQL syntax hard-coded into GoldSim which implements the data extraction from an ODBC linked database.

SQL Server ODBC Driver Configuration

The ODBC Data Source Administrator program is used in Windows 7 and 8 to add an ODBC data source to (i.e. configure the appropriate ODBC driver on) a computer. Details of the use of this program to configure ODBC data sources for use with GoldSim are available in the GoldSim User’s Manual in the “Adding Data Sources to Your Computer” section of Chapter 10 and in the GoldSim Knowledge Base. The one departure from this available documentation for configuring a SQL Server database as an ODBC data source is that the “SQL Server” driver needs to be selected, as shown in Figure 2 and Figure 3, rather than the “Microsoft Access Driver”.

Figure 2: ODBC Data Source Administrator

Figure 3: Use the SQL Server driver

Summary

Scalar, vector, and matrix GoldSim data elements can be linked directly to a Microsoft SQL Server database. The SQL Server database needs to have the GoldSimGeneric Database format, and the ODBC Data Source Administrator program needs to be used to configure the SQL Server ODBC driver to make the SQL Server database available to ODBC-compliant programs like GoldSim. The other two GoldSim database formats (Simple Database and Yucca Mountain Database) will not work with a SQL Server database because the SQL statements in GoldSim are expecting to receive data types from the attached database which are not supported in SQL Server.

References

Microsoft. (2014a). SQL Server Management Studio. Retrieved November 18, 2014, from Developer Network: http://msdn.microsoft.com/en-us/library/hh213248.aspx

Microsoft. (2014b). SQL Server Migration Assistant for Access. Retrieved November 18, 2014, from Developer Network: http://msdn.microsoft.com/en-us/library/hh302874(v=sql.105).aspx

w3schools.com. (2014, November 18). SQL Data Types for Various DBs. Retrieved November 18, 2014, from SQL Tutorial:http://www.w3schools.com/sql/sql_datatypes.asp

Wikipedia. (2014a, November 11). Microsoft SQL Server. Retrieved November 17, 2014, from Wikipedia: The Free Encyclopedia: http://en.wikipedia.org/wiki/Microsoft_SQL_Server

Wikipedia. (2014b, October 15). Open Database Connectivity. Retrieved November 17, 2014, from Wikipedia: The Free Encyclopedia:http://en.wikipedia.org/wiki/Open_Database_Connectivity