ODBC Vs OLEDB

Hi all, Hope this post will help for the DB beginners. :)
Back in the old days, database connectivity was difficult. Everybody had their own database formats, and developers had to know a low level API for each database they wished to develop for. There was a push for a universal API, an API which would work for numerous data stores. It was about this time that ODBC, or Open Database Connectivity, which was an early attempt at creating this universal API. A number of databases conformed to this standard, and became known as ODBC-compliant databases. ODBC-compliant databases consist of Access, MS-SQL Server, Oracle, Informix, etc.

ODBC is Open Data Base Connectivity, which is a connection method to data sources and other things. It requires that you set up a data source, or what's called a DSN using an SQL driver or other driver if connecting to other database types. Most database systems support ODBC

Well, ODBC wasn't perfect. It still contained a lot of low-level calls, and was difficult to develop with. Developers had to focus more on low-level communications with the database, as opposed to being able to concentrate on getting the data they needed and using it how they saw fit. Along came Microsoft's solution: DAO, or Data Access Objects.

.OLE is Object Linking and Embedding. OLEDB is partly distinguished from OLE itself, now called "automation". OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal. In many cases the OLEDB components offer much better performance than the older ODBC.OLEDB is a different type of data provider that came about with MS's Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM as of SQL 7.0.

OLEDB sits between the ODBC layer and the application. With your ASP pages, ADO is the "application" that sits above OLEDB. Your ADO calls are first sent to OLEDB, which are then sent to the ODBC layer. You can connect directly to the OLEDB layer, though, and if you do so, you'll see an increase in performance for server-side cursors (the default cursor type for recordsets, and the most common type of cursor used).

Visual Studio Test Attributes

When we built our test in the previous section, we were required to use the following two attributes:
· [TestMethod] – Used to mark a method as a test method. Only methods marked with this attribute will run when you run your tests.
· [TestClass] – Used to mark a class as a test class. Only classes marked with this attribute will run when you run your tests.
When building tests, you always use the [TestMethod] and [TestClass] attributes. However, there are several other useful, but optional, test attributes. For example, you can use the following attribute pairs to setup and tear down tests:
· [AssemblyInitialize] and [AssemblyCleanup] – Used to mark methods that execute before and after all of the tests in an assembly are executed
· [ClassInitialize] and [ClassCleanup] – Used to mark methods that execute before and after all of the tests in a class are executed
· [TestInitialize] and [TestCleanup] – Used to mark methods that execute before and after each test method is executed
For example, you might want to create a fake HttpContext that you can use with all of your test methods. You can setup the fake HttpContext in a method marked with the [ClassInitialize] attribute and dispose of the fake HttpContext in a method marked with the [ClassCleanup] attribute.
There are several attributes that you can use to provide additional information about test methods. These attributes are useful when you are working with hundreds of unit tests and you need to manage the tests by sorting and filtering the tests:
· [Owner] – Enables you to specify the author of a test method
· [Description] – Enables you to provide a description of a test method
· [Priority] – Enables you to specify an integer priority for a test
· [TestProperty] – Enables you to specify an arbitrary test property
You can use these attributes when sorting and filtering tests in either the Test View window or the Test List Editor.
Finally, there is an attribute that you can use to cause a particular test method to be ignored when running a test. This attribute is useful when one of your tests has a problem and you just don’t want to deal with the problem at the moment:
· [Ignore] – Enables you to temporarily disable a test. You can use this attribute on either a test method or an entire test class

Unit test in VS 2005

If you didn't know it already, it is not a difficult one to learn.Tremendous progress is being made on several fronts: IDE integration, process integration, and new test fixtures. In here I will cover unit testing in Visual Studio 2005, including VSTS unit testing, NUnit and MBUnit--the Superman of unit testing. First post I’ll cover NUnit testing.

NUnit
NUnit is the unit testing framework that has the majority of the market share. It utilizes attributes to identify what a test is.
The TestFixture attribute is used to identify a class that will expose test methods.
The Test attribute is used to identify a method that will exercise a test subject.
Let's get down to business and look at some code. First we need something to test.
public class Subject
{
public Int32 Add(Int32 x, Int32 y)
{
return x + y;
}
}
That Subject class has one method: Add.
We will test the Subject class by exercising the Add method with different arguments.
[TestFixture]
public class tSubject
{
[Test]
public void tAdd()
{
Int32 Sum;
Subject Subject = new Subject();
Sum = Subject.Add(1,2);
Assert.AreEqual(3, Sum);
}
}
The class tSubject is decorated with the attribute TestFixture, and the method tAdd is decorated with the attribute Test.
You can compile this and run it in the NUnit GUI application. It will produce a successful test run.
That is the basics of what NUnit offers. There are attributes to help with setting up and tearing down your test environment:
SetUp, SetUpFixture, TearDown, and TearDownFixture. SetUpFixture is run once at the beginning when the fixture is first created; similarly,
TearDownFixture is run once after all tests have completed. SetUp and TearDown are run before and after each test.
NUnit tests can be run several different ways: from the GUI application, from the console's application, and from a NAnt task. NUnit has been integrated into Cruise Control .NET as well. In the last product review, you will see how it has been integrated into the VS.NET IDE as well.

'Interplanetary internet' passes first test

NASA has finished its first deep-space test of what could become an 'interplanetary internet'. The new networking commands could one day be used to automatically relay information between Earth, spacecraft, and astronauts, without the need for humans to schedule transmissions at each point.

Spacecraft usually communicate directly with Earth - the first to do so through an intermediary were the Mars Explorations Rovers, which launched in 2003. The Spirit and Opportunity rovers transmit data to orbiters, which then send the data back to Earth.

But human intervention is still required to schedule communications sessions for orbiters and Landers. "The traditional method of operations is largely manual," says Jay Wyatt of NASA's Jet Propulsion Laboratory in Pasadena, California. "People get in a room and decide when they can send data."

A new method would automate and streamline this process by sending data through an interplanetary 'internet'. Just as data is sent from one point to another on the internet via a linked network of hubs, or nodes, spacecraft scattered throughout the solar system could be used as nodes to transmit data through space.

Last week, NASA completed a month-long test of a simulated network of Mars Landers, orbiters and mission operations centers on Earth.

For the test, dozens of images of Mars and its moon Phobos were transmitted back and forth between computers on Earth and NASA's Deep Impact spacecraft. The craft, which sent an impactor into Comet Tempel 1 in 2005, has been renamed "Epoxi" now that it its mission has been extended to search for extrasolar planets.

Measuring Performance of Stored Procedures

Database developers need to write stored procedures which are not only fully functional, but also which perform acceptably. As database servers use permanent storage media heavily (mainly because of ACID properties), which are known for slow performance, optimizing the stored procedures for performance is very important. In here I’ll describe on some of the counters used to measure performance and analyses methods of capturing these counters. This post is intended for database developers who write stored procedures and optimize for performance.
Currently there are three counters widely used for measuring performance of the system.

• Execution time
• CPU Cost
• IO Cost

Execution Time
The most primitive method is to get the time taken to execute the query from SQL Server Management Studio (SSMS). The status bar displays the time taken in terms of hour, minute and second. This may be a measure used when a query takes a longer time (Usually more than 10 seconds, so that a 10% improvement to the query could be measured) and the time difference in sub seconds is insignificant. When a query executes within a second, SSMS rounds the value. Thus this value could not be used to get the value if the query executes within sub seconds.Another method is to get the system time before and after the execution of the stored procedure and analyze the difference. SQL Server management studio could be used for this purpose, simply by adding print statements before and after the stored procedure.

PRINT CONVERT(varchar, GETDATE(), 114)

The developer could also use variables to hold the values and calculate the time difference.The advantage over this method is it could be used within a query too. For a stored procedure to be executed multiple times with different parameter for each time, print statement could be injected between each execution to analyze the time difference. For a multi statement procedure, Developers need to modify the stored procedure adding print statement, but it gives better control over the data to the developers. On the disadvantages side, one of the major points is its disability over getting time taken for compilation. Instead of PRINT statements, the time could be inserted into a table for further analysis is required. As datetime data type as it allows the value to be accurate up to 3 milliseconds the results may have vary with actual up to 3 milliseconds, and better than the previous method.

SQL Server has some other methods too.

SQL Server has a Set option which could be used to display the time taken:

SET STATISTICS TIME ON

SET STATISTICS TIME OFF

When this set option is ON. SQL Server will return message which may look similar to:

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 164 ms.

Elapsed time is the execution time of the query.

However, there is another time involved in this query: The time taken to compile the query. To view that, the statement should be issued when SET STATISTICS TIME ON Statement is already executed. When a batch of statements is submitted, SQL Server goes through ALL of them but compiles one by one. As GO is considered as batch separator, inserting a GO statement between the SET command and the query will make SQL Server to consider each statement as a batch and compile and execute them separately. Executing the set command first and executing the query (i.e. in two batches) will also do the trick.

When done the messages may be different.

SQL Server parse and compile time:

CPU time = 62 ms, elapsed time = 72 ms.

(1139 row(s) affected)

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 167 ms.

In this case, the time taken by the server for execution is 167 milliseconds. In addition to this, 72 milliseconds have been taken for compilation. The unit of measurement is in milliseconds and smaller figures will be rounded. When a multi statement stored procedure is called, the total time may slightly differ from the calculation. For this reason, SQL Server provides a summary too. Additionally, some of the internal operations like creating worktables, statistics on temporary tables etc. may create additional load but not be captured as individual statements. However, they will be added to the final cost.

I'll explain the other topics in my next post.

Full Text Indexing in SQL Server

Sql Server’s Full-Text search can let a developer create some very slick features disturbingly easily. And, unlike many other Full-Text implementations, it is not limited to plain text fields. It can also search within binary fields with the proper setup considerations. Needless to say, there are a few protips to making Full-Text indexes work and taking advantage of them. In this post, I will tell you how to get Full-Tex.

Getting Started

First, you are going to need a copy of Sql Server 2000 or 2005 Standard edition. MSDE or Sql Express do not have Full-Text capabilities. In order to enable Full-Text search, you must do a few things.

• Enable Full-Text search on the database.
• Create a Full-Text catalog.
• Enable Full-Text searching on specific columns in your data.

Full Text Indexing in SQL Server 2000

• Create and populate a table.
• Enable the pubs database for full-text searching
• Create a full-text catalog.
• Register the new table and certain columns in it for full-text search.
• Populate the new full-text catalog with full-text index information from the new
• tableExecute a full-text query against the new table.

USE Pubs

-- Create and populate a table.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'FulltextTest')

DROP TABLE FulltextTest

GO

CREATE TABLE FulltextTest

( article_id int IDENTITY(100,1)

CONSTRAINT PK_title_id PRIMARY KEY,article_title nvarchar(200))

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan has always enjoyed ice skating.')

INSERT FulltextTest (article_title) VALUES (N'Elvis Stoiko: The best male figure skater')

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan On Ice: Skating Reaches Tops in Public Opinion Poll')

INSERT FulltextTest (article_title) VALUES (N'Last night, Steven Buchanan skated on the ice!! Skating fans cheer!')

INSERT FulltextTest (article_title) VALUES (N'Ice-skating brings out the best in Steven. Buchanan exults in first victory...')

GO

-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'

GO

-- Create a new full-text catalog.

EXEC sp_fulltext_catalog 'StevenBCatalog', 'create' GO

-- Register the new table and column within it for full-text querying,

-- then activate the table.

EXEC sp_fulltext_table 'FulltextTest', 'create', 'StevenBCatalog', 'PK_title_id'

EXEC sp_fulltext_column 'FulltextTest', 'article_title', 'add'

EXEC sp_fulltext_table 'FulltextTest', 'activate'GO

-- Start full population of the full-text catalog. Note that it is

-- asynchronous, so delay must be built in if populating a

-- large index.

EXEC sp_fulltext_catalog 'StevenBCatalog', 'start_full'

WHILE (SELECT fulltextcatalogproperty('StevenBCatalog','populatestatus'))<>0

BEGINWAITFOR DELAY '00:00:02'

-- Check every 2 seconds to see if full-text index population is complete.

CONTINUE

END

-- Execute a full-text query against the new table.

SELECT article_title

FROM FulltextTest

WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')

GO