Work with PHP and MsSQL

http://msdn.microsoft.com/en-us/library/cc296170.aspx

Creating Routines in Talend

package routines;
import java.util.Date;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;


public class calculateTime {

    /**
     * helloExample: not return value, only print "hello" + message.
     *
     *
     * {talendTypes} String
     *
     * {Category} User Defined
     *
     * {param} string("world") input: The string need to be printed.
     *
     * {example} helloExemple("world") # hello world !.
     */
    public static void helloExample(String message) {
        if (message == null) {
            message = "World"; //$NON-NLS-1$
        }
        System.out.println("Hello " + message + " !"); //$NON-NLS-1$ //$NON-NLS-2$
    }
   
    public static String printCurrentTime() {
        Date dNowj1 = new Date();
        SimpleDateFormat ftj1 = new SimpleDateFormat ("E yyyy.MM.dd 'at' hh:mm:ss a zzz");
       
        return ftj1.format(dNowj1);
    }
   
    public static String getCurrentTime() {
        Date dNowj1 = new Date();
        SimpleDateFormat ftj1 = new SimpleDateFormat ("yy/MM/dd HH:mm:ss");
       
        return ftj1.format(dNowj1);
    }
   
    public static String getTimeDiff(String dateStart,String dateStop) {
       
        // Custom date format
        SimpleDateFormat format = new SimpleDateFormat("yy/MM/dd HH:mm:ss"); 

        Date d1 = null;
        Date d2 = null;
       
        try {
            d1 = format.parse(dateStart);
            d2 = format.parse(dateStop);
        } catch (ParseException e) {
            e.printStackTrace();
        }        

        // Get msec from each, and subtract.
        long diff = d2.getTime() - d1.getTime();
        long diffSeconds = diff / 1000%60;        
        long diffMinutes = diff / (60 * 1000)%60;        
        long diffHours = diff / (60 * 60 * 1000)%60;    
       
        String timeDiff = diffHours+":" + diffMinutes+":"+ diffSeconds;
        return timeDiff;
    }
   
    public static void wtireLogs(String FilePath, String LogContents) {
        File file = new File(FilePath);
         
        try {
            FileWriter fileWriter = new FileWriter(file,true);
            BufferedWriter bufferFileWriter = new BufferedWriter(fileWriter);
            fileWriter.append(LogContents);
            bufferFileWriter.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Open source Charts build easily

http://www.webdetails.pt/ctools/ccc.html#charts

http://mbostock.github.io/protovis/

Talend- source file row count

((Integer)globalMap.get("tFileExcelSheetInput_2_NB_LINE"))

Removing the special chaters from a string using user defined function using PHP

1) Function
DELIMITER //
CREATE DEFINER=`platformuser`@`localhost` FUNCTION `fn_RemoveSpecialChars`(`pString` CHAR(50)) RETURNS char(50) CHARSET latin1
    DETERMINISTIC
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE CleanString varchar(1000) DEFAULT '';
  DECLARE c CHAR(1);
  DECLARE regx INT(1);

  IF pString IS NULL
  THEN
    RETURN "";
  END IF;

  SET len = LENGTH( pString );
  REPEAT
    BEGIN
      SET c = MID( pString, i, 1 );
      SELECT c REGEXP '[^a-zA-Z0-9 /t/-/_]' into regx;
     
      IF c="" THEN
          SET CleanString=CONCAT(CleanString,' ');
      ELSEIF regx =0 THEN
        SET CleanString=CONCAT(CleanString,c);
      END IF;    
     
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
   RETURN CleanString;
END//
DELIMITER ;

---------------------------------------------------------------------------
output

 SELECT fn_RemoveSpecialChars('welcone world !#@#@');

 Result: welcome world

How to Solve Port 80 Problems When Running Apache on Windows

There are a number of well-known Windows programs which use port 80:
IIS
The most likely culprit is Microsoft Internet Information Server. You can stop the service from the command line on Windows 7/Vista:

net stop was /y

or XP:

net stop iisadmin /y

SQL Server Reporting Services
SSRS can remain active even if you uninstall SQL Server. To stop the service:

1. Open SQL Server Configuration Manager.
2. Select “SQL Server Services” in the left-hand pane.
3. Double-click “SQL Server Reporting Services”.
4. Hit Stop.
5. Switch to the Service tab and set the Start Mode to “Manual”.

Skype
Irritatingly, Skype can switch to port 80. To disable it, select Tools > Options > Advanced > Connection then uncheck “Use port 80 and 443 as alternatives for incoming connections”.

What’s Using Port 80?

Further detective work is necessary if IIS, SSRS and Skype are not to blame. Enter the following on the command line:

netstat -ao

The active TCP addresses and ports will be listed — locate the line with local address “0.0.0.0:80″ and note the PID value.
Now right-click the task bar and select Start Task Manager. Navigate to the Processes tab and, if necessary, click View > Select Columns… to ensure “PID (Process Identifier)” is checked. You can now locate the PID you noted above. The description and properties should help you determine which application is using the port.
The Task Manager allows you to kill the process, but be a little wary about doing that — especially if it’s “NT Kernel & System”.

Microsoft-HTTPAPI/2.0

NT Kernel & System is an essential service. Stopping it will probably stop Windows in a blue-screeny-like way. Therefore, enter the following at the command line:

telnet 127.0.0.1 80

If you’re faced with a blank screen, type “GET” and hit return. The chances are, you’ll see a line stating that Microsoft-HTTPAPI/2.0 is listening on port 80. If that’s the case, open Services from Administrative Tools and locate “Web Deployment Agent Service”. Stop the service and set it’s startup type to “Manual”.
The Web Deployment Agent Service is deployed with WebMatrix and was the cause of my woes. It may also be distributed with other applications installed using Microsoft’s Web Platform Installer.
That caused me a few frustrating hours so I hope it solves your Apache or WAMP start-up problems.

kill a Windows process from the command line with taskkill

The ability to perform tasks from a system’s command line allows those tasks to be used in batch files. This recipe describes several uses of taskkill to terminate Windows processes.

If you know the name of a process to kill, for example notepad.exe, use the following command from a command prompt to end it:
taskkill /IM notepad.exe

This will cause the program to terminate gracefully, asking for confirmation if there are unsaved changes. To forcefully kill the same process, add the /F option to the command line. Be careful with the /F option as it will terminate all matching processes without confirmation.
To kill a single instance of a process, specify its process id (PID). For example, if the desired process has a PID of 827, use the following command to kill it:
taskkill /PID 827

Using filters, a variety of different patterns can be used to specify the processes to kill. For example, the following filter syntax will forcefully kill all processes owned by the user Quinn:
taskkill /F /FI "USERNAME eq Quinn"

Using Putty to check Linux system memory

There are three another way to check memory on Linux

1:- free -t -m
2: vmstat
3: top

Local File Size using java

import java.io.*;
String dFilePath = "D:/Talend/Rathna/NPPES_Data_Dissemination_May_2013.zip";
File file =new File(dFilePath);
    System.out.println(" Local file Size: " + file.length());

Remote File Size using Java

Remote File Size

Question:
How can I discover the size of a remote file stored on an HTTP server? Answer:
The HTTP protocol supports the transfer of extra information about the content referenced by a URL. This information is stored in the HTTP header fields. The Content-Length header provides the size of the object referenced by the URL. Not all web servers will provide this information, so you should not develop your client code to rely on its availability.

The java.net.URLConection class provides access to any arbitrary HTTP header field through the getHeaderField() method, which will return the value of header as a string. Two other methods, getHeaderFieldDate() and getHeaderFieldInt(), will return the values of fields containing dates or integers as Date and int types respectively. They are convenience methods that save you the trouble of parsing the header field. URLConnection also provides convenience methods for accessing commonly used header fields, such as Last-Modified, Content-Type, and, yes, even Content-Length. The value of the Content-Length header is directly returned as an integer by getContentLength(). If the header value does not exist, the method returns -1.

Before querying the value of a header, you first need to establish a connection. The normal way to do this is to create a URL instance that points to the object you want to access, and then create a URLConnection with openConnection(). The following example demonstrates how to open a URLConnection and obtain the byte length of the content referenced by the URL.

import java.io.*;
import java.net.*;

public final class URLFileSize {
  public static final void main(String[] args) {
    URL url;
    URLConnection connection;
    int fileSize;

    if(args.length != 1) {
      System.err.println("Usage: URLFileSize ");
      return;
    }

    try {
      url = new URL(args[0]);

      connection = url.openConnection();
      fileSize = connection.getContentLength();

      if(fileSize < 0)
 System.err.println("Could not determine file size.");
      else
 System.out.println(args[0] + "\nSize: " + fileSize);

      connection.getInputStream().close();
    } catch(IOException e) {
      e.printStackTrace();
    }
  }
}

How to backup and restore MySQL database

Two ways to take backup of existing MySQL database:

1. Exporting tables to text files using MySQL dump.
2. Copying database files. (within same version) not recommended.

 

Exporting tables to text files using MySQL dump:

 

This is the best solution to keep backup of database so that we can restore it to any version of MySQL in any platform.

 

Syntax for exporting database to text files:
mysqldump -u root -p --all-databases > C:\MySQL_Backup.sql

Syntax for restoring database:
mysql --user=root --password=password < c:\ MySQL_Backup.sql

Copying database files:

 

MySQL stores all the tables in data directory which is divided table-wise, where table and table indexes are represented in the form of files. This approach is only valid for moving data between same version of MySQL.

 

Keep a copy of data directory so that we can just place it while restoring database. This process is valid only for moving database within same version.

 

Note: Unix file names are case sensitive whereas windows are not. So tables with names in mixed case are corrupted when we move database and tables from window to UNIX. If we use mixed case names in database and tables then it might not work properly. For best results ALWAYS use lower case names.

Physical Mysql - what are these? (.FRM, .MYI, .MYD). Need this?

Most of us work with PHPMYADMIN, Mysql Query Browser, Mysql Migration Tool, Mysql Administrator where we see the database and tables with their logical view. How many of us have see the physical structure of our db?. When the term physical comes, several question arises 

 

1) where do they reside?

2) what sort of file structure they possess?

3) how can we check it?

4) What are .frm, .myi, .myd inside them?

5) Are they going to differ for different Engines we use?

 

Generally you can find the data directory of your file with your .my.cnf file. The physical structure of db is some thing like a directory structure, where each database is a subdirectory under the main directory and has some files in it. Each table has its own file. Bascially one can see three types of files .frm, .myi, .myd.. But they are not same for all tables and db. They differ based on the engines you use and sometimes even differ with the os. There are lots of other factors that is in the backend behind the type of files you see. We will  see some basic differences.

 

.FRM  =>  It has the table structure of your table or table definition

 

.MYI  =>   It has the indexes of your table

 

.MYD =>   It contains your data

 

For ex: if your db name is school and tables called class and student. The Physical structure will have a directory called school and files class.frm, class.myi, class.myd, student.frm, student.myi, student.myd.

 

Engines Specific:

 

Lets consider table student belongs to innodb and table class has MyIsam

 

Innodb:

      Innodb has only .frm files and it has its own tablespace where it contains indexes and datas and its shared in databases. for ex: student.frm

 

MyISAM:

      MyIsam has all the three files. where .myi has your indexes, .myd has your table datas and .frm has its table definition. 

ex: class.frm, class.myi, class.myd

 

Need this:

 

   You can use these files when your db crash, or when you upgrade your db to another version and it can also be used while migrating and repairing your indexes without affecting data..

 

Bits and pieces

 

   This is for those who are not aware about engines. Apart from Innodb and MyIsam there are also some other engines in mysql such as Merge, Memory, Cluster etc..

How to find and change the engines of MySQL tables

Mostly programmers use two types of engines for MySQL DB. They are Innodb, MyISAM. You can check this details very easily in PHPMyAdmin. But if you are in MySQL interactive mode in command prompt. you cant get the details that easily from your DB. But the details of your DB, Tables etc .. are maintained in a db called information_schema. So in order to fetch the details of the engines being used type the following query:

 

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name];

 

This will list all your tables with their respective engines being used. Incase you know table_name, you can add that as a condition to your query

 

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name];

 

 

To change the Engines

 

Alter table table_name Engine=[engine you want to change]

Some Interesting statement terminators

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name]\g

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name]\G

 

Instead of semicolon, use \g and \G and see the difference :)

Deleting Duplicate Data In MySQL

For deleting duplicate rows in a MySQL table if we are writing the query as folows

 DELETE FROM table_name
where column_name in (
    SELECT  column_name
    FROM table_name
    GROUP BY column_A, column_B, column_C, ... 
    having COUNT(*)>1);

 then it will show

Error Code: 1093. You can't specify target table 'supplier_matrix' for update in FROM clause

It is required add a temporary table otherwise it will throw error.

Following query first selects the duplicate rows and creates a temporary table temp and the deletes the duplicate rows.

 

DELETE FROM table_name 
where column_name in(
    SELECT  column_name
        FROM (
                select column_name
                FROM table_name
                GROUP BY column_A, column_B, column_C, ...
                having COUNT(*)>1)temp
                );

Mysql Restrictions

http://architects.dzone.com/news/10-things-mysql-won%E2%80%99t-work

Technology Needs to know

5th jun 2013 4:41 Pm

1. R Programming language
        http://www.r-project.org/
2. Python
3. SSAS
4. SSIS    
      https://www.simple-talk.com/sql/ssis/
          http://msdn.microsoft.com/en-us/library/ms140234%28v=sql.105%29.aspx 
           http://www.sql-server-performance.com/2009/SQL-Server-2008-Integration-Services-Tasks/ 
5. SSRS
6. Talend
http://www.talend.com/resources/webinars

7. Mysql DBA
8. MsSql DBA

Form submitting using javascript: works for all the browsers

<form>
<input type="submit" onclick="Close()" value="Submit">
</form>

function Close(){

document.forms0.submit(); 

} 

Open source Reporting scripts

http://www.highcharts.com/demo/pie-basic

Unlocking tables in MySQL

Here's what i do to FORCE UNLOCK FOR some locked tables in MySQL 1) Enter MySQL mysql -u your_user -p 2) Let's see the list of locked tables mysql> show open tables where in_use>0; 3) Let's see the list of the current processes, one of them is locking your table(s) mysql> show processlist; 4) Let's kill one of these processes mysql> kill put_process_id_here;

Exporting MYSQL data into xml file

Exporting MYSQL data into xml file Using command line scripts

Exporting Mysql data to XML file

Using Mysql Procedure and function to exporting the mysql data into xml file

# --------------------------------------------------------
# Host:                         localhost
# Server version:               5.5.16
# Server OS:                    Win32
# HeidiSQL version:             6.0.0.3603
# Date/time:                    2013-03-12 12:52:19
# --------------------------------------------------------
=================================================================
 Step -1 (creating the table structure)
# Dumping database structure for sample
CREATE DATABASE IF NOT EXISTS `sample`;
USE `sample`;


# Dumping structure for table sample.employee
CREATE TABLE IF NOT EXISTS `employee` (
  `EmpId` int(11) DEFAULT NULL,
  `EmpName` varchar(10) DEFAULT NULL,
  `EmpSSN` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

====================================================
 Step -2 (Inserting the table records)

# Dumping data for table sample.employee: ~11 rows (approximately)
INSERT INTO `employee` (`EmpId`, `EmpName`, `EmpSSN`) VALUES
    (1, 'Jack', '555-55-5555'),
    (2, 'Joe', '555-56-5555'),
    (3, 'Fred', '555-57-5555'),
    (4, 'Mike', '555-58-5555'),
    (5, 'Cathy', '555-59-5555'),
    (6, 'Lisa', '555-70-5555'),
    (7, 'Jack', '555-55-5555'),
    (8, 'Mike', '555-58-5555'),
    (9, 'Cathy', '555-59-5555'),
    (10, 'Lisa', '555-70-5555'),
    (11, 'Lisa', '555-70-5555');

=========================================================================
 Step -3 (Procedure for exporting the data as .xml file)

# Dumping structure for procedure sample.myproc
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`(IN `employee_id` VARCHAR(50), IN `xml_path` VARCHAR(50))
BEGIN
    -- Define xml pretags for export
     SET @xmlPreStart = "<resultset xmlns:xsi=http://www.w3.org/2001/XMLSchema- instance > \n";
     SET @xmlPreEnd = "</resultset>";
    
     -- Define the columns to be export
    SET @col1 = " GROUP_CONCAT( '<row>\n', xml_tag('EmpId',EmpId,null,null), ";
   SET @col2 = " xml_tag('EmpName',EmpName,null,null),'</row>\n' SEPARATOR '' )";
  
     -- Check the EmpId parameter for filter the result   
    IF employee_id <> '' THEN
        SET @WhereCon = CONCAT(" WHERE EmpId IN(", REPLACE(employee_id, ";", "','"),")");
    ELSE
        SET @WhereCon = " ";
    END IF;
   
    -- Concatenating the XML pretag and colums to form the query
   SET @sqlstring =  CONCAT("SELECT  @xmlPreStart UNION ALL SELECT " ,@col1, @col2,                              
                            " FROM employee  ", @WhereCon
                           );
     -- Concatenating the query to export as .XML file
    SET @sqlstringFinal = CONCAT("SELECT  * INTO OUTFILE '",
                           REPLACE(xml_path, '\\', '\\\\'),
                           "' LINES TERMINATED BY '\r\n' FROM (",
                           @sqlstring,
                           " UNION ALL SELECT  @xmlPreEnd ) derived");
                          
   -- Execute dynamic SQL string.
   PREPARE dySQL FROM @sqlstringFinal;
   EXECUTE dySQL;
   DEALLOCATE PREPARE dySQL;
  
END//
DELIMITER ;
======================================================================
Step -4 (Function to handling the attributes)

# Dumping structure for function sample.xml_attr
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `xml_attr`(tagname VARCHAR(2000),
tagvalue VARCHAR(2000)) RETURNS varchar(2000) CHARSET latin1
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN CONCAT(' ', tagname ,'="',xml_escape(tagvalue),'" ');
END//
DELIMITER ;

====================================================================

Step -5 (Handling escape string)
# Dumping structure for function sample.xml_escape
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `xml_escape`( tagvalue VARCHAR(2000)) RETURNS varchar(2000) CHARSET latin1
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
tagvalue,'&','&amp;'),
'<','&lt;'),
'>','&gt;'),
'"','&quot;'),
'\'','&apos;');
END//
DELIMITER ;
====================================================================
Step -6 (Function to building the row columns)
# Dumping structure for function sample.xml_tag
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `xml_tag`(`tagname` VARCHAR(2000), `tagvalue` VARCHAR(2000), `attrs` VARCHAR(2000), `subtags` VARCHAR(2000)) RETURNS varchar(2000) CHARSET latin1
BEGIN
DECLARE result VARCHAR(2000);
SET result = CONCAT(' <field name = "' , tagname,'"');
IF attrs IS NOT NULL THEN
SET result = CONCAT(result,' ', attrs);
END IF;
IF (tagvalue IS NULL AND subtags IS NULL) THEN
RETURN CONCAT(result,' />');
END IF;
RETURN CONCAT(result ,'>',ifnull(xml_escape(tagvalue),''),
ifnull(subtags,''),'</field>\n');
END//
DELIMITER ;
===================================================================
Step -7 (Executing the procedure)

Check the output using folowing commands

call myproc ("1,2,3,4","D:\\Rathinasamy\\test.xml"); 

O/P

----- 

<resultset xmlns:xsi=http://www.w3.org/2001/XMLSchema- instance >

<row>

<field name = "EmpId">1</field>

<field name = "EmpName">Jack</field>

</row>

<row>

<field name = "EmpId">2</field>

<field name = "EmpName">Joe</field>

</row>

<row>

<field name = "EmpId">3</field>

<field name = "EmpName">Fred</field>

</row>

<row>

<field name = "EmpId">4</field>

<field name = "EmpName">Mike</field>

</row>

</resultset>