Use ADO objects to access ODBC data in DELPHI programs
-------------------------------------------------- ----------------------------------
As an ASP enthusiast, I often use ADO objects in ASP pages to operate ODBC databases. I feel that it is indeed very convenient to use ASP to create Web application systems. Although in my programming career, the author prefers the Borland series products and is a bit repulsive to Microsoft products, I am an exception to ASP. One day, it suddenly occurred to me that the ADO object is a standard OLE object. It would be great if ADO could be used to operate the database in a Delphi application. Especially when using DELPHI as a network database application, if the Web site is a WINNT site and supports ASP pages, you can use ADO objects to access the ODBC database without uploading such a large BDE to the site. In this way, you can make full use of the programming advantages of DELPHI and ASP to make better ISAPI/NSAPI/CGI. After programming and testing, you can successfully use ADO objects to access ODBC databases in Delphi. Now I will write down my experience and share it with everyone, so that we can have another method of accessing ODBC databases.
In 32-bit Delphi, you can declare a variant variable (such as AVariant), and then create an OLE object through CreateOleObject, such as AVariant:=CreateOleObject ('ADODB.Connection') to get an instance of the database connection object, and then you can use The methods and properties of this object are used to operate the ODBC database.
The following is a brief introduction to the ADO objects used to access ODBC databases and their methods and properties.
1. Database connection object (ADODB. Connection)
This object is used to establish a connection with the ODBC database, and all operations on the database are performed through this connection.
The database connection object ADODB. Connection functions like the TDatabase object in Delphi.
The method to establish a connection object is (AConnection is a Variant type variable):
AConnection:=CreateOleObject('A DODB.Connection')
The method used to establish a connection is Open, and the usage syntax is (taking the object AConnection as an example):
AConnection.Open( ConnectionString, UserId, PassWord )
The three parameters are all of string type, where UserId and Password are the user name and user password, which can be omitted when used to access the database, because the user name and user password can also be specified in ConnectionString. ConnectionString is a string used to describe ODBC data source information. Its format is:
'PRovider=ProviderName; DSN=DSNName; DRIVER=driver; SERVER=server; DATABASE=database; UID=user; PWD=password'
in:
Provider: Data provider, MSDASQL by default, Microsoft OLEDB, usually omitted;
DSN: The OBDC system data source (DSN) corresponding to the database to be opened is an optional parameter;
DRIVER: The name of the driver used in the database to be opened, such as access corresponding to Microsoft Access Driver (*.mdb), which is an optional parameter;
SERVER: The name of the server where the database to be opened is located, available on this machine (local), and is an optional parameter;
DATABASE: The name of the database to be opened, which is an optional parameter;
UID: user name, used to access the database, is an optional parameter;
PWD: User password, used to access the database, is an optional parameter.
The above parameters are optional, but sufficient information must be provided to describe a system data source.
If an ODBC system DSN has been defined and is named MyDsn, then you can use the following statement to establish a database connection:
AConnection.Open('DSN=MyDsn');
In order to prevent application running errors when the DSN does not exist or its settings are modified by others, you can use ADODB.Connection to create a temporary ODBC data source. This can ensure that the parameter settings of the system DSN we use are correct. The following statement can create a temporary ODBC system DSN, corresponding to an ACCESS database, the path is C:Inetpub wwwroot test.mdb:
AConnection.open('Driver= {Microsoft Access Driver (*.mdb)};DBQ=C:inetpub wwwroot est.mdb')
After establishing an ADODB.Connection, if there is no need to return the operation results (such as deletion, modification, update, etc.), you can perform normal SQL operations on the database. At this time, another method of ADODB.Connection, Execute, is used. The syntax is:
AConnection.Execute( strSQL );
Among them, strSQL is the SQL statement to perform the operation. For example, the deletion operation can be: delete from wfjcommu. Use AConnection.Close to close a database connection.
2. Data set object (ADODB. RecordSet)
If you want to perform query operations and return query results, or if you want to operate the data table more conveniently, you need to use the dataset object.
The data set object ADODB.RecordSet acts like a TTable or TQuery object in Delphi.
The method to create a data set object is (ARecordSet is a Variant type variable):
ARecordSet:=CreateOleObject ('ADODB.RecordSet')
The method to obtain data from the data table is the Open method. The specific usage method is:
ARecordSet.Open( strCommand,ActiveConnection,int CursorType,intLockType, intCommandType);
in:
strCommand: String, which is a command parameter. It can be a Table name, a SQL statement, or a stored procedure (StoredProc) name on the server. The specific parameter needs to be specified by the following parameter intCommandType.
ActiveConnection: The database connection to be used is an ADODB. Connection object.
intCursorType: long integer, Cursor type of the data set, optional parameters, please see the comments in the program.
intLockType: long integer, the locking type of the data table, optional parameters, please refer to the comments in the program.
intCommandType: Long integer, the type of command parameter, used to indicate the role of strCommand. You can specify strCommand as a command (such as a SQL statement) or a data table (TTable) or a stored procedure (StoredProc). For optional parameters, please see the comments in the program.
To execute a SQL query, you can use the following statement:
ARecordSet.Open('Select * from wfjcommu',adOpenStatic,ad LockOptimistic,adCmdText);
Other common properties and methods compared with TTable and TQuery are as follows (see the ASP help file for details):
eof,bof: eof,bof. MoveFirst, MoveLast: First, LastMovePrevious, MoveNext: Prior, NextMove: MoveByAddNew: appendUpdate: PostClose: close
Delete plus Update: delete, all modifications to the data table must use Update to make the operation effective, which is different from Delphi.
Fields[FieldNo]: Fields[FieldNo]
Fields['FieldName']: FieldByName('FieldName')
3. Other common objects (objects corresponding to Delphi):
ADODB.Field: TField ADODB.Parameter: TPara ADODB.Error: EDBEngineError
ADODB.Command: None ADODB.Property: None
Let’s look at an application example:
procedure TForm1.Button1Click(Sender: TObject);
{****************************************************** ****
Use ADO to operate ODBC database
In this program, a temporary ODBC system data source will be created, pointing to a MsAccess database, and then the data tables in it will be displayed, added, modified, deleted and queried.
NOTE: Please include the ComObj unit in the Uses statement
*************************************************** ***}
const{Some constant declarations, please see adovbs.inc for details}
{----Constant description of CommandType----}
adCmdUnknown = 0008;//Unknown, needs to be judged by the system, slow, default value
adCmdText = 0001;//Command statement such as SQL statement
adCmdTable = 0002; //Data table name
adCmdStoredProc = 0004;//Stored procedure name
{----Constant description of CursorType----}
adOpenForwardOnly = 0;//can only be accessed in one direction from front to back, which is the default value
adOpenKeyset = 1;//Modifications to data by other users are visible, but additions and deletions by other users are not visible.
adOpenDynamic = 2; //Additions, modifications and deletions of data by other users are visible
adOpenStatic = 3; //Additions, modifications and deletions of data by other users are not visible
{----Constant description of LockType---}
adLockReadOnly = 1; //Read-only, the default value
adLockPessimistic = 2; //When modifying, lock by single record
adLockOptimistic = 3; //When updating after modification, lock by single record
adLockBatchOptimistic = 4; //Record lock during batch update
var
AConnection, ARecordSet : variant;
longintTemp : integer;
strTemp: string;
intIndex : integer;
begin
{Create a temporary ODBC data source, point to a MsAccess database, and use this DSN to establish a database connection}
AConnection := CreateOleObject('ADODB.Connection');
AConnection.Open('Driver={Microsoft Access Driver (*.mdb)};DBQ=C:inetpubwwwroot est');
{Create a dataset object and extract data from the data table}
ARecordSet := CreateOleObject('ADODB.RecordSet');
ARecordSet.open( 'wfjcommu',AConnection,adOpenStatic,adLockOptimistic,adCmdTable );
memo1.lines.clear;
memo1.lines.add('The original content of the ********data table is as follows********');
{Display the domain name of each domain}
strTemp := '';
for intIndex := 0 to ARecordSet.Fields.count - 1 do
strTemp := strTemp + ARecordSet.Fields[intIndex].name+';';
memo1.lines.add( strTemp );
{Display the content of each domain}
while not ARecordSet.eof do
begin
strTemp := '';
for intIndex := 0 to ARecordSet.Fields.count - 1 do
strTemp := strTemp + ARecordSet.Fields[intIndex].value+';';
memo1.lines.add( strTemp );
ARecordSet.MoveNext;//Move to the next bar, Next
end;
{Add a record}
ARecordSet.AddNew;//Add,Append
ARecordSet.Fields['AName'] := '1';//Access by FieldByName
ARecordSet.Fields['Portable'] := '2';
ARecordSet.Fields(2) := '3';//Access in the form of Fields[index]
ARecordSet.Fields(3) := '4';
ARecordSet.Fields(4) := '5';
ARecordSet.Update;//Update, Post
ARecordSet.MoveFirst;//Move to the first item, First
memo1.lines.add('********The content of the data table after adding a record is as follows********');
{Display the content of each domain}
while not ARecordSet.eof do
begin
strTemp := '';
for intIndex := 0 to ARecordSet.Fields.count - 1 do
strTemp := strTemp + ARecordSet.Fields[intIndex].value+';';
memo1.lines.add( strTemp );
ARecordSet.MoveNext;//Move to the next bar, Next
end;
{Modify the last record}
ARecordSet.MoveLast;
ARecordSet.Fields['AName'] := '11';//Access using FieldByName
ARecordSet.Fields['Portable'] := '22';
ARecordSet.Fields(2) := '33';//Access in the form of Fields[index]
ARecordSet.Fields(3) := '44';
ARecordSet.Fields(4) := '55';
ARecordSet.Update;//Update, Post
ARecordSet.MoveFirst;//Move to the first item, First
memo1.lines.add('********The content of the data table after modifying the last record is as follows********');
{Display the content of each domain}
while not ARecordSet.eof do
begin
strTemp := '';
for intIndex := 0 to ARecordSet.Fields.count - 1 do
strTemp := strTemp + ARecordSet.Fields[intIndex].value+';';
memo1.lines.add( strTemp );
ARecordSet.MoveNext;//Move to the next bar, Next
end;
{delete last record}
ARecordSet.MoveLast;//Move to the last bar, Last
ARecordSet.delete;//Delete, delete
ARecordSet.Update;//Update, not needed in Delphi
ARecordSet.MoveFirst;//Move to the first item, First
memo1.lines.add('********The contents of the data table after deleting the last record are as follows********');
{Display the content of each domain}
while not ARecordSet.eof do
begin
strTemp := '';
for intIndex := 0 to ARecordSet.Fields.count - 1 do
strTemp := strTemp + ARecordSet.Fields[intIndex].value+';';
memo1.lines.add( strTemp );
ARecordSet.MoveNext;//Move to the next bar, Next
end;
ARecordSet.Close;{Close the data set}
{Use SQL statement to query the records named "Zhang San"}
{Note that in SQL statements, strings should be enclosed in single quotes}
ARecordSet.open( 'select * from wfjcommu where AName = ''Zhang San''',
AConnection,adOpenStatic,adLockOptimistic,adCmdText );
memo1.lines.add('********Zhang San’s content is as follows********');
memo1.lines.add( 'Total' + IntToStr( ARecordSet.RecordCount ) + 'Matching records' );
{Display the content of each domain}
while not ARecordSet.eof do
begin
strTemp := '';
for intIndex := 0 to ARecordSet.Fields.count - 1 do
strTemp := strTemp + ARecordSet.Fields[intIndex].value+';';
memo1.lines.add( strTemp );
ARecordSet.MoveNext;//Move to the next bar, Next
end;
{Close data set and database connection}
ARecordSet.close;
AConnection.close;
end;
Listening to others say it is better to experience it yourself by seeing actual examples. In this example, we will demonstrate how to use ADO objects to query, add records, modify records, and delete records on a data table. Please refer to the comments in the program for specific usage. If you have some experience in Delphi database programming, I believe it is not difficult to understand.
The database used in our example is Test.MDB, which has a data table called wfjcommu, with five fields AName, Portable, Tel, BP, and PostAddress, which represent name, mobile phone number, telephone number, pager number, and mailing address respectively.
The above program was debugged and passed under PWIN98+ Delphi 3.0+PWS (Personal Web Server) 4.0.