zsqlprocessor problem with postgresql

[marcioinfo]

I am new user of Postgresql and ZeosLib. The problem is that Im trying to change DDL of my database using Zsqlprocessor
Actually, trying to create the function bellow.

CREATE OR REPLACE FUNCTION ano_mes_b_ins_f()
RETURNS "trigger" AS
$BODY$
BEGIN
IF NEW.mes is not null THEN
IF (NEW.mes<1) or (NEW.MES> 12) THEN
raise exception 'O campo mes deve ser um valor valido(entre 1 e 12)';
END IF;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql';

Using the "pgAdmin II" it works well but with zsqlprocessor comes up with the massage below:

"Sql error:ERROR unterminated dollar_quoted at or near "$BODY"

So any help is good. Thanks any way....

[mdaems]

What delimiter do you use?
If it's the standard delimiter it's ';'. You use it in you procedure, so your script is broken in 2 pieces...
Try 'empty line' delimiter type or choose a delimiter as $$ or £ or...
Mark

[marcioinfo]

I have used like bellow:
CREATE OR REPLACE FUNCTION ano_mes_b_ins_upd_f()
RETURNS "trigger" AS
BEGIN
IF NEW.mes is not null THEN
IF (NEW.mes<1) or (NEW.MES> 12) THEN
raise exception 'O campo mes deve ser um valor valido(entre 1 e 12)';
END IF;
END IF;
END;
but the error continue

[mdaems]

What delimiter do you use?

I mean : what is the value of the ZSQLProcessor.Delimiter property?
Default is ';'
This means your script will be split like this:

Code: Select all

CREATE OR REPLACE FUNCTION ano_mes_b_ins_upd_f()
RETURNS "trigger" AS
BEGIN
IF NEW.mes is not null THEN
IF (NEW.mes<1) or (NEW.MES> 12) THEN
raise exception 'O campo mes deve ser um valor valido(entre 1 e 12)'

Code: Select all

END IF

Code: Select all

END IF

Code: Select all

END

This is obviously not what you desire...
Try adding

Code: Select all

ZSQLProcessor1.Delimiter := '££';

or

Code: Select all

ZSQLProcessor1.DelimiterType := dtEmptyLine;

before calling ZSQLProcessor1.Execute

[marcioinfo]

I have used the script bellow

CREATE OR REPLACE FUNCTION ano_mes_b_ins_upd_f()
RETURNS "trigger" AS
BEGIN
IF NEW.mes is not null THEN
IF (NEW.mes<1) or (NEW.MES> 12) THEN
raise exception 'O campo mês deve ser um vlr valido(entre 1 e 12)'
END IF
END IF
END

And Setting
ZSQLProcessor1.DelimiterType := dtEmptyLine;
ZSQLProcessor1.Delimiter :='';
Then executed, didn't appeared any error but didn't change as well...

The way You wrote above comes up with error too....