How to create mysql stored procedure (without using mysql-query-browser)

How to create mysql stored procedure (without using mysql-query-browser)

In here I’m explaning to simple creating mysql stored procedure without using mysql query browser. So let’s say hello using mysql stored procedure.

1) First you have to open text editor to write the mysql procdure code. So windows users can use notepad and linux users can use vim for that.  File name is “hello.sql”. The file format should be “.sql”.


2) Write the following code on your text editor,  and save.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`hello` $$
CREATE PROCEDURE `test`.`hello`()
BEGIN
SELECT ‘hello’;
END $$

DELIMITER ;


3) Explanation about the code

Normally mysql user “;” as the end of a statement (delimiter).  But since stored procedures contain semicolons in the procedure body, we need to use a different delimiter to identify the end of the procedure. Therefore in here I’m using “$$” as delimiter of the procedures. To define the delimiter we can use DELIMITER key word as the first line of the above code.

DROP PROCEDURE IF EXISTS `test`.`hello`$$ ” line will remove the stored procedure if it already exists in the test database. In here you can see I have use “$$” procedure delimiter as the end of the statement.

CREATE PROCEDURE `test`.`hello`() “ line is the start of a stored procedure definition. “hello” is the name of the procedure.

BEGIN “ line is the start of the stored procedure program.

Between BEGIN and END we can write the procedure program with using sql stetments. And we use “;” as the delimiter in the program. “SELECT ‘hello’; “ is the simplest sql statement.

END $$ ” line ends the procedure program, and it use “$$” delimiter.

After the end of procedure we should redefine the delimiter to “;” for normal use by “DELIMITER ;”.


4) Now you should log on to the mysql database in command line. 
(Windows users should go to the C:\Program Files\MySQL\MySQL Server 5.1…\bin\ where the mysql.exe located)
# Mysql –u –p


5) After login to the mysql you should type SOURCE Hello.sql

Example for linux:
mysql> SOURCE /home/Hello.sql

Example for windows: 
mysql > SOURCE D:/Hello.sql


6) After that you should type following code to run the procedure.
CALL test.hello(); 
or 
USE test;
CALL hello();

The out put,
+——-+
| Hello |
+——-+
| Hello |
+——-+

Easy way to select numerical data from MySQL table

I’ll explain this using a example,


Lets thing we have table called EMP and column called SALARY.
Now suppose there are numerical data and string data in the SALARY column. (ex: 12000, 25000, not available, null…)
For this kind of columns we can use following method to select only numerical data;


SELECT SALARY FROM EMP WHERE (SALARY+0)!=0;

Simple PHP,AJAX application for get updates without doing any action at the client side using prototype JavaScript framework

This sample program contains 5 php files as follows;

1) config.php – This file use for manage the database access configuration.
2) add_name.php – This file use for send data to insert_name.php using AJAX.
3) insert_name.php – This file use for insert new record (which are receive from the add_name.php file) to the database.
4) view_names.php – This file use for view the database and database changes.
5) select_names.php – This file use for get all database data and print.

I have user a javascript framework called “PROTOTYPE” to develop this example (don’t worry, its not gona be a hard job for you). It is a single javascript file. So you have to download the latest version of it from http://prototypejs.org/ (the file called prototype.js)

config.php is required for manage your database connection information easily.
config.php
————-
<?php
// change the parameters to connect to your DB
// CHANGE ONLY $db_host, $db_name, $username, $password
$db_host=”localhost”;
$db_name=”test”;
$username=”root”;
$password=”password”;

$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);
?>

add_name.php is provide a text field for enter the data (eg: names) into the database. By clicking the insert button the text form data send to the insert_name.php through ajax functions of prototype javascript framework.
add_name.php

——————









function insertName(){
new Ajax.Request(‘insert.php’, {
parameters: $(‘name’).serialize(true),
});
}





insert_name.php is insert post data to the database.
insert_name.php
——————–
<?php
if(isset($_POST[‘name’])){
// Connection to Database
include(‘config.php’);
// Remove HTML tag to prevent query injection
$name = strip_tags($_POST[‘name’]);

$sql = ‘INSERT INTO USER (name) VALUES(
“‘.$name.'”)’;
mysql_query($sql);
echo $name;
} else { echo ‘0’; }
?>

view_names.php is print the data of the database. It call the select_names.php file and get the data.
view_names.php
——————–

DB names

new Ajax.PeriodicalUpdater(‘names’, ‘select_names.php’,
{
method: ‘get’,
insertion: Element.update,
frequency: 1,
decay: 1
});

selest_names.php is get the data from the database and print.
select_names.php
———————
“;
while ($row = mysql_fetch_array($result)){
echo “”.$row[0].””;
}
echo “”;

?>


Mysql with C sharp

dot net commonly use sql server as DBMS. But now it is possible to us Mysql which is open source DBMS commonly use for web developers.

First download latestConnector/Net from http://dev.mysql.com/downloads/connector/net/ link. Install the connector to your pc. This will install the documentation too. You need to add Mysql.Data into the project references before use mysql in your project.

(Solution Explorer -> Your solution -> Your project -> References -> right click -> add reference… -> double click on Mysql.Data)
Now you have to add using MySql.Data.MySqlClient;’ to the top of your source code.
Try to understand following simple code :

string MyConnString = “SERVER=localhost;” + “DATABASE=database;” + “UID=root;” + “PASSWORD=passoword;”;

MySqlConnection connection = newMySqlConnection(MyConnString);

MySqlCommand command = connection.CreateCommand();

MySqlDataReader Reader;

command.CommandText = “select * from tablename”;

connection.Open(); Reader = command.ExecuteReader();

while (Reader.Read()) {

string row = “”;

for (int i = 0; i
row += Reader.GetValue(i).ToString() + ” , “;

System.Console.WriteLine(row);

}

connection.Close();

%d bloggers like this: