Professional Web Applications Themes

DBI and subqueries in mysql 4.1 - PERL Modules

Using perl DBI, mysql 4.1, we don't get any rows back. The query is perfectly ok, works on mysql-client. Trying to get some results from this code: (do not worry about the usefullness of the query. the fact that it's a subquery is probably the cause, or isn't it?) my $sql2 = <<EOF; select max.max_id from ( select qu2.id as max_id , max(qa2.value) as max_value from question_answer qa2 , question qu2 where qa2.ref_question = qu2.id and qa2.ref_questionnaire = 5 group by qu2.id ) as max where max.max_id > 0 EOF # Trust me, this works. $dbh = Web::Util->get_ctrl_dbh(); # Get ...

  1. #1

    Default DBI and subqueries in mysql 4.1

    Using perl DBI, mysql 4.1, we don't get any rows back. The query is
    perfectly ok, works on mysql-client. Trying to get some results from this
    code: (do not worry about the usefullness of the query. the fact that it's a
    subquery is probably the cause, or isn't it?)

    my $sql2 = <<EOF;
    select max.max_id
    from
    ( select qu2.id as max_id
    , max(qa2.value) as max_value
    from question_answer qa2
    , question qu2
    where qa2.ref_question = qu2.id
    and qa2.ref_questionnaire = 5
    group by qu2.id ) as max
    where max.max_id > 0
    EOF

    # Trust me, this works.
    $dbh = Web::Util->get_ctrl_dbh();
    # Get the list from the database
    eval {
    $sth = $dbh->prepare( $sql2 );
    $sth->execute();
    my $name = undef;
    $sth->bind_columns(undef, \$name);
    while ($sth->fetch) {
    $doc .= "<$name></$name>";
    }
    Web::Util->log('info', "$self->load()", $doc);
    $self->xml($doc);
    };
    if ($) {
    Web::Util->log('error', "$self->load()", "Error: $");
    }

    Any ideas?

    Cheers,
    --
    Marc Lambrichs


    Marc Lambrichs Guest

  2. #2

    Default Re: DBI and subqueries in mysql 4.1

    Hi,

    did you try the traditionnal way :

    adapt your query :
    .....
    my $sth = $dm_dbh->prepare(q{SELECT * FROM Files WHERE Fic_Nom=? and
    Fic_Dir_Nom=? and Fic_User_Id=?});
    my $retval=$sth->execute($nomfic,$dm_rep,$dm_user) or die "can't execute the
    query: " . $sth->errstr; #retval=1=ok
    $sth->finish();
    .....

    Marc Lambrichs wrote:
    > Using perl DBI, mysql 4.1, we don't get any rows back. The query is
    > perfectly ok, works on mysql-client. Trying to get some results from this
    > code: (do not worry about the usefullness of the query. the fact that it's a
    > subquery is probably the cause, or isn't it?)
    >
    > my $sql2 = <<EOF;
    > select max.max_id
    > from
    > ( select qu2.id as max_id
    > , max(qa2.value) as max_value
    > from question_answer qa2
    > , question qu2
    > where qa2.ref_question = qu2.id
    > and qa2.ref_questionnaire = 5
    > group by qu2.id ) as max
    > where max.max_id > 0
    > EOF
    >
    > # Trust me, this works.
    > $dbh = Web::Util->get_ctrl_dbh();
    > # Get the list from the database
    > eval {
    > $sth = $dbh->prepare( $sql2 );
    > $sth->execute();
    > my $name = undef;
    > $sth->bind_columns(undef, \$name);
    > while ($sth->fetch) {
    > $doc .= "<$name></$name>";
    > }
    > Web::Util->log('info', "$self->load()", $doc);
    > $self->xml($doc);
    > };
    > if ($) {
    > Web::Util->log('error', "$self->load()", "Error: $");
    > }
    >
    > Any ideas?
    >
    > Cheers,
    --
    -=O=------------------------------------------=O=-
    Lionel Valéro
    yste Informatique Département Génie Chimique
    École Polytechnique de Montréal
    C.P. 6079, succ. centre-ville
    Montréal (Québec) H3C 3A7
    Tel: (514) 340 - 4711 # 4805 / C552
    Fax: (514) 340 - 4159
    -=O=------------------------------------------=O=-
    Lionel Valero Guest

  3. #3

    Default Re: DBI and subqueries in mysql 4.1

    Marc Lambrichs wrote:
    >
    > # Trust me, this works.
    > $dbh = Web::Util->get_ctrl_dbh();
    are you setting RaiseError when you create the $dbh?
    > # Get the list from the database
    > eval {
    > $sth = $dbh->prepare( $sql2 );
    > $sth->execute();
    > my $name = undef;
    > $sth->bind_columns(undef, \$name);
    > while ($sth->fetch) {
    > $doc .= "<$name></$name>";
    > }
    > Web::Util->log('info', "$self->load()", $doc);
    > $self->xml($doc);
    > };
    > if ($) {
    > Web::Util->log('error', "$self->load()", "Error: $");
    > }
    >
    > Any ideas?
    what error message do you get and what line?
    >
    > Cheers,
    join [email]dbi-userslists.perl.org[/email] and ask there. you might get more help.

    good luck.

    Terrence Brannon Guest

Similar Threads

  1. #39146 [NEW]: Named placeholder is not bound correctly with Subqueries
    By php at mailingslists dot birkholz dot biz in forum PHP Bugs
    Replies: 1
    Last Post: October 15th, 04:46 PM
  2. Using COALESCE for subqueries
    By marty3d in forum MySQL
    Replies: 1
    Last Post: June 15th, 09:02 PM
  3. Joins or subqueries
    By alexford in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 26th, 02:31 AM
  4. subqueries in query of queries
    By helenmhudson in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 7th, 04:25 PM
  5. Why are subqueries recongized as dependencies
    By Chris Becker in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 07:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139