PHP Language - Is this a bug ?

Author

Is this a bug ?

McHenry

2006-05-02, 9:57 pm

I am trying to add a new row to a MySQL table using a stored procedure and
then obtain the new id value. The rows are added and affected _rows returns
1 confirming the statement worked however no insert_id value is returned.
The results of running this script are:

New ID:0 New Rows:1

MySQL Code
========================================
======

CREATE TABLE testing (
testid INT AUTO_INCREMENT PRIMARY KEY,
testname VARCHAR(25)
)

CREATE PROCEDURE `addtest`(_testname VARCHAR(25))
INSERT INTO testing (testname) VALUES (_testname)

PHP Code
========================================
======
<?php
$testname = "McHenry";

//Open the connection
$mysqli = new mysqli("**********", "**********", "**********",
"**********");

// create the statement
$stmt = $mysqli->prepare("call addtest(?)");

/* bind parameters for markers */
$stmt->bind_param("s", $testname);

/* execute query */
$stmt->execute();

//Return the status of the query and display
$newid=$mysqli->insert_id;
$affected_rows=$stmt->affected_rows;
Echo "New ID:$newid New Rows:$affected_rows";

/* close statement */
$stmt->close();

/* close connection */
$mysqli->close();
?>

Thanks in advance...

p.s. If this is a bug is it treated like a new comet etc, do they name it
after you ?

robert

2006-05-02, 9:57 pm

it may have been good to keep this in your last post thread. i had asked in
that thread to see exactly what you've posted here. either way, it's good
for us to see. have you tried isolating the problem using simple examples
that have been known to work? this is a quick one that i've run and it
outputs as expected.

===========

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

mysqli_query($link, "CREATE TABLE myCity LIKE City");

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart',
617000)";
mysqli_query($link, $query);

printf ("New Record has id %d.\n", mysqli_insert_id($link));

/* drop table */
mysqli_query($link, "DROP TABLE myCity");

/* close connection */
mysqli_close($link);
?>

===========

the "myCity" table has to be made from scratch...but you could substitute it
w/ your own. what we're trying to do here is just get *something* to work.
once that happens, we can introduce new factors until it no longer works...i
suspect it may be with the binding.

i'm not sure if they name bugs after people...but you're welcome to it. ;^)
and, there have been several bugs in the past related to the insert_id. once
reported, they are fixed promptly. we just have to document where it breaks.

McHenry

2006-05-02, 9:57 pm

"robert" <ab@no.spam-alama-ding-dong> wrote in message
news:O9U5g.63$tD3.60@fe03.lga...
> it may have been good to keep this in your last post thread. i had asked
> in
> that thread to see exactly what you've posted here. either way, it's good
> for us to see. have you tried isolating the problem using simple examples
> that have been known to work? this is a quick one that i've run and it
> outputs as expected.

I posted the new thread after I had received your reply asking for the
details sorry.
The example I posted was a trivial example which illustrates the scenario
however the real stored procedure is more complex.
I would prefer to keep the sql within a sp if possible.

p.s. Nice choice of city !!!

>
> ===========
>
> <?php
> $link = mysqli_connect("localhost", "my_user", "my_password", "world");
>
> /* check connection */
> if (mysqli_connect_errno()) {
> printf("Connect failed: %s\n", mysqli_connect_error());
> exit();
> }
>
> mysqli_query($link, "CREATE TABLE myCity LIKE City");
>
> $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU',
> 'Stuttgart',
> 617000)";
> mysqli_query($link, $query);
>
> printf ("New Record has id %d.\n", mysqli_insert_id($link));
>
> /* drop table */
> mysqli_query($link, "DROP TABLE myCity");
>
> /* close connection */
> mysqli_close($link);
> ?>
>
>
> ===========
>
> the "myCity" table has to be made from scratch...but you could substitute
> it
> w/ your own. what we're trying to do here is just get *something* to work.
> once that happens, we can introduce new factors until it no longer
> works...i
> suspect it may be with the binding.
>
> i'm not sure if they name bugs after people...but you're welcome to it.
> ;^)
> and, there have been several bugs in the past related to the insert_id.
> once
> reported, they are fixed promptly. we just have to document where it
> breaks.
>
>

robert

2006-05-02, 9:57 pm

| I posted the new thread after I had received your reply asking for the
| details sorry.

no big. no need for sorries.

| The example I posted was a trivial example which illustrates the scenario
| however the real stored procedure is more complex.
| I would prefer to keep the sql within a sp if possible.

it should be fine to keep all your sql in the sp. i take it that this
simplified example you posted doesn't return the insert_id either...right?
the more complex one is ultimately performing an insert...right? updates
return a insert_id too. well...lol...supposed to. we have to get there
first.

| p.s. Nice choice of city !!!

you know i'm braindead! i just grabbed this example off the net and gave it
a shot on my pc with as little modification as possible. granted, i do know
where stuttgart is...but i can guarantee you, i couldn't spell it to save my
life. ;^) i suppose brisbane would have garnered a more picturesque
ambiance. ;^)

|
| >
| > ===========
| >
| > <?php
| > $link = mysqli_connect("localhost", "my_user", "my_password", "world");
| >
| > /* check connection */
| > if (mysqli_connect_errno()) {
| > printf("Connect failed: %s\n", mysqli_connect_error());
| > exit();
| > }
| >
| > mysqli_query($link, "CREATE TABLE myCity LIKE City");
| >
| > $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU',
| > 'Stuttgart',
| > 617000)";
| > mysqli_query($link, $query);
| >
| > printf ("New Record has id %d.\n", mysqli_insert_id($link));
| >
| > /* drop table */
| > mysqli_query($link, "DROP TABLE myCity");
| >
| > /* close connection */
| > mysqli_close($link);
| > ?>
| >
| >
| > ===========
| >
| > the "myCity" table has to be made from scratch...but you could
substitute
| > it
| > w/ your own. what we're trying to do here is just get *something* to
work.
| > once that happens, we can introduce new factors until it no longer
| > works...i
| > suspect it may be with the binding.
| >
| > i'm not sure if they name bugs after people...but you're welcome to it.
| > ;^)
| > and, there have been several bugs in the past related to the insert_id.
| > once
| > reported, they are fixed promptly. we just have to document where it
| > breaks.
| >
| >
|
|

McHenry

2006-05-02, 9:57 pm

"robert" <ab@no.spam-alama-ding-dong> wrote in message
news:_vU5g.71$tD3.27@fe03.lga...
>| I posted the new thread after I had received your reply asking for the
> | details sorry.
>
> no big. no need for sorries.
>
> | The example I posted was a trivial example which illustrates the
> scenario
> | however the real stored procedure is more complex.
> | I would prefer to keep the sql within a sp if possible.
>
> it should be fine to keep all your sql in the sp. i take it that this
> simplified example you posted doesn't return the insert_id either...right?
> the more complex one is ultimately performing an insert...right? updates
> return a insert_id too. well...lol...supposed to. we have to get there
> first.

Correct, the simplified example is exactly the same scenario and exhibits
the same problems as the real example, if we can solve the simple one the
solution will carry over I'm sure.

>
> | p.s. Nice choice of city !!!
>
> you know i'm braindead! i just grabbed this example off the net and gave
> it
> a shot on my pc with as little modification as possible. granted, i do
> know
> where stuttgart is...but i can guarantee you, i couldn't spell it to save
> my
> life. ;^) i suppose brisbane would have garnered a more picturesque
> ambiance. ;^)

Home of Porsche...

>
> |
> | >
> | > ===========
> | >
> | > <?php
> | > $link = mysqli_connect("localhost", "my_user", "my_password",
> "world");
> | >
> | > /* check connection */
> | > if (mysqli_connect_errno()) {
> | > printf("Connect failed: %s\n", mysqli_connect_error());
> | > exit();
> | > }
> | >
> | > mysqli_query($link, "CREATE TABLE myCity LIKE City");
> | >
> | > $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU',
> | > 'Stuttgart',
> | > 617000)";
> | > mysqli_query($link, $query);
> | >
> | > printf ("New Record has id %d.\n", mysqli_insert_id($link));
> | >
> | > /* drop table */
> | > mysqli_query($link, "DROP TABLE myCity");
> | >
> | > /* close connection */
> | > mysqli_close($link);
> | > ?>
> | >
> | >
> | > ===========
> | >
> | > the "myCity" table has to be made from scratch...but you could
> substitute
> | > it
> | > w/ your own. what we're trying to do here is just get *something* to
> work.
> | > once that happens, we can introduce new factors until it no longer
> | > works...i
> | > suspect it may be with the binding.
> | >
> | > i'm not sure if they name bugs after people...but you're welcome to
> it.
> | > ;^)
> | > and, there have been several bugs in the past related to the
> insert_id.
> | > once
> | > reported, they are fixed promptly. we just have to document where it
> | > breaks.
> | >
> | >
> |
> |
>
>

Sponsored Links