mysql stored function :using mysqli function
Objective :
  • Create MySQL function , converting Celsius to Farenheit
  • Using MySQL Work Bench
  • Testing with mysql command line, and php (IIS 7.0 FastCGI)
  • mysqli_connet()
  • Calling function  & comparing mysqli_use_result with mysql_data_seek();
    • $query = "SELECT ctof(29.25)";
      $result->data_seek(0);
       or
      $result = $mysqli->use_result()
      $row = $result->fetch_row();

There are numerous ways one call stored function. I picked up a routine from the list of functions.
Start MySQL Work Bench

Use SQL Development

I have user Manas9 and pwd Manas9237, having all privileges granted. The default user root/Manas9237, can perform similar tasks.

 

Add or update this code

DROP FUNCTION IF EXISTS ctof;
DELIMITER $$

CREATE FUNCTION ctof (i NUMERIC(4,2))
RETURNS NUMERIC(4,2)
BEGIN
DECLARE t1 NUMERIC(4,2) ;
set t1 = ((i * 9/5)+32);
RETURN t1;

END$$

Click On Apply

On the review screen click on Apply again.


Note the function was created under test schema
Testing using my

Workstation: Manas9 / pwd Manas9237
Show current User. "SELECT USER()";

Command: use test; (Not case sensitive)

 
 
Testing with PHP

A)   Using $result = $mysqli->use_result()

<html>
<head><title>MySQL Stored function: 2</title>
<body>
Using $result = $mysqli->use_result(); <br/>
$row = $result->fetch_row();<br/> php code starts ---<br/>
<?php
//$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli = new mysqli("Manas9", "Manas9", "Manas9237", "test");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
echo "Connected to Server Manas9 and Schema/DB test ..";
//$query = "SELECT CURRENT_USER();" /* optional */
$query = "SELECT ctof(29.25)";
if ($mysqli->multi_query($query)) {
do{
/* store first result set */
if ($result = $mysqli->use_result()) {
while ($row = $result->fetch_row()) {
echo"<br/> 29.25 <sup>0</sup>C :: ". $row[0] . "<sup>0</sup>F";

}
$result->close();
}

} while ($mysqli->next_result());
}

/* close connection */
echo "<br/> closing DB at the end";
$mysqli->close();
?>
<br/>php code ends
</body>
<html>

 

 

B) Using : $result->data_seek(0); $row = $result->fetch_row();

<html>
<head><title>MySQL Stored function: 3</title>
<body>
Using $result->data_seek(0); <br/>
$row = $result->fetch_row();<br/> php code starts ---<br/>
<?php
//$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli = new mysqli("Manas9", "Manas9", "Manas9237", "test");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
echo "Connected to Server Manas9 and Schema/DB test ..";

$query = "SELECT ctof(29.25)";
if ($result = $mysqli->query( $query)) {
$result->data_seek(0);
$row = $result->fetch_row();
echo "<br/> 29.25 <sup>0</sup>C :: ". $row[0]. "<sup>0</sup>F";
$result->close();
}

/* close connection */
echo "<br/>closing DB at the end";
$mysqli->close();
?>
<br/>PHP code ends
</body>
<html>
With MySQL PHP query Application:
Special notes:
  • mysqli_use_result
    • each row must be retrieved individually by making calls to mysql_fetch_row().
    • This function reads directly from the server without storing results in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result().
    • Avoid calling lengthy and numerous rows
    • Avoid with mysql_data_seek, mysql_row_seek
  • mysqli_data_seek
    • creates an offset value with a row number, requires local storage, can be used with mysql_store_result(), can't be used with mysqli_use_result().