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 operated safely. I believe that programmers who have used DELPHI to do database applications are already 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 the DSN set by the ODBC management program is in the registry. If you don’t believe it, you can go to HKEY_LOCAL_MACHINESoftwareODBC.
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_MACHINESoftwareODBC
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.INIODBC 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.INIDriverName (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:WINDOWSSYSTEMFileName.DLL".
Then look at the registration information of the system DSN. In HKEY_LOCAL_MACHINESoftwareODBCODBC.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:InetpubwwwrootTest.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_MACHINESoftwareODBCODBC.INIODBC 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_MACHINESoftwareODBCODBC.INI, that is, create
Create a key as HKEY_LOCAL_MACHINESoftwareODBCODBC.INIMyAccess, and then create some
Key value, a detailed description of the configuration information of a system DSN. The main information is (the content in [] is the author’s comment):
DBQ=C:InetpubwwwrootTest.mdb [String, indicating database path]
Description=My ACCESS [string, representing database description]
Driver=C:PWIN98Systemodbcjt32.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_MACHINESoftwareODBCODBC.INIMyAccess,
Then create a subkey (SubKey) Jet under it, that is, create a key as
HKEY_LOCAL_MACHINESoftwareODBCODBC.INIMyAccessEnginesJet and then 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 we 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:Inetpubwwwroot est.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 SoftwareODBCODBC.INIODBC Data Sources
if OpenKey('SoftwareODBCODBC.INIODBC 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 SoftwareODBCODBC.INIMyAccess and write DSN configuration information
if OpenKey('SoftwareODBCODBC.INIMyAccess',True) then
begin
WriteString( 'DBQ', 'C:inetpubwwwroot est.mdb' );//Database directory
WriteString( 'Description', 'My new data source' );//Data source description
WriteString( 'Driver', 'C:PWIN98SYSTEModbcjt32.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 SoftwareODBCODBC.INIMyAccessEnginesJet
//Write DSN database engine configuration information
if OpenKey('SoftwareODBCODBC.INIMyAccessEnginesJet',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],
EnginesJetImplicitCommitSync[default is Yes], EnginesJetMaxBufferSize[default 512],
EnginesJetPageTimeout[default is 512], EnginesJetThreads[default is 3],
EnginesJetUserCommitSync [default 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(=pathxxx.xls), 5.0/7.0(=pathxxx.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,
EnginesExcelImplicitCommitSync, EnginesExcelMaxScanRows[number, number of scanned rows, default is 8],
EnginesExcelThreads, EnginesExcelUserCommitSync,
EnginesExcelFirstRowHasName[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,
EnginesXbaseImplicitCommitSync,
EnginesXbaseCollating[String, sorting basis, can be ASCII, International, Norwegian-Danish,
Swedish-Finnish],
EnginesXbaseDeleted[binary, whether to not display soft-deleted records, 0 means display, default is 1],
EnginesXbasePageTimeout[default is 600], EnginesXbaseUserCommitSync,
EnginesXbaseThreads, EnginesXbaseStatistics [binary, whether to use the approximate number of lines, 1 is 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,
EnginesXbaseCollating[String, sorting basis, can be ASCII, International],
EnginesXbaseDeleted[binary, whether to not display soft-deleted records, 0 means display, default is 1],
EnginesXbasePageTimeout[default is 600], EnginesXbaseUserCommitSync,
EnginesXbaseThreads, EnginesXbaseStatistics [binary, whether to use the approximate number of lines, 1 is yes, default 0]
Note: (The DriverId of Foxpro2.5 and Foxpro2.6 versions 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.