ZSQLProcessor error when creating Stored Procedure

[rvargas17]

Hello,
I'm having a problem when creating a stored procedure using the ZSQLProcessor Component.
My application has an option where I can dinamically create a new blank database. (This is necessary for this application)
Everything is working fine, except for the creation of the stored procedure.
The code in delphi:

Code: Select all

                    {Creating the Database - Procedure that works fine and creates a new blank and empty database}
                    CrearBaseDeDatos(EmpresaID);

                    {Creating tables}
                    ZSQLProcessor1.LoadFromFile('C:\database.sql');
                    ZSQLProcessor1.Execute;

                    ZSQLProcessor1.LoadFromFile('C:\procedures.sql');
                    ZSQLProcessor1.Delimiter:='$$';
                    ZSQLProcessor1.Execute;

Creating the database - works OK!
Creating the Tables - works OK!
Creating the Stored Procedures - Error!

"You have an error in your SQL syntax; Check the manual that corresponds to your SQL server version for the right syntax to use near '' at line 1"

I simplyfied the stored procedure as much as I could to see where is the error and even a simple procedure as follow gets an error:
(This is the script inside file procedures.sql that I'm loading in the ZSQLProcessor)

Code: Select all

CREATE PROCEDURE RecalcularCuentas( IN d04 datetime) 
BEGIN 
END$$

I know is empty - just for testing - and If I run this script in PHPmyAdmin it works perfectly... so, I'm not sure what is going on.
seems to be related with the delimiter or something... I have spend a couple of days trying to solve this

Any ideas and suggestions are higly appreciated !

[marsupilami]

Hello rvargas17,

did you also set the DelimiterType property of ZSQLProcessor1 to dtSetTerm? I do this with firebird and it works.
Best regards,

Jan

[rvargas17]

Hello Jan, Thanks for your suggestion.
I changed it to dtSetTerm as you said, but I get the same error.

[rvargas17]

I updated Zeoslib to 7.0.4 (I was using 6.6)
The component TZSQLProcessor in this version has the "dtDelimiter" in Delimiter Type Property.
(And also looks much better)

But unfortunatelly I still get this error. It's just not working properly I think.

[soe.clon]

ok, this is something i did, basicaly i noted that i could execute my file in mysql but not un ZSQLProcesor so i tried to emulate the process like it would be a text file. in your case it would be

procedure CreateStoredProcedure();
var
aConnection : TZConnection;
aProc : TZSQLProcessor;
aQSentence : TStringList;
begin
aConnection := TZConnection(nil);
aConnection.User := 'User';
aConnection.Password := 'pass';
aConnection.HostName := 'hostname'
aConnection.Database := 'dbname';
aProc := TZSQLProcessor.Create(nil);
aQSentence := TStringList.Create();
try
aProc.Connection := aConnection;
aProc.Delimiter := '#$D#$A';
aProc.DelimiterType := ZScriptParser.dtDelimiter;
aProc.ParamChar := '@';
aProc.ParamCheck := false;
aQSentence.Add('delimiter $$');
aQSentence.Add('drop procedure if exist RecalcularCuentas $$');
aQSentence.Add('CREATE PROCEDURE RecalcularCuentas( IN d04 datetime)');
aQSentence.Add('BEGIN');
aQSentence.Add('END$$');

if not aConnection.Connected then aConnection.Connect;

aProc.Script.Add(aQSentence.Text);
aProc.Execute;
finally
aProc.Destroy;
aConnection.Disconnect;
aQSentence.Destroy;
end;
end;