Use ADO objects in Delphi program to access ODBC database ------------------------------------- ---------------------------------------- Wang Fajun 2003-6-28 Source: Feed Peoplehouse
Keywords: Delphi - MIS
As an ASP enthusiast, the author often uses the ADO object to operate the ODBC database in the ASP page. It is really convenient to create a web application system with ASP. Although in the programming career, the author prefer Borland series products, a bit exclusive to Microsoft products, but the ASP is exception. One day, suddenly, the ADO object is a standard OLE object. If you can use the ADO operation database in the Delphi application, it should be quite good. Especially when you use Delphi network database applications, if the web site is a Winnt site and supports the ASP page, you can access the ODBC database with the ADO object without putting that big BDE uploads to the site. This makes it possible to make better ISAPI / NSAPI / CGI using Delphi and ASP programming advantages. After programming and testing, in Delphi, you can have an ODBC database with ADO objects in Delphi. It is now written out, sharing with you, let us access the ODBC database.
In 32-bit Delphi, you can declare a Variant variable (such as avariant), then create an OLE object via CreateoleObject, such as avariant: = creteoleObject ('adoDb.connection') can get an instance of a database connection object, and then you can use The object's method and attributes operate the ODBC database. The following is briefly introduced to access the ADO objects and methods and properties they used by the ODBC database. 1. Database Connection Objects (AdoDB. Connection) This object is used to establish a connection to the ODBC database, all of which are performed on the database. The database connection object adoDB. Connection works like the TDatabase object in Delphi. Building a connection object is (aconnection): aconnection: = CreateoleObject ('a dodb.connection ") Used to establish a connection method is Open, use the syntax for (take object aconnection): AConnection.Open The three parameters are string types, where userid and password are user names and user passwords, which can be omitted when accessing the database is used because the user name and user password can also be specified in Connectionstring. Connectionstring is a string used to illustrate the ODBC data source information, its format: 'provider = proviDERNAME; DSN = dsnname; driver = driver; server = server; database = database; uid = user; pwd = password' in which: provider: PROVIDER: Data provider, default, MSDasql, for Microsoft OLEDB, usually omitted; DSN: The database to open the OBDC System Data Source (DSN), is an optional parameter; Driver: The name of the driver used to open, If Access corresponds to Microsoft Access Driver (* .mdb), it is an optional parameter; Server: The server name to be opened, the machine is available (local), is an optional parameter; Database: The name of the database to open is Selection parameters; UID: User name, used to access the database, is an optional parameter; PWD: User password, used to access the database, is an optional parameter. All parameters are optional parameters, but sufficient information must be provided to describe a system data source. If an ODBC system DSN is defined, the name is MYDSN, then a database connection can be created with the following statement: aconnection.Open ('DSN = mydsn'); in order to prevent the DSN from being modified by others, it is applied Run an error, you can create a temporary ODBC data source with AdoDb.Connection, which ensures 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/test.mdb ') After establishing an adodb.connection, if you do not need to return an operation result (such as deletion, modification, update, etc.) can make normal SQL operations on the database, at which point ADODB is applied. Another method of Connection, using the syntax: aconnection.execute (strsql); where strsql is the SQL statement that performs the operation, such as the delete from wfjcommu. Use aconnection.close to close a database connection. 2, Dataset Object (AdoDB. Recordset) If you want to perform a query action and return query results, or more easily operate the data table, you need to use the data set object. Data set object AdoDb.Recordset's role like the TTable or TQuery object in Delphi. Establish a data set object to a Variant type variable: alloleObject: = CreateObject ('adoDb.recordset') A method of obtaining data from a data table is an Open method, specific usage method: ArecordSet.Open (STRCOMMAND, ActiveConnection) , int CURSORTYPE, INTLOCKTYPE, INTCOMMANDTYPE); where: strcommand: string, for command parameters, can be a Table name, can be a SQL statement, or a storedProc name on a server, the specific needs back Parameters IntCommandType is specified. ActiveConnection: The database connection to use is an AdoDB. Connection object. INTCURSORTYPE: Long integer, CURSOR type of data set, optional parameters, please refer to the program. INTLOCKTYPE: Long integers, lock types of data sheets, optional parameters, please refer to the program. INTCOMMAVANDTYPE: Type of long integer, command parameters, is used to indicate the role of strcommand, you can specify strcommand as commands (such as SQL statements) or data table (TTABLE) or storage procedure, optional parameters, please refer to the program.
If a SQL query can be used: ArecordSet.Open ('Select * from wfjcommu', adopenStatic, adkeroptimistic, adpMdText); other common attributes and methods are compared to TTable and Tquery (see ASP Help file) : EOF, BOF: EOF, BOF. MoveFirst, MoveLast: First, LastmovePrevious, MoveNext: prior, nextmove: move ,addnew: appendupdate: PostClose: Closedelete plus Update: delete, all the modifications to the data sheet must be valid with the Delphi. Fields [FieldNo]: Fields [FieldNo] Fields [ 'FieldName']: FieldByName ( 'FieldName') 3, other common objects (the object corresponding to Delphi): ADODB.Field: TField ADODB.Parameter: TPara ADODB.Error: EDBEngineErrorADODB For adodb.property: No bottom to see an application example: procedure tform1.button1click (sender: TOBJECT); {********************** **************************** In an ADO ODBC Database This program, a temporary ODBC system data source will be created. A MSAccess database, then display, add, modify, delete, and query the data table. Note: Please include COMOBJ unit in the USES statement ******************* *********************************} const {some constant declarations, please refer to Adovbs.inc} { --- CommandType constant description ----} adcmdunknown = 0008; // Unknown, you need a system to determine, slow speed, for the default value adcmdtext = 0001; // command statement such as SQL statement adcmdtable = 0002; // data Table Name AdcmdStoredProc = 0004; // Stored Procedure Name {---- CURSORTYPE constant description ----} adopenforwardonly = 0; // can only be accessed by the frontward order, for the default value AdopenKeyset = 1; / / Visible for other users' changes to data, but adding and deleting an increase and deletion of other users; // Other users to increase modification and deletion of data to data, and delete data. Delete is not visible
{---- LockType's constant description ---} AdlockReadOnly = 1; // read-only, for the default value AdlockPESSIMISTIC = 2; // When modified, press a single record to lock AdlockOptimistic = 3; // Update after modification When you press a single record to lock the AdlockBatchOptiMistic = 4; // Record the locked Varaconnection, all, defrupted, 4: integer; startp: string; intindex: integer; begin {create a temporary ODBC data source, point to a msaccess Database, and use this DSN to create a database connection} aconnection: = createoleObject ('adoDb.connection "; aconnection.open (' driver = {Microsoft Access Driver (* .mdb)}; dbq = c: / inetpub / wwwroot / Test '); {Establish a dataset object and extract data from the data table} allocordset: = CreateoleObject (' adoDb.recordset '); ArecordSet.Open (' wfjcommu ', aconnection, adopenStatic, adlockoptimistic, adpTable;
Memo1.Lines.clear; Memo1.Lines.Add ('******** Data table The original content is as follows ********'); {Domain name display of each domain} Strtemp: = ' '; for intindex: = 0 to all catset.fields.count - 1 dostrtemp: = strTemp aRectSet.fields [intIndex] .name '; '; memo1.lines.add (strTemp);
{Display of each domain} while not all defordset.eof dobeginstertemp: = ''; for intindex: = 0 to all istertemp: = strTemp defordset.fields [intIndex] .value ';'; MEMO1. LINES.ADD (Strtemp); alldset.movenext; // Move to the lower strip, nextend;
{Add a record} all; // increase, appendarecordset.fields ['Aname']: = '1'; // Access all 'portablenamet.fields [' Portable ']: =' 2 '; defordset .Fields (2): = '3'; // Access all of defordset.fields (3) in fields [index]; 4 '4'; defordset.fields (4): = '5'; defordset.update; // Update, postarecordset.movefirst; // Move to the first, firstmemo1.lines.add ('******* Added the contents of the data table after the record ********' {Show the contents of each domain} while not all defordset.eof dobeginstrTemp: = '; for intindex: = 0 to all defordset.fields.count - 1 dostrtemp: = strTemp all, @fields [intindex] .Value '; '; Memo1.Lines.Add (strTemp); // Move to the lower strip, nextend; {Modify the last record} areadset.movelast; allagedset.fields ['Aname']: = '11'; // Take FieldbyName Way access to alldset.fields ['portable']: = '22'; ArecordSet.fields (2): = '33'; // Access ArecordSet.fields (3) in fields [index] (3): = ' 44 '; defordset.fields (4): =' 55 '; defordset.Update; // update, postarecordset.movefirst; // Move to the first, firstmemo1.lines.add (' *******) The content of the last recorded data sheet is as follows; {Show content of each domain} While not all defordset.eof dobeginstrTemp: = ''; for intIndex: = 0 TO ARECO Rdset.fields.count - 1 dostrtemp: = strtemp allcordset.fields [intindex] .value ';'; memo1.lines.add (strTemp); allMoTset.Movenext; // Move to the lower strip; nextend;
{Delete the last record} all, LastRecordSet.Delete; // delete, deleteareArecordSet.Update; // Update, not required in Delphi does not need defirstset.Movefirst; // moved to the first, firstmemo1. LINES.ADD ('******** Deleted the content of the last recorded data table as follows ********'); {Show the contents of each domain} While not all defordset.eof dobeginstrtemp: = ''; for intindex: = 0 to all-1 dostrtemp: = startemp allcordset.fields [intindex] .Value '; Memo1.Lines.Add (strTemp); usecordset.movenext; // shift To the following, nextend; defordset.close; {close data set}
{Query with the SQL statement} {Note, in the SQL statement, the string should be included in single quotes} all} area} all} areadset.open ('select * from wfjcommu where aname =' Zhang 3 '', Aconnection, adopenStatic, adlockoptimistic, adcturext; memo1.lines.add ('******** Zhang San's content is as follows ********'); memo1.lines.add ( 'Total' INTOSTR (ARECORDSET.Recordcount) 'The recorded record'); {Show the contents of each domain} while not all defordset.eof dobeginstrTemp: = ''; for intIndex: = 0 to all defordset.fields.count - 1 DOSTRTEMP: = Strtemp allx.fields [intindex] .value ';'; memo1.lines.add (strTemp); all.movenext; // Move to the lower strip, nextend;
{Close the data set and database connection} area; In this example, how to demonstrate how to use the ADO object to query a data table, add record, modify the record, and delete record operations. For specific usage, please refer to the comment in the program, if there is a bit Delphi database programming experience, I believe it is not difficult to understand. The database used in our example is Test.mdb, where there is a data sheet for Wfjcommu, five fields Aname, Portable, Tel, BP, Postaddress, indicate name, mobile number, phone number, mobile number, mobile number, and communication addresses. The above programs are debugged under the PWIN98 Delphi 3.0 PWS (Personal Web Server) 4.0.
