ODBC |
| An ODBC data source stores information about how to connect to a specific data provider. Any program can connect to an ODBC, and the ODBC gives the access to the database as shown in the figure below. One of the main advantages of using ODBC is that a program can connect to a different database server without making any changes to the program. One of the main disadvantages is that the user must set up the ODBC in the Control Panel. Una fuente de datos ODBC almacena información acerca de como conectarse a un proveedor de datos específico. Cualquier programa puede conectarse a un ODBC, y el ODBC da el acceso a la base de datos como se muestra en la figura de abajo. Una de las principales ventajas de usar ODBC es que un programa puede conectarse a diferentes servidores de bases de datos sin hacer cambios en el programa. Una de las principales desventajas es que el usuario debe configurar el ODBC en el Panel de Control. |
| Tip |
| To check that Microsoft SQL is running, open the Control Panel and then the Administrative Tools. Open the Services to check that the SQL service is running. If it is not running, you may start the service. Para verificar que Microsoft SQL está corriendo, abra el Panel de Control y entonces las Herramientas Administrativas. Abra los Servicios para verificar que el servicio de SQL está corriendo. Si no está corriendo, usted puede iniciar el servicio. |
| Problem 1 |
| Create the table category inside the database best_buy by executing the following code in Microsoft Visual Studio. File > New File > General > SQL File . To save some typing you can use Wintempla Templates Tools > Add Wintempla Item... > Clipboard Code > Microsoft SQL Server > Basic |
| best_buy.sql |
| USE master; GO IF EXISTS(SELECT * FROM sysdatabases WHERE NAME='best_buy') BEGIN RAISERROR('Dropping best_buy',0,1) DROP DATABASE best_buy; END GO CREATE DATABASE best_buy; GO USE best_buy; GO IF db_name()<>'best_buy' BEGIN RAISERROR('Error to create best_buy database',22,127) WITH log DROP DATABASE best_buy; END GO CREATE TABLE dbo.category ( category_id INT NOT NULL PRIMARY KEY IDENTITY, descr VARCHAR(32) NOT NULL ) GO -- ______________________________________________ category INSERT INTO category(descr) VALUES('Video');--1 INSERT INTO category(descr) VALUES('Computers');--2 INSERT INTO category(descr) VALUES('Audio');--3 INSERT INTO category(descr) VALUES('Music');--4 INSERT INTO category(descr) VALUES('Digital Photo');--5 INSERT INTO category(descr) VALUES('Computer Accessory');--6 INSERT INTO category(descr) VALUES('Appliances');--7 GO |
| Tip |
| If you are not connected to a database the login dialog will display as shown below. Provide the server name and login information. Microsoft Visual Studio will display the results in the output window. If there are any errors, correct them. |
Wintempla Templates |
| Wintempla provides a collection of templates than can save some typing when creating a program. The templates include SQL and C++ code that you can drag and drop in your files. To use the templates: Open Microsoft Visual Studio, then click on the TOOLS menu and select Add Wintempla Item (Add Clipboard Code > Add). |
| Tip |
| Microsoft Windows provides a tool to manage the ODBCs (Data Sources) of a computer. This tool can be found inside the Administrative Tools of the Control Panel as shown below. |
| Tip |
| An ODBC can be used to interact between a Microsoft Excel file and export data to a database. |
A System Data Source (System DSN) |
| Most information systems require a system DSN. An ODBC system data source is visible to all users on a computer, including NT services. To create a system DSN use the Add button. The button will open a Wizard that will take you step by step to create the required DSN. Some ODBC drivers may be already installed in your computer; some can be installed using the setup program provided by the company that created the database engine. For instance, to connect to a MySQL database, the respective ODBC driver may be downloaded from the Internet, and then it has to be installed in each computer that will connect to the database. |
Connection String |
| A connection string provides an alternative method to connect to a database without any configuration in the Control Panel. The connection string is a text string that indicates how to connect to a specific data provider. Wintempla uses the stdafx.h file to define the connection string as shown in the code below. You may use the Internet to find more information about how to use and create a connection string. |
| stdafx.h |
| ... //Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN>Add>SQL Native Client>Server: computer_name\SQLExpress #define DSN L"dsn_MyDatabase" #define USERNAME L"root" #define PASSWORD L"123" #define CONNECTION_STRING L"DRIVER={SQL Server};server=MyComputer\\SQLEXPRESS;database=MyDabatase;Trusted_Connection=yes" |
| Tip |
| As a web applications run only in few computers (the web servers), it is recommended to use an ODBC for web applications. |
| Tip |
| As the connection string is used in several part of a program, never write the connection string in several parts of your program. If you are using a Wintempla use the macro CONNECTION_STRING defined in the stdafx.h. If you are using C# create a static function of a class that returns the connection string. |
| Problem 2 |
| Most beginners have a hard time trying to decide between: ODBC and a connection string. (a) List the advantages and disadvantages of ODBC. (b) List the advantages and disadvantages of using a connection string. |
| Problem 3 |
| Create a Data Connection using Microsoft Visual Studio. Note that when you Microsoft Visual Studio is connected to a database, you CANNOT delete the database. If you want to run a script that deletes the database, you must disconnect Microsoft Visual Studio from the database. |
| Solution 3 |
| Open the Server Explorer view, if it is not open. Use the context menu, and select the option to add a connection as shown. Press the Change button, select Microsoft SQL Server and press OK. You may use the Test Connection button to test the connection. Once the data connection has been created verify the contents of the category table. |
| Problem 4 |
| Create an ODBC to access the best_buy database. Set the DSN name to dsn_best_buy. |
| Step A |
| Open the Control Panel and then the Administrative Tools. Open the Data Sources (ODBC) to add a new System DSN. |
| Step B |
| Open the System DSN tab and press the Add... button. |
| Step C |
| Complete the information and press the Next button. |
| Step D |
| In the following step press the Next button. Complete the information as shown. Press the Next button. Then, press the Finish button. |
| Step E |
| Press the Test Data Source... button to test the ODBC. |
| Tip |
| In Microsoft SQL Express, the access to the server is through Windows Authentication. Thus, a login and password is not required. However, in Microsoft SQL you must use Windows Authentication with the permissions of the account or provide a login and a password. When using Windows Authentication, the ODBC or the program uses the permissions of the user currently logged on in the computer. In this case, you must configure the permission in the Domain Controller or in the local machine where the database is running. |
| Tip |
| ODBC error: "La arquitectura del DSN especificado no coincide entre el controlador y la aplicación." To fix this error you may execute C:\Windows\SysWOW64\odbcad32.exe |