ORA-1036 on manually created parameter.

[psbo]

Hello.
I have a problem when opening a TUniQuery.
This Query has 3 parameters that the TUniQuery detect (PAramCheck := true).
But when I passes values to each param with a managed function, I passe a third param with name that not exists on the ParamList Query.
I modify the 3rd param dynamically and I open the Query.
Here an Exception is raised: ORA 1036
this is an exemple :

Code: Select all

//....
function fAddParameter(var poQuery: TUniQuery; psName: String; poDataType: TFieldType; pvValue: Variant): boolean;
var
  LengthValue: Integer;
  Index, I: Integer;
  pParam: TParam;
begin

  Result := False;
  poQuery.ParamCheck := False;
  index := -1;
  pParam := nil;
  if psName = '' then
    Exit;

  if IsEmptyOrNull( pvValue ) then
    pvValue := NULL;

  for I := 0 to poQuery.ParamCount-1 do
  begin
    if (poQuery.FindParam(psName) = nil) and (IsEmptyOrNull(poQuery.Params[I].Value)) then
    begin
      index := I;
      Break;
    end;
  end;

  if (poQuery.FindParam(psName) = nil) and index <> -1 then
  begin
    poQuery.Params.Delete(index);
    pParam := poQuery.Params.AddParameter;
	pParam.index := Index; 	
    pParam.Name := psName;
    poQuery.Params.AddParam(pParam);
  end
   
  poQuery.ParamByName(psName).ParamType := ptInput;
  poQuery.ParamByName(psName).DataType  := poDataType;
  poQuery.ParamByName(psName).Value     := pvValue;

  Result := True;

end;
//....
procedure TForm6.Button1Click(Sender: TObject);
begin
  qrSQL.Close;
{ // SQL text.
SELECT * FROM TIERS 
WHERE TIERS_FORMJ_ID = :jID
AND TIERS_LANGU_ID = :gID
AND TIERS_SCGEO_ID = :sID
}
  qrSQL.SQL.Clear;
  qrSQL.SQL.Text := mmSQL.Text ;
//mmRES.Lines.Add( IntToStr (qrSQL.ParamCount) );

  fAddParameter( qrSQL, 'jID'  , ftInteger,  1817 );
  fAddParameter( qrSQL, 'gID' , ftInteger,  1 );
  fAddParameter( qrSQL, 'x3'   , ftInteger,  1 );
  qrSQL.Open;

  pAddToMemo(mmRES, '-------------------------------------------------------------');
end;

It Change the paramName by raises an ORA-1036.

Please Help.

[MaximG]

To investigate the issue, we need a complete code base of your test project, including all functions used in your project, e.g. IsEmptyOrNull. Please send it to us via the e-support form : https://www.devart.com/company/contactform.html

[psbo]

Hello.
I have send you a complete exemple with SQL creation table.
I've send this mail with email : d******@saverglass.com
since we are your client.


Thanks.

[MaximG]

Am I right, that the application that you've sent us is intended to demonstrate the inability to use a parameter named sThisParamIsHeigherThenThirteenCharacters' ?
Please note that the maximum parameter name length cannot exceed 30 characters for an Oracle database:
https://docs.oracle.com/cd/E18283_01/ti ... /names.htm

[psbo]

No not for that.
I know that the max characters in a parameter is 30.
I want to tell you that If I name a parameter in my SQL as 'param1' then I declare it with the function fAddParameter as 'paramt1' then I want from the component to delete the real parameter and replace it with the name I force on it.

Exemple :

Code: Select all

  QueryTR.SQL.Text := 'SELECT *FROM TIERS WHERE TIERS_ID = :t1';
  fAddParameter(QueryTR, 'tC', ftInteger; 55);
  QueryTR.Open;

When calling fAddParameter, it will look that 'tC' does not exists, then It Create IT after deleting the 't1'.
With Firebird this code works but not in Oracle.

Thanks

[ViktorV]

Thank you for the information. We will investigate this UniDAC behavior and let you know the result.

[psbo]

Heello,
Did you solved the problem ??
I'm still waiting.

Thanks you.

[MaximG]

We've tested our components according to your description of the issue. In your code, you use different names for the same parameter in the query and during parameter binding. Such behavior is not correct in terms of Oracle. The same name must be used in the query and during parameter binding. Otherwise, you'll get a corresponding error message.
As such, UniDAC behaves correctly in your code example.