10.9 automatically register DSN and Create Table

10.9 automatically register DSN and Create Table

When writing your own database applications, readers will soon encounter two headaches. First, you must manually register the DSN (Data Source Name) in the ODBC manager before accessing the ODBC data source ). This kind of application requires the user to do additional work, which is not very professional. Another problem is that Appwizard and classwizard do not support table creation. It seems that programmers must first create a table with DBMS before using it. If a database application cannot create tables by itself, its functions will be greatly reduced.

In fact, with some tips, you can allow applications to transparently register DSN with users and create any table. This section aims to teach readers how to solve these two problems. The ODBC and Dao solutions are provided in the following sections.

10.9.1 automatic DSN Registration

Whether using ODBC or Dao, you must register a DSN before accessing the ODBC data source. You can automatically register a DSN by calling the sqlconfigdatasource function. Of course, Dao can be used to directly access some common databases, instead of using ODBC (see 10.8.3 ).

The code in listing 10.14 demonstrates the process of registering a DSN. This code first registers a FOXPRO 2.5 data source named mydb with sqlconfigdatasource, and then calls the cdatabase: OPEN function to connect to the data source. Note that when using this code, you must include the afxdb. h header file. You can put this file in stdafx. h.

　

Listing 10.14 automatic DSN Registration

# Include "afxdb. h"

...

Cdatabase dB;

If (! Sqlconfigdatasource (null, odbc_add_dsn, "Microsoft FoxPro Driver (*. DBF )",

"DSN = mydb/0"

"Defaultdir = C: // mydir/0"

& Quot; fil = FOXPRO 2.5/0 & quot"

"Driverid = 280/0 "))

{

Afxmessagebox ("can't add DSN! ");

Return;

}

Try

{

DB. Open ("mydb ");

}

Catch (cdbexception, E)

{

Afxmessagebox (e-> m_strerror );

Return;

}

End_catch

　

When registering a DSN, the second parameter of the sqlconfigdatasource function should be odbc_add_dsn, and the third parameter specifies the ODBC driver. For details, refer to the driver page of the ODBC manager. The fourth parameter describes the attributes of the data source. It consists of a series of substrings. Each substrings must end with a "/0 ". The most important attribute is "DSN = Data Source Name". Other attributes include the default directory and driver version information. In the above example, The FoxPro 2.5 version is used, so the driverid should be 280. Correspondingly, the driverid OF FoxPro 2.6 is 536, and that of FoxPro 2.0 is 24.

If you do not know how to set the fourth parameter of the sqlconfigdatasource function, you can open the Windows registry and check the attributes of the registered DSN. Run regedit to open the Registry, and then enable HKEY_CURRENT_USER-> Software-> ODBC. INI, you can see the registered DSN, open each DSN, you can see the DSN attributes, the reader can follow the DSN attribute to set the fourth parameter.

The DSN name must be unique. Therefore, if the DSN to be registered has been registered, sqlconfigdatasource modifies the attribute of the original DSN.

　

10.9.2 Use ODBC to create a table

Since ODBC classes do not support DDL, you can only create tables using ODBC APIs. The program needs to call cdatabase: executesql to directly execute the SQL statement.

Listing 10.15 provides an example of creating a table. The program first automatically registers a FOXPRO 2.5 data source named mydb, and then creates a table named offices. DBF file). In this table, there are two text fields, officeid and officename. The length is 4 and 10 bytes respectively. Note: To use this code, you must include afxdb. h and odbcinst. h.

　

Listing 10.15 example of creating a table using ODBC

# Include "afxdb. h"

# Include "odbcinst. H"

...

Cdatabase dB;

If (! Sqlconfigdatasource (null, odbc_add_dsn, "Microsoft FoxPro Driver (*. DBF )",

"DSN = mydb/0"

"Defaultdir = C: // mydir/0"

& Quot; fil = FOXPRO 2.5/0 & quot"

"Driverid = 280/0 "))

{

Afxmessagebox ("can't add DSN! ");

Return;

}

Try

{

DB. Open ("mydb ");

DB. executesql ("create table offices (officeid text (4 )"

", Officename text (10 ))");}

Catch (cdbexception, E)

{

Afxmessagebox (e-> m_strerror );

Return;

}

End_catch

　

　

Executesql executes a real SQL statement create to create a table. It seems that the user should look for this SQL book for research. Note that the table name in a database must be unique. If the table to be created already exists, executesql will generate an exception.

10.9.3 use DAO to create a table

Because Dao directly supports DDL, it is easier to use DAO to create tables than ODBC. The cdaotabledef class of Dao defines the structure of a table, which provides a member function for creating a table.

Listing 10.16 demonstrates an example of using the DAO class to create a table. Note: To use this code, the afxdao. h header file should be included. In this example, First connect to a FOXPRO 2.5 database (actually a directory), then construct a cdaotabledef object, and then call cdaotabledef :: the create function creates a table named students (students. DBF), call cdaotabledef: createfield to create two fields for the table. The field names are ID and name, And the types are integer and dbtext, the field length is 2 and 10 bytes respectively. Finally, call cdaotabledef: append to save the newly created table to the database.

List

10.16 example of creating a table with Dao

# Include

"Afxdao. h"

...

Cdaodatabase daodb;

Try

{

Daodb. Open ("", false, false, "FOXPRO 2.5; database = D: // zwin ");

Cdaotabledef table (& daodb );

Table. Create ("Students ");

Table. createfield ("ID", dbinteger, 2 );

Table. createfield ("name", dbtext, 10 );

Table. append ();

}

Catch (cdaoexception * E)

{

Afxmessagebox (e->

M_perrorinfo-> m_strdescription );

E-> Delete ();

Return false;

}

For instructions on cdaodatabase: open, see 10.8.3. Note that the table name in a database must be unique. If the table to be created already exists, cdaotabledef: Create will generate an exception.