FAQ
This is a patch against 5.2.9 to fix mysqli::query so a user can call stored
procedures the same as they do any other statement. No more multi_query() and
next_result() work arounds necessary to avoid a "Commands out of sync" error.

I note this has been rejected several times in the tracker as not being a bug.
I feel its a clear encapsulation violation making the user special case a
query just because it's calling a stored procedure. Aside from just being
very inconvenient, some API wrappers around mysqli, Drupal 6 for example,
leave the user with no way to get at mysqli::multi_query().

Anyhow, here's a complete patch with a test. The technique and code is lifted
from Perl's DBD::mysql driver, which you can see here as
mysql_st_free_result_sets()
http://cpansearch.perl.org/src/CAPTTOFU/DBD-mysql-4.011/dbdimp.c

Before each query it frees any results still hanging around on the connection,
which is essentially what a user has to do. I'm not really a C programmer so
I left most of the code as is, do/while loop and all.

I'm not sure how mysqli normally handles errors so I just went with a printf()
and return trusting that you'll fix that up. It causes test 057 to fail
because that test deliberately generates an out of sync error, which my code
diligently prints. So that should go away with fixed error handling.

Thanks,
Schwern


--
THIS I COMMAND!

Search Discussions

  • Andrey Hristov at Apr 26, 2009 at 7:17 am
    Hi,
    could you show how you want to express that in PHP code that will work
    with calling prepared statements. How the API should work? (Example PHP
    code that uses the new API).


    Best,
    Andrey

    Michael G Schwern wrote:
    This is a patch against 5.2.9 to fix mysqli::query so a user can call stored
    procedures the same as they do any other statement. No more multi_query() and
    next_result() work arounds necessary to avoid a "Commands out of sync" error.

    I note this has been rejected several times in the tracker as not being a bug.
    I feel its a clear encapsulation violation making the user special case a
    query just because it's calling a stored procedure. Aside from just being
    very inconvenient, some API wrappers around mysqli, Drupal 6 for example,
    leave the user with no way to get at mysqli::multi_query().

    Anyhow, here's a complete patch with a test. The technique and code is lifted
    from Perl's DBD::mysql driver, which you can see here as
    mysql_st_free_result_sets()
    http://cpansearch.perl.org/src/CAPTTOFU/DBD-mysql-4.011/dbdimp.c

    Before each query it frees any results still hanging around on the connection,
    which is essentially what a user has to do. I'm not really a C programmer so
    I left most of the code as is, do/while loop and all.

    I'm not sure how mysqli normally handles errors so I just went with a printf()
    and return trusting that you'll fix that up. It causes test 057 to fail
    because that test deliberately generates an out of sync error, which my code
    diligently prints. So that should go away with fixed error handling.

    Thanks,
    Schwern
  • Michael G Schwern at Apr 26, 2009 at 7:47 am

    Andrey Hristov wrote:
    could you show how you want to express that in PHP code that will work
    with calling prepared statements. How the API should work? (Example PHP
    code that uses the new API).
    Sorry, I don't understand what you're asking.

    There is no changes to the mysqli API in this patch, its just a bug fix for
    mysqli::query(). If you're asking for a demonstration of the bug, there's a
    phpt test in the patch and demo code in the bugs.
    http://bugs.php.net/bug.php?id=35203
    http://bugs.php.net/bug.php?id=48065

    Here's a stand alone demo of the problem this patch is fixing.

    <?php

    // Just a simple SQL statement for testing.
    function stmt() {
    return "select 1";
    }

    // Create a stored procedure around our testing statement.
    function setup_database() {
    $mysqli = connection();

    $sql = stmt();
    $mysqli->query("DROP PROCEDURE IF EXISTS test_bug");
    $mysqli->query("
    CREATE PROCEDURE test_bug()
    BEGIN
    $sql;
    END
    ");
    print $mysqli->error;
    }

    // Get a database connection.
    // Assumes a running MySQL 5 server on localhost with
    // a test account and database (no password) which can
    // drop and create procedures.
    function connection() {
    return new mysqli("localhost", "test", "", "test");
    }

    // Using query() to call two stored procedures using query()
    // will fail with "Commands out of sync"
    function test_double_query($query) {
    $mysqli = connection();

    if( !$mysqli->query($query) )
    printf("First query FAILED: %s\n", $mysqli->error);

    if( !$mysqli->query($query) )
    printf("Second query FAILED: %s\n", $mysqli->error);

    printf("ok - $query\n\n");
    }


    setup_database();

    // querying using statements or a procedure which contains
    // the same statement should work the same. It doesn't.
    test_double_query("call test_bug");
    test_double_query(stmt());

    ?>



    --
    The interface should be as clean as newly fallen snow and its behavior
    as explicit as Japanese eel porn.
  • Michael G Schwern at May 14, 2009 at 11:04 pm
    Ping?

    There's a fully formed patch here, with tests, to fix a mysqli bug. I haven't
    gotten any feedback.

    Here's the original message with the patch.
    http://news.php.net/php.internals/43773


    Michael G Schwern wrote:
    This is a patch against 5.2.9 to fix mysqli::query so a user can call stored
    procedures the same as they do any other statement. No more multi_query() and
    next_result() work arounds necessary to avoid a "Commands out of sync" error.

    I note this has been rejected several times in the tracker as not being a bug.
    I feel its a clear encapsulation violation making the user special case a
    query just because it's calling a stored procedure. Aside from just being
    very inconvenient, some API wrappers around mysqli, Drupal 6 for example,
    leave the user with no way to get at mysqli::multi_query().

    Anyhow, here's a complete patch with a test. The technique and code is lifted
    from Perl's DBD::mysql driver, which you can see here as
    mysql_st_free_result_sets()
    http://cpansearch.perl.org/src/CAPTTOFU/DBD-mysql-4.011/dbdimp.c

    Before each query it frees any results still hanging around on the connection,
    which is essentially what a user has to do. I'm not really a C programmer so
    I left most of the code as is, do/while loop and all.

    I'm not sure how mysqli normally handles errors so I just went with a printf()
    and return trusting that you'll fix that up. It causes test 057 to fail
    because that test deliberately generates an out of sync error, which my code
    diligently prints. So that should go away with fixed error handling.

    Thanks,
    Schwern

    --
    ROCKS FALL! EVERYONE DIES!
    http://www.somethingpositive.net/sp05032002.shtml

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupphp-internals @
categoriesphp
postedApr 26, '09 at 5:00a
activeMay 14, '09 at 11:04p
posts4
users2
websitephp.net

People

Translate

site design / logo © 2022 Grokbase