0

I'm trying to run a plsql with firedac , but I'm not getting it. I've tried with FDScript1 and FDQuery1 .

Is a parameter not found error . Does anyone know to run this plsql ?

the error is

FDQuery1: Parameter 'TALHAO_ID' not found.

unit Unit11;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, Data.DB,
  FireDAC.Comp.Client,FireDAC.Phys.PG, FireDAC.VCLUI.Wait, FireDAC.Stan.Param,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Vcl.StdCtrls, Vcl.Grids,
  Vcl.DBGrids, FireDAC.Comp.DataSet, FireDAC.Comp.UI, Datasnap.Provider,
  Datasnap.DBClient, JvExStdCtrls, JvCombobox, JvDBCombobox, JvExControls,
  JvDBLookup, FireDAC.Comp.ScriptCommands, FireDAC.Comp.Script;

type
  TForm11 = class(TForm)
    Connection: TFDConnection;
    FDWait: TFDGUIxWaitCursor;
    FDQuery1: TFDQuery;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Button1: TButton;
    FDPhysPgDriverLink1: TFDPhysPgDriverLink;
    FDManager1: TFDManager;
    Button2: TButton;
    Edit1: TEdit;
    Edit2: TEdit;
    JvDBComboBox1: TJvDBComboBox;
    ComboCultura: TJvDBLookupCombo;
    FDScript1: TFDScript;
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form11: TForm11;

implementation

{$R *.dfm}

procedure TForm11.Button2Click(Sender: TObject);
begin

 FDQuery1.Close;
 FDQuery1.SQL.text:='DO LANGUAGE plpgsql $$ '+
    'BEGIN                    '+
    '   update  talha_safra set  TALHAO_ID=:TALHAO_ID, SAFRA_ID=:SAFRA_ID, SIT_CADASTRAL=:SIT_CADASTRAL, REPLICACAO_EFETUADA=:REPLICACAO_EFETUADA where talhao_id=:talhao_id and safra_id=:safra_id ' +
    '        IF found THEN  '+
    '              RETURN;      '+
    '        END IF;            '+
    '            '+
    '            '+
    ' INSERT INTO TALHAO_SAFRA (TALHAO_ID, SAFRA_ID, SIT_CADASTRAL, REPLICACAO_EFETUADA)  VALUES  (:TALHAO_ID,:SAFRA_ID,:SIT_CADASTRAL,:REPLICACAO_EFETUADA) '+
    '        RETURN; '+
    '        EXCEPTION WHEN unique_violation THEN '+
    '        END; '+
    '               '+
    'END;             '+
    '$$;';
     FDQuery1.Params.ParamByName('TALHAO_ID').AsInteger:=12;
     FDQuery1.Params.ParamByName('SAFRA_ID').AsInteger:=1  ;
     FDQuery1.Params.ParamByName('SIT_CADASTRAL').AsString:='Ativo';
     FDQuery1.Params.ParamByName('REPLICACAO_EFETUADA').AsString:='NÃO';
     FDQuery1.Open;


end;

procedure TForm11.FormCreate(Sender: TObject);
begin
Connection.Connected:=true;
end;

end.
Rafael Maires Rangel
  • 91
  • 1
  • 10
  • Can you [edit] your post and at least make an effort to properly indent/format the code so that it's readable? – Ken White Apr 15 '15 at 13:37
  • Ken White - the PLSQL is running on pgAdmin . There is no problem SQL . I wonder how passes parameters to Firedac . If it was just an insert I would use query1.parambyname ( ' safra_id ' ) . AsInteger , but how much is not PLSQL wheel. I had this same problem Some years ago with the Firebird dbExpress and when I tried to do a run block – Rafael Maires Rangel Apr 15 '15 at 14:23
  • If you post code and it's a mess and hard to read, there's no point in posting it, except it is necessary so we can see how the parameters are declared. So please [edit] it so we can read it, as I asked in my last comment. Thanks. – Ken White Apr 15 '15 at 14:31
  • Not sure what the issue is with the parameters, but you need to use `FDQuery.ExecSQL` instead of `FDQuery.Open`. `Open` is for queries that return a result set (rows); `UPDATE` and `INSERT` do not return a result set. See http://docwiki.embarcadero.com/Libraries/XE7/en/FireDAC.Comp.Client.TFDCustomQuery.ExecSQL.html – Ken White Apr 15 '15 at 15:15
  • Grateful for the return. I used FDQuery1.ExecSQL ; and FDQuery1.Open ; and the error still continues .. FDQuery1: Parameter 'TALHAO_ID' not found. – Rafael Maires Rangel Apr 15 '15 at 16:30
  • plpgsql (PL/pgSQL) is the procedural language of Postgres, plsql (PL/SQL) is the one for Oracle. – Erwin Brandstetter Apr 15 '15 at 17:47
  • Grateful Erwin ! Help-me then ! – Rafael Maires Rangel Apr 15 '15 at 17:55
  • You probably need to create the params manualy. FDQuery1.Params.CreateParam(ftInteger, 'TALHAO_ID', ptInput); ... – oPsDCadarn Apr 15 '15 at 20:00
  • http://www.postgresql.org/docs/9.0/static/sql-do.html "The code block is treated as though it were the body of a function with no parameters, returning void." – da-soft Apr 16 '15 at 04:44
  • oPsDCadarn - This was not – Rafael Maires Rangel Apr 16 '15 at 11:57

1 Answers1

0

You have several issues. One of them is that instead of Open you have to use ExecSQL for an Update/Insert into a postgreSQL Database. The second issue is that your query is wrong. According to this document http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html assigning values to a plpgsql variable is done similar to pascal/Delphi using the assignment sign ":=" not "=:" as in your query. Secondly, when executing a query, either via plpgsql or sql language the assignment sign is "=" not ":=" or "=:". Correct your mistakes in SQL ant the function will succeed. Your return message is form postgreSQL backend.

JD_GRINDER
  • 344
  • 2
  • 6