I'd rather not go that way (I don't like solutions that require
mangling the sql in odd ways).
I think I'd rather optionally ignore placeholders named ':old' and ':new'.
Also, I think there was a patch floating around that optionally
disabled :foo style placeholders. That would also suffice.
Tim.
On Tue, Dec 04, 2001 at 04:19:13PM +0100, Flemming Frandsen wrote:
>
> To create a trigger in SAP DB you use something like this:
> CREATE TRIGGER a_update FOR TEST.A AFTER UPDATE EXECUTE (
> INSERT INTO TEST.changelog (oid) VALUES ( :old.string_field );
> )
>
> The problem is that DBD::ODBC thinks that :old is a placeholder and it
> mangles the SQL because of it, clealy this is not good.
>
> The patch allows you to use :: in stead of : and thus be able to pass a
> : to the dabase:
> CREATE TRIGGER a_update FOR TEST.A AFTER UPDATE EXECUTE (
> INSERT INTO TEST.changelog (oid) VALUES ( ::old.string_field );
> )
>
> Included in the patch is also a small fix to stop the driver from
> complaining about "SQLDriverConnect unsupported."
>
> Here is the patch to appy to the latest version of DBD::ODBC (0.29):
>
> --- orig/DBD-ODBC-0.29/dbdimp.c Thu Nov 8 10:48:01 2001
> +++ DBD-ODBC-0.29/dbdimp.c Tue Dec 4 15:58:40 2001
> @@ -183,16 +183,15 @@
> * and level 2+ just to indicate that we are trying SQLConnect.
> */
> if (!SQL_ok(rc)) {
> + if (DBIS->debug > 3) {
> #ifdef DBD_ODBC_NO_SQLDRIVERCONNECT
> PerlIO_printf(DBILOGFP, "SQLDriverConnect unsupported.\n");
> #else
> - if (DBIS->debug > 3) {
> PerlIO_printf(DBILOGFP, "SQLDriverConnect failed:\n");
> AllODBCErrors(imp_dbh->henv, imp_dbh->hdbc, 0, 1);
> - }
> -
> #endif /* DriverConnect supported */
> -
> + }
> +
> if (DBIS->debug >= 2)
> PerlIO_printf(DBILOGFP, "SQLConnect '%s', '%s', '%s'\n",
> dbname, uid, pwd);
>
> @@ -499,7 +498,11 @@
> *p = 0;
> style = 2;
> }
> - else { /* perhaps ':=' PL/SQL construct */
> + else if (ch == ':' && *src == ':') { /* :: -> : needed for sapdb
> triggers */
> + *dest++ = *src++;
> + continue;
> + }
> + else { /* perhaps ':=' PL/SQL construct
> */
> *dest++ = ch;
> continue;
> }
>
>
> --
> Regards Flemming Frandsen aka. Dion/Swamp http://dion.swamp.dk
>
> On Tue, 4 Dec 2001, Flemming Frandsen wrote:
>
> > On Tue, 4 Dec 2001, Fries Robert wrote:
> >
> > > You can try the following statement:
> > >
> > > CREATE TRIGGER a_update FOR TEST.A AFTER UPDATE EXECUTE (
> > > INSERT INTO TEST.changelog (oid) VALUES (:OLD.string_field ));
> >
> > > CREATE TRIGGER a_update FOR TEST.A AFTER UPDATE EXECUTE (
> > > INSERT INTO TEST.changelog (oid) VALUES (:OLD.string_field );
> > > )
> >
> > Ah!, I'm using the DBD:ODBC driver and it supports : style placeholders,
> > this means that it thinks that the :old.yadda_yadda is a placehodler and
> > it replaces it with something else before handing it to sapdb.
> >
> > I'll need to teach the DBD::ODBC driver to not support : style
> > placeholders when you are creating a trigger, and I suspect, also when
> > creating stored procedures.
> >
> > Sorry to have bothered you, thanks for your help, I'll write a fix for
> > DBD:SAPDB and post it here and on dbi-dev.
> >
> > --
> > Regards Flemming Frandsen aka. Dion/Swamp http://dion.swamp.dk
> >
> >
>
>
