在Delphi中使用Firedac的PlSql

Question

我试图使用firedac运行plsql，但我没有得到它。我试过FDScript1和FDQuery1。

是未找到错误的参数。有人知道如何运行这个plsql吗？

错误是

FDQuery1:未找到参数'TALHAO_ID‘。

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.  

Answer 1

你有几个问题。其中之一是，您必须使用ExecSQL来更新/插入到postgreSQL数据库，而不是打开。第二个问题是您的查询是错误的。根据本文档，http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html为plpgsql变量赋值类似于pascal/Delphi，使用赋值符号":=“不是"=:”，就像在查询中一样。其次，在通过plpgsql或sql语言执行查询时，赋值符号是"=“而不是":=”或"=:“。纠正您在SQL ant中的错误，函数将成功。您的返回消息是表单postgreSQL后端。