[DB] ODBC 데이타소스 생성하기 (DSN 생성)

ODBC 데이타소스 생성하기 (DSN 생성)
조경민 오후 8:46 2003-01-13 (bro@shinbiro.com)
================================================================

Data Source: Programmatically Configuring an ODBC Data Source
Home |  Overview |  How Do I |  FAQ |  Sample | Tutorial |  ODBC Driver List

This article explains how you can configure ODBC (Open Database Connectivity) data source names programmatically. This gives you flexibility to access data without forcing the user to explicitly use the ODBC Administrator or other programs to specify the names of data sources.

Typically, a user runs the ODBC Administrator program to create a data source, provided that the associated database management system (DBMS) supports this operation.

When creating a Microsoft Access ODBC data source through the ODBC Administrator program, you are given two choices: you can select an existing .MDB file or you can create a new .MDB file. There is no programmatic way of creating the .MDB file from your MFC ODBC application. Therefore, if your application requires that you place data into a Microsoft Access data source (.MDB file), you most likely will want to have an empty .MDB file that you can use or copy whenever you need it.

However, many DBMSs allow programmatic data source creation. Some data sources, such as FoxPro, maintain a directory specification for databases. That is, a directory is the data source and each table within the data source is stored in a separate file (in the case of dBASE or FoxPro, each table is a .DBF file). Drivers for other ODBC databases, such as Microsoft Access and SQL Server, require that some specific criteria be satisfied before a data source can be established. For example, when using the SQL Server ODBC driver you need to have established a SQL Server.

SQLConfigDataSource Example
The following example uses the ::SQLConfigDataSource ODBC API function to create a new Excel data source called “New Excel Data Source”:

SQLConfigDataSource(NULL,ODBC_ADD_DSN, "Excel Files (*.xls)",
                   "DSN=New Excel Data Source\0"
                   "Description=New Excel Data Source\0"
                   "FileType=Excel\0"
                   "DataDirectory=C:\\EXCELDIR\0"
                   "MaxScanRows=20\0");

Note that the data source is actually a directory (C:\EXCELDIR); this directory must exist. The Excel driver uses directories as its data sources, and files as the individual tables (one table per .XLS file).

For additional information on creating tables, see the article Data Source: Programmatically Creating a Table in an ODBC Data Source.

The information below discusses the parameters that need to be passed to the ::SQLConfigDataSource ODBC API function. To use ::SQLConfigDataSource, you must include the ODBCINST.H header file and use the ODBCINST.LIB import library. Also, ODBCCP32.DLL must be in the path at run time (or ODBCINST.DLL for 16 bit).

You can create an ODBC data source name using the ODBC Administrator program or a similar utility. However, sometimes it is desirable to create a data source name directly from your application to obtain access without requiring the user to run a separate utility.

The ODBC Administrator (typically installed in the Windows Control Panel) creates a new data source by putting entries in the Windows registry (or, for 16 bit, in the ODBC.INI file). The ODBC Driver Manager queries this file to obtain the required information about the data source. It’s important to know what information needs to be placed in the registry because you'll need to supply it with the call to ::SQLConfigDataSource.

Although this information could be written directly to the registry without using ::SQLConfigDataSource, any application that does so is relying on the current technique that the Driver Manager uses to maintain its data. If a later revision to the ODBC Driver Manager implements record keeping about data sources in a different way, then any application that uses this technique would be broken. It is generally advisable to use an API function when one is provided. For example, your code is portable from 16 bit to 32 bit if you use the ::SQLConfigDataSource function, as the function will correctly write to the ODBC.INI file or to the registry.

SQLConfigDataSource Parameters
The following explains the parameters of the ::SQLConfigDataSource function. Much of the information is taken from the ODBC API Programmer's Reference supplied with Visual C++ version 1.5 and later.

Function Prototype
BOOL SQLConfigDataSource(HWND hwndParent,UINT fRequest, LPCSTR lpszDriver, LPCSTR lpszAttributes);

Parameters and Usage
hwndParent   The window specified as the owner of any dialog boxes that either the ODBC Driver Manager or the specific ODBC driver creates to obtain additional information from the user about the new data source. If the lpszAttributes parameter doesn’t supply enough information, a dialog box appears. The hwndParent parameter may be NULL; see the ODBC Programmer’s Reference for details.

lpszDriver   The driver description. This is the name presented to users rather than the physical driver name (the DLL).

lpszAttributes   List of attributes in the form “keyname=value”. These strings are separated by null terminators with two consecutive null terminators at the end of the list. These attributes are primarily default driver-specific entries, which go into the registry for the new data source. One important key that is not mentioned in the ODBC API reference for this function is “DSN” (“data source name”), which specifies the name of the new data source. The rest of the entries are specific to the driver for the new data source. Often it is not necessary to supply all of the entries because the driver can prompt the user with dialog boxes for the new values. (Set hwndParent to NULL to cause this.) You might want to explicitly supply default values so that the user is not prompted.

To determine the description of a driver for the lpszDriver parameter using the ODBC Administrator program

Run the ODBC Administrator program.


Choose Add.
This will give you a list of installed drivers and their descriptions. It is this description that you use as the lpszDriver parameter. Note that you use the entire description ? for example, “Excel Files (*.xls)” ? including the file extension and parentheses if they exist in the description.

As an alternative, you can examine the registry (or, for 16 bit, the file ODBCINST.INI), which contains a list of all driver entries and descriptions under the registry key “ODBC Drivers” (or the section [ODBC Drivers] in ODBCINST.INI).

One way to find the keynames and values for the lpszAttributes parameter is to examine the ODBC.INI file for an already configured data source (perhaps one that has been configured by the ODBC Administrator program):

To find keynames and values for the lpszAttributes parameter

Run the Windows registry editor (or, for 16 bit, open the ODBC.INI file).


Find the ODBC data sources information using one of the following.
For 32 bit, find the key HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources in the left-hand pane.
The right-hand pane lists entries of the form: “pub: REG_SZ:<data source name>”, where <data source name> is a data source that has already been configured with the desired settings for the driver you intend to use. Select the data source you want, for example, SQL Server. The items following the string “pub:” are, in order, the keyname and value to use in your lpszAttributes parameter.

For 16 bit, find the section in the ODBC.INI file marked by [<data source name>].
The lines following this line will be of the form “keyname=value”. These are exactly the entries to use in your lpszAttributes parameter.

You might also want to examine the documentation for the specific driver you are going to use. You can find useful information in the online help for the driver, which you can access by running the ODBC Administrator. These help files are usually placed in the WINDOWS\SYSTEM directory for Windows NT, Windows 3.1, or Windows 95.

To obtain online help for your ODBC driver

Run ODBC Administrator.


Choose Add.


Select the driver name.


Choose OK.
When ODBC Administrator displays the information for creating a new data source for that particular driver, select Help. This opens the help file for that particular driver, which generally contains important information concerning the use of the driver.

For related information, see the Installer DLL Function Reference in the ODBC SDK on the MSDN Library CD.


--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.


테이블 생성하기



Data Source: Programmatically Creating a Table in an ODBC Data Source
Home |  Overview |  How Do I |  FAQ |  Sample | Tutorial |  ODBC Driver List

This article explains how to create a table for your data source, using the ExecuteSQL member function of class CDatabase, passing the function a string that contains a CREATE TABLE SQL statement.

For general information about ODBC data sources in MFC, see the article Data Source (ODBC). The article Data Source: Programmatically Configuring an ODBC Data Source describes creating data sources.

Once you have the data source established, you can easily create tables using the ExecuteSQL member function and the CREATE TABLE SQL statement. For example, if you had a CDatabase object called myDB, you could use the following MFC code to create a table:

myDB.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4)" ",
                         OfficeName TEXT(10))");

The code above creates a table called “OFFICES” in the Microsoft Access data source connection maintained by myDB; the table contains two fields “OfficeID” and “OfficeName.” For more information about creating tables as well as primary keys and indexes for them, see Appendix C in the ODBC Programmer’s Reference.

Note   The field types specified in the CREATE TABLE SQL statement may vary according to the ODBC driver that you are using. The Microsoft Query program (distributed with Visual C++ 1.5) is one way to discover what field types are available for a data source. In MS Query, select File, choose Table_Definition, select a table from a data source, and look at the type shown in the “Type” combo box. Appendix C in the ODBC Programmer’s Reference describes the supported SQL syntax. SQL syntax also exists to create indexes. The Programmer’s Reference is in the ODBC SDK on the MSDN Library CD.


--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.