How to create a Stored Procedure at runtime ?

[Tommi]

I'm using Firebird, how can I create a Stored Procedure in a database with zeos ?
I tried using zquer.sql.add and zquery.exec but if the procedure is too long I receive error.

Thank You

[Michael]

Imho the best way to create a stored procedure at runtime is to use the TZSQLProcessor component.

• Add the creation script to the Script property (ZSQLProcessor1.Script.Add(sScript) or ZSQLProcessor1.LoadFromFile(sFilename))
• Run the Script (ZSQLProcessor1.Execute)
• Call your stored proc

Please refer to the Firebird-Tutorial (yes, it's a little bit older) in our knowledge base. There you will find a section called "TZStoredProc" concerning stored proc calls.

Good Luck

M.

[Tommi]

I tried but I've same issue with ZSQLProcessor also.

This is my code:

Code: Select all

      StoProc:=TStringList.Create;
      StoProc.add('CREATE PROCEDURE NOME (CRCURL BIGINT) RETURNS (URL VARCHAR(100))');
      StoProc.add('AS');
      StoProc.add('DECLARE VARIABLE aCRCDominio BIGINT; ');
      StoProc.add('DECLARE VARIABLE aCRCPercorso BIGINT; ');
      StoProc.add('DECLARE VARIABLE aTIPO VARCHAR(10); ');
      StoProc.add('DECLARE VARIABLE aLivelliX VARCHAR(20); ');
      StoProc.add('DECLARE VARIABLE aDominio VARCHAR(30); ');
      StoProc.add('DECLARE VARIABLE aPercorso VARCHAR(30); ');
      StoProc.add('DECLARE VARIABLE aFile VARCHAR(30); ');
      StoProc.add('begin ');
      StoProc.add('SELECT CRCDominio,CRCPercorso FROM Link WHERE CRCLink=:crcurl INTO aCRCDominio,aCRCPercorso; ');
      StoProc.add('SELECT Percorso,xFile FROM Percorsi WHERE CRC=:aCRCPercorso INTO aPercorso,aFile; ');
      StoProc.add('SELECT Tipo,LivelliX,Dominio FROM DOMINI WHERE CRC=:aCRCDominio INTO aTIPO,aLivelliX,aDominio; ');
      StoProc.add('IF (aLivelliX <> '+#39+#39+') THEN ' );
      StoProc.add('URL=aTipo || '+#39+'://'+#39+' || aLivelliX || '+#39+'.'+#39+' || aDominio || '+#39+'/'+#39+'; ');
      StoProc.add('ELSE ');
      StoProc.add('URL=aTipo || '+#39+'://'+#39+' || aDominio || '+#39+'/'+#39+'; ');
      StoProc.add('IF (aPercorso <> '+#39+#39+') THEN URL=URL || aPercorso || '+#39+'/'+#39+'; ');
      StoProc.add('IF (aFile <> '+#39+#39+') THEN URL=URL || aFile; ');
      StoProc.add('SUSPEND; ');
      StoProc.add('end ');


Sto:=TZSQLProcessor.Create(nil);
Sto.Connection:=ZConnection1;
Sto.Script.AddStrings(StoProc);
// StoProc.DelimiterType:=dtEmptyLine;
Sto.Execute;
Sto.Free; 
StoProc.free;

In this case I obtain this error:

Code: Select all

2008-11-24 12.35.50 cat: Execute, proto: firebirdd-2.0, msg:  Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 3, column 30, errcode: -104, error: Invalid token The SQL: CREATE PROCEDURE NOME (CRCURL BIGINT) RETURNS (URL VARCHAR(100))
AS
DECLARE VARIABLE aCRCDominio BIGINT
; 

If I uncomment StoProc.DelimiterType:=dtEmptyLine; I obtain this:

Code: Select all

2008-11-24 12.38.55 cat: Transaction, proto: firebirdd-2.0, msg:  invalid request BLR at offset 222 bad parameter number, errcode: -104, error: Invalid token

[Tommi]

SOLUCTION FOUND!!

I need use Zquery with ParamCheck property = false.
I don't know what does ParamCheck mean but if I set it = false my code works correctly:

Code: Select all

      StoProc:=TStringList.Create;
      StoProc.add('CREATE PROCEDURE NOME (CRCURL BIGINT) RETURNS (URL VARCHAR(100))');
      StoProc.add('AS');
      StoProc.add('DECLARE VARIABLE aCRCDominio BIGINT;');
      StoProc.add('DECLARE VARIABLE aCRCPercorso BIGINT;');
      StoProc.add('DECLARE VARIABLE aTIPO VARCHAR(10);');
      StoProc.add('DECLARE VARIABLE aLivelliX VARCHAR(20); ');
      StoProc.add('DECLARE VARIABLE aDominio VARCHAR(30); ');
      StoProc.add('DECLARE VARIABLE aPercorso VARCHAR(30); ');
      StoProc.add('DECLARE VARIABLE aFile VARCHAR(30); ');
      StoProc.add('begin ');
      StoProc.add('SELECT CRCDominio,CRCPercorso FROM Link WHERE CRCLink=:crcurl INTO aCRCDominio,aCRCPercorso; ');
      StoProc.add('SELECT Percorso,xFile FROM Percorsi WHERE CRC=:aCRCPercorso INTO aPercorso,aFile; ');
      StoProc.add('SELECT Tipo,LivelliX,Dominio FROM DOMINI WHERE CRC=:aCRCDominio INTO aTIPO,aLivelliX,aDominio; ');
      StoProc.add('IF (aLivelliX <> '+#39+#39+') THEN ' );
      StoProc.add('URL=aTipo || '+#39+'://'+#39+' || aLivelliX || '+#39+'.'+#39+' || aDominio || '+#39+'/'+#39+'; ');
      StoProc.add('ELSE ');
      StoProc.add('URL=aTipo || '+#39+'://'+#39+' || aDominio || '+#39+'/'+#39+'; ');
      StoProc.add('IF (aPercorso <> '+#39+#39+') THEN URL=URL || aPercorso || '+#39+'/'+#39+'; ');
      StoProc.add('IF (aFile <> '+#39+#39+') THEN URL=URL || aFile; ');
      StoProc.add('SUSPEND; ');
      StoProc.add('end ');

  try
   ZQuery1.SQL.Clear;
   ZQuery1.SQL.AddStrings(StoProc);
   ZQuery1.ExecSQL;
   result:=true;
  except
   on E : EZSQLException do
   begin
    CodiceErrore:=(EZSQLException(E).ErrorCode);
    MsgErrore:=EZSQLException(E).message;
    result:=false;
   end;
  end;
   ZQuery1.SQL.Clear;

StoProc.free;

Where can I find a reference of any property, function or method of zeos libraries ? I looked for it but I din't found anything.

[mdaems]

We have the documentation generated by doxygen and DelphiCodeToDoc in a separately downloadable zip file. It's not complete, but it may help you from time to time.

The downloads are in our downloads directory at http://zeosdownload.firmos.at/downloads/releases and on the sourceforge downloads page. As there hasn't been much improvement since 6.6.3 release we didn't upload newer versions for 6.6.4.

If people want to add documentation in the code to be extracted by these documentation tools, please add the patches to the documentation forum. I'll try to merge them in from time to time.

Mark

BTW : paramcheck replaces all :XXXX combinations by ? and adds parameter objects instead. So in case you want to include ':' in a script this should not happen. That's why setting checkparams=false helps.

[btrewern]

BTW if you want to use ':' in your query which also has parameters then double your ':'s.

Regards,

Ben