Mega Code Archive
The use of TADOCommand Revised
<![CDATA[
Title: The use of TADOCommand - Revised
Question: If your flavour of business is programming database apps then you prefer probably to use straightforward SQL
If you are using ADO between Delphi and SQL Server or Oracale 9I you will certainly use TADOCommand.
Answer:
If your flavour of business is programming database apps then you prefer probably to use straightforward SQL. If you are using ADO between Delphi and SQL Server or Oracale 9I you will certainly use TADOCommand.
You have two way to handle an update table or an insert into elegantly with TADOCommand.
FIRST OPTION
The first option is to concot the SQL statement as a string.
You do something like
cs :=
'INSERT INTO table_x ( field_x1, field_x2, field_x3, field_x4 )' +
' VALUES (' +
'1' + ',' +
datasety.fieldbyname('field_y1').asstring + ',' +
quotedstr('2') + ',' +
quotedstr(datasety.fieldbyname('field_y2').asstring) + ')' ;
In this example are
field_x1 and field_x2 integer field (Note: use asstring)
field_x3 and field_x4 string fields. (Note: always use quotedstr and asstring)
There are two ways to proceed with the TADOCOMMAND (hereafter named MyComm1)
a. Direct
MyComm1.commandtext := cs;
Mycomm1.execute;
b. Controlled
It is better to create your own wrapper around the TADOCommand which includes transaction processing, errorhandling and all other kind of niceties.
The simplest controlling function would look like
ExecComm(MyCommName: TADOcommand;MySqlString:String):boolean.
Resulting in:
If not ExecComm(MyComm1,cs) then
..... (what you have to do if the insert fails)
THE SECOND OPTION
The first option is very handy when you do not have to repeat the SQL statement in different parts of your application.
For a Field_x1...Field_x4 situation this is not a problem, but for a Field_x1 ... Field_x99 it is something different.Polite for 'it is a mess'.
The way to execute this kind of SQL statements is to use a TADOCommand with (its) parameters:
Applied to the same example
Place a TADOCommand object on the form or in the dataunit.
Hereafter named: MyDedicatedComm1.
Enter the the SQL Insert with parameters in the Commandtext property; that is:
INSERT INTO table_x ( field_x1, field_x2, field_x3, field_x4 )
VALUES ( :Val01, :val02, :val03, val04)
Ready!
At the very moment you need in your code to do the insert:
With MyDedicatedComm1 do
begin
parameters[0].value := 1;
parameters[1].value := datasety.fieldbyname('field_y1').asinteger;
parameters[2].value := '2' ;
parameters[3].value := datasety.fieldbyname('field_y2').asstring;
Execute;
end ;
Note the difference with the first option.
= With the first option you have to put everything in a string and you have to use the quotedstr function to make strings of strings.
= With the second option you only feed the parameters with the correct values.
Of course it is also possible to enter the commandtext at runtime and use a re-usable or general TADOCommand. Let us consider the a TADOCommand named MyGeneralComm1 as such a general TADOCommand.
At the very moment you need to do the insert:
With MyGeneralComm1 do
begin
command.text :=
'INSERT INTO table_x ( field_x1, field_x2, field_x3, field_x4 ) '+
'VALUES ( :Val01, :val02, :val03, val04)';
parameters.parsesql(command.text,true);
parameters[0].value := 1;
parameters[1].value := datasety.fieldbyname('field_y1').asinteger;
parameters[2].value := '2' ;
parameters[3].value := datasety.fieldbyname('field_y2').asstring;
Execute;
end ;
But there are pitfalls everywhere.
If you enter the commandtext at designtime (the dedicated TADOCommand) it will work without problems for the backend of that moment. The moment you change backend you will encounter datatype conflicts in the parameters.
If enter the commandtext at runtime (the non-dedicated TADOCommand) you will also encounter datatype conflicts in the parameters.
Whatever you do, both Oracle 9I and Microsoft SQL Server 2000 will generate errors - from time to time, from situation to situation and only for some fields - telling you to cast your data to the required datatype. E.g.: '...field_x3 has a different datatype, cast value to NVarChar'.
The problems I have encountered are always with parameters for 'string fields'.
So, when your app should run on diffent backends, you can not use the dedicated TADOCommand. If you use a non-dedicated TADOCommand - you enter the commandtext at runtime - you have to find out on which parameters datatypes conflicts will occur.
For those parameters you have to cast the parameters, depending on the backend! - to the correct datatype.
As I work only with Oracle 9i and MS SQL Server 2000, I have a variabele ORACLE and a function ORA_SS for converting string parameters:
function ORA_SS(instr, strlen: string): string;
begin
result := 'cast( ' + instr + ' as ' +
ifthen(mainunit.oracle, 'varchar2(' + strlen + '))', 'nvarchar)');
end;
If - as an example - the fourth parameter raises an error I change the runtime commandtext to :
command.text :=
'INSERT INTO table_x ( field_x1, field_x2, field_x3, field_x4 ) '+
'VALUES ( :Val01, :val02, :val03,' + ORA_ASS(':va14', '15') +')';
forcing the the fourth parameter as nVarChar in MS SS 2000 and VarChar2(15) in Oracle 9i.
CONCLUSION
Be sure never the use the direct method of the first option.
Be aware that you will always encounter datatype conflicts when you use the TADOCommand with parameters. That makes the use of parameters a lot less funny.
But, after some good sweat, blood and tears, it will work...
Have fun and for further information don't hesitate to contact me
]]>