SQL Server Commands

SQL Server commands for date :

convert(date, start_date) != convert(date, GETDATE())

update [THIRD_PARTY_DELIVERY].[dbo].[Integration_FTP]
set ftp_url_path = REPLACE(ftp_url_path, '/pre_processed','')
where network_name = 'thomsonreuters'

MySQL Caps first

DELIMITER $$


DROP FUNCTION IF EXISTS `CAP_FIRST `$$


CREATE FUNCTION CAP_FIRST (input VARCHAR(255))


RETURNS VARCHAR(255)


DETERMINISTIC


BEGIN

DECLARE len INT;

DECLARE i INT;


SET len = CHAR_LENGTH(input);

SET input = LOWER(input);

SET i = 0;


WHILE (i < len) DO

IF (MID(input,i,1) = ' ' OR i = 0) THEN

IF (i < len) THEN

SET input = CONCAT(

LEFT(input,i),

UPPER(MID(input,i + 1,1)),

RIGHT(input,len - i - 1)

);

END IF;

END IF;

SET i = i + 1;

END WHILE;


RETURN input;

END$$


DELIMITER ;

select CAP_FIRST(item_name) from cms_menu_items;

Java - Date Formats

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
String dt = sdf.format(payloadAccountRequest.getLastModifiedTime());
query.append(dt.replace(" ", "T") + "Z");

import java.sql.TimeStamp;
Calendar calendar = Calendar.getInstance();
calendar.add(calendar.YEAR, -5);
Timestamp lastModifiedTime = new Timestamp(calendar.getTimeInMillis());

public void compareTimeStamp() {
String sfdcTimeStampString = "2010-10-29T15:01:28.000Z";
sfdcTimeStampString = sfdcTimeStampString.replace("T", " ");
sfdcTimeStampString = sfdcTimeStampString.replace("Z", " ");

Calendar calendar = Calendar.getInstance();

Timestamp sdktimeStamp = new Timestamp(calendar.getTimeInMillis());

Timestamp sfdcTimeStamp = Timestamp.valueOf(sfdcTimeStampString);

if (sdktimeStamp.after(sfdcTimeStamp)) {
System.out.println("SDK Wins");
} else {
System.out.println("SFDC Wins");
}
}

setDate(new Date(System.currentTimeMillis()))

Java - Conversion Tips

Covert Long to String with Format


format : Returns a formatted string using the specified format string and arguments.


Long customerId = new Long(12);
System.out.println("Output..." + String.format("%04d", customerId));

Convert Double to String

Double doubleValue = new Double(40.5508995056152);
        double doubleVal = doubleValue;
        System.out.println("Double value.." + Double.toString(doubleVal));



String destinationDataType = destinationClass.toString();
String sourceValue = sourceFieldValue.toString();

SimplePropertyValueTO destinationFieldProperty = new SimplePropertyValueTO();
destinationFieldProperty.setPropertyName(param);
destinationFieldProperty.setPropertyType(destinationDataType);

if (destinationDataType.equalsIgnoreCase("string")) {
destinationFieldProperty.setPropertyStringValue(sourceValue);
} else if (destinationDataType.equalsIgnoreCase("short") || destinationDataType.equalsIgnoreCase("int")
|| destinationDataType.equalsIgnoreCase("long")) {

Long longDestinationValue = null;
longDestinationValue = longDestinationValue.parseLong(sourceValue);
destinationFieldProperty.setPropertyLongValue(longDestinationValue);
} else if (destinationDataType.equalsIgnoreCase("double")) {
Double doubleDestinationValue = null;
doubleDestinationValue = doubleDestinationValue.parseDouble(sourceValue);
} else if (destinationDataType.equalsIgnoreCase("boolean")) {
Boolean booleanDestinationValue = false;
booleanDestinationValue = booleanDestinationValue.parseBoolean(sourceValue);
} else if (destinationDataType.equalsIgnoreCase("Timestamp")) {
Timestamp timestampDestinationValue = null;
timestampDestinationValue = Timestamp.valueOf(sourceValue);
destinationFieldProperty.setPropertyDateValue(timestampDestinationValue);
}

Convert longblob data into Object
request = (Request) ByteConverter.getObject(requestMessage.getRequest());

public static byte[] getBytes(Object obj) throws java.io.IOException {
if (obj == null) {
return null;
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(obj);
oos.flush();
oos.close();
bos.close();
byte[] data = bos.toByteArray();
return data;
}
public static Object getObject(byte[] objectData) throws IOException, ClassNotFoundException {
Object object = null;

if (objectData != null) {
ObjectInputStream in = null;
ByteArrayInputStream bin = new ByteArrayInputStream(objectData);
BufferedInputStream bufin = new BufferedInputStream(bin);

in = new ObjectInputStream(bufin);
object = in.readObject();
if (in != null) {
in.close();
}
}
return object;
}

snippet : writing-objects-to-file-with-objectoutputstream

ObjectOutputStream outputStream = null;

try {

//Construct the LineNumberReader object
outputStream = new ObjectOutputStream(new FileOutputStream(filename));

Person person = new Person();
person.setFirstName("James");
person.setLastName("Ryan");
person.setAge(19);

outputStream.writeObject(person);

person = new Person();

person.setFirstName("Obi-wan");
person.setLastName("Kenobi");
person.setAge(30);

outputStream.writeObject(person);


} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
//Close the ObjectOutputStream
try {
if (outputStream != null) {
outputStream.flush();
outputStream.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}

How to connect to FTP without any software

Steps

1. Open your browser
2. In browser type : ftp://@ (eg : ftp://dowgeneric@ftp.operative.com) Press enter
3. Enter ftp password.

You will redirect to home ftp directory.

Salesforce.com Apex Classes and Apex Triggers

Apex Classes:
-------------
You can write sf.com Apex code in Apex classes. Using this Apex classes you can call your own application and pass data from sf.com.

Apex classes can be called from Apex Triggers. Apex classes having logging mechanism. You can create apex classes with any salesforce.com Versions.

Setup -> App Setup -> Develop -> Apex Classes

Apex Triggers:
---------------
You can create your own Apex triggers, and the trigger can be called when you create / update accounts, contacts, opportunity etc..

From Apex trigger you can call your Apex Classes and Apex class will sync information to your application.

Setup -> App Setup -> Develop -> Apex Triggers

This apex classes and Apex Triggers is specific to the sf.com credential, in which credential you deployed.

Debug sf.com System Logs:
--------------------------
Login into sf.com. In the top right corner you can see System Log next to setup. Click System Log (Its for debugging and executing and testing your classes and triggers).
1. Click show filter settings (Make sure Apex Code : DEBUG level for debugging the errors.)

Type the Apex trigger / class code in Execute Apex and click 'Execute' button. See the errors in errors log list.

SalesForce.com Setup Info.

Reset Security Token
---------------------
Security token is necessary when you use api salesforce.com with credentials.
Login into sf.com -> In top right click Setup -> In Personal Setup expand My personal Information -> Click 'Reset my security token'.

Reset Owner Email address for activation etc..
------------------------------------------------
Login into sf.com -> In top right click Setup -> In Personal Setup expand Email -> My Email Settings

Add Trusted Ip's in salesforce.com
-----------------------------------
When you send any request from your organization system through api to salesforce.com with username and password which will not allow with out security token. So, you can add your ip in trusted ip's in salesforce.com. So, which will allow api transaction.

Login into sf.com Setup -> Administration Setup -> Security Controls -> Network Access

Invoking triggers from sf.com to your application:
---------------------------------------------------
If you need to invoke triggers from sf.com to your application, you need add your application web address in All Remote Sites in sf.com

Login into sf.com Setup -> Administration Setup -> Security Controls -> Remote Site Settings -> create New view

Dumping MySQL Stored Procedures, Functions and Triggers

Below is the command, Dumping StoredProcedures along with database.

-R - Dump the stored procedures, functions and triggers

 --single-transaction - This command is needed when we take mysqldump to avoid transactions locking tables. you can add --quick for large tables. This option should not use for clustered tables.


Dump the db and skip the definer before we do for the views
mysqldump -u -p -h -R  --single-transaction  | sed 's/DEFINER=[^*]*\*/\*/' > outputfile.sql

Command to take a backup of only stored procedures and functions

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt --skip-trigger > outputfile.sql

--no-data --routines --events --triggers

#databases=`mysql --user="" --password="" --host="" -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

username=$1

password=$2

databases=("db1" "db2" )

for db in ${databases[@]}; do

  if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] && [[ "$db" != "$IGNORE_DATABASE" ]] && [[ "$db" != "sys" ]]; then

echo "Dumping DDL database: $db"

# No Data

mysqldump --user="${username}" --password="${password}" --host="${host}" --no-data --routines --events --triggers "$db" > ./"V1__create_initial_scheme_${db}".sql

  fi

done

mysqldump --user="${username}" --password="${password}" --host="${host}" --no-create-info --skip-triggers --compact "alerts_configs" alert_types alert_notification_templates > ./"default-master-data_alerts_configs".sql

Dump selected tables
mysqldump -uuser -ppwd --tables tblName1 tblName2 > dbname_tables.sql

Issue i faced when i export routines and import.

This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’

Use –skip-triggers also. To avoid above error. Because, triggers are default loaded in mysqldump.

Importing outputfile.sql is usual as MySQL database import.
use information_schema;
select ROUTINE_NAME from ROUTINES where ROUTINE_SCHEMA = '' ;
select TRIGGER_NAME from TRIGGERS where TRIGGER_SCHEMA = '' ;


Stored procedure in mysql introduced after 5.x

-- Stored Procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS MIGRATE_CUSTOMER_FIELD_MAPPING_DATA$$
CREATE PROCEDURE MIGRATE_CUSTOMER_FIELD_MAPPING_DATA()
BEGIN
-- Declare the variables in the beginning
DECLARE no_more_customer INT DEFAULT 0;
DECLARE customer_id int(11);

-- Declare the cursor
DECLARE cur_customer CURSOR FOR
  SELECT ID FROM CUSTOMER;

-- Declare handler for exception
  DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_customer = 1;

-- Open the cursor
OPEN cur_customer;
-- Fetch the data from cursor into variable
FETCH cur_customer INTO customer_id;
REPEAT
 INSERT INTO CUSTOMER_FIELD_MAPPING(CUSTOMER_ID, DEVICE_TYPE_ID, SERIAL_NUMBER_UNIQ, ZIGBEE_MAC_ID_UNIQ,CREATED_ON)
       VALUES (customer_id, 1, "TRUE", "TRUE", NOW());

 INSERT INTO CUSTOMER_FIELD_MAPPING(CUSTOMER_ID, DEVICE_TYPE_ID, SERIAL_NUMBER_UNIQ, ZIGBEE_MAC_ID_UNIQ,CREATED_ON)
       VALUES (customer_id, 2, "TRUE", "TRUE", NOW());

FETCH cur_customer INTO customer_id;
UNTIL no_more_customer = 1
END REPEAT;
-- close the cursor
CLOSE cur_customer;

-- End the stored procedure with delimiter
END$$

DELIMITER ;

call MIGRATE_CUSTOMER_FIELD_MAPPING_DATA();

DROP PROCEDURE IF EXISTS MIGRATE_CUSTOMER_FIELD_MAPPING_DATA;

Install MySQL - Tips and Errors with Resolution

MySQL - bit datatype
a) This type will be used to store true / false information. (false will be stored as value 1 and true value stored as 2)

b) eg: to update bit value (true / false) : UPDATE SET column = TRUE;

c) To view the bit datatype information in command prompt type column_name + 1

eg query : SELECT columnName + 1, tableName.* from tableName; (columnName is a bit data type column).

INSERT statement from SELECT statement
eg : INSERT INTO operation_processor (processor_id , operation_id)
VALUES(1, (SELECT operation_id FROM operation WHERE production_system_template_id = 3 AND name = 'AreaCodePull'));

MySQL Workbench : 
If you want to do ER Diagram or Querying database use MySQL WorkBench. Its very nice. You can do "Reverse Engineering" also under database menu. This will give you ER diagram for the existing database or tables.

MySQLImport - Import file directly into table.
-------------------------------------------------
mysqlimport --compress --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --ignore-lines=1 --lines-terminated-by="\n" --local -uusername -ppwd -hhost db_name /path/fileName.csv

You can use REPLACE for import based on unique key for update.

Source.csv is tableName.csv

ERROR 1449 (HY000) at line 13718: The user specified as a definer ('username'@'%') does not exist

CREATE USER 'user'@'localhost' IDENTIFIED BY 'user';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES; 

GRANT SELECT, SHOW VIEW ON dbame.`table` to 'user_name'@`%`;

To Revoke permission
REVOKE REFERENCES ON  `jabord_dev_db`.* FROM 'jabord_user'@'%'

If you upgrade your mysql from lower version to higher try "mysql_upgrade" to avoid corrupted table issue.

http://forge.mysql.com/wiki/Top10SQLPerformanceTips

MySQL Install 5.5

sudo apt-get purge mysql-server

sudo apt-get purge mysql-client

sudo apt-get purge mysql-common

sudo apt-get autoremove

sudo apt-get autoclean

sudo rm -fr /var/lib/mysql

sudo wget http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-server-5.5.14-1.linux2.6.x86_64.rpm

sudo wget http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-client-5.5.14-1.linux2.6.x86_64.rpm

sudo apt-get install alien

sudo alien -i MySQL-server-5.5.14-1.linux2.6.x86_64.rpm --scripts

sudo alien -i MySQL-client-5.5.14-1.linux2.6.x86_64.rpm --scripts

sudo apt-get install libaio1 libaio-dev

sudo /usr/sbin/mysqld --skip-grant &

sudo /etc/init.d/mysql start

Before command failure :  

ALTER TABLE tableName ADD column columnName dataType primary key auto_increment BEFORE secondColumnName; 

Above command may fail. Because you cannot add first column with 'before' command. Use FIRST replacing BEFORE keyword and you should be good

MySQL - Views

MySQL View is very useful to see data in one view place. IF we have multiple tables and data's and if you specifically interested to view specific data you can create view below is example.

CREATE VIEW test.v AS SELECT * FROM t;

DROP VIEW IF EXISTS im_job_info;
CREATE VIEW im_job_info AS
SELECT DISTINCT a.req_id , a.source_req_id , a.source, a.sub_source,
b.job_id, b.external_target_id, e.operation_type, b.status AS 'jobstatus', a.status AS 'requeststatus',
c.send_status, b.last_status, b.priority, b.production_system_id,
a.received_at,b.queued_at, c.sent_at

FROM
request_message a, job b, response_message c, external_target d,
operation_type e

WHERE
a.req_id = b.req_id AND c.im_req_id = b.req_id AND
d.external_target_id = b.external_target_id AND
d.operation_type_id = e.operation_type_id;

Query to List all the views from the db
Syntax : 
     SHOW FULL TABLES IN `db_name` WHERE TABLE_TYPE LIKE 'VIEW';

MySQL - Duplicates Handling with Insert Ignore / Replace

CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);

INSERT IGNORE INTO person_tbl VALUES( 'Thomas', 'Jay', 'Male'); - This query will try to insert and if the record already exist it ignores to insert. It checks unique / primary key

REPLACE INTO person_tbl (last_name, first_name, sex) VALUES( 'Jay', 'Thomas', 'FeMale'); - This query will try to replace if the same unique / primary key record already exist. If record not exist it will insert.

MySQL - Event Scheduler - Example

event table exist in mysql database for more info. to get about event. It will be in MYsql VERSION > 5.1.16

CREATE TABLE a (
c1 INT(1));

SET GLOBAL event_scheduler = 1; -- Turn on event scheduler variable
-- DROP EVENT IF EXISTS insert_record;

DELIMITER //
CREATE EVENT IF NOT EXISTS insert_record
ON SCHEDULE AT now() + INTERVAL 1 MINUTE
[ON COMPLETION [NOT] PRESERVE]
DO
BEGIN
INSERT INTO a (c1) values (2);
INSERT INTO a (c1) values (5);
END//

select * from mysql.event;