We have following conditional statements in Mysql SPs
- IF statement
- ELSE statement
- IFELSE statement
CREATE PROCEDURE PROC_TEST(IN x INT, OUT str VARCHAR(32))
SET str=’Smaller than 5′;
SET str=’Greater than 5 but smaller than 10′;
SET str=’Greater than 10′;
We encounter various situations where we need to execute multiple queries in order to get the expected results, sometimes the output of one query is the input to another query. Lets take an example of the situation where a coupon code is offered for the customers who have spent more than 2000 rs. in our products and based on the coupon we need to provide the discount, so the process should be as follows:-
- Get the coupon code provided by the customer.
- Get the customer id
- Get the total value of the items in the shopping cart
- Check if the customer’s total purchase amount > 2000, if yes then proceed with the next steps else we need to show an error message that coupon is valid for the customers whose total spent amount>2000.
- Check if coupon is valid. If valid then proceed with the next steps else terminate here and show user an error message that coupon is not valid.
- Get the discount amount from the coupon table corresponding to that coupon.
- Calculate the last paid amount after the discount to the customer.
- Return this amount for further processing.
If we will try to implement this functionality by using simple Mysql queries and PHP then we need to execute lots of queries and after getting the result of one query we will make some computations at the PHP end and then again we will get back to the Mysql with a new query, so the control will move back and forth between the Mysql and PHP, which is not effective. Stored procedures is a solution for this problem where we can put all this business logic at the Mysql end itself so that PHP will execute a single query and Mysql will return the expected results after all the calculations and other necessary actions. We can define Stored Procedure as :-
“Stored procedure is a set of SQL statements that executes as a single SQL query to provide the required results”.
How to write a stored procedure (SP)
A SP runs as a single SQL statement, but the problem is that SP contains multiple SQL statements so how will the Mysql takes multiple SQL as a single SQL statement?
When we write a mysql query then it ends with a DELIMITER semicolon ‘;’ which tells the compiler that the query ends here. For a stored procedure we change the DELIMITER so that we could put multiple SQL statements in a single query. So the steps should be :-
- Change delimiter (e.g. $$)
- Create procedure
- Write all the MySQL statements
- Change the delimiter again to semicolon ‘;’
CREATE PROCEDURE PROC_ADDNUM(IN num1 INT, IN num2 INT, OUT numAdd INT)
#All Declaration goes here if any
DECLARE idTest int; #We are not using idTest and hence this statement could be removed, I just wrote it here to show the syntax
#All SQL statements goes here
SET numAdd= num1+num2;
We can use this procedure by using ‘CALL’:-
CALL PROC_ADDNUM(3,2,@outVal); #This will execute the SP and will store the output in @outVal
SELECT @outVal; #This will show the value in the out param @outVal (i.e. the sum of two numbers (3+2=5)
We can do a lot of things in the SPs, some of them are as follows:-
- Conditional statements
- Nested SPs
- Creating dynamic queries
What Is SOAP
SOAP (Simple Object Access Protocol) is simple XML based protocol that allows application interact over HTTP.
- Soap is platform independent
- Soap is language independent
- Recommended by w3c
A SOAP message is simple XML document that contains following elements:-
- Envelope element: This element tells if the XML document contains SOAP message
- Header element: Contains header information
- Body Element: Contains the call and response information
- Fault Element: Contains response regarding error and status
SOAP message must be encoded with using XML
PHP With SOAP
Need to enable the soap extension , in case of https openssl extension is also required.
use SoapClient PHP class for the SOAP calls
Important Soap Functions
SoapClient::__getFunctions — Returns list of available SOAP functions
SoapClient::__getTypes — Returns a list of SOAP types
SoapClient::__soapCall — Calls a SOAP function
read more here Soap Client
xls2csv – Is a script that converts xls files to csv. The author of this script is Ken Prows. This script is free so you can modify it, redistribute it under the same terms as Perl itself. This script can be downloaded from here: http://search.cpan.org/~ken/xls2csv-1.06/
- -x : filename of the source spreadsheet
- -b : the character set the source spreadsheet is in (before)
- -c : the filename to save the generated csv file as
- -a : the character set the csv file should be converted to (after)
- -q : quiet mode
- -s : print a list of supported character sets
- -h : print help message
- -v : get version information
- -W : list worksheets in the spreadsheet specified by -x
- -w : specify the worksheet name to convert (defaults to the first worksheet)
1. To list all worksheets:- xls2csv -W -x xlsfilename.xls
2. To convert a xls file to csv :- xls2csv -x EXCEL_FILE -b CHARSET -c CSV_FILE -a CHARSET (If xls is having multiple worksheets then by default it will process the first worksheet)
3. To convert a particular worksheet:- xls2csv –x xlsfilename.xls -c csvfilename.csv -w “worksheetname”
Converting all XLS worksheets to CSV:-
It can be done in following steps:-
1. Get the list of all the worksheets first (xls2csv -W -x xlsfilename.xls)
4. Then process the conversion for each worksheet (xls2csv –x xlsfilename.xls -c csvfilename.csv -w “worksheetname”)
First Perl should be installed with following modules:-
For installation you can use CPAN :-
For Interactive Mode:-
perl -MCPAN -e shell;
For Batch Mode:-
autobundle, clean, install, make, recompile, test
To install any module:
1. In interactive mode type: install , e.g. install Spreadsheet::ParseExcel