export import of mysql database on LINUX machine

How to take export of mysql database  on sql file and after this we are going to import this on new database on mysql (installed on linux machine).

take export of database to sql file_name
current path
"/home/singh/"
(you can choose path your own)
mysqldump -u root -p database_name > file_name.sql

import sql dbbackup to database
- create new database
    mysql - u root -p
    - mysql> create database database_name_new;
    - mysql> use database_name_new;
    - mysql> source /home/singh/file_name.sql

Search String in MYSQL

to search string from mysql database from all tables, than here we have procedure for it. Execute below two queries,
1. create table, which will store the result into it
2. execute procedure on database, from which you want to search string.


create table temp_details (t_db varchar(50), t_table varchar(50) , t_field varchar(50));

DELIMITER $$

DROP PROCEDURE IF EXISTS search_string $$

CREATE PROCEDURE search_string(in_search varchar(50))
READS SQL DATA
BEGIN
    DECLARE trunc_cmd VARCHAR(50);
    DECLARE search_string VARCHAR(250);
    DECLARE db,tbl,clmn CHAR(50);
    DECLARE done INT DEFAULT 0;
    DECLARE COUNTER INT;
    DECLARE table_cur CURSOR FOR
        SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('schema_name');
    
    # note change schema name to present schema name
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    
    PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
    EXECUTE trunc_cmd ;
    
    OPEN table_cur;
    table_loop:LOOP
        FETCH table_cur INTO search_string,db,tbl,clmn;
        #Executing the search
        SET @search_string = search_string;
        #SELECT search_string;
        PREPARE search_string FROM @search_string;
        EXECUTE search_string;
        SET COUNTER = @CNT_VALUE;
        #SELECT COUNTER;
        IF COUNTER>0 THEN
            # Inserting required results from search to table
            INSERT INTO temp_details VALUES(db,tbl,clmn);
        END IF;
        IF done=1 THEN
            LEAVE table_loop;
        END IF;
    END LOOP;
    
    CLOSE table_cur;
    
    #Finally Show Results
    SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;
END $$
DELIMITER ;

call search_string('abc');

it will return some set of queries, which we can directly execute to check string respective value in table.

related link:

SEARCH STRING IN POSTGRES

http://j4info.blogspot.in/2014/10/string-search-in-database-postgres.html

 

alphanumeric sort in postgres

alphanumeric sort in postgres

one day i have identify that postgres is behaving abnormally while sorting of alphanumeric column. I googled it alot and reach at decision that postgres not able to do natural sort of mix type words.
generally it give sort order like 1,1a, 10 , 2a, 2b, 30

as per natural sort it should be like 1, 1a, 2a, 2b, 10, 30

i tried with different datatype but behaviour remain same.

the solution is create a function, which will return natural sort

create table test_sort (column1 varchar);
insert into test_sort valuepad_numberss ('032');
insert into test_sort valuepad_numberss ('2a');
insert into test_sort valuepad_numberss ('01');
insert into test_sort valuepad_numberss ('1b');
insert into test_sort valuepad_numberss ('1c');
insert into test_sort valuepad_numberss ('1d');
insert into test_sort valuepad_numberss ('2');
insert into test_sort valuepad_numberss ('32t');
insert into test_sort valuepad_numberss ('4');
insert into test_sort valuepad_numberss ('28k');
insert into test_sort valuepad_numberss ('001');
insert into test_sort valuepad_numberss ('1a');
insert into test_sort valuepad_numberss ('40');
insert into test_sort valuepad_numberss ('1');

select * from test_sort order by 1
\


CREATE FUNCTION pad_numbers(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace($1,
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;


select * from test_sort order by pad_numbers(column1);

QV22 Script error code in Qlikview

Scripterror code are generally used to handle script error i.e. error handling in qlikview.

This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component.
 

• No error (Script Error =0)
• General error (Script Error =1)
• Syntax error (Script Error =2)
• General ODBC error (Script Error =3)
• General OLE DB error (Script Error =4)
• General custom database error (Script Error =5)
• General XML error (Script Error =6)
• General HTML error (Script Error =7)
• File not found (Script Error =8)
• Database not found (Script Error =9)
• Table not found (Script Error =10)
• Field not found (Script Error =11)
• File has wrong format (Script Error =12)
• BIFF error (Script Error =13)
• BIFF error encrypted (Script Error =14)
• BIFF error unsupported version (Script Error =15)
• Semantic error (Script Error =16)

Documentation
http://community.qlik.com/docs/DOC-5342

Example:
Check column existence in table 

QV21 Check column existence in table

If you need to check the column exists in table ( qlikview table , resident table or any other type of load), use below code to check column existence. 

tab1:

load * inline [

sno, deleted

1,y

2,y

3,n

4,n

];

ErrorMode=0;          // continue running on errors

LOAD

  deleted,

  rowno() as row

  RESIDENT tab1: where row=1;

IF ScritpError=11 THEN

  // field doesnt exist

  TRACE field 'deleted' does not exist ;

ELSE

  // field exists

  TRACE field 'deleted'  exist;

ENDIF

ErrorMode=1;          // restore deafult error mode


or

ErrorMode=0;          // continue running on errors

LOAD

  deleted,

  rowno() as row

  RESIDENT tab1: where row=1;

IF ScriptErrorCount >= 1 THEN

  // field doesnt exist

  TRACE field 'deleted' does not exist ;

ELSE

  // field exists

  TRACE field 'deleted'  exist;

ENDIF

ErrorMode=1;          // restore deafult error mode