dkhatibi
چهارشنبه 15 اسفند 1386, 19:05 عصر
تو سایت About بود . فکر کنم به درد بخوره
Collection of frequently asked questions, answers, tips and tricks about ADO programming.
More of this Feature
Printer friendly versionPrinter friendly version
Join the Discussion
"Post your views and comments to this chapter of the free Delphi database Programming Course"
Discuss!
Related Resources
• free DB Course.TOC
• more Database articles
This chapter of free Delphi/ADO DB course offers specific recommendations to help improve and speed up the development of your database applications made with Delphi and ADO.
Our intention is to update this chapter dynamically with new tips, and code suggestions. If you have a tip (or a question) on (Delphi) ADO programming feel free to add it to this page.
Note that some of the questions you might ask are most likely already answered through the chapters of this Course.
Access, ADOExpress, dbGo
My clients do not have Access on their machines, will my Delphi / ADO application work?
In general: what ever database you create (Paradox or Access) the users of your software do not need to have MS Access or Paradox on their machines. When using Access with ADO, MS provides MDAC (components to access an Access database). Note that Microsoft Jet Database Engine is not included with MDAC 6.0 and above. MDAC and MS Jet Engine can be downloaded from www.microsoft.com/data.
I have Delphi 5 Professional, where is ADOExpress?
Either you'll need to buy at least Delphi 6 Professional, or try with some third party ADO VCL components
Connecting to data stores
How do I connect to a MS Access 2000 database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\My Database.mdb;Persist Security Info=False';
How do I connect to a password protected MS Access 2000 database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=XXXXXX;DataSource=C:\MyDatabase.mdb;Persi st Security Info=False';
What provider should I use for MS Access
For MS Access 97 use Microsoft.Jet.OLEDB.3.51
For MS Access 2000 use Microsoft.Jet.OLEDB.4.0
How do I connect to a dBase database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\My Database.mdb;Extended Properties="dBase 5.0;"';
How do I connect to a Paradox database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\My Database.mdb;Extended Properties="Paradox 7.X;"';
How do I connect to a MS Access database on a CD (read only) drive?
ADOConnection.Mode := cmShareExclusive;
Data retrieving and manipulation
How do I use multiword table / field names (spaces in Table or Field name)?
Enclose multiword names in [ ] brackets:
ADOQuery1.SQL.Text := 'SELECT [Last Name], [First Name] FROM [Address Book]';
How do I use constant fields in an SQL query?
ADOQuery1.SQL.Text := 'SELECT ''2002'', [First Name], Salary FROM Employess';
How do I delete all records in a table?
ADOQuery1.SQL.Text := 'DELETE * FROM TableName';
Why do I keep getting a "-1" for the RecordCount property
If you need the RecordCount to be correct, set the CursorType to something other than ctOpenForwardOnly.
I'm using AutoNumber for Primary Key field to make every record unique. If I want to read or Edit some ADOTable record after one was appended (and Post-ed) I get en error: "The specified row could not be located for updating. Some values may have been changed since it was last read". Why?
After every new record you should use:
var bok: TBookmarkStr;
begin
bok:=adotable1.Bookmark;
adotable1.Requery();
adotable1.Bookmark:=bok;
end;
How do I create a disconnected ADO recordset? I want to run a query, pick the data and delete some records but not physically.
In order to create a disconnected ADO recordset, you must first set the ADODataSets CursorLocation property to "clUseClient". Then open the Recordset. Then set the ADODatasets Connection to Nil. Do not close the ADODataset.
How do I retrieve a system information, for example, list of tables, fields (columns), indexes from the database?
TADOConnection object has an OpenSchema method that retrieves system information like list of tables, list of columns, list of data types and so on. The following example shows how to fill an ADODataSet (DS) with a list of all indexes on a table (TableName):
var DS:TADODataSet;
...
ADOConnection.OpenSchema(siIndexes, VarArrayOf([Unassigned, Unassigned, Unassigned, Unassigned, TableName]), EmptyParam, DS);
How can I improve the performance of my Ado application (like speed up query data retrieval)?
. Avoid returning too many fields. ADO performance suffers as a larger number of fields are returned. For example using "SELECT * FROM TableName" when TableName has 40 fields, and you really need only 2 or 3 fields
. Choose your cursor location, cursor type, and lock type with care. There is no single cursor type you should always use. Your choice of cursor type would depend on the functionality you want like updatability, cursor membership, visibility and scrollability. Opening a keyset cursor may take time for building the key information if you have a lot of rows in the table whereas opening a dynamic cursor is much faster.
. Release your dynamically created ADO objects ASAP.
. Check your SQL expression: when joining tables with Where t1.f1 = t2.f1 and t2.f2 = t2.f2 it is important that f1 and f2 as fields are set to be indexed.
Collection of frequently asked questions, answers, tips and tricks about ADO programming.
More of this Feature
Printer friendly versionPrinter friendly version
Join the Discussion
"Post your views and comments to this chapter of the free Delphi database Programming Course"
Discuss!
Related Resources
• free DB Course.TOC
• more Database articles
This chapter of free Delphi/ADO DB course offers specific recommendations to help improve and speed up the development of your database applications made with Delphi and ADO.
Our intention is to update this chapter dynamically with new tips, and code suggestions. If you have a tip (or a question) on (Delphi) ADO programming feel free to add it to this page.
Note that some of the questions you might ask are most likely already answered through the chapters of this Course.
Access, ADOExpress, dbGo
My clients do not have Access on their machines, will my Delphi / ADO application work?
In general: what ever database you create (Paradox or Access) the users of your software do not need to have MS Access or Paradox on their machines. When using Access with ADO, MS provides MDAC (components to access an Access database). Note that Microsoft Jet Database Engine is not included with MDAC 6.0 and above. MDAC and MS Jet Engine can be downloaded from www.microsoft.com/data.
I have Delphi 5 Professional, where is ADOExpress?
Either you'll need to buy at least Delphi 6 Professional, or try with some third party ADO VCL components
Connecting to data stores
How do I connect to a MS Access 2000 database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\My Database.mdb;Persist Security Info=False';
How do I connect to a password protected MS Access 2000 database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=XXXXXX;DataSource=C:\MyDatabase.mdb;Persi st Security Info=False';
What provider should I use for MS Access
For MS Access 97 use Microsoft.Jet.OLEDB.3.51
For MS Access 2000 use Microsoft.Jet.OLEDB.4.0
How do I connect to a dBase database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\My Database.mdb;Extended Properties="dBase 5.0;"';
How do I connect to a Paradox database?
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\My Database.mdb;Extended Properties="Paradox 7.X;"';
How do I connect to a MS Access database on a CD (read only) drive?
ADOConnection.Mode := cmShareExclusive;
Data retrieving and manipulation
How do I use multiword table / field names (spaces in Table or Field name)?
Enclose multiword names in [ ] brackets:
ADOQuery1.SQL.Text := 'SELECT [Last Name], [First Name] FROM [Address Book]';
How do I use constant fields in an SQL query?
ADOQuery1.SQL.Text := 'SELECT ''2002'', [First Name], Salary FROM Employess';
How do I delete all records in a table?
ADOQuery1.SQL.Text := 'DELETE * FROM TableName';
Why do I keep getting a "-1" for the RecordCount property
If you need the RecordCount to be correct, set the CursorType to something other than ctOpenForwardOnly.
I'm using AutoNumber for Primary Key field to make every record unique. If I want to read or Edit some ADOTable record after one was appended (and Post-ed) I get en error: "The specified row could not be located for updating. Some values may have been changed since it was last read". Why?
After every new record you should use:
var bok: TBookmarkStr;
begin
bok:=adotable1.Bookmark;
adotable1.Requery();
adotable1.Bookmark:=bok;
end;
How do I create a disconnected ADO recordset? I want to run a query, pick the data and delete some records but not physically.
In order to create a disconnected ADO recordset, you must first set the ADODataSets CursorLocation property to "clUseClient". Then open the Recordset. Then set the ADODatasets Connection to Nil. Do not close the ADODataset.
How do I retrieve a system information, for example, list of tables, fields (columns), indexes from the database?
TADOConnection object has an OpenSchema method that retrieves system information like list of tables, list of columns, list of data types and so on. The following example shows how to fill an ADODataSet (DS) with a list of all indexes on a table (TableName):
var DS:TADODataSet;
...
ADOConnection.OpenSchema(siIndexes, VarArrayOf([Unassigned, Unassigned, Unassigned, Unassigned, TableName]), EmptyParam, DS);
How can I improve the performance of my Ado application (like speed up query data retrieval)?
. Avoid returning too many fields. ADO performance suffers as a larger number of fields are returned. For example using "SELECT * FROM TableName" when TableName has 40 fields, and you really need only 2 or 3 fields
. Choose your cursor location, cursor type, and lock type with care. There is no single cursor type you should always use. Your choice of cursor type would depend on the functionality you want like updatability, cursor membership, visibility and scrollability. Opening a keyset cursor may take time for building the key information if you have a lot of rows in the table whereas opening a dynamic cursor is much faster.
. Release your dynamically created ADO objects ASAP.
. Check your SQL expression: when joining tables with Where t1.f1 = t2.f1 and t2.f2 = t2.f2 it is important that f1 and f2 as fields are set to be indexed.