Access ODBC database using ADO objects in Delphi program
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 constant description ---} AdlockReadOnly = 1; // read-only, for the default value AdlockPESSIMISTIC = 2; // When modified, press a single record lock AdlockOptimistic = 3; // When updating after modifying, press a single record to lock AdlockBatchOptimistic = 4; // In batches update When record locking varAConnection, ARecordSet: variant; longintTemp: integer; strTemp: string; intIndex: integer; begin {Create a temporary ODBC data sources, points to a MsAccess database, and using the DSN establish a database connection} AConnection: = CreateOleObject ( 'AdoDb.connection'); aconnection.open ('driver = {microsoft access driver (* .mdb)}; dbq = c: / inetpub / wwwroot / test'); {establish a dataset object, and from the data table extracting data} ARecordSet: = CreateOleObject ( 'ADODB.RecordSet'); ARecordSet.open ( 'wfjcommu', AConnection, adOpenStatic, adLockOptimistic, adCmdTable); memo1.lines.clear; memo1.lines.add ( '***** *** The original content of the data sheet is as follows ******** '); {Domain name display each domain} strTemp: =' '; for intIndex: =
0 TO ARECORDSET.FIELDS.COUNT - 1 DOSTRTEMP: = Strtemp all.Name ';'; Memo1.Lines.Add (strTemp); {Show the contents of each domain} While Not all defordset.eof dobeginstemp: = '; for intindex: = 0 to all catset.fields.count - 1 dostrtemp: = strTemp defordset.fields [infindex] .Value '; "Memo1.Lines.Add (strTemp); all.MOVENEXT; / / Move to Next, nextend; {add a record} all; // increase, appendarecordset.fields ['Aname']: = '1'; // Access to-defordset.fields ['Portable'] in fieldbyname; = PORTABLE ']: = '2'; ArecordSet.fields (2): = '3'; // Access ArecordSet.fields in Fields [Index]: = '4'; ArecordSet.fields (4): = '5' 40; // update, postarecordset.movefirst; // Move to the first, firstmemo1.lines.add ('******* Added a recorded data table is as follows **** **** '); {Displays the contents of each domain} while not all defordset.eof dobeginstrTemp: =' '; for intindex: = 0 to all defordset.fields.count - 1 dostrtemp: = strtemp allset.fields [intIndex]. Value ';'; Memo1.Lines.add (strTemp); allMoVenext; // Move to the lower strip, nextend; {Modify the last record} alldset.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 table is as follows; {Displays the contents of each domain} while not all defordset.eof dobeginstrTemp: = ''; for intIndex: = 0 to all defordset.fields. Count - 1 dostrtemp: = strTemp
