Informix Date conversion

When we store time stamp in database the column can be of type DATE TIME YEAR TO SECOND .

It Will contain day of month, month, year, time in seconds. But usually the scenarios that we Will be handling Will be pull all the data that happened this year or pull all the data that happened on particular date

on these scenarios we need to extract the columns from time stamp data.

here is the list of keywords

Day
Month
Year
Date

select DAY(activity_date) from table .

select MONTH(activity_date) from table.

select YEAR(activity_date) from table.

select DATE(activity_date) from table

say the timestamp is
2014-03-05 13:17:40

queries will return

5
3
2014
03/05/2014

Sql Fundas

sometimes we spend very hard time by using some weird inner joins, group by clauses and nested queries for retrieving set of data from our database.

Thumb rule : when finding the rows that you want is moving very hard , then try eliminating rows that you don’t want to be there.

one simple delete statement can do an awesome job that a group by and complex sub queries does.

so , always keep in mind that they are two approaches for getting the data.

1. finding what you want
2. eliminating what you don’t want

Using SQL OR and UNION operators

Whenever we write an sql , it is very important that we check the cost of it. If there is any room for improvement in the query based on the time taken by the query to fetch the result.

One such occasion is using an OR clause. There are certain situations where we need “Either this or that” kind of results . We are forced to use OR conditions on that query . But, i personally feel OR condition are little slower than UNION when we hit on the bigger tables .

The UNION query looks bigger , but it actually runs faster than the OR condition. Just for the clarity , am just adding a simple example where we need to find out the employee names and their salary if they have an income greater than 2000 or their past increment is higher than 200

we have the table structures given below

table name : emp
empid empname
1      alice
2      bob
3      david
4      charlie

table name : emp_sal
empid  salary  increment
1      1000     100
2      2000     200
3      4000     400
4      5000     500

by using or :

select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and (s.salary > 2000 or s.increment > 200);

by using UNION :

select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and s.salary > 2000
UNION
select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and  s.increment > 200;

The union query looks bigger. But , it runs pretty faster than OR query .

Extracting n lines from file linux

For extracting lines from file use sed commmand in linux

sed -n '10p,20p' input.txt > output.txt

in the above command -n represent lines and 10 and 20 represents the offset , input.txt is the file from which we are extracting the input and storing the output in output.txt

say if i just need the nth line , then use the command as

sed -n '10p' input.txt > output.txt

The above command will extract only the 10th line

if i need selected lines say 10,15,17

sed -n '10p;15p;17p' input.txt > output.txt

Note : Be careful when you are using the > operator . If it is misplaced entire file will get erased.

ORA – 1205, TNS: listener does not currently know of SID given in connection descriptor

This error occurred in my code when i was trying to connect oracle 11g using java .

1. Jdbc connectivity code was perfect
2. Oracle service was running
3. Was able to login sqlplus
4. Database startup and shutdown commands were working .
5. All sql queries worked fine

Even thought i was not able to establish connection. After a long search in Google i found that we need to restart tns by this command .

Lsnrctl start

Don’t forget to restart your database.

Shutdown

Startup

Tips for writing sql

These are the few points that need to be taken care of when we write sql statements. I furnish details upto my knowledge , any suggestions and improvements are most welcome.

1. Try to write sql in aligned format. Type of alignment differ from people to people . The alignment that i follow is

select
  a.field1,
  a.field2,
  b.field1,
  b.field2
from 
  table1 a,
  table2a
where
  a.fieldx=b.fieldx and
  a.fieldy=xxx and
  b.fieldz=ttt;

keywords align in the left side whereas the content will be a one tab space from the left margin. Alignment improves readability. Editing and making comparison can be done easily.

2. The next part is the order in which we use table. Try to keep the order descending. if table1 is bigger than table2 , then the order should be table1,table2.

3. As we follow the order for table names, we need to follow the order for conditions too. For example if table a is bigger than b then finish off all the conditions related to a and then start b and so on.

4. Be careful when you use alias. setting keywords as alias will end you in trouble.

insert text from file to mysql

i had a file with 58000 rows in it.. was searching for a command to insert all the rows in one shot..

very simple..

LOAD DATA INFILE ‘/path/to/sample.txt’ INTO TABLE `database_name`.`table_name`

worked like a charm..

MS-ACCESS Database Connection

OleDbConnection con=new OleDbconnection(“Provider=Microsoft.Jet.OleDb.4.0;Data Source=Path”);
con.open();

con.close();

The path for example

C:\\xxx.mdb

If the file xxx.mdb is located in the c directory.

Oracle Database Connection

String str = “Data Source=orcl;User Id=system;password=system;”;
OracleConnection con = new OracleConnection(str);
con.Open();

con.close();

SQL SERVER CONNECTION

When we use a database this first thing is the connecting to the database. When you get error while connecting the whole thing will collapse. There are number of ways to connect to the SQL server. I will give my method below

string connection = ” Data Source=server; Initial Catalog=Database_Name; Integrated Security=True “;
Sqlconnection con=new Sqlconnection(connection);
con.open();

con.close();