Create procedure with Zeos on Delphi 7

[albert.pratama]

Hi all,

I'm trying to create procedure for Firebird with Zeos on Delphi 7.
I have tried to use ZQuery component with SQL:

Code: Select all

CREATE PROCEDURE UPDATE_PASIEN(
  PAS_ID INTEGER,
  PAS_NO_RM VARCHAR(20) CHARACTER SET NONE,
  PAS_NAMA VARCHAR(255) CHARACTER SET NONE,
  PAS_ALAMAT VARCHAR(255) CHARACTER SET NONE,
  PAS_KELAMIN CHAR(1) CHARACTER SET NONE,
  PAS_TGL_LAHIR DATE,
  PAS_HAPUS CHAR(1) CHARACTER SET NONE)
AS
BEGIN
  UPDATE PASIEN
  SET
    PAS_NO_RM = :PAS_NO_RM,
    PAS_NAMA = :PAS_NAMA,
    PAS_ALAMAT = :PAS_ALAMAT,
    PAS_KELAMIN = :PAS_KELAMIN,
    PAS_TGL_LAHIR = :PAS_TGL_LAHIR,
    PAS_HAPUS = :PAS_HAPUS
  WHERE
  (PAS_ID = :PAS_ID);
END;

When I try to ExecSQL the ZQuery, I got an error that said there was an access violation bla bla bla (not a Firebird SQL error, but a Delphi error).

I'm not sure whether I did this in the right way. Does anybody have an idea how I should create procedure at runtime?

Thanks in advance.

Albert

[tkszeos]

You have tried with TZSQLProcessor?

Nicola

[albert.pratama]

After going through some other errors, finally I have been able to do it. Still with the same component, TZQuery, but with different way. The code is now:

Code: Select all

var
  testQuery: TZQuery;
begin
  testQuery := TZQuery.Create(nil);
  testQuery.Connection := dmSIDPZ.connSIDP;
  testQuery.ParamCheck := false;

  testQuery.SQL.Add(
    'SELECT * FROM RDB$PROCEDURES '
    + 'WHERE '
    + '  RDB$PROCEDURE_NAME = ' + QuotedStr('UPDATE_PASIEN')
  );
  testQuery.Open;

  if testQuery.RecordCount = 0 then
  begin
    testQuery.Close;
    testQuery.SQL.Clear;
    testQuery.SQL.Add('CREATE PROCEDURE UPDATE_PASIEN('
      + 'PAS_ID INTEGER, '
      + 'PAS_NO_RM VARCHAR(20) CHARACTER SET NONE, '
      + 'PAS_NAMA VARCHAR(255) CHARACTER SET NONE, '
      + 'PAS_ALAMAT VARCHAR(255) CHARACTER SET NONE, '
      + 'PAS_KELAMIN CHAR(1) CHARACTER SET NONE, '
      + 'PAS_TGL_LAHIR DATE, '
      + 'PAS_HAPUS CHAR(1) CHARACTER SET NONE) '
      + 'AS '
      + 'BEGIN '
      + '  UPDATE PASIEN '
      + '  SET '
      + '    PAS_NO_RM = :PAS_NO_RM, '
      + '    PAS_NAMA = :PAS_NAMA, '
      + '    PAS_ALAMAT = :PAS_ALAMAT, '
      + '    PAS_KELAMIN = :PAS_KELAMIN, '
      + '    PAS_TGL_LAHIR = :PAS_TGL_LAHIR, '
      + '    PAS_HAPUS = :PAS_HAPUS '
      + '  WHERE '
      + '  (PAS_ID = :PAS_ID); '
      + 'END;');
    try
      testQuery.ExecSQL;
      testQuery.Close;
      testQuery.Destroy;
    except
    end;
  end;
end;

At first the procedure was created wrongly, because the variables inside the procedure were changed (by Delphi) to ? (question marks). But then a friend suggested me to set ParamCheck to false, and everything is running smoothly.

Hope this also helps somebody else with the same problem

Thanks tkszeos

Regards,
Albert

[Brkic]

This helps me too, because I have same problem.
Thanks.