I have a program which needs to connect to the users database and query it's structure. It could be Access, SQL Server, Oracle, mySQL... 1. I need to find either an autoinc field or the primary key for every table in a database (could be Access, SQL Server, Oracle, mySQL...) At present I do this: I have a TADODataSet called adoTestLimit. I set MaxRecords to 1 and CursorLocation to clUseClient. I did try clUseServer but then it identified autoinc fields as integers, and couldn't identify primary keys. For each table in the database I do something like: [login to view URL]:='SELECT * FROM '+sT_TableName+' WHERE ID=-1'; [login to view URL]:=True; for iV:=0 to adoTestLimit.Recordset.Fields.Count-1 do if ([login to view URL][iV].DataType=ftAutoInc) then ... If it doesn't find an autoinc field then it cycles through the fields again and looks for the primary key using: if ([login to view URL][iV].Properties['keycolumn'].value) then ... It works, but it is really slow when accessing tables over a network, and some users say they can't get it to identify primary keys/autoinc fields in SQL Server / Oracle (works ok on my SQL Server though). The first question is - is there a better, quicker, more reliable way of retrieving this information? Without resorting to database specific tricks. 2. I am building SQL statements to be run on a website, so I need to know what format it expects the date in (e.g. Access uses # as a seperator, most others use a single quote. SQL Server expects month first, mySQL expects Year first. At present I ask the user to set these parameters, but since many aren't technical it is a source of problems. Is there a reliable way of finding out what format it expects? 3. Finally, in Access SQL if a fieldname has special characters in it you need to surround it with square brackets. I found it was safer to do this whether or not it contains special characters because people would use reserved SQL words as field names such as "Date" or "Select". However, if you use square brackets in other databases it generally crashes. The way I test it at present is, I create a SQL statement that selects the first field from the first table in the database and surround the field/table name with Square Brackets. I execute it and if it throws an exception then I assume it cannot handle square brackets. Is there are more elegant way of doing this?
## Deliverables
1) Complete and fully-functional answers to questions.
## Platform
Delphi 7