Error while creating stored procedure through ODBC connection

am 25.01.2007 20:35:12 von Gary Greenberg

This is a multi-part message in MIME format.

------=_NextPart_000_00E0_01C74074.E267B5A0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am trying to generate a database by running DDL script through the ODBC
connection.

It went through almost to the end and then barfed on creating procedure.

Here is a snippet from the generation log:

*** Executing statement 311:

CREATE FUNCTION mask_new_permission () RETURNS trigger AS
$mask_new_permission$

begin

new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from
permissions, 1)

*** Execution of statement failed:

ERROR: unterminated dollar-quoted string at or near "$mask_new_permission$

begin

new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from
permissions, 1)";

Error while executing the query

SQLSTATE = 42601

*** Executing statement 312:

return new

*** Execution of statement failed:

ERROR: syntax error at or near "return";

Error while executing the query

SQLSTATE = 42601

*** Executing statement 313:

end

*** Statement successfully executed.

*** Executing statement 314:

$mask_new_permission$ LANGUAGE plpgsql

*** Execution of statement failed:

ERROR: unterminated dollar-quoted string at or near "$mask_new_permission$
LANGUAGE plpgsql";

Error while executing the query

SQLSTATE = 42601

*** Executing statement 315:

create trigger permissions_bit before insert on permissions

for each row execute procedure mask_new_permission()

*** Execution of statement failed:

ERROR: function mask_new_permission() does not exist;

Error while executing the query

SQLSTATE = 42883

When I am executing the same piece of code from psql prompt it is going
through without complaining:

ops2=> CREATE FUNCTION mask_new_permission () RETURNS trigger AS
$mask_new_permission$

ops2$> begin

ops2$> new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK)
from permissions, 1);

ops2$> return new;

ops2$> end;

ops2$> $mask_new_permission$ LANGUAGE plpgsql;

CREATE FUNCTION

ops2=>

ops2=>

ops2=> create trigger permissions_bit before insert on permissions

ops2-> for each row execute procedure mask_new_permission();

CREATE TRIGGER

Can anyone explain it to me, please?

Thx,

Gary

BTW, ODBC driver is PSQLODBC30A.dll

------=_NextPart_000_00E0_01C74074.E267B5A0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

charset=3Dus-ascii">

namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"City"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"place"/>

style=3D'font-size:10.0pt;
font-family:Arial'>I am trying to generate a database by running DDL =
script through
the ODBC connection.

style=3D'font-size:10.0pt;
font-family:Arial'>It went through almost to the end and then barfed on
creating procedure.

style=3D'font-size:10.0pt;
font-family:Arial'>Here is a snippet from the generation =
log:

style=3D'font-size:10.0pt;
font-family:Arial'>*** Executing statement =
311:

style=3D'font-size:10.0pt;
font-family:Arial'>CREATE FUNCTION mask_new_permission () RETURNS =
trigger AS
$mask_new_permission$

style=3D'font-size:10.0pt;
font-family:Arial'> begin

style=3D'font-size:10.0pt;
font-family:Arial'>
new.PERMISSION_MASK :=3D coalesce(select 2*max(PERMISSION_MASK) from =
permissions,
1)

style=3D'font-size:10.0pt;
font-family:Arial'>*** Execution of statement =
failed:

style=3D'font-size:10.0pt;
font-family:Arial'>ERROR: unterminated dollar-quoted string at or near
"$mask_new_permission$

style=3D'font-size:10.0pt;
font-family:Arial'> begin

style=3D'font-size:10.0pt;
font-family:Arial'>
new.PERMISSION_MASK :=3D coalesce(select 2*max(PERMISSION_MASK) from =
permissions,
1)";

style=3D'font-size:10.0pt;
font-family:Arial'>Error while executing the =
query

style=3D'font-size:10.0pt;
font-family:Arial'>SQLSTATE =3D 42601

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>*** Executing statement =
312:

style=3D'font-size:10.0pt;
font-family:Arial'>return new

style=3D'font-size:10.0pt;
font-family:Arial'>*** Execution of statement =
failed:

style=3D'font-size:10.0pt;
font-family:Arial'>ERROR: syntax error at or near =
"return";

style=3D'font-size:10.0pt;
font-family:Arial'>Error while executing the =
query

style=3D'font-size:10.0pt;
font-family:Arial'>SQLSTATE =3D 42601

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>*** Executing statement =
313:

style=3D'font-size:10.0pt;
font-family:Arial'>end

style=3D'font-size:10.0pt;
font-family:Arial'>*** Statement successfully =
executed.

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>*** Executing statement =
314:

style=3D'font-size:10.0pt;
font-family:Arial'>$mask_new_permission$ LANGUAGE =
plpgsql

style=3D'font-size:10.0pt;
font-family:Arial'>*** Execution of statement =
failed:

style=3D'font-size:10.0pt;
font-family:Arial'>ERROR: unterminated dollar-quoted string at or near
"$mask_new_permission$ LANGUAGE =
plpgsql";

style=3D'font-size:10.0pt;
font-family:Arial'>Error while executing the =
query

style=3D'font-size:10.0pt;
font-family:Arial'>SQLSTATE =3D 42601

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>*** Executing statement =
315:

style=3D'font-size:10.0pt;
font-family:Arial'>create trigger permissions_bit before insert on =
permissions

style=3D'font-size:10.0pt;
font-family:Arial'>for each row execute procedure =
mask_new_permission()

style=3D'font-size:10.0pt;
font-family:Arial'>*** Execution of statement =
failed:

style=3D'font-size:10.0pt;
font-family:Arial'>ERROR: function mask_new_permission() does not =
exist;

style=3D'font-size:10.0pt;
font-family:Arial'>Error while executing the =
query

style=3D'font-size:10.0pt;
font-family:Arial'>SQLSTATE =3D 42883

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>When I am executing the same piece of code from psql =
prompt
it is going through without complaining:

style=3D'font-size:10.0pt;
font-family:Arial'>ops2=3D> CREATE FUNCTION mask_new_permission () =
RETURNS
trigger AS $mask_new_permission$

style=3D'font-size:10.0pt;
font-family:Arial'>ops2$> =
begin

style=3D'font-size:10.0pt;
font-family:Arial'>ops2$> &nb=
sp;
new.PERMISSION_MASK :=3D coalesce(select 2*max(PERMISSION_MASK) from =
permissions,
1);

style=3D'font-size:10.0pt;
font-family:Arial'>ops2$> &nb=
sp;
return new;

style=3D'font-size:10.0pt;
font-family:Arial'>ops2$> =
end;

style=3D'font-size:10.0pt;
font-family:Arial'>ops2$> $mask_new_permission$ LANGUAGE =
plpgsql;

style=3D'font-size:10.0pt;
font-family:Arial'>CREATE FUNCTION

style=3D'font-size:10.0pt;
font-family:Arial'>ops2=3D>

style=3D'font-size:10.0pt;
font-family:Arial'>ops2=3D> create trigger permissions_bit before =
insert on
permissions

style=3D'font-size:10.0pt;
font-family:Arial'>ops2-> for each row execute procedure
mask_new_permission();

style=3D'font-size:10.0pt;
font-family:Arial'>CREATE TRIGGER

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>Can anyone explain it to me, =
please?

style=3D'font-size:10.0pt;
font-family:Arial'>Thx,

style=3D'font-size:10.0pt;
font-family:Arial'> =
w:st=3D"on"> w:st=3D"on">Gary

style=3D'font-size:10.0pt;
font-family:Arial'>BTW, ODBC driver is =
PSQLODBC30A.dll

------=_NextPart_000_00E0_01C74074.E267B5A0--

Re: Error while creating stored procedure through ODBC connection

am 25.01.2007 21:24:07 von Hiroshi Inoue

Gary Greenberg wrote:
>
> I am trying to generate a database by running DDL script through the
> ODBC connection.
>
> It went through almost to the end and then barfed on creating procedure.
>

How do you use the ODBC driver, directly or via some tools or middleware ?

Could you send me directly the Mylog output ?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster