TADO Parameters behaving badly

[BrianGooch]

Delphi 2009, Win32, Firebird 1.5

TADO parameters do not perform as stated in the on-line Help.

All 3 examples are based on the same query with SQL on the fly, and Prepared

1. Parameters.AddParameter followed by Refresh clears the Parameters - thus, none in the Collection

2. Parameters.CreateParameter( 'Parm1', ftString, pdInput, 10, {null or nil}) fails.
Parameters.CreateParameter( 'Parm1', ftString, pdInput, 10, 0) works;
To update the value call
Parameters.Refresh
Parameters.ParamByName('Parm1').Value:= 'Ok'

3. Copy parameters to another dataset:
for i:= 0 to Qry2.RecordCount -1 do
Qry1.Parameters.Assign(Qry2.Parameters;
produces
EListError 'List Index out of bounds(0)'
The error occurs whether Parameters.Clear is called or not.

Anybody any ideas? There is no problem with the connection to the Firebird db.

Thankyou in advance.

 

[whosrdaddy]

what is your actual problem? (besides the fact that the documentation is incorrect)

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[BrianGooch]

/Daddy

Good question.

I had this idea of retrieving from the system tables via a TADOcommand the output parameters of an SP on a Server and using them as input parameters to a DB on to a client machine elsewhere on the network. The value of the parameters is USER data read from the Server via another TADO qry.

I can retrieve the SP parameters, and I can use them to create the TADOqry input parameters, but on creation the value is automatically nil (even if you use 0 - since nil and null fail). The crux occurs when trying to Assign the user data to the input parameters for inserting into the client DB - it fails with 2 possible error messages:

1) cannot perform this on a closed dataset ie. the INSERT qry is closed.

2) If you try to open the INSERT qry, it fails presumably because it cannot find any input values.

The rationale behind this approach is that we are evolving version 2.xxx into version 3.00 and have several hundred SPs to deal with. Setting it all up manually using the object inspector takes too long and is prone to error. Thus, we thought if we matched the Server SP with the client INSERT qry we could get the machinery to do the rest.

Looks like so far, Delphi fails to provide the tools with which to achieve this. Today, we are looking at just using the TADO command throughout.

All comments are welcome.

Kind Regards,
Brian Gooch

 

[whosrdaddy]

please show a full code sample and I will tell you what the problem is

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[BrianGooch]

whosrdaddy,

Here's one which seems to have just one crucial failing - if I can solve that, we might be getting somewhere.

start here>>

// copy parameters from ADOQry1 to QryClient
// Parameters.CreateParameter works but does not have all the attributes

**** commence snippet ****
for i:= 0 to ADOQry1.RecordCount - 1 do
with QryClient do
with Parameters.AddParameter do begin
DataType:= ADOQry1.Parameters.Datatype;
Direction:= ADOQry1.Parameters.Direction;
Name:= ADOQry1.Parameters.Name;
NumericScale:= ADOQry1.Parameters.NumericScale;
Precision:= ADOQry1.Parameters.Precision;
Size:= ADOQry1.Parameters.Size;
//Value:= ADOQry1.Parameters.Value; fails here !!!!
Value:= 0; // nil, null don't work here
end; {loop}

// test app only: check list of parameters + how many

with QryClient do begin
Prepared:= True;
for i:= 0 to Parameters.Count - 1 do
LB1.Items.Add(Parameters.Name);
L4.Caption:= 'ServParm '+IntToStr(ADOQry1.Parameters.Count) +
': ClientParm '+ IntToStr((Parameters.Count));

// all parameters have been created
// USER data in Retrieved: copy values to input parameters

for i:= 0 to Parameters.Count - 1 do begin
Retrieved.First;
// start at the beginning: assume any order, just in case
for j:= 0 to Retrieved.FieldCount - 1 do
if (Retrieved.Fields[j].FieldName =
Parameters.Name) then begin
pname:= Retrieved.Fields[j].FieldName;
// hops out here
// Error: QryClient cannot do this on a closed dataset

Parameters.ParamByName(pname).Value:= Retrieved.Fields[j].Value;
Break;
end
else Next;
end; {qryclient}

**** end of snippet ****

Note: ADOQry1 & Retrieved both active; QryClient is closed
Opening QryClient fails: it cannot find the first input parameter even though they clearly show up in the check list LB1 above, and are displayed on screen.

Kind Regards,
Brian Gooch

 

[whosrdaddy]

Brian,

a couple of remarks:

-please post code using the [ignore]

Your code

[/ignore] tags

problem #1
your code is incorrect:

for i:= 0 to ADOQry1.RecordCount - 1 do
  with QryClient do  
    with Parameters.AddParameter do begin               
     DataType:= ADOQry1.Parameters[i].Datatype;
...

the number of returned records has nothing to do with the number of parameters (ie RecordCount could be 100 and ADOQuery.Parameters.Count could be 10).

If you know you need 10 parameters on the source, then you should also need 10 parameters on the destination.

I would solve it like this:

InputQuery:

var SQL := 'DECLARE @Parm1 as INT' +
'DECLARE @Parm2 as INT' +
....
'EXEC SourceStoredProcedure @RealParameter1 = @Parm1 OUTPUT, @RealParameter2 = @Parm2 OUTPUT, ...'+
'SELECT @Parm1 AS ''Parm1'', @Parm2 AS ''Parm2'', ...';

OutputQuery:

var SQL := 'DECLARE @Parm1 as INT' +
'DECLARE @Parm2 as INT' +
....
'EXEC SourceStoredProcedure @RealParameter1 = :Parm1, @RealParameter2 = :Parm2,  ...';

then you can do:

OuputQuery.Parameters.ParamByName['Parm1'].Value := InputQuery.FieldByName['Parm1'].Value;

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[BrianGooch]

whosrdaddy

Thankyou for your quick reply. However, it seems a little clarification is needed.

Step 1: ADOQry1 reads the parameter details of the stored proc on the server. This gives all the Firebird metadata info for the input parameters of the QryClient, the direction being reversed prior to using AddParameter. Thus no declarations are required - what is read as metadata from the Server is used by the Client without any manual intervention (the direction autmatically being changed.)

2. The User data is retrieved via a TADOCommand executing the stored proc on the Server into a dataset Retrieved: always a singleton output - thus the no.of dataset columns = the no. of QryClient parameters. the checks prove they match in both no. and sequence.

Everything in the code works fine until here.

3. Using the QryClient parameters, carry the User data and INSERT it into the Client DB.
The Retrieved value is being read correctly from the Server dataset; it is just not being assigned to the QryClient input parameters whether by means of a loop or by using the Parameters.Assign method. Why it fails, I cannot fathom at all.

As I am unfamiliar with the approach you have suggested, I am not sure how the auto reading of the SP parameter details links up with the Declare statements, or how the unknown no. of parameters is accommodated.

I appreciate the time you have spent looking at this prob; responses have been very thin on the ground.

Kind Regards,
Brian Gooch