Page 1 of 1
Learning stored procedures
Posted: Thu Nov 29, 2007 9:10 am
by feinstimmer
I have problem with iserting data in Mysql table using this stored procedure:
Code: Select all
CREATE PROCEDURE pop_preregister(pre_regcode VARCHAR(8),
pre_link VARCHAR(40), pre_vreme INT(20))
BEGIN
INSERT INTO preregister VALUES (pre_regcode, pre_link, pre_vreme);
END
Procedure works if i insert data from sql using phpMyAdmin like:
Code: Select all
CALL pop_preregister('8w7q6i3M',
'd66dc262443f88d8f7e145142eed630544d5975f', 1195817114);
and data is inserted in table.
But , if i try to do that from php script like:
Code: Select all
<?php
require_once ( "class.DbaseConnect.php" );
$regcode = "8w7q6i3M";
$link = "d66dc262443f88d8f7e145142eed630544d5975f";
$vreme = "1195817114";
$mysqli = dbaseConnect::db_connect();
$sql1 = "CALL pop_preregister($regcode, $link, $vreme)";
$result = $mysqli->query($sql1);
printf("%d Row inserted.\n<br />", $mysqli->affected_rows);
$mysqli->close();
?>
it returns -1 rows and nothing is insertid in table.I don't get eny error warning, only white screen.
MiSql version is 5.0.45.
It is probably some stypid mistake, but i can't see it.Thanks in advance.
Posted: Thu Nov 29, 2007 10:12 am
by RobertGonzalez
You need to wrap string vars in quotes when passing them as params into the proc.
Posted: Thu Nov 29, 2007 11:12 am
by feinstimmer
They are allready in quotes... i think?
Code: Select all
$sql1 = "CALL pop_preregister($regcode, $link, $vreme)";
Posted: Thu Nov 29, 2007 11:17 am
by feyd
Echo $sql1. You should notice a difference from the expected, proper result.

Posted: Thu Nov 29, 2007 11:17 am
by vigge89
Nope
Code: Select all
$sql1 = "CALL pop_preregister('$regcode', '$link', $vreme)";
Posted: Thu Nov 29, 2007 11:28 am
by feinstimmer
I get this result with echo:
no matter if i do this
Code: Select all
$sql1 = "CALL pop_preregister($regcode, $link, $vreme)";
or this:
Code: Select all
$sql1 = "CALL pop_preregister('$regcode', '$link', $vreme)";
Posted: Thu Nov 29, 2007 11:29 am
by RobertGonzalez
feinstimmer wrote:They are allready in quotes... i think?
Code: Select all
$sql1 = "CALL pop_preregister($regcode, $link, $vreme)";
I said your string vars, not your procedure. The query, when presented to the database, should look like the example just above this post:
Code: Select all
CALL my_stored_proc('some string variable', 10, 'another string');
In PHP it would look like this:
Code: Select all
<?php
$var1 = 'String text';
$var2 = 10; // Integer value
$var3 = 'More string text';
$sql = "CALL my_stored_proc('$var1', $var2, '$var3')";
?>
You are also going to want to look at
mysqli_multi_query() as well, since stored procs return the result data of the proc in the first result set and the proc status in the second.
Posted: Thu Nov 29, 2007 11:43 am
by feinstimmer
If this is right, it still returns the same:
Code: Select all
<?php
require_once ( "class.DbaseConnect.php" );
$regcode = "8w7q6i3M";
$link = "d66dc262443f88d8f7e145142eed630544d5975f";
$vreme = 1195817114;
$mysqli = dbaseConnect::db_connect();
$sql1 = "call pop_preregister('$regcode', '$link', $vreme)";
$result = $mysqli->query($sql1);
printf("%d Row inserted.\n<br />", $mysqli->affected_rows);
echo "$sql1";
$mysqli->close();
?>
Posted: Thu Nov 29, 2007 2:08 pm
by RobertGonzalez
Have you checked the table to see if the data is in there?
Also, I mentioned earlier to use
mysqli_multi_query() instead of
mysqli_query().
I wrote this, and posted it several times before. But try it and see what happens:
Code: Select all
<html>
<head><title>Procedure Tester - MySQL</title></head>
<body>
<?php
$sql = '';
$show_results = false;
if (isset($_POST['form_submitted']))
{
// The form was submitted
$sql = $_POST['query'];
echo '<p>The query you entered entered was <strong>' . $sql . '</strong>.</p>';
$mysql = new mysqli('localhost', 'username', 'password', 'databasename');
if (mysqli_connect_errno())
{
die(printf('MySQL Server connection failed: %s', mysqli_connect_error()));
}
// Check our query results
if ($mysql->multi_query($sql))
{
$show_results = true;
$rs = array();
do {
// Lets work with the first result set
if ($result = $mysql->use_result())
{
// Loop the first result set, reading it into an array
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
$rs[] = $row;
}
// Close the result set
$result->close();
}
} while ($mysql->next_result());
}
else
{
echo '<p>There were problems with your query [' . $sql . ']:<br /><strong>Error Code ' . $mysql->errno . ' :: Error Message ' . $mysql->error . '</strong></p>';
}
$mysql->close();
}
echo '<form id="proc_tester" action="' . basename($_SERVER['SCRIPT_FILENAME']) . '" method="post">
<p>Enter your procedure:</p>
<p><input type="text" name="query" size="175" maxlength="255" value="' . $sql . '" /></p>
<p><input type="hidden" name="form_submitted" value="true" /><input type="submit" name="submit" value="Submit query" /></p>
</form>';
if ($show_results) {
echo '<pre>';
print_r($rs);
echo '</pre>';
}
?>
</body>
</html>
Posted: Sat Dec 01, 2007 5:02 am
by feinstimmer
Now, forum is up again, i can finally post reply.
I solved it, it was privileges problem.When i made SP i made it from root user which of corse have
all privileges.From php script user was DB user and it didn't have execute wright.When i added that wright
it worked perfect.Thanks everybody for advices and support!!
Posted: Sat Dec 01, 2007 8:45 am
by RobertGonzalez
Yeah, that's kind of a biggie.
What I have been doing lately is making two user accounts for each application: one to actually administer the database as a root type account (without actually being 'root') and another that is for the application. The application only has two privileges: USE and EXECUTE. This eliminates the ability for an app to run a direct query against the database and requires all queries by the app to hit a procedure when it needs to interact with the DB.
Posted: Sat Dec 01, 2007 10:51 am
by feinstimmer
That is interesting, gives more safety to app, but than that means that you do everithing via stored
procedures with interacting with DB.That is how i see it with my unexperienced eye.Or not?
Posted: Sat Dec 01, 2007 3:55 pm
by RobertGonzalez
Yes, that is what it means. I go about it this way because you, as the role of a DBA, have a lot more control over what the database sees in terms of queries. So literally every query in my apps now are in stored procs that are called by the app. The app user for the database has USE and EXECUTE privilege so I know that at the app can use the database and can execute the procs I wrote. If the app tries to call a direct query of any other type then that is a potential hack attempt and it is thwarted at the database level.
Posted: Sat Dec 01, 2007 4:17 pm
by John Cartwright
That is a really good idea Everah
However, how do you handle the creation of dynamic SQL? I guess that really limits the possibility of having TableGateways and such.
Posted: Sat Dec 01, 2007 8:13 pm
by RobertGonzalez
Yes, definitely dynamic SQL become an issue for the app, unless you right a proc that can handle the creation of dynamic SQL (think prepared statements inside of a proc

).