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. Great and really helpful article! Adding to the conversation, providing more information, or expressing a new point of view...Nice information and updates. Really i like it and everyday am visiting your site..

    CRO Agency in Chennai

    ReplyDelete
  3. 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
  4. Interesting blog which attracted me more.this blog shows that you have a great future as a content writer.keep updating.
    Digital marketing company in Chennai

    ReplyDelete