In the DELPHI database application, we usually have two ways to access the database. One is to search through the BDE database engine, that is, using the database driver that comes with DELPHI. The advantage of this method is that it is fast, but its application scope is limited. When the database version is updated, it may not be able to operate the new database; another method is through ODBC, which has the advantages The point is that it can be provided with the operating system (such as WINDOWS) and can be adapted to a variety of databases as a standard interface. The disadvantage is that it is slow. In preparation During the process, we can choose one of the methods according to our needs. When using ODBC to access the database, the usual method is to set up an ODBC system data source in the ODBC management panel. (System DSN), and then set a database alias (Alias) in the DBD or in the program to correspond to the DSN, so that you can use The database can be easily operated. I believe that programmers who have used DELPHI to do database applications are already very familiar with this aspect, and I don’t know many. Said. In practical applications, the author has encountered such a situation. Our database application relies on the ODBC system data source. The application program that accesses and operates the database has been running well until one day, a person who is familiar with the WINDOWS system but not familiar with it Too savvy users accidentally modify or delete our pre-set system DSN... Therefore, the author began to study how to dynamically set the content of the ODBC system DSN in the program, so that I can increase my own program. The order is solid. After a whole day of researching the WINDOWS registry, I finally found the secret to setting up DSN with the ODBC manager. ("There is justice in heaven and earth, and you will always be rewarded for your efforts!" This is not an advertisement!) I am writing it down to share it with everyone, and I also ask for advice from experts. The secret of setting DSN by ODBC management program is in the registry. If you don’t believe it, you can go to HKEY_LOCAL_MACHINE/Software/ODBC. Take a look, it will definitely make you feel that you are already halfway successful. First, let's take a look at the ODBC database driver installed in the system. In HKEY_LOCAL_MACHINE/Software/ODBC/ ODBCInst.INI stores information about the installed ODBC database driver. From here you can check the installed ODBC number. Information such as the DLL file corresponding to the database driver. In each key value of ODBCInst.INI/ODBC Drivers, the key name is the driver The name (such as Microsoft access Driver (*.mdb)) and the key value is "Installed", indicating that the driver has been installed. exist ODBCInst.INI/DriverName (DriverName is a driver name, such as Microsoft Access Driver (*.mdb)) , there is detailed information about the driver. We mainly obtain the path and file name of the DLL file corresponding to the ODBC driver from here, that is The key value of the key name Driver is generally "C:/WINDOWS/SYSTEM/FileName.DLL". Then look at the registration information of the system DSN. In HKEY_LOCAL_MACHINE/Software/ODBC/ODBC.INI, the system is stored The registration information of DSN, the DSN parameters we set in the ODBC management panel are here. Let's take a look at the steps to create an ODBC system DSN. That is, after we complete the parameter settings in the ODBC management panel, the ODBC management How does the program register DSN information in the registry? Take creating a Ms Access97 type system DSN named MyAccess as an example. The parameters we specify mainly include database type (Microsoft Access Driver (*.mdb)), data source name (MyAccess), Data source description (My ACCESS), database path (C:/Inetpub/wwwroot/Test.mdb), other parameters such as user name, User password, exclusive, read-only, system database, default directory, buffer size, number of scan lines, page timeout, etc. use the system Default parameters. At this time, the registration system DSN generally should have the following steps: 1． Add a string key in HKEY_LOCAL_MACHINE/Software/ODBC/ODBC.INI/ODBC Data Sources The value is MyAccess = Microsoft Access Driver(*.mdb), where are the data source name and database type respectively. This is to register a system DSN name in the registry. 2. Create a subkey (SubKey) MyAccess in HKEY_LOCAL_MACHINE/Software/ODBC/ODBC.INI, that is, create Create a key as HKEY_LOCAL_MACHINE/Software/ODBC/ODBC.INI/MyAccess, and then create some Key value, which describes the configuration information of a system DSN in detail. The main information is (the content in [] is the author’s comment): DBQ=C:/Inetpub/wwwroot/Test.mdb [String, indicating database path] Description=My ACCESS [string, representing database description] Driver=C:/PWIN98/System/odbcjt32.dll [String, indicating driver, visible ODBCINST.INI] DriverId=0x00000019(25) [Number, indicating driver identification, cannot be changed] FIL=Ms Access; [String, may be related to Filter] SafeTransaction=0x00000000 [Number, may indicate the number of transactional operations supported] UID="" [String, indicating user name, here is an empty string] 3. Create a subkey (SubKey)Engines in HKEY_LOCAL_MACHINE/Software/ODBC/ODBC.INI/MyAccess, Then create a subkey (SubKey)Jet under it, that is, create a key as HKEY_LOCAL_MACHINE/Software/ODBC/ODBC.INI/MyAccess/Engines/Jet and create some under it Key value, which describes in detail the database engine configuration information of a system DSN. The main information is as follows (the content in [] is the author’s annotation): ImplicitCommitSync=Yes [String, may indicate whether data modifications are reflected immediately] MaxBufferSize=0x00000200(512) [number indicating buffer size] PageTimeout=0x00000005(5) [number indicating page timeout] Threads=0x00000003(3) [number, possibly indicating the maximum number of threads supported] UserCommitSync=Yes [String, may indicate whether data modifications will be reflected immediately to the user] The above is the basic information for establishing a system DSN (other information such as options or advanced options are also set here, but because the default information, not listed in the registry), we operate the registry according to the above steps in the program, and can also add a system DSN or modify its configuration. In the following example program, a system DSN will be created according to the above steps. Please pay attention to the comments in the program. {************************************************ **** In this procedure, an ODBC system data source (DSN) will be created, Data source name: MyAccess Data source description: My new data source Database type:ACCESS97 Corresponding database: C:/Inetpub/wwwroot/test.mdb *************************************************** *****} {Note that Registry should be included in the USES statement} 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;//Set the root key value to HKEY_LOCAL_MACHINE //Find Software/ODBC/ODBC.INI/ODBC Data Sources if OpenKey('Software/ODBC/ODBC.INI/ODBC Data Sources',True) then begin //Register a DSN name WriteString( 'MyAccess', 'Microsoft Access Driver (*.mdb)' ); end else begin//Failed to create key value memo1.lines.add('Failed to add ODBC data source'); exit; end; CloseKey; //Find or create Software/ODBC/ODBC.INI/MyAccess and write DSN configuration information if OpenKey('Software/ODBC/ODBC.INI/MyAccess',True) then begin WriteString( 'DBQ', 'C:/inetpub/wwwroot/test.mdb' );//database directory WriteString( 'Description', 'My new data 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( 'SafeTransaction', 0 );//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 key value begin memo1.lines.add('Failed to add ODBC data source'); exit; end; CloseKey; //Find or create Software/ODBC/ODBC.INI/MyAccess/Engines/Jet //Write DSN database engine configuration information if OpenKey('Software/ODBC/ODBC.INI/MyAccess/Engines/Jet',True) then begin WriteString( 'ImplicitCommitSync', 'Yes' ); WriteInteger( 'MaxBufferSize', 512 );//Buffer size WriteInteger( 'PageTimeout', 10 ); //Page timeout WriteInteger( 'Threads', 3 );//The number of threads supported WriteString( 'UserCommitSync', 'Yes' ); end else//Failed to create key value begin memo1.lines.add('Failed to add ODBC data source'); exit; end; CloseKey; memo1.lines.add('Add new ODBC data source successfully'); Free; end; end; The above program was debugged and passed under PWIN98+DELPHI3.0. The following is the information that needs to be set to create a DSN of common database types ([] is the comment content, except for special comments, each parameter can be seen in the previous description): 1． Access(Microsoft Access Driver(*.mdb)) DBQ, Description, Driver[odbcjt32.dll], DriverID[25], FIL[Ms Access;], SafeTransaction[default is 0], UID[default is empty], Engines/Jet/ImplicitCommitSync[default is Yes], Engines/Jet/MaxBufferSize[default 512], Engines/Jet/PageTimeout[default is 512], Engines/Jet/Threads[default is 3], Engines/Jet/UserCommitSync [default is Yes] Optional settings: SystemDb[string, path to system database], ReadOnly[binary, whether to open in read-only mode, 1 means yes, default is 0], Exclusive[binary, whether to open in exclusive mode, 1 means 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 is 8], Engines/Excel/Threads、Engines/Excel/UserCommitSync、 Engines/Excel/FirstRowHasName[binary, whether the first row is the domain name, 1 means 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; DefaultDir corresponds to a directory, which is the path corresponding to DBQ in Excel97 and Excel7.0/5.0. It is the same as DBQ under Excel4.0 and Excel3.0; the DriverID of each version is different. 3. dBase(Microsoft dBase Driver(*.dbf)) DefaultDir[string, directory where the database file is located], 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 not display soft-deleted records, 0 means display, default is 1], Engines/Xbase/PageTimeout[default is 600], Engines/Xbase/UserCommitSync, Engines/Xbase/Threads, Engines/Xbase/Statistics [binary, whether to use the approximate number of lines, 1 means yes, default 0] Note: (The DriverId of the two versions of dBaseIV and dBase5.0 is different) 4. Foxpro(Microsoft Foxpro Driver(*.dbf)) DefaultDir[the directory where the database file is located], 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 not display soft-deleted records, 0 means display, default is 1], Engines/Xbase/PageTimeout[default is 600], Engines/Xbase/UserCommitSync, Engines/Xbase/Threads, Engines/Xbase/Statistics [binary, whether to use the approximate number of lines, 1 means yes, default 0] Note: (The DriverId of Foxpro2.5 and Foxpro2.6 are different) Make the above program into a COM or ActiveX control, which can be used in many high-level programming languages such as DELPHI, C++Buider, VB, VC, and PB. |