MARKS TUTORIAL

This article will define a basic blueprint for a mysql stored procedure that will declare a cursor to fetch employee details of a particular department and log them into a separate table. The stored procedure will also handle any error occurred during the script execution. The behavior is to rollback the work done and exit from the procedure. SET @sql_notes = @@session.sql_notes; SET SESSION sql_notes = 0; DROP PROCEDURE IF EXISTS usp_fetch_and_log_emp_name; DELIMITER $$ CREATE PROCEDURE usp_fetch_and_log_emp_name(IN p_deptid INT) BEGIN declare v_empid INT; declare v_empname VARCHAR(100); declare v_deptid INT; declare no_more_emp boolean; declare emp_cursor cursor for select emp.empid, emp.name, emp.deptid from employees emp where emp.deptid = p_deptid; declare exit handler for sqlexception begin rollback; close emp_cursor; select @@warning_count as warning_count; show errors; end; declare exit handler for sqlwarning begin rollback; c...

I was working on a customer case where I ran into an interesting problem - Query joining about 23 tables got stuck in statistics stage. The customer need to generate a report in pipe separated values format from their database. Since the database is hosted as AWS MySQL RDS, using SELECT ... INTO OUTFILE was not an option. Therefore I decided to dump the data by using mysql command line tool along with sed command to convert tab delimited to pipe delimited column values. mysql -e "select te.EId AS \"EID\", tea.AId AS \"AID\", tea.ActivationState AS \"Status\", tp.PRDName AS \"Product\", tea.FamilyId AS \"Product Family\", tp.Ver AS \"Product Version\", tc.CSTMRName AS \"Customer Name\", tc.CSTMRIdentifier AS \"Customer ID\", tea.ActivationDateTime AS \"Activation Date\", tea.quantity AS \"Activated Quantity\", caswuid.AttrValue AS \"SWUID\", cadm.AttrValue AS \"D...

SQL stands for Structured Query Language that works on a set of data. A set can consist of a single record or multiple records. SQL is called a language because it has syntax ( the set of rules that defines the combinations of symbols that are considered to be a correctly structured document or fragment in that language.) as well as semantics ( the meaning of the formed sentence). SQL is called a set language and not a programming language because in SQL we only tell the system what we want and the system decides the how to part itself. But in a programming language, we mention what and how to part ourselves. Let us take an example: In MySQL, we only tell the system that we want all the records from the employee table where the first name is John. The MySQL system decides itself that how to retrieve that information. It decides that should I scan the whole table or use another method, with the help of available statistics and optimizer. Now take a real world example to und...

In this article I will explain the difference between Encode In and Convert To. You will find similar menus in one of the popular text editor called Notepad++. Encode In means keep the raw information (byte sequence) same and map that to the specified character set. Convert To means keep the character information same while map byte sequence to the specified character set. Let us take a hypothetical example to understand it. We have two character set: X and Y as follows: Character Set X Character - A, B, C Hex Format - 3C, 49, 2D Character Set Y Character - A, B, C Hex Format - 49, 2D , 3C We have a file in character set X named message.txt with content as ACB BC. Hex Content as 3C2D49 492D. Encode In character set Y, file becomes :  Hex Content - 3C2D49 492D Character - CBA AB Convert To character set Y, file becomes :  Hex Content - 493C2D 2D3C Character - ACB BC (I have not replaced the space character with its hex value.) In short ...