Tuesday, 30 July 2013

" DUAL " KEYWORD IN SQL SERVER / ORACLE


What is DUAL in Oracle?

Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X.
You can check the content of the DUAL table using the following syntax.
SELECT * FROM dual
It will return only one record with the value ‘X’.

What is the reason for following error in SQL Server?

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dual’.
The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround.

What is the Equivalent of DUAL in SQL Server to get current datetime?

Oracle:
select sysdate from dual
SQL Server:
SELECT GETDATE()

What is the equivalent of DUAL in SQL Server?

None. There is no need of Dual table in SQL Server at all.
Oracle:
select ‘something’ from dual
SQL Server:
SELECT ‘something’

I have to have DUAL table in SQL Server, what is the workaround?

If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.
Here is a quick script to do the said procedure.
CREATE TABLE DUAL(DUMMY VARCHAR(1)
)
GOINSERT INTO DUAL (DUMMY)VALUES ('X')GO
After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.

WHAT IS - > " SELECT * FROM [TABLE_NAME] WHERE 1=1 " ??

What is

select * from xxx where 1=0 what does this mean?
also there is one
select * from yyy where 1=1

??
??

ANSWER ->


1. Select * from table where 1=0
return just header of the fields (attribute)
but with 0 rows

2. Select * from table where 1=1
return whole table entries
this is same as select * from table


Simple ans.

Logical ?

If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like:
and <condition> 
and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.
I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.

How to get top " n " number of rows from table


Suppose you want top n values from table , so execute following query
with replacement of following item as mention below :

1 )  Replace your our tablename with [table_name]

2 )  Replace your "n" with row numbers  (how many rows you want )
[
Note :  You can do multiply and division operations also
]

Query for Top rows :

SELECT
MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number;


Query for Bottom  rows :

SELECT MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,
COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 10 - 2 ) ORDER BY Row_Number;

Example :

SELECT *
FROM
(SELECT row_number() over (order by transID) AS line_no, *
FROM [dbo].[UserMast]) as users
WHERE
users.transID < 10
OR
users.transID BETWEEN 34 and 67
 

Removing Leading Zeros From Column in Table


Step 1: 

Improving the ResultSet
I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well.
USE tempdb
GO-- Create sample table

CREATE TABLE Table1 (Col1 VARCHAR(100))INSERT INTO Table1 (Col1)SELECT '0001'UNION ALLSELECT '000100'UNION ALLSELECT '100100'UNION ALLSELECT '000 0001'UNION ALLSELECT '00.001'UNION ALLSELECT '01.001'UNION ALLSELECT '0000'

Step 2:

SELECT CASE PATINDEX('%[^0 ]%', Col1 + ' ‘')WHEN 0 THEN ''ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))END
FROM
Table1


See the result.........



Difference between inner join and outer join

Assuming you're joining on columns with no duplicates, which is by far the most common case:
  • An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
  • An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.
Examples
Suppose you have two Tables, with a single column each, and data as follows:
A    B
-    -1    32    43    54    6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--3 | 34 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;select a.*,b.*  from a,b where a.a = b.b(+);

a |  b  
--+-----1 | null2 | null3 |    34 |    4
Full outer join
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b  
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4null |    6null |    5

How to get primary key column in Oracle?

Step 1:
 
Create sample table with one of the column defined as primary key constraint
 
Step 2:
 
Execute following command :
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;


:):)  Enjoy

Wednesday, 24 July 2013

Working with Oracle's BLOB and Microsoft's C#


We used this title for describing technologies , we are working in.

Here we will see
How to store files in oracle database using c# ?  "


Step 1:
          Use file uploader controller to browse directories.

Step 2:
         Before uploading file or its path , we need to understand BLOB of Oracle

         BLOB  ( Binary Large Objects ) : 
                                                  File content store in binary format.In Oracle , BLOB and    CLOB  (Character Large Object) used.Technically BLOB prefered.For more info regarding BLOB click here .


Step 3 : 
      In C# , use System.IO namespace for file operations.
   
Step 4:
        string path = FileUpload1.PostedFile.FileName.ToString();
        string FileName = FileUpload1.FileName.ToString();
        string FileSize = FileUpload1.PostedFile.ContentLength.ToString();
        string FileContent = FileUpload1.PostedFile.ContentType.ToString();
        string filename = Path.GetFileName(path);

        /* FileStream that used to handle reading file path , file mode setting  etc*/        FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
        int streamLength = (int)fs.Length;
        BinaryReader br = new BinaryReader(fs);  
        byte[] bytes = br.ReadBytes((Int32)fs.Length);   // Binary reader need to store values in byte array        byte[] _data = File.ReadAllBytes(path);     // This is another way of reading values of direct file

      //Declare Oracle connections and Oracle command
       OracleBlob myLob = new OracleBlob(conn);           
       //Transfer data to server       myLob.Write(_data, 0, streamLength);
       //Perform INSERT query       cmd = new OracleCommand(query, conn);
       OracleParameter myParam = cmd.Parameters.Add("fileContent", OracleDbType.Blob);
       myParam.Value = myLob;
      cmd.ExecuteNonQuery();



Step 5 :

    After storing file into database , you need to retrieve it for dowloading.
   
   - Write " Select query  " for retrieving all details.
   - Get all details in binarty format , for that refer below code,
         
            cmd = new OracleCommand();
            cmd.CommandText = query;           
            cmd.Connection = conn;
            OracleDataReader dr = cmd.ExecuteReader();
            byte[] data = null;
            if (dr.Read())
            {
                if (dr["DOCCONTENT"] != null)   // Column name
                {
                    data = (byte[])dr["DOCCONTENT"];       // Column name
                }
            } 


     -  Read data in byte array
                 
            Response.ContentType = _fileContentType.ToString();
            Response.AddHeader("Content-Disposition", "attachment;filename=\""+fileName+"\"");
            Response.BinaryWrite((byte[])_data);  // Byte array receive data            Response.End();    
  

Happy Coding...........Enjoy./....