1. ADO
1.1. How do I check in my application if ADO is installed?
1.2. What is the newest ADO version? updated!!!
1.3. Where can I find ADO informations? updated!!!
1.4. Why are Adonis components not available for Delphi 2 and C++ Builder 1?
1.5. How do I get ADO error information?
1.6. How do I get ADO error information in my C++ Builder application?
1.7. How do I avoid showing ADO exceptions?
1.8. How do I create and use a TADODataSet instance in my C++ Builder
application?
1.9. How do I create an ADO recordset in my C++ Builder application?
1.10. How do I drop a table?
1.11. I am trying to use Adonis in a multithreaded application /in a
service/ in a CGI or WinCGI application. How do I solve the 'CoInitialize has not been
called' error?
1.12. How do I use multiword table names?
1.13. Why is Adonis slow opening very large tables?
1.14. How to find value which contains ' (quote) character?
1.15. How to read ADO properties in my application?
1.16. How do I retrieve in my application parameters from stored procedure?
1.17. How do I retrieve in design time parameters from stored procedure?
1.18. Why my parameterized SQL statement don't works?
1.19. Why OnADOError event don't works for ExecSQL and ExecProc?
1.20. How can I directly access ADO fields of current record?
1.21. How do I view MS SQL server PRINT messages?
1.22. How do I use constant fields in SQL query?
1.23. Why I can't compile my application with ADO events?
1.24. How to use asynchronous SQL execution?
1.25. How do I use Access indexes?
1.26. How do I retrieve all users connected to the Access database?
1.27. How do I achieve the best ADO performance?
1.28. How do I retrieve Jet ISAM statistics?
1.29. How do I delete all the records in a table?
1.30. How do I use TADOStream component?
1.31. How do I use TADORecord component?
1.32. How do I use GetRows function?
1.33. How do I use Seek method on compound indexes?
1.34. How do I retrieve users connected to Access database?
1.35. How do I retrieve autoincrement field information?
1.36. How do I retrieve the ADO properties of TADODataSet at runtime? new!!!
2. OLE DB
2.1. What OLE DB providers do I use for MS Access? MSSQL7? Oracle?
2.2. Where I can find all the available OLE DB providers?
3. Data sources
3.1. What Jet 4.0 ISAM drivers are available on my computer?
3.2. How do I connect to a dBase database?
3.3. How do I connect to a Paradox database?
3.4. How do I connect to an Excel file?
3.5. How do I export MS Access table into an Excel file?
3.6. How do I connect to a text file?
3.7. How do I export MS Access table into a text file?
3.8. How do I connect to a CSV (comma separated values) text file?
3.9. How do I connect to a HTML file?
3.10. How do I export MS Access table into an HTML file?
3.11. How do I save a recordset in XML (Extensible Markup Language)
format?
3.12. How do I open a recordset from XML file?
3.13. How do I save a recordset in ADTG (Advanced Data TableGram)
format?
3.14. How do I open a recordset from an ADTG file?
3.15. How do I use the ADO DataFactory with Adonis?
3.16. How do I use UDL files?
3.17. How do I create a UDL file in my application?
3.18. How do I create a UDL file in my C++ Builder application?
3.19. How do I specify a MS Access workgroup information file?
3.20. How do I connect to a MS Access password protected database?
3.21. How do I connect to a MS Access database on a CD drive?
3.22. How do I instantiate a 'creatable' ADO Recordset?
3.23. How can I query heterogenous datasources (diverse data
sources)?
3.24. How to I disconnect a recordset?
3.25. How do I set database connection interactively in my application?
3.26. How do I reload a saved ADTG recordset, modify it, then reconnect and
update database?
3.27. How do I reconnect a disconnected ADODataSet and apply changes?
3.28. How do I read connection parameters from UDL file?
4. RDS
4.1. I can't get the nTier ADO sample application to work over
DCOM?
4.2. I can't get the nTier ADO sample application to work over
HTTP?
5. ADOX
5.1. How do I create a new Access database in my application?
5.2. How do I retrieve all table names from a database?
5.3. How to create MS Access table?
5.4. How do I create an Access stored query?
5.5. How do I create an Access parameterized stored query?
5.6. How do I create an Access string field with AllowZeroLength property?
5.7. How do I create an Access autoincrement field?
5.8. How do I access Access description fields?
5.9. How do I use Adonis database connection in ADOX?
5.10. How do I create Access linked table?
5.11. How do I create Access memo field?
5.12. How do I create Access secondary index which allow duplicate keys?
5.13. How do I create Access secondary index which ignore nulls in the columns?
5.14. How do I set up Access relationships between tables?
5.15. How do I specify Access referential integrity?
5.16. How do I retrieve a list of Access groups/users?
5.17. How do I retrieve/change user permissions?
5.18. How do I create "Allow Zero Length" field?
6. JRO
6.1. How do I compact an Access database?
6.2. How do I change a password of Access database?
6.3. How do I make an Access database replicable?
6.4. How do I make an Access object replicable?
6.5. How do I make an Access object local?
6.6. How do I create an Access replica?
6.7. How do I create an Access partial replica?
6.8. How do I retrieve a list of Access replica filters?
6.9. How do I synchronize Access replicas?
7. OLAP
7.1. How do I use the MSOLAP provider?
8. SQLDMO
8.1. How do I create a new MS SQL server database?
8.2. How do I retrieve a list of MS SQL server database users?
8.3. How do I retrieve a list of registered MS SQL servers?
8.4. How do I retrieve a list of databases from registered MS SQL server?
8.5. How do I retrieve a list of tables in any given database from registered MS SQL server?
8.6. How do I drop a table?
8.7. How do I create a new table?
8.8. How do I set a table permissions?
1.1. How do I check in my application if ADO is
installed?
try
ShowMessage('ADO version ' + GetADOVersion + ' installed');
except
ShowMessage('ADO not installed');
end;
Also you can use the supplied external ADOTEST.EXE
utility to confirm that ADO is correctly installed.
Adonis 4.0 supports MDAC Installed Version
type library, so you can detect MDAC version precisely (ADO 2.5 or higher
required):
uses MDACVer;
ShowMessage(CoVersion.Create.String_);
1.2. What is the newest ADO version?
The latest ADO version is ADO 2.8.
http://msdn.microsoft.com/data
1.3. Where can I find ADO informations?
http://msdn.microsoft.com/data
Newsgroups at msnews.microsoft.com:
microsoft.public.data.ado
microsoft.public.data.ado.rds
microsoft.public.data.oledb
microsoft.public.data.oledb.olap
microsoft.public.vb.database.ado
MDAC Installation
INFO: ADO 2.0 and ADO 2.1 Binary Compatibility
INFO: ADO 2.1 Ships with an ADO 2.0 Type Library
HOWTO: Maintain Binary Compatibility in Components Exposing ADO
INFO: Maintaining Binary Compatibility in Components with ADOR
Clsidvw.exe OLE/Createable Objects Registry Diagnostic
Remote Data Service (RDS) Frequently Asked Questions
INFO: Err 0x80070005 if RDS Bus Obj not Added to ADCLAUNCH Key
Migrating from DAO to ADO Using ADO with the Jet Provider
Resynchronising Disconnected ADO Recordsets
PRB: Views Created Using ADOX Are Not Visible in Access 2000
ClsIDView tool
ADO FAQ
RDS FAQ
Introducing ADO+
ACC2000: Updated Version of Microsoft Jet 4.0 Available in Download Center
INFO: MDAC Version 2.6 and Later Do Not Contain Jet or Desktop ODBC Drivers
How to Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
1.4. Why are Adonis components not available for Delphi
2 and C++ Builder 1?
The VCL in Delphi 2 and C++ Builder 1 doesn't support custom
datasets. The primary Adonis components are TDataSet descendants (custom
datasets).
1.5. How do I get ADO error information?
This code can be placed into OnPostError, OnADOError, etc:
uses ADO;
with ADODataSet.Errors do
for i := Count - 1 downto 0 do
with Item[i] do
ShowMessage(
' Description: ' +
Description + #13 +
' Number: ' +
IntToStr(Number) + #13 +
' Native: ' +
IntToStr(NativeError) + #13 +
' Source: ' +
Source + #13 +
' State: ' +
SQLState + #13);
with ADODatabase.Errors do
for i := Count - 1 downto 0 do
with Item[i] do
ShowMessage(
' Description: ' +
Description + #13 +
' Number: ' +
IntToStr(Number) + #13 +
' Native: ' +
IntToStr(NativeError) + #13 +
' Source: ' +
Source + #13 +
' State: ' +
SQLState + #13);
1.6. How do I retrieve ADO error information in my C++
Builder application?
This code can be placed into OnPostError, OnADOError, etc:
_di_Errors Errors = ADODatabase->Handle->Errors;
for (int
i = Errors->Count - 1; i >= 0; --i)
ShowMessage(
" Description: " +
Errors->Item[i]->Description + "\n" +
" Number: " +
IntToStr(Errors->Item[i]->Number) + "\n" +
" Native: " +
IntToStr(Errors->Item[i]->NativeError) + "\n"
+
" Source: " +
Errors->Item[i]->Source + "\n" +
" State: " +
Errors->Item[i]->SQLState + "\n");
1.7. How do I avoid showing ADO exceptions?
procedure
TForm1.ADODatasetADOError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
begin
Action := daAbort;
end;
1.8. How do I create and use a TADODataSet instance in
my C++ Builder application?
// this line fixes an incorrectly generated ADO
header file
__interface Ado::_Recordset : public
Recordset15 {};
void __fastcall
TFormAdonisDemo::Button1Click(TObject *Sender)
{
TADODataSet *Table = new TADODataSet(this);
Table->DatabaseProvider = "Microsoft.Jet.OLEDB.4.0";
Table->DatabaseConnect = "Data Source =
simple.mdb";
Table->CommandType = ctTable;
Table->TableName = "COUNTRY";
Table->Active = true;
Table->Insert();
Table->FieldByName("NAME")->AsString =
"MyName";
Table->FieldByName("AREA")->AsInteger
= 10000;
Table->Post();
Table->Close();
delete Table;
}
1.9. How do I create an ADO recordset in my C++
Builder application?
// this line fixes an incorrectly generated ADO header
file
__interface Ado::_Recordset : public
Recordset15 {};
void __fastcall
TFormAdonisDemo::Button1Click(TObject *Sender)
{
_di__Recordset Recordset = CoRecordset::Create(NULL);
Recordset->Open(Variant("SELECT * FROM COUNTRY"), Variant("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=simple.mdb"),
adOpenKeyset, adLockOptimistic, adCmdText);
ShowMessage("Record count = " +
IntToStr(Recordset->RecordCount));
}
1.10. How do I drop a table?
ADODatabase.ExecSQL('DROP TABLE MyTable');
1.11. I am trying to use Adonis in a multithreaded
application /in a service/ in a CGI or WinCGI application. How do I solve the
'CoInitialize has not been called' error?
CoInitialize prepares you application to instantiate COM objects. In a normal
application Delphi handles this for you. Sometimes you may have to do this manually. In
each thread you need to call CoInitialize and CoUninitialize:
uses ActiveX;
procedure MyThread.Execute;
begin
CoInitialize(nil);
try
// your code
finally
CoUninitialize;
end;
end;
1.12. How do I use multiword table names?
Enclose multiword names in [ ] brackets:
ADODataSet1.SQL.Text := 'SELECT * FROM [Financial
Reports]';
1.13. Why is Adonis slow opening very large
tables?
The default behaviour in Adonis is to use a Client cursor as it offers more
functionality than a Server cursor. Client cursors must load the entire recordset
into memory. If your recordset is large consider using a Server cursor which will only
load a small number (CacheSize) records at one time.
CursorLocation := clServer;
Alternatively you can use asynchronous fetching of the recordset.
1.14. How to find value which contains ' (quote)
character?
if ADODataSet.Locate('FieldName', 'er''''ik', []) then
ShowMessage('Find');
1.15. How to read ADO properties in my application?
var
i: Integer;
Value: String;
with ADODataset.Recordset.Properties do
for i := 0 to Count - 1 do
begin
try Value :=
Item[i].Value; except Value := '' end;
ShowMessage(Item[i].Name + ':' + Value);
end;
1.16. How do I retrieve in my application parameters
from stored procedure?
ADOStoredProc.RetrieveParams;
1.17. How do I retrieve in design time parameters from
stored procedure?
Click Clear button in Adonis Parameters editor, then confirm parameters
retrieving.
1.18. Why my parameterized SQL statement doesn't
works?
'ParamType' and 'DataType' parameter properties can't be retrieved from SQL
statement, so you need to set it in your code:
with ADODataSet do
begin
SQL.Text := 'SELECT * FROM MyTable WHERE ID >
:Param';
ParamByName('Param').ParamType :=
ptInput;
ParamByName('Param').DataType :=
ftInteger;
ParamByName('Param').AsInteger := 1;
Open;
end;
Or you can use automatic parameter retrieving (must be supported by OLE DB
provider) using RetrieveParams procedure:
with ADODataSet do
begin
SQL.Text := 'SELECT * FROM MyTable WHERE ID >
:Param';
RetrieveParams;
ParamByName('Param1').AsInteger := 1;
Open;
end;
In this case names of parameters can be different from names in SQL statement (depends on
OLE DB provider).
1.19. Why OnADOError event doesn't works for ExecSQL and
ExecProc?
ExecSQL and ExecProc methods don't use OnADOError event. Purpose of this event
is catching internal Adonis exceptions (for example when user works with DBGrid, because
there is no other way to catch these exceptions). Errors raised from ExecSQL or ExecProc
can be easily catched in your code, so there is no need for event processing. In Adonis it is possible to use CheckADOError method to use OnADOError event with ExecSQL,
ExecProc or even custom ADO calls:
with ADOStoredProc do
try
ExecProc;
except
on E: Exception do CheckADOError(E.Message);
end;
1.20. How can I directly access ADO fields of current
record?
You must first use UpdateCursorPos method to ensure, that physical ADO recordset
position matches the logical cursor position of ADODataSet:
ADODataSet.UpdateCursorPos;
ADODataSet.Recordset.Fields.Item['ID'].Value;
1.21. How do I view MS SQL server PRINT messages?
PRINT messages are retrieved in Errors.Description property:
CREATE PROCEDURE MyStoredProc AS
PRINT "Hello"
ADOStoredProc1.ExecProc;
with ADODatabase1.Handle.Errors do
for i := Count - 1 downto 0 do
ShowMessage(' Description: ' +
Item[i].Description);
1.22. How do I use constant fields in SQL query?
SELECT 'Hello', Name FROM MyTable
1.23. Why I can't compile my application with ADO
events?
Add ADO unit into interface unit list:
interface
uses ADO, ...
1.24. How to use asynchronous SQL execution?
ADODataSet.ExecProc;
try
// some other statements
finally
ADODataSet.CompleteProc;
end;
1.25. How do I use Access indexes?
1. OLE DB provider must supports indexes
2. CursorLocation property must be
set to clServer
3. CommandType property must be set
to ctTableDirect
4. opWithoutADOEvents in Options property must be True
See also Direct example supplied with Adonis.
1.26. How do I retrieve all users connected to
the Access database?
const JET_SCHEMA_USERROSTER = '{947bb102-5d43-11d1-bdbf-00c04fb92675}';
with
ADOConnection.OpenSchema(adSchemaProviderSpecific, EmptyParam, JET_SCHEMA_USERROSTER) do
while not
EOF do
begin
ShowMessage('Computer: ' + Fields['COMPUTER_NAME'].Value);
ShowMessage('Login: ' + Fields['LOGIN_NAME'].Value);
MoveNext;
end;
1.27. How do I achieve the best ADO performance?
Use native OLE DB provider
- for MS Access 97 use Microsoft.Jet.OLEDB.3.51
- for MS Access 2000 use Microsoft.Jet.OLEDB.4.0
- for MS SQL 7 use SQLOLEDB
Use server cursors (CursorLocation := clServer), but they may have
some limitation in functionality
Use asynchronous fetching/executing
Best performance you achieve with MS Access 2000 database,
Microsoft.Jet.OLEDB.4.0 provider and ctTableDirect CommandType, see Q74
Use SQL statements when working with multiple records instead of
processing each record separately in loop
Don't use SELECT * FROM MyTable, but instead specify each field
explicitly: SELECT Name, Address FROM MyTable
1.28. How do I retrieve Jet ISAM
statistics?
const
JET_SCHEMA_ISAMSTATS = '{8703b612-5d43-11d1-bdbf-00c04fb92675}';
var
i: Integer;
Line: String;
with ADOConnection.OpenSchema(adSchemaProviderSpecific, EmptyParam, JET_SCHEMA_ISAMSTATS)
do
while not EOF
do
begin
Line := '';
with Fields do
for i := 0
to Count - 1 do
Line := Line + Item[i].Name + ':' + IntToStr(Item[i].Value) +
#13;
ShowMessage(Line);
MoveNext;
end;
1.29. How do I delete all the records in a
table?
ADODatabase.ExecSQL('DELETE FROM MyTable');
1.30. How do I use TADOStream component?
with TADOStream.Create(Self) do
try
Active := True;
Source := 'URL=http://computer/MyFile.htm';
Charset := 'ascii';
State := adStateOpen;
// shows content of MyFile.htm
ShowMessage(ReadText(adReadAll));
finally
Free;
end;
1.31. How do I use TADORecord component?
with TADORecord.Create(Self) do
try
Active := True;
ActiveConnection := 'URL=http://localhost/xxx';
State := adStateOpen;
// shows all fields
for i := 0 to
ADOFields.Count - 1 do
ShowMessage(ADOFields[i].Name + ' : '
+ String(ADOFields[i].Value));
finally
Free;
end;
1.32. How do I use GetRows function?
var
Rows: OleVariant;
i, j: Integer;
// returns and shows first 5 rows, fields NAME and CAPITAL
only
Rows := ADODataSet.RecordSet.GetRows(5, adBookmarkFirst, VarArrayOf(['NAME',
'CAPITAL']));
for i := VarArrayLowBound(Rows, 1)
to VarArrayHighBound(Rows, 1) do
for j := VarArrayLowBound(Rows, 2)
to VarArrayHighBound(Rows, 2) do
ShowMessage(Rows[i, j]);
1.33. How do I use Seek method on compound indexes?
ADODataSet.Index := 'IndexAreaPopulation';
if ADODataSet.Seek(VarArrayOf([100000, 1000000]), soAfterEQ)
then
ShowMessage('Find');
1.34. How do I retrieve users connected to Access database?
const
JET_SCHEMA_USERROSTER = '{947bb102-5d43-11d1-bdbf-00c04fb92675}';
var
i: Integer;
Line: String;
with ADOConnection1.OpenSchema(adSchemaProviderSpecific, EmptyParam, JET_SCHEMA_USERROSTER)
do
while not EOF
do
begin
Line := '';
with Fields do
for i := 0 to Count - 1
do
begin
Line := Line + Item[i].Name + ':';
if VarIsNull(Item[i].Value)
then
Line := Line + '(Null)'
else
Line := Line + String(Item[i].Value);
Line := Line + #13;
end;
// remove zero bytes
for i := 1 to Length(Line)
do
if Line[i] =
#0 then
Line[i] := ' ';
ShowMessage(Line);
MoveNext;
end;
1.35. How do I retrieve autoincrement field information?
if ADODataSet.Recordset.Fields['MyField'].Properties['ISAUTOINCREMENT'].Value
then
ShowMessage('Is autoincrement field');
1.36. How do I retrieve the ADO properties of TADODataSet at runtime?
with ADODataSet.Recordset.Properties do
for i := 0 to Count - 1
do
ShowMessage(Item[i].Name + ': ' + WideString(Item[i].Value));
2.1. What OLE DB providers do I use for MS
Access? MSSQL7? Oracle?
ODBC: MSDASQL
MS Access: Microsoft.Jet.OLEDB.4.0 (Access 2000) or Microsoft.Jet.OLEDB.3.51
(Access 97)
MS SQL 7: SQLOLEDB
Oracle: MSDAORA
2.2. Where I can find all the available OLE DB
providers?
http://www.microsoft.com/data/oledb/products/product.htm
CodeBase OLE DB/ADO provider
3.1. What Jet 4.0 ISAM drivers are available on my
computer?
Run program REGEDIT.EXE and see next key, which contain list of Jet 4.0 ISAM
drivers:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats
I am aware about these drivers:
dBase 5.0
dBase III
dBase IV
Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0
Exchange 4.0
HTML Export
HTML Import
Jet 2.x
Jet 3.x
Lotus WJ2
Lotus WJ3
Lotus WK1
Lotus WK3
Lotus WK4
Outlook 9.0
Paradox 3.X
Paradox 4.X
Paradox 5.X
Paradox 7.X
Text
3.2. How do I connect to a dBase database?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data
Source=C:\DbFiles;Extended Properties="dBase 5.0;"';
3.3. How do I connect to a Paradox database?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\PxFiles;Extended
Properties="Paradox 7.X;"';
3.4. How do I connect to an Excel file?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\MyFile.xls;Extended
Properties="Excel 8.0;"';
ADODataSet.TableName := '[MySheet$]';
3.5. How do I export MS Access table into an
Excel file?
ADODatabase.ExecSQL('SELECT * INTO MySheet IN
"c:\MyFile.xls" "Excel 8.0;" FROM MyTable');
3.6. How do I connect to a text file?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\TxtFiles;Extended
Properties="Text;"';
3.7. How do I export MS Access table into a text
file?
ADODatabase1.ExecSQL('SELECT * INTO MyText#txt IN
"c:\" "Text;" FROM MyTable');
You can specify delimiter, field descriptions, etc in SCHEMA.INI
file:
[MyText.txt]
ColNameHeader=True
CharacterSet=1250
Format=Delimited(,)
Col1=NAME Char Width 24
Col2=CAPITAL Char Width 24
Col3=CONTINENT Char Width 24
Col4=AREA Float
Col5=POPULATION Float
3.8. How do I connect to a CSV (comma separated
values) text file?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\TxtFiles;Extended
Properties="Text;"';
ADODataSet.TableName := 'TxtFile#csv';
SCHEMA.INI file must be created and placed into same directory as
TxtFile.csv (in this example C:\TxtFiles). Content of SCHEMA.INI:
[TxtFile.csv]
Format=CSVDelimited
You can also specify field definitions in SCHEMA.INI:
[country.txt]
ColNameHeader=True
CharacterSet=OEM
Format=Delimited(;)
Col1=NAME Char Width 24
Col2=CAPITAL Char Width 24
Col3=CONTINENT Char Width 24
Col4=AREA Float
Col5=POPULATION Float
Format specifier valid values are:
TabDelimited - fields are delimited by tabs
CSVDelimited - fields are delimited by commas (comma-separated values)
Delimited(custom character) - fields are delimited by custom character
Delimited( ) - no delimiter specified
FixedLength - fields are of a fixed length
3.9. How do I connect to a HTML file?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\MyPage.htm;Extended
Properties="HTML Import;"';
3.10. How do I export MS Access table into an HTML
file?
uses ShellApi;
DeleteFile('MyPage.htm');
with ADODataSet do
begin
DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
DatabaseConnect := 'Data Source=C:\simple.mdb';
SQL.Text := 'SELECT * INTO [MyPage.htm] IN
"." [HTML Export;] FROM Country';
ExecSQL;
ShellExecute(Handle, 'open', 'MyPage.htm', nil, '', SW_SHOW); // show page in web browser
end;
3.11. How do I save a recordset in XML (Extensible
Markup Language) format?
ADODataSet.SaveXML('c:\myfile.xml');
The output is an XML fragment. You must add an XML version header to the
output for it to be a 'valid' xml document:
<?xml version="1.0" encoding="UTF-8">
3.12. How do I open a recordset from XML file?
ADODataSet.CommandType := ctFile;
ADODataSet.TableName := 'c:\myfile.xml';
ADODataSet.Open;
or use Load method:
ADODataSet.Load('c:\myfile.xml');
3.13. How do I save a recordset in ADTG (Advanced
Data TableGram) format?
ADODataSet.SaveADTG('c:\myfile.adtg');
The ADTG format is more efficient than XML but the output is in proprietary
Microsoft format.
3.14. How do I open a recordset from an ADTG file?
ADODataSet.CommandType := ctFile;
ADODataSet.TableName := 'c:\myfile.adtg';
ADODataSet.Open;
or use Load method:
ADODataSet.Load('c:\myfile.adtg');
3.15. How do I use the ADO DataFactory with Adonis?
Adonis directly supports DataFactory without writing any special code. Just
use Adonis ApplyUpdates and CancelUpdates methods.
The ADO DataFactory, when used with the Adonis RDSRemoteObject,
allows you to open, edit and save a recordset from a remote machine using DCOM, HTTP
or HTTPS as the transport protocol.
3.16. How do I use UDL files?
ADODatabase.DatabaseConnect := 'File Name=c:\conninfo.udl';
UDL files allow you to store all your database connection information in an
external file so you can change it easily without having to recompile your application.
3.17. How do I create an UDL file in my application?
uses MSDASC;
var DataInitialize: IDataInitialize;
DataInitialize := CoMSDAInitialize.Create;
if
Failed(DataInitialize.WriteStringToStorage('c:\myudl.udl', 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\simple.mdb', 1)) then
raise Exception.Create('Can''t write UDL');
3.18. How do I create an UDL file in my C++ Builder
application?
#include <msdasc.hpp>
_di_IDataInitialize DataInitialize = CoMSDAINITIALIZE::Create(NULL);
if
(FAILED(DataInitialize->WriteStringToStorage(L"c:\\myudl.udl",
L"Microsoft.Jet.OLEDB.4.0;Data Source=c:\\simple.mdb", 1)))
throw Exception("Error");
3.19. How do I specify a MS Access workgroup
information file?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=database.mdb;Jet
OLEDB:System database=c:\mysystem.mdw';
3.20. How do I connect to a MS Access password
protected database?
ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=database.mdb;Jet
OLEDB:Database Password=mypwd';
3.21. How do I connect to a MS Access database on a CD
drive?
ADODatabase.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODatabase.DatabaseConnect := 'Data Source=E:\database.mdb';
ADODatabase.ConnectionMode := cmShareExclusive; // exclusive
access, so lock file isn't created
3.22. How do I instantiate a 'creatable' ADO
Recordset?
An ADO creatable recordset allows you to build a recordset in memory and
populate it with data without connecting to a database.
with ADODataSet1 do
begin
CommandType = ctFile;
FieldDefs.Clear;
FieldDefs.Add('Name', ftString, 25, True);
FieldDefs.Add('Age', ftInteger, 0, False);
Open;
end;
It is also possible to create the persistent fields interactively at
designtime in the Fields Editor. First set CommandType property to ctFile (TableName
and SQL properties must be empty). Then add persistent fields using the Fields Editor.
When you set the ADODataset to true a creatable recordset will be
automatically instantiated. You can insert and edit data as normal.
3.23. How can I query heterogenous
datasources (diverse data sources)?
A heterogeneous query is a query between two databases (eg. two different
SQL server catalogs), two types of database (eg. an Access database and an SQL Server
database) or databases on two machines.
It is possible to query two different SQL Server catalogs in SQL.
select * from cat1..table1 as t1, cat2..table2 as t2 where t1.id = t2.id
Due to the ADO limitation that a recordset can only be opened from one
connection object it is not possible to query two different types of database or
query two machines.
Intersolv produce a third-party heterogeneous query processor called
ISGNavigator for ADO.
3.24. How to I disconnect a recordset?
ADODataSet.DatabaseConnect := '';
resp.
ADODataSet.Database := nil;
3.25. How do I set database connection interactively
in
my application?
if ADODataSet.SelectConnection then
ADODataSet.Open;
3.26. How do I reload a saved ADTG recordset, modify
it, then reconnect and update database?
Loading ADTG data:
ADODataSet.CommandType := ctFile;
ADODataSet.LockType := ltBatchOptimistic;
ADODataSet.TableName := 'country.ADTG';
ADODataSet.Open;
Reconnecting and updating changes into database:
ADODataSet.DatabaseConnect := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
ADODataSet.ApplyUpdates;
ADTG file name must correspond to table name in database.
3.27. How do I reconnect a disconnected ADODataSet and
apply changes?
ADODataSet.DatabaseConnect := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
ADODataSet.ApplyUpdates;
ADODataSet.LockType property must be ltBatchOptimistic
3.28. How do I read connection parameters from an UDL file?
var
DataInitialize: IDataInitialize;
ConnectionString: PWideChar;
begin
DataInitialize := CoMSDAINITIALIZE.Create;
DataInitialize.LoadStringFromStorage('c:\erik.udl',
ConnectionString);
ShowMessage(ConnectionString);
end;
4.1. I can't get the nTier ADO sample
application to work over DCOM?
See the Adonis help for information on how to set the ComputerName property to
select transport protocol.
Nb. If you attempt to use DCOM and the client and middle-tier applications are
on the same machine RDS will use COM, not DCOM. If you want to test multi-machine
deployment on a single machine you should use the HTTP transport protocol during testing.
Single Machine
1. Set RDSRemoteObject1.ComputerName to a blank string in the Client.
2. Register AppServer.exe by running it once.
Multi Machine
Client:
1. Before you can run an nTier client application across to machines you
must add the Business object's progid to the Client machines registry. Save the
following into a file called BusObj.reg and double-click on it from explorer.
REGEDIT4
[HKEY_CLASSES_ROOT\Srv.BusinessObject]
@="BusinessObjectObject"
[HKEY_CLASSES_ROOT\Srv.BusinessObject\Clsid]
@="{45CECB44-4CBB-11D2-9D8E-204C4F4F5020}"
Server:
1. Register your server by running it.
2. Run DCOMCNFG if you wish to configure your business objects security settings and
launch permissions. If you want your business object to be visible on the server then set
'identity' to interactive user. Nb. The server must be logged in at all times to use this
option. If you recieve the error 'Unable to create business object' on your server it
usually indicates a security problem between the client and server.
4.2. I can't get the nTier ADO sample
application to work over HTTP?
Using HTTP or HTTPs as a transport protocol requires these steps:
1. Make sure the Business Object is marked as 'safe for scripting' and 'safe for
launching'.
2. Add the Business Object to the ADCLaunch key of IIS
(HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Parameters\ADCLaunch).
Here are some troubleshooting tips for RDS and HTTP:
- if you have marked your Virtual Web site or Virtual directory as 'Run in a
separate memory space' you may encounter 'Unexpected error 0x80070558'
- if you don't mark your BO's as ADCLaunch you may encounter 'Unexpected error
0x80070005'
- if your web server doesn't have execute privelege enabled ('Home Directory'
page in Internet Service Manager's web site properties), not just scripting then
the client application may freeze
5.1. How do I create a new Access database in my
application?
ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=new.mdb'; // select interactively
ADOXCatalog.CreateCatalog;
or even simpler one line call:
ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=new.mdb');
You can use additional properties in connection string to specify
encryption, database version, database password, locale:
ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=new.mdb;Jet OLEDB:Encrypt Database=True'); //
encrypted database
ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=new.mdb;Jet OLEDB:Engine Type=4'); // Jet 3.X format
(MS Access 97)
ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=new.mdb;Jet OLEDB:Database Password=MyPwd'); //
MyPwd password
ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=new.mdb;Locale Identifier=0x0405'); // Czech locale
Jet OLEDB:Engine Type codes:
Jet 1.0 1 // Jet 1.0
Jet 1.1 2 // Jet 1.1
Jet 2.0 3 // Jet 2.x
Jet 3.X 4 // Jet 3.x (MS Access 97)
Jet 4.X 5 // Jet 4.x (MS Access 2000)
Locale Identifier codes:
| Chinese Punctuation |
0x00000804 |
| Chinese Stroke Count |
0x00020804 |
| Chinese Stroke Count (Taiwan) |
0x00000404 |
| Chinese Bopomofo (Taiwan) |
0x00030404 |
| Croatian |
0x0000041a |
| Czech |
0x00000405 |
| Estonian |
0x00000425 |
| French |
0x0000040c |
| General |
0x00000409 |
| Georgian Modern |
0x00010437 |
| German Phone Book |
0x00010407 |
| Hungarian |
0x0000040e |
| Hungarian Technical |
0x0001040e |
| Icelandic |
0x0000040f |
| Japanese |
0x00000411 |
| Japanese Unicode |
0x00010411 |
| Korean |
0x00000412 |
| Korean Unicode |
0x00010412 |
| Latvian |
0x00000426 |
| Lithuanian |
0x00000427 |
| Macedonian |
0x0000042f |
| Norwegian/Danish |
0x00000414 |
| Polish |
0x00000415 |
| Romanian |
0x00000418 |
| Slovak |
0x0000041b |
| Slovenian |
0x00000424 |
| Spanish Traditional |
0x0000040a |
| Spanish Modern |
0x00000c0a |
| Swedish/Finnish |
0x0000041d |
| Thai |
0x0000041e |
| Turkish |
0x0000041f |
| Ukrainian |
0x00000422 |
| Vietnamese |
0x0000042a |
5.2. How do I retrieve all table names from a
database?
with ADOXCatalog.ADOXTables do
for i := 0 to Count - 1 do
ShowMessage(Item[i].Name);
To avoid retrieving of system tables, add simple condition:
with ADOXCatalog.ADOXTables do
for i := 0 to Count - 1 do
if Item[i].Type_ = 'TABLE' then
ShowMessage(Item[i].Name);
5.3. How do I create an Access table?
ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
// create table
ADOXTable.CreateNew('Contacts');
// create columns
ADOXColumn.CreateNew('Name', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXTable);
ADOXColumn.CreateNew('Phone', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXTable);
// create index
ADOXIndex.CreateNew('MyIndex');
ADOXIndex.PrimaryKey := True;
ADOXIndex.Unique := True;
ADOXColumn.CreateNew('Name', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXIndex);
ADOXIndex.AppendTo(ADOXTable);
ADOXTable.AppendTo(ADOXCatalog);
5.4. How do I create an Access stored query?
ADOXView.CreateNew('AllContacts', 'SELECT * FROM Contacts');
ADOXView.AppendTo(ADOXCatalog);
5.5. How do I create an Access parameterized stored
query?
ADOXView.CreateNew('Contacts by name', 'SELECT * FROM Contacts WHERE Name LIKE MyParam');
ADOXView.AppendTo(ADOXCatalog);
5.6. How do I create an Access string field with AllowZeroLength
property?
ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
ADOXCatalog.Open;
ADOXTable.CreateNew('Country');
ADOXColumn.CreateNew('MyField', adVarWChar, 20);
ADOXColumn.ADOXColumn.ParentCatalog := ADOXCatalog.ADOXCatalog;
ADOXProperty.OpenExisting(ADOXColumn, 'Jet OLEDB:Allow Zero Length');
ADOXProperty.Value := True;
ADOXColumn.AppendTo(ADOXTable);
ADOXTable.AppendTo(ADOXCatalog);
5.7. How do I create an Access autoincrement field?
ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
ADOXCatalog.Open;
ADOXTable.CreateNew('Country');
ADOXColumn.CreateNew('Id', adInteger, 0);
ADOXColumn.ADOXColumn.ParentCatalog := ADOXCatalog.ADOXCatalog;
ADOXProperty.OpenExisting(ADOXColumn, 'AutoIncrement');
ADOXProperty.Value := True;
ADOXColumn.AppendTo(ADOXTable);
ADOXTable.AppendTo(ADOXCatalog);
5.8. How do I access Access description fields?
Set ADOXProperty.PropertyName property to 'Description' and Active to True. Field
description text will be showed in Value property.
You can set also properties in source code:
ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
ADOXTable.OpenExisting(ADOXCatalog, 'MyTable');
ADOXColumn.OpenExisting(ADOXTable, 'MyColumn');
ADOXProperty.OpenExisting(ADOXColumn, 'Description');
ShowMessage(ADOXProperty.Value);
5.9. How do I use Adonis database connection in ADOX?
ADOXCatalog.OpenExisting(ADODatabase.Handle);
5.10. How do I create Access linked table?
ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=database.mdb';
ADOXCatalog.Open;
ADOXTable.CreateNew('MyTable');
ADOXTable.ADOXTable.ParentCatalog := ADOXCatalog.ADOXCatalog;
ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Create Link');
ADOXProperty.Value := True;
ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Link Datasource');
ADOXProperty.Value := 'c:\mydb.mdb';
ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Remote Table Name');
ADOXProperty.Value := 'Country';
// these two lines are required only if linked table is password protected
ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Link Provider
String');
ADOXProperty.Value := 'MS Access;PWD=mypwd;';
ADOXTable.AppendTo(ADOXCatalog);
5.11. How do I create Access memo field?
ADOXColumn.CreateNew('MyMemoField',
adLongVarCharWChar, 0);
ADOXColumn.AppendTo(ADOXTable);
5.12. How do I create Access secondary index which allows duplicate keys?
ADOXIndex.CreateNew('MyIndex');
ADOXIndex.ADOXColumns.Append('Name', adVarWChar, 24);
ADOXIndex.ADOXColumns.Append('Capital', adVarWChar, 24);
ADOXIndex.Unique := False; // allow duplicate key
ADOXIndex.AppendTo(ADOXTable);
5.13. How do I create Access secondary index which ignore nulls in the columns?
ADOXIndex.CreateNew('MyIndex');
ADOXIndex.ADOXColumns.Append('Name', adVarWChar,
24);
ADOXIndex.ADOXColumns.Append('Capital', adVarWChar, 24);
ADOXIndex.IndexNulls := adIndexNullsIgnore;
// ignore null values
ADOXIndex.AppendTo(ADOXTable);
5.14. How do I set up Access relationships between tables?
ADOXKey.CreateNew('MyKey');
ADOXKey.KeyType := adKeyForeign;
ADOXKey.RelatedTable := 'Master';
ADOXColumn.CreateNew('MasterId', adInteger, 0);
ADOXColumn.RelatedColumn := 'Id';
ADOXColumn.AppendTo(ADOXKey);
ADOXKey.UpdateRule := adRICascade;
ADOXKey.AppendTo(ADOXTableDetail);
5.15. How do I specify Access referential integrity?
use TADOXKey DeleteRule and UpdateRule properties
5.16. How do I retrieve a list of Access
groups/users?
Jet 4.0 provider is required and workgroup information file (MDW) must be specified:
ADOXCatalog.OpenExisting('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=simple.mdb;Jet OLEDB:System
database=system.mdw');
// show groups
with ADOXCatalog.ADOXGroups do
for i := 0 to Count - 1
do
ShowMessage(Item[i].Name);
// show users
with ADOXCatalog.ADOXUsers do
for i := 0 to Count - 1
do
ShowMessage(Item[i].Name);
5.17. How do I retrieve/change user permissions?
var Rights: Integer;
ADOXCatalog.OpenExisting('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=simple.mdb;Jet OLEDB:System
database=system.mdw');
ADOXUser.OpenExisting(ADOXCatalog, 'admin');
// retrieve permissions
Rights := ADOXUser.GetPermissions('MyTable', adPermObjTable, EmptyParam);
if Rights = adRightNone then
ShowMessage('RightNone');
if Rights and adRightDrop <> 0
then ShowMessage('RightDrop');
if Rights and adRightExclusive <> 0
then ShowMessage('RightExclusive');
if Rights and adRightReadDesign <> 0
then ShowMessage('RightReadDesign');
if Rights and adRightWriteDesign <> 0
then ShowMessage('RightWriteDesign');
if Rights and adRightWithGrant <> 0
then ShowMessage('RightWithGrant');
if Rights and adRightReference <> 0
then ShowMessage('RightReference');
if Rights and adRightCreate <> 0
then ShowMessage('RightCreate');
if Rights and adRightInsert <> 0
then ShowMessage('RightInsert');
if Rights and adRightDelete <> 0
then ShowMessage('RightDelete');
if Rights and adRightReadPermissions <> 0
then ShowMessage('RightReadPermissions');
if Rights and adRightWritePermissions <> 0
then ShowMessage('RightWritePermissions');
if Rights and adRightWriteOwner <> 0
then ShowMessage('RightWriteOwner');
if Rights and adRightMaximumAllowed <> 0
then ShowMessage('RightMaximumAllowed');
if Rights and adRightFull <> 0
then ShowMessage('RightFull');
if Rights and adRightExecute <> 0
then ShowMessage('RightExecute');
if Rights and adRightUpdate <> 0
then ShowMessage('RightUpdate');
if Rights and adRightRead <> 0
then ShowMessage('RightRead');
// deny 'Insert' right
ADOXUser.SetPermissions('MyTable', adPermObjTable, adAccessDeny, adRightInsert, adInheritNone, EmptyParam);
5.18. How do I create "Allow Zero Length" field?
// create a field
ADOXColumn.CreateNew('MyField', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXTable);
// set 'Allow Zero Length' property
ADOXProperty.OpenExisting(ADOXColumn, 'Jet OLEDB:Allow Zero
Length');
ADOXProperty.Value := True;
6.1. How do I compact an Access database?
JROJetEngine.SourceConnection := 'Data Source=c:\old.mdb';
// can be selected interactively
JROJetEngine.DestConnection := 'Data Source=c:\new.mdb';
// can be selected interactively
JROJetEngine.Compact;
You can also specify encryption, database version, database password, locale:
JROJetEngine.SourceConnection := 'Data Source=c:\old.mdb';
JROJetEngine.DestConnection := 'Data Source=c:\new.mdb;Jet OLEDB:Engine Type=4'); // Jet 3.X (MS Access 97)
JROJetEngine.Compact;
6.2. How do I change a password of Access
database?
JROJetEngine.SourceConnection := 'Data
Source=c:\old.mdb;Jet OLEDB:Database Password=OldPwd'; // can
be selected interactively
JROJetEngine.DestConnection := 'Data Source=c:\new.mdb;Jet
OLEDB:Database Password=NewPwd'; // can be selected
interactively
JROJetEngine.Compact;
6.3. How do I make an Access database replicable?
JROReplica.ActiveConnection := 'Data
Source=C:\database.mdb';
JROReplica.MakeReplicable(True);
6.4. How do I make an Access object replicable?
JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.SetObjectReplicability('MyTable', 'Tables', True);
6.5. How do I make an Access object local?
JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.SetObjectReplicability('MyTable', 'Tables', False);
6.6. How do I create an Access replica?
JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.CreateReplica('C:\replica.mdb', 'My replica', jrRepTypeFull, jrRepVisibilityGlobal, 90,
jrRepUpdFull);
6.7. How do I create an Access partial replica?
JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.CreateReplica('C:\replica.mdb', 'My replica', jrRepTypePartial, jrRepVisibilityGlobal, 90,
jrRepUpdFull);
JROReplica.OpenExisting('Data Source=C:\replica.mdb');
JROFilter.CreateNew(JROReplica, 'MyTable', jrFilterTypeTable,
'Country = ''USA''');
6.8. How do I retrieve a list of Access replica filters?
with JROReplica.JROFilters do
for i := 0 to Count - 1 do
ShowMessage(Item[i].TableName + ' : ' +
Item[i].FilterCriteria);
6.9. How do I synchronize Access replicas?
JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.Synchronize('C:\replica.mdb', jrSyncTypeImpExp,
jrSyncModeDirect);
7.1. How do I use the MSOLAP provider?
with ADODataSet do
begin
DatabaseProvider := 'MSOLAP';
DatabaseConnect := 'DataSource=Erik;Server=Tutorial';
SQL.Text :=
'SELECT { [Measures].[Units Shipped],
[Measures].[Units Ordered] } ON COLUMNS, ' +
'NON EMPTY [Store].[Store Name].members ON ROWS '
+
'FROM Warehouse ';
Open;
end;
8.1. How do I create a new MS SQL server database?
var
SQLServer: SQLDMO.SQLServer;
Database: SQLDMO.Database;
SQLServer := CoSQLServer.Create;
SQLServer.Connect('MyServer', 'sa',
'');
Database := CoDatabase.Create;
Database.Name := 'NewDB';
SQLServer.Databases.Add(Database);
ShowMessage('Database created');
8.2. How do I retrieve a list of MS SQL server database users?
var
SQLServer: SQLDMO.SQLServer;
Database: SQLDMO.Database;
i: Integer;
SQLServer := CoSQLServer.Create;
SQLServer.Connect('', 'sa',
'');
try
Database := SQLServer.Databases.Item('pubs', EmptyParam);
with Database.Users do
for i := 1 to Count
do
ShowMessage(Item(i).Name);
finally
SQLServer.Disconnect;
end;
8.3. How do I retrieve a list of registered MS SQL servers?
with CoApplication_.Create.ServerGroups
do
for i := 1 to Count
do
begin
ShowMessage('Server group: ' + Item(i).Name);
with Item(i).RegisteredServers
do
for j := 1
to Count do
ShowMessage('Server: ' + Item(j).Name);
end;
8.4. How do I retrieve a list of databases from registered MS SQL server?
with CoSQLServer.Create do
begin
Connect('', 'sa',
'');
with Databases do
for i := 1 to Count
do
ShowMessage(Item(i, EmptyParam).Name);
end;
8.5. How do I retrieve a list of tables in any given database from registered MS SQL server?
with CoSQLServer.Create do
begin
Connect('', 'sa',
'');
with Databases.Item('pubs', EmptyParam).Tables
do
for i := 1 to Count
do
ShowMessage(Item(i, EmptyParam).Name);
end;
8.6. How do I drop a table?
with CoSQLServer.Create do
begin
Connect('', 'sa',
'');
Databases.Item('MyDatabase',
EmptyParam).Tables.Remove('MyTable', EmptyParam);
end;
8.7. How do I create a new table?
var
NewColumn: Column;
NewTable: Table;
NewColumn := CoColumn.Create;
NewColumn.Name := 'MyColumn';
NewColumn.DataType := 'varchar';
NewColumn.Length := 20;
NewTable := CoTable.Create;
NewTable.Name := 'MyTable';
NewTable.Columns.Add(NewColumn);
with CoSQLServer.Create do
begin
Connect('', 'sa',
'');
Databases.Item('MyDatabase', EmptyParam).Tables.Add(NewTable);
end;
8.8. How do I set a table permissions?
var
Table: SQLDMO.Table;
with CoSQLServer.Create do
begin
Connect('', 'sa',
'');
Table := Databases.Item('MyDatabase',
EmptyParam).Tables.Item('MyTable', EmptyParam);
Table.Grant(SQLDMOPriv_Select, 'UserName',
EmptyParam, EmptyParam, EmptyParam);
end;