Searching...
Saturday 3 November 2012

Procedures In Mysql

11:07 pm

Procedures In Mysql

when you pass an sql query using mysql_query() function each the qyery gets compiled and after that it gets executed even if the query is same. This reduces the performance your application.
To overcome this ,we can use a procedure or a function in mysql.
Procedure will not a return a value back to the caller (but, using out parameters we are able to send multiple values to caller) where as a function returns a single a value back to the caller.
Procedure as associated with mainly two type of parameters.
1.      IN –pass by value
2.      OUT- pass by reference.
Steps to create a procedure :
1.      Start mysql db server.
2.      Open mysql prompt
C:\xampp\mysql\bin>mysql –u root –p
3.      Change the delimiter
Mysql> delimiter//
4.      Select a DB
Mysql>use employee //
Mysql> create a procedure myproc(OUT param1 INT)
BEGIN
Select count(*) into param1 from emp;
End//
Mysql>delimiter;
To invoke a procedure
Mysql> call myproc(@p);
Mysql> select @p;
Php  program to invoke the above procedure?
<?php
 $con = mysql_connect(“localhost”,”root”,””);
Mysql_select_db(“employee”);
$res = mysql_query(“call myproc(@s)”);
If($res)
{
$rs=mysql_query(“select @s”);
//var_dump($rs);
$row = mysql_fetch_row($rs);
Echo “number of records=”.$row[0];
}
Else
Echo “procedure not found”;
Mysql_close($con);
?>
Inserting images in mysql:
Mysql has a BLOB(binary large objects) datatype which can be used to store binary data . blobs are typically images ,audio or other multimedia blob objects. Mysql has 4 blobs types.
a.       TINYBLOB
b.      BLOB
c.       MEDIUMBLOB
d.      LONGBLOB
All these types differ only in their sizes.
1.      Create a table named “test_image” in mysql “employee” database having 3 columns show below.
Mysql> create table test_image(id int(10) auto_increment primary key, name varchar(20)m image blob);
Program to insert an image:
<?php
 $con = mysql_connect(‘localhost’,’root’,’’);
Mysql_select_db(“employee”);
$data = file_get_contents(“images/back1.jpg”);
$data = addslashes($data);
$res = mysql_query(“insert into test_image(name,image) values(‘back1.jpg’,’$data’)”);
If($res)
Echo “record inserted”;
Else
Echo mysql_error();
Mysql_close();
?>
Displaying images stored in mysql?
<?php
$con = mysql_connect('localhost','root','');
mysql_select_db("emp");
$rs = mysql_query("select * from test_image where id=1");
if(mysql_num_rows($rs)>0)
{
    $row = mysql_fetch_row($rs);
    $n = file_put_contents('Photo0104.jpg', $row[2]);
    header("content_type:image/jpeg");
    echo $row[2];
}
else
    echo "no matching record found";
mysql_close($con);
?>
OutPut:
Image displayed.

procedures in mysql, stored procedures in mysql, mysql variables in stored procedures, stored procedures in mysql examples., 

4 comments:

  1. thus php sql program is really interesting and i got lot of information about the basics and applications thanks for sharing.

    dotnet Training in Chennai

    ReplyDelete
  2. I simply want to say I’m very new to blogs and actually loved you’re blog site. Almost certainly I’m going to bookmark your blog post . You absolutely come with great well written articles. Thanks a lot for sharing your blog.
    Architects in Chennai

    ReplyDelete
  3. its really very useful topic. it should be help us more and then thanks to post article like this.
    I had a chance to get some useful and unique information.
    I would like to suggest your blog.
    Digital Marketing Company in India

    ReplyDelete
  4. I got to gain a lot new knowledge, and assuredly this article might be considerable for many newbies as well as experienced programmers. Keep sharing such beneficial guidance.
    Web Design Agency | Website Redesign Company

    ReplyDelete