Dynamically set ODBC data sources in the Delphi Program

 

Dynamically set ODBC data sources in the Delphi Program

In the Delphi Database Application, we usually have two methods to access the database. one is through the BDE database search engine, that is, using the database driver that comes with Delphi. This method has the advantage of fast speed, but has limited application scope. When the database version is updated, it may not be used to operate new databases. Another method is through ODBC. What is the advantage of this method?

When using ODBC to access the database, the common method is to set an ODBC system data source (system DSN) in the ODBC Management Panel ), then, set a database alias (alias) in DBD or in the program to be DSN, so that you can operate the database as expected. I believe that programmers who have used Delphi for database applications are already familiar with this aspect. I will not say much about it?

Therefore, the author began to study how to dynamically set the content of the ODBC system DSN in the program, so as to increase the robustness of the program. after a whole day of research on the Windows registry, I finally found the secret of setting the DSN In the ODBC Management Program ("the world has its own justice, and the reward will always be paid! ", Not advertising !), I want to share it with you now. please stick your finger too?

The ODBC Administrator sets the DSN secret in the registry. If you do not believe it, you can go to HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC to check it. It will surely make you feel half done.

First, let's take a look at the installed ODBC database driver in the system. in HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ odbcinst. INI, stores information about the ODBC database driver installed. You can find the DLL file corresponding to the installed ODBC database driver. in odbcinst. among the key values of INI \ ODBC drivers, the key name is

Then let's take a look at the registration information of the system DSN in HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC. INI stores the registration information of the system DSN. The DSN parameter we set on the ODBC Management Panel is here.

Next, let's take a look at the procedure of creating an ODBC system DSN, that is, after we complete the parameter settings in the ODBC Management Panel, how does the ODBC Management Program register the DSN information in the registry. take the creation of a system DSN of the MS Access97 type named myaccess as an example. The parameters we specify mainly include the database type (Microsoft Access Driver (*. MDB), data source name (

1. in HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC. add a string key value to ini \ ODBC data sources, which is myaccess = Microsoft Access Driver (*. MDB), where the data source name and database type are respectively. this is to register a system DSN name in the registry.

2. in HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC. create a subkey myaccess in ini, that is, create a key named HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC. INI \ myaccess, and then create some key values under it to describe the configuration information of a system DSN in detail. The main information is (the content in [] is the author's note ):

DBQ = c: \ Inetpub \ wwwroot \ test. mdb [String, indicating the database path]
Description = my access [String, indicating the database description]
Driver = c: \ pwin98 \ System \ odbcjt32.dll [String, indicating the driver, which can be seen in odbcinst. INI]
Driverid = 0x00000019 (25) [number, indicating the driver ID, which cannot be changed]
FIL = MS access; [String, may be related to filter]
Safetransaction = 0x00000000 [number, which may indicate the number of transactional operations supported]
Uid = "" [String, indicating the user name, which is a Null String]

3. in HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC. create a subkey engines in ini \ myaccess, and then create a subkey jet under it. That is, create a key named HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC. INI \ myaccess \ engines \ jet, and then create some key values under it to describe in detail the database engine of a system DSN

Implicitcommitsync = Yes [string, which may indicate whether to immediately reflect data modification]
Maxbuffersize = 0x00000200 (512) [number, indicating the buffer size]
Pagetimeout = 0x00000005 (5) [number, indicating page timeout]
Threads = 0x00000003 (3) [number, which may indicate the maximum number of threads supported]
Usercommitsync = Yes [string, which may indicate whether to immediately reflect data modification to the user]

The above is the basic information for creating a system DSN (other information, such as options or advanced options, is also set here, except that the default information is not listed in the registry ), in the program, follow the above steps to register the table, you can also add a system DSN or modify its configuration. in the example program below, a system DSN will be created according to the above steps. Please pay attention to the program?

{*************************************** ****************
In this program, an ODBC Data Source (DSN) will be created, with the data source name myaccess data source description: My new data source database type: Access97 corresponding database: C: \ Inetpub \ wwwroot \ test. MDB
**************************************** ***************}

{Note that the uses statement should contain the Registry}

Procedure tform1.button1click (Sender: tobject );
VaR registertemp: Tregistry;
Bdata: array [0 .. 0] of byte;
Begin
Registertemp: = Tregistry. Create; // create a registry instance
With registertemp do begin
Rootkey: = HKEY_LOCAL_MACHINE; // you can specify HKEY_LOCAL_MACHINE as the root key.

// Find SOFTWARE \ ODBC. ini \ ODBC Data sources
If openkey ('Software \ ODBC. ini \ ODBC Data Sources ', true) then
Begin
// Register a DSN name
Writestring ('myaccess', 'Microsoft access Driver (*. mdb )');
End
Else
Begin
// Failed to create the key value
Memo1.lines. Add ('add ODBC Data Source failed ');
Exit;
End;

Closekey;

// Locate or create SOFTWARE \ ODBC. ini \ myaccess and write the DSN configuration information
If openkey ('Software \ ODBC. ini \ myaccess', true) then
Begin
Writestring ('dbq', 'c: \ Inetpub \ wwwroot \ test. mdb '); // database directory
Writestring ('description', 'My new source'); // data source description
Writestring ('driver ', 'c: \ pwin98 \ System \ odbcjt32.dll'); // driver DLL file
Writeinteger ('driverid', 25); // driver ID
Writestring ('fil ', 'Ms access;'); // filter basis
Writeinteger ('Safety action', 0); // the number of supported transaction operations
Writestring ('uid', ''); // User Name
Bdata [0]: = 0;
Writebinarydata ('exclusive ', bdata, 1); // non-exclusive mode
Writebinarydata ('readonly', bdata, 1); // non-read-only mode
End
Else // failed to create the key value
Begin
Memo1.lines. Add ('add ODBC Data Source failed ');
Exit;
End;

Closekey;

// Locate or create SOFTWARE \ ODBC. ini \ myaccess \ engines \ Jet
// Write the configuration information of the DSN Database Engine
If openkey ('Software \ ODBC. ini \ myaccess \ engines \ jet', true) then
Begin
Writestring ('implicitcommitsync', 'yes ');
Writeinteger ('maxbuffersize', 512); // buffer size
Writeinteger ('pagetimeout', 10); // page timeout
Writeinteger ('threads', 3); // number of supported threads
Writestring ('usercommitsync', 'yes ');
End
Else // failed to create the key value
Begin
Memo1.lines. Add ('add ODBC Data Source failed ');
Exit;
End;

Closekey;

Memo1.lines. Add ('adding a new ODBC Data Source successfully ');
Free;
End;
End;

The above program is successfully debugged under pwin98 + Delphi3.0.

The following information needs to be set to create a DSN for a common database type ([] is the comment content, except for the special comment, the parameters are described above ):

1. Access (Microsoft Access Driver (*. mdb ))

DBQ, description, driver [odbcjt32.dll], driverid [25], Fil [MS access;],
Safetransaction [0 by default], uid [null by default],
Engines \ jet \ implicitcommitsync [Yes by default],
Engines \ jet \ maxbuffersize [512 by default],
Engines \ jet \ pagetimeout [512 by default],
Engines \ jet \ threads [Default Value: 3],
Engines \ jet \ usercommitsync [Yes by default]
Optional settings:
Systemdb [String, path of the system database],
Readonly [binary, whether to open in read-only mode, 1 is yes, default is 0],
Exclusive [binary, whether to enable exclusive mode, 1 is yes, default is 0],
PWD [String, User Password]

2. Excel (Microsoft Excel Driver (*. xls ))

DBQ [excel97 (= path \ xxx.xls), 5.0/7.0 (= path \ xxx.xls), 4.0 (= path), 3.0 (= path)],
Description, driver [odbcjt32.dll],
Defaultdir [excel97 (<> DBQ), 5.0/7.0 (<> DBQ), 4.0 (= DBQ), 3.0 (= DBQ)],
Driverid [790 (excel97), 22 (5.0/7.0), 278 (4.0), 534 (3.0)],
FIL [excel5.0;], readonly, safetransaction, uid,
Engines \ Excel \ implicitcommitsync,
Engines \ Excel \ maxscanrows [number, number of scanned rows, default value: 8],
Engines \ Excel \ threads,
Engines \ Excel \ usercommitsync,
Engines \ Excel \ firstrowhasname [binary, the first line is a domain name, 1 indicates yes, the default is 1]

Note: DBQ of excel97 and excel7.0/5.0 corresponds to an xls file, while excel4.0 and excel3.0 correspond to a directory. defadir dir corresponds to a directory, in excel97 and excel7.0/5.0, it is the path corresponding to DBQ, while in excel4.0 and excel3.0 it is the same as DBQ; different versions of driverid are different.

3. DBASE (Microsoft DBASE Driver (*. DBF ))

Defaultdir [String, database file directory],
Description, driver [odbcjt32.dll],
Driverid [277 (IV), 533 (5.0)],
FIL [dbase iii;], safetransaction, uid,
Engines \ xbase \ implicitcommitsync,
Engines \ xbase \ collating [String, sorting basis, can be ASCII, international, Norwegian-Danish, Swedish-Finnish],
Engines \ xbase \ deleted [binary, whether to show soft deleted records, 0 indicates display, default: 1],
Engines \ xbase \ pagetimeout [600 by default],
Engines \ xbase \ usercommitsync,
Engines \ xbase \ threads,
Engines \ xbase \ statistics [binary, whether to use the approximate number of rows; 1 is yes, default 0] Note: (The driverid of dbaseiv and dbase5.0 are different)

4. FoxPro (Microsoft FoxPro Driver (*. DBF ))

Defaultdir [directory of database files],
Description, driver [odbcjt32.dll],
Driverid [536 (2.6), 280 (2.5)],
FIL [FoxPro 2.0;], safetransaction, uid,
Engines \ xbase \ collating [String, sorting basis, can be ASCII, international],
Engines \ xbase \ deleted [binary, whether to show soft deleted records, 0 indicates display, default: 1],
Engines \ xbase \ pagetimeout [600 by default],
Engines \ xbase \ usercommitsync,
Engines \ xbase \ threads,
Engines \ xbase \ statistics [binary, whether to use the approximate number of rows; 1 is yes, the default value is 0] Note :( foxpro2.5 and foxpro2.6 have different driverids)

Make the above program into a com or ActiveX control, which can be used in many advanced programming languages such as Delphi, C ++ buider, VB, Vc, and Pb.

Author: Wang Jianjun, Shenzhen

1. You can use the wnetgetuser () function to obtain the user name;
2. The product serial number of Windows 95 can be found in the registry database using Tregistry;
// Obtain the user name
Function GetUserName: ansistring;
VaR
Lpname: pansichar;
Lpusername: pansichar;
Lpnlength: DWORD;
Begin
Result: = '';
Lpnlength: = 0;
Wnetgetuser (nil, nil, lpnlength); // obtain the string length
If lpnlength> 0 then
Begin
Getmem (lpusername, lpnlength );
If wnetgetuser (lpname, lpusername, lpnlength) = no_error then
Result: = lpusername;
Freemem (lpusername, lpnlength );
End;
End; {GetUserName}

 

Bool createdirectory (

Lpctstr lppathname, // pointer to a directory path string

Lpsecurity_attributes lpsecurityattributes // pointer to a Security Descriptor

);

 

VaR name: pchar; I: Cardinal;
Begin
Getmem (name, 255 );
I: = 255;
Getcomputername (name, I );
End;

QQ: 7477697
13818299517
Let's talk about it in detail.