Migrating to FireDAC from ADO – Stored Proc Param Type

Migrating to FireDAC from ADO – Stored Proc Param Type

Found an interesting little deviation today.  

In a MSSQL stored proc with an TinyInt parameter, the insert statement suddenly complained that it could not insert the param with NULL value (as per constraint).

After a lot of tracing, I was certain that we passed a 0 and not a NULL – and the only plausible reason was that I could see, was that the code was setting the param value, before setting the param datatype – so I rearranged the assignments to ensure that value was the last thing to be set.

That fixed the issue.  Go figure.

Params is Array of Variant.

// ADO, MSSQL

for i := Low(Params) to High(Params) do

begin

  p := AStoredProc.Parameters.AddParameter;

  p.Direction := pdInput;

  p.Value := Params[i];

  p.DataType := VariantTypeToFieldType(VarType(Params[i]));

  if p.Value = null

   then p.Attributes := [paNullable]

  else if VarType(Params[i]) = varBoolean

  then begin

    p.DataType := ftInteger;

    if Params[i]

     then p.Value:=1

      else p.Value:=0;

  end;

end;

// FireDAC, MSSQL

for i := Low(Params) to High(Params) do

begin

  p := AStoredProc.Params.Add;

  p.ParamType := TParamType.ptInput;

  vt := VarType(Params[i]);

  if vt vtBoolean

  then begin

    p.DataType := VariantTypeToFieldType(vt);

    p.Value := Params[i];

  end

  else begin

    p.DataType := ftInteger;

    if Params[i]

     then p.Value:=1

      else p.Value:=0;

  end;

end;

2 thoughts on “Migrating to FireDAC from ADO – Stored Proc Param Type


  1. I might recommend IBO here but then you would not be able to connect to MsSQL 🙂


    This reminds me of the discussion about Indy SMTP and setting character encodings… se below (sic).

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.