How to create a Stored Procedure at runtime ?

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

Tommi
Fresh Boarder
Fresh Boarder
Posts: 11
Joined: 11.07.2008, 13:27

How to create a Stored Procedure at runtime ?

Postby Tommi » 22.11.2008, 22:02

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
ZeosLib's Handyman :o)
ZeosLib's Handyman :o)
Posts: 189
Joined: 15.08.2005, 16:08
Location: Wehrheim
Contact:

Postby Michael » 23.11.2008, 17:53

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 :zwinker:

M.
:prog2: Use the source, dude!

[align=right]..::.. ZeosLib on SourceForge ..::..[/align]

Tommi
Fresh Boarder
Fresh Boarder
Posts: 11
Joined: 11.07.2008, 13:27

Postby Tommi » 24.11.2008, 12:39

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
Fresh Boarder
Fresh Boarder
Posts: 11
Joined: 11.07.2008, 13:27

Postby Tommi » 24.11.2008, 12:59

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.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 24.11.2008, 15:17

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.
Image

btrewern
Expert Boarder
Expert Boarder
Posts: 193
Joined: 06.10.2005, 18:51

Postby btrewern » 24.11.2008, 16:08

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

Regards,

Ben


Return to “6.5.1 (alpha) - 6.6.x (beta)”

Who is online

Users browsing this forum: No registered users and 1 guest