As an ASP enthusiast, I often use ADO objects in ASP pages to operate ODBC databases. I feel that it is very convenient to use ASP to create WEB application systems. Although in my programming career, the author prefers Borland series products and is a bit repellent to Microsoft products, I am an exception to ASP. One day, I had an idea. 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, so that you can fully Use 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 the ODBC database in DELPHI. Now I will write down the experience and share it with you. Let us have another way to access the ODBC database.
---- 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, Then you can use the methods and properties of the object 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('ADODB.Connection')
---- The method used to establish a connection is Open, and the usage syntax is (take 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 password, used to access
---- Used in the database, it can be omitted 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, default is MSDASQL, is Microsoft OLEDB, usually omitted
---- DSN: The OBDC system data source (DSN) corresponding to the database to be opened, which is an optional parameter.
---- DRIVER: The driver name used for the database to be opened, such as access corresponding
----Microsoft Access Driver(*.mdb), is an optional parameter
---- SERVER: The name of the server where the database to be opened is located. It is available on this machine (local) and is an optional parameter.
---- DATABASE: The name of the database to be opened, 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 all optional parameters, but sufficient information must be provided to describe a system data source.
---- If an ODBC system DSN has been defined, 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:Inetpubwwwrootest.mdb AConnection.open('Driver={Microsoft Access Driver (*.mdb)};DBQ=C:inetpubwwwrootest.mdb')---- After establishing an ADODB.Connection, if you do not need to return the operation results (such as deletion, modification, update, etc.), you can perform normal SQL operations on the database. At this time, use another method Execute of ADODB.Connection. The usage 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.
---- ２． 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 data set object.
---- The data set object ADODB.RecordSet functions 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 of obtaining data from the data table is the Open method. The specific usage method is:
ARecordSet.Open( strCommand,ActiveConnection,intCursorType,intLockType,intCommandType);
---- Among them: 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 intCommandType needs to be specified.
---- 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 data table (TTable) or stored procedure (StoredProc). For optional parameters, please see Comments in the program.
---- If you execute a SQL query, you can use the following statement:
ARecordSet.Open('Select * from wfjcommu',adOpenStatic,adLockOptimistic,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: TFieldADODB.Parameter: TParaADODB.Error: EDBEngineErrorADODB.Command: None ADODB.Property: None
---- Let's take a look at an application example. It's better to hear what others say than to see the actual example yourself. 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.
---- In our example, the database used is Test.MDB. There is a data table called wfjcommu, which has five fields: AName, Portable, Tel, BP, and PostAddress, which represent name, mobile phone number, phone number, and pager number respectively. and correspondence address.
----
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 queryed. Note: Please click on Uses The statement contains the ComObj unit
*************************************************** ***}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;//The addition, modification and deletion of data by other users are visible adOpenStatic = 3;//The addition, modification and deletion of data by other users are not visible {---- Constant description of LockType---} adLockReadOnly = 1; //Read-only, the default value adLockPessimistic = 2; //When modifying, lock according to a single record adLockOptimistic = 3;//Lock by a single record when updating after modification adLockBatchOptimistic = 4;//Lock the record when updating in batches var AConnection, ARecordSet: variant; longintTemp: integer; strTemp: string; intIndex: integer;begin {Create a Temporary ODBC data source, pointing to a MsAccess database, and using this DSN to establish a database connection} AConnection := CreateOleObject('ADODB.Connection'); AConnection.Open('Driver={Microsoft Access Driver (*.mdb)};DBQ=C:inetpubwwwrootest'); {Create a data set 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 each field domain name} strTemp := '; for intIndex := 0 to ARecordSet.Fields.count - 1 do strTemp := strTemp + ARecordSet.Fields[intIndex].name+';'; memo1.lines.add( strTemp ); {Display the contents of each field} 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 line, Next end; {Add a record} ARecordSet.AddNew;//Add, Append ARecordSet.Fields[ 'AName'] := '1'; //Access ARecordSet.Fields['Portable'] using FieldByName := '2'; ARecordSet.Fields(2) := '3'; //Access ARecordSet.Fields(3) := '4'; ARecordSet.Fields(4) := '5' in the form of Fields[index] ; 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 contents of each field} 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 record, Next end; {Modify the last record} ARecordSet.MoveLast; ARecordSet.Fields['AName'] := '11'; //Access ARecordSet using FieldByName. Fields['Portable'] := '22'; ARecordSet.Fields(2) := '33'; //Access ARecordSet.Fields(3) as Fields[index] := '44'; ARecordSet.Fields(4) := '55'; ARecordSet.Update;//Update, Post ARecordSet.MoveFirst;// Move to the first item, First memo1.lines.add('********The contents of the data table after modifying the last record are as follows**********'); {Display the contents of each field } 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 record, Next end; {Delete the last record} ARecordSet.MoveLast;//Move to the last line, Last ARecordSet.delete;//Delete, delete ARecordSet.Update;//Update, not needed in Delphi ARecordSet.MoveFirst;//Move to the first line, First memo1.lines.add ('********The contents of the data table after deleting the last record are as follows********'); {Display the contents of each field} 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, query records named "Zhang San"} {Note that in the SQL statement, the string 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 contents of each field} 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 the data set and database connection} ARecordSet.close; AConnection.close;end;