Keeping data in a database storage has several advantages over conventional file system such as reliability, fast access and play back ability. For these reasons, databases are very suitable for analysis tasks where a huge amount of data is to be kept and constantly analysed.
My research background is Machine Learning and I always had to look at multiple sections of data in different ways to be able to comprehend and design my algorithms. Building an efficient database takes lots of research and practice which comes in time. But for now, let’s focus on a very basic question, how do we connect from Matlab to MSSQL? This is going to give us some headlight on the journey.
I start with a brief explanation on setting up MSSQL tables and then a few setting that should be done before connection, in my next blog I will include a sample program on how to write and read data from MSSQL database. All screenshots are for MSSQL 2000 but the interface hasn’t changed much in newer versions. So you should still be able to follow.
Creating a Database in MSSQL 2000
Database files can be built and modified using SQL Server Enterprise Manager. Open the Enterprise Manager and right click on Databases, select the new database.
Creating a Table
By creating a database, a new tab will open for setting up tables. Again you can build a new table with a simple right click and new.
In the window above we can give column name, its data type. For example first column is for ID and we want to increment the ID for each row of the variable, then we can take its data type ‘int’ and in column description select ‘yes’ in identity and identity increment by 1.
Security Setting in SQL
We can secure out database by adding the user name and password. For this we have to security and then open ‘logins’ and select the name of security and select the database name ’check’. Also we can chose either the window authentication or SQL Server authentication.
In Database access, select database and permit all the database access because we want to access this data base from MATLAB.
Connection with MATLAB
To make connection with MATLAB, we need to have the database tool box installed in MATLAB. Database toolbox in MATLAB can do open data base connectivity (ODBC) and Joint data base connectivity (JDBC).
We use the windows ODBC driver to connect MATLAB and SQL Server. For this, in Control Panel-Administrator Tools-Data sources (ODBC). In ODBC we can add data file from excel, access, MS SQL, my SQL .
Finally we test the connection to see if everything is working.