dbi-link: fix error on insert/update/delete into/from remote mysql table

I have installed dbi-link version 2.0.0 into my PostgreSQL 8.4 environment. The purpose is to query or modify remote mysql table from within postgresql user-defined-function. And whenever I try to insert/update/delete record into/from remote mysql table, I consistently get error message:
ERROR: error from Perl function “shadow_trigger_func”: Can’t call method “quote” on an undefined value at line 61
So i go to the mentioned function and found out sub functions: make_pairs, do_insert, do_update and do_delete try to access variables: $data_source_id and $table which are declared locally in main function.
Knowing that problem, I have modified parameter passing as follows:

Within main function:

if ($iud->{ $_TD->{new}{iud_action} }) {
   $iud->{ $_TD->{new}{iud_action} }->({
      payload => $_TD->{new},
      tbl => $table,
      source_id => $data_source_id
   });
}
else {
   die “Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"
}
And inside sub functions: modify $table and $data_source_id into $params->{tbl} and $params->{source_id}, respectively.
Advertisements
6 comments
  1. Filip said:

    Abdulyadi, nice.
    Did you send patch to David so he can include it in next dbi-link release?

    (BTW, in this post’s comments we can see a sneaky kind of SEO trackback spam)

  2. dan said:

    Hello, works to insert but does not work to eliminate, get this message:

    SQL Error: ERROR: error from Perl function “shadow_trigger_func”: Can’t call method “quote” on an undefined value at line 156.

    Thanks.

  3. Ok, my complete code is:

    CREATE OR REPLACE FUNCTION dbi_link.shadow_trigger_func()
    RETURNS trigger AS
    $BODY$
    require 5.8.3;
    ######################################################
    # #
    # Immediately reject anything that is not an INSERT. #
    # #
    ######################################################
    if ($_TD->{event} ne ‘INSERT’) {
    return “SKIP”;
    }

    spi_exec_query(‘SELECT dbi_link.dbi_link_init()’);
    my $data_source_id = shift;
    die “In shadow_trigger_function, data_source_id must be an integer”
    unless ($data_source_id =~ /^\d+$/);
    my $query = “SELECT dbi_link.cache_connection( $data_source_id )”;
    warn “In shadow_trigger_function, calling\n $query” if $_SHARED{debug};
    warn “In shadow_trigger_function, the trigger payload is\n”. Dump(\$_TD) if $_SHARED{debug};
    my $rv = spi_exec_query($query);

    my $table = $_TD->{relname};
    warn “Raw table name is $table”;
    warn “In trigger on $table, action is $_TD->{new}{iud_action}” if $_SHARED{debug};
    $table =~ s{
    \A # Beginning of string.
    (.*) # Actual table name.
    _shadow # Strip off shadow.
    \z # End of string.
    }
    {$1}sx;
    warn “Cooked table name is $table”;

    my $iud = {
    I => \&do_insert,
    U => \&do_update,
    D => \&do_delete,
    };

    if ($iud->{ $_TD->{new}{iud_action} }) {
    $iud->{ $_TD->{new}{iud_action} }->({
    payload => $_TD->{new},
    tbl => $table,
    source_id => $data_source_id
    });
    }
    else {
    die “Trigger event was $_TD->{new}{iud_action}{payload});
    die “In do_insert, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my (@keys, @values);
    foreach my $key (sort keys %{ $params->{payload} } ) {
    next unless $key =~ /^.?new_(.*)/;
    my $real_key = $1;
    push @keys, $real_key;
    push @values, $_SHARED{dbh}{ $params->{source_id} }->quote(
    $params->{payload}{$key}
    );
    }
    my $sql = <{tbl} (
    @{[
    join(
    “,\n “,
    @keys
    )
    ]}
    )
    VALUES (
    @{[
    join(
    “,\n “,
    @values
    )
    ]}
    )
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

    sub do_update {
    my ($params) = @_;
    die “In do_update, must pass a payload!”
    unless (defined $params->{payload});
    die “In do_update, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my $sql = <{tbl}
    SET
    @{[ make_pairs({
    payload => $params->{payload},
    which => ‘new’,
    joiner => “,\n “,
    source_id => $params->{source_id}
    }) ]}
    WHERE
    @{[ make_pairs({
    payload => $params->{payload},
    which => ‘old’,
    joiner => “\nAND “,
    transform_null => ‘true’,
    source_id => $params->{source_id}
    }) ]}
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

    sub do_delete {
    my ($params) = @_;
    die “In do_delete, must pass a payload!”
    unless (defined $params->{payload});
    die “In do_delete, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my $sql = <{tbl}
    WHERE
    @{[ make_pairs({
    payload => $params->{payload},
    which => ‘old’,
    joiner => “\nAND “,
    transform_null => ‘true’,
    source_id => $params->{source_id}
    }) ]}
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

    sub make_pairs {
    my ($params) = @_;
    die “In make_pairs, must pass a payload!”
    unless (defined $params->{payload});
    die “In make_pairs, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    warn “In make_pairs, parameters are:\n”. Dump($params) if $_SHARED{debug};
    my @pairs;
    foreach my $key (
    keys %{ $params->{payload} }
    ) {
    next unless $key =~ m/^(.?)$params->{which}_(.*)/;
    my $left = “$1$2”;
    warn “In make_pairs, raw key is $key, cooked key is $left” if $_SHARED{debug};
    if (
    defined $params->{transform_null} && # In a WHERE clause,
    !defined $params->{payload}{$key} # turn undef into IS NULL
    ) {
    push @pairs, “$left IS NULL”;
    }
    else {
    push @pairs, “$left = ” . $_SHARED{dbh}{ $params->{source_id} }->quote(
    $params->{payload}{$key}
    );
    }
    }
    my $ret =
    join (
    $params->{joiner},
    @pairs,
    );
    warn “In make_pairs, the pairs are:\n”. Dump(\@pairs) if $_SHARED{debug};
    return $ret;
    }

    $BODY$
    LANGUAGE plperlu VOLATILE;

  4. dan said:

    I’m using Postgresql 8.3

    CREATE OR REPLACE FUNCTION dbi_link.shadow_trigger_func()
    RETURNS trigger AS
    $$
    require 5.8.3;
    ######################################################
    # #
    # Immediately reject anything that is not an INSERT. #
    # #
    ######################################################
    if ($_TD->{event} ne ‘INSERT’) {
    return “SKIP”;
    }

    spi_exec_query(‘SELECT dbi_link.dbi_link_init()’);
    my $data_source_id = shift;
    die “In shadow_trigger_function, data_source_id must be an integer”
    unless ($data_source_id =~ /^\d+$/);
    my $query = “SELECT dbi_link.cache_connection( $data_source_id )”;
    warn “In shadow_trigger_function, calling\n $query” if $_SHARED{debug};
    warn “In shadow_trigger_function, the trigger payload is\n”. Dump(\$_TD) if $_SHARED{debug};
    my $rv = spi_exec_query($query);

    my $table = $_TD->{relname};
    warn “Raw table name is $table”;
    warn “In trigger on $table, action is $_TD->{new}{iud_action}” if $_SHARED{debug};
    $table =~ s{
    \A # Beginning of string.
    (.*) # Actual table name.
    _shadow # Strip off shadow.
    \z # End of string.
    }
    {$1}sx;
    warn “Cooked table name is $table”;

    my $iud = {
    I => \&do_insert,
    U => \&do_update,
    D => \&do_delete,
    };

    if ($iud->{ $_TD->{new}{iud_action} }) {
    $iud->{ $_TD->{new}{iud_action} }->({
    payload => $_TD->{new},
    tbl => $table,
    source_id => $data_source_id
    });
    }
    else {
    die “Trigger event was $_TD->{new}{iud_action}{payload});
    die “In do_insert, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my (@keys, @values);
    foreach my $key (sort keys %{ $params->{payload} } ) {
    next unless $key =~ /^.?new_(.*)/;
    my $real_key = $1;
    push @keys, $real_key;
    push @values, $_SHARED{dbh}{ $params->{source_id} }->quote(
    $params->{payload}{$key}
    );
    }
    my $sql = <{tbl} (
    @{[
    join(
    “,\n “,
    @keys
    )
    ]}
    )
    VALUES (
    @{[
    join(
    “,\n “,
    @values
    )
    ]}
    )
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

    sub do_update {
    my ($params) = @_;
    die “In do_update, must pass a payload!”
    unless (defined $params->{payload});
    die “In do_update, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my $sql = <{tbl}
    SET
    @{[ make_pairs({
    payload => $params->{payload},
    which => ‘new’,
    joiner => “,\n “,
    }) ]}
    WHERE
    @{[ make_pairs({
    payload => $params->{payload},
    which => ‘old’,
    joiner => “\nAND “,
    transform_null => ‘true’
    }) ]}
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

    sub do_delete {
    my ($params) = @_;
    die “In do_delete, must pass a payload!”
    unless (defined $params->{payload});
    die “In do_delete, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my $sql = < $params->{payload},
    which => ‘old’,
    joiner => “\nAND “,
    transform_null => ‘true’
    }) ]}
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

    sub make_pairs {
    my ($params) = @_;
    die “In make_pairs, must pass a payload!”
    unless (defined $params->{payload});
    die “In make_pairs, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    warn “In make_pairs, parameters are:\n”. Dump($params) if $_SHARED{debug};
    my @pairs;
    foreach my $key (
    keys %{ $params->{payload} }
    ) {
    next unless $key =~ m/^(.?)$params->{which}_(.*)/;
    my $left = “$1$2”;
    warn “In make_pairs, raw key is $key, cooked key is $left” if $_SHARED{debug};
    if (
    defined $params->{transform_null} && # In a WHERE clause,
    !defined $params->{payload}{$key} # turn undef into IS NULL
    ) {
    push @pairs, “$left IS NULL”;
    }
    else {
    push @pairs, “$left = ” . $_SHARED{dbh}{ $params->{source_id} }->quote(
    $params->{payload}{$key}
    );
    }
    }
    my $ret =
    join (
    $params->{joiner},
    @pairs,
    );
    warn “In make_pairs, the pairs are:\n”. Dump(\@pairs) if $_SHARED{debug};
    return $ret;
    }
    $$
    LANGUAGE ‘plperlu’
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER;

  5. Hi, I have found that you have missed some code lines containing WHERE clause in your do_delete function.

  6. dan said:

    you were right thank you very much: D

    sub do_delete {
    my ($params) = @_;
    die “In do_delete, must pass a payload!”
    unless (defined $params->{payload});
    die “In do_delete, payload must be a hash reference!”
    unless (ref $params->{payload} eq ‘HASH’);
    my $sql = <{tbl}
    WHERE
    @{[ make_pairs({
    payload => $params->{payload},
    which => ‘old’,
    joiner => “\nAND “,
    transform_null => ‘true’,
    source_id => “$params->{source_id}”
    }) ]}
    SQL
    warn “SQL is\n$sql” if $_SHARED{debug};
    $_SHARED{dbh}{ $params->{source_id} }->do($sql);
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: