Disable automatic table creation in Entity Framework

Usually I prefer code first approach which in turn leads to create or update database based on the any change that I do in my entity model classes. This is very straight forward.

But in one of my recent project for and enterprise client they asked not to do any change in database from code as database should be maintained by the DBA team only so I have to disable database creation and upgrade from my code. To do that I simply have to update one line in my code that is Database.SetInitializer. Keep on reading

Resolve Delimiter Issue While Creating Trigger Or Stored Procedure In MySQL phpMyAdmin

I was creating a trigger using Delimiter in MySQL phpMyAdmin and I faced a #1064 error  i.e.

#1064 - You have an error in your SQL syntax; check the  manual that corresponds to your MySQL server version for the right  syntax to use   near '' at line 6

I have searched at many places but I didn’t find any solution, so I decided to post the solution here after resolving that error. The problem was that I was applying Delimiter at the end of every statement after declaring it. Thus, in phpMyAdmin, whenever we define a Trigger or stored procedure, we have to follow these steps:

  1. Define delimiter that your want to be treated as actual delimiter by MySQL during execution. e.g : DELIMITER $$. Purpose for defining this delimiter is to instruct MySQL not to consider semicolons in our code as delimiters.
  2. In you code use semicolons as delimiter and use new defined delimiter when you want to end your trigger (or stored procedure code) i.e. END, e.g. END $$
  3. Again declare delimiter with semicolon so that MySQL can use semicolons as default delimiter again..

I have written both my wrong code and corrected code below , so that everyone can get idea about how the delimiter is actually implemented.

My wrong code was like this :

DELIMITER $$
CREATE TRIGGER WpafDbDefaultTableRenameTrigger
    BEFORE UPDATE
    ON wp_options
      FOR EACH ROW BEGIN
        IF NEW.option_name = "wpaf_db_table_name" AND NEW.option_value != OLD.option_value         THEN
          SET NEW.option_value = CONCAT("wp_",NEW.option_value)$$
        END IF$$
      END$$
DELIMITER ;

The correct code is:

DELIMITER $$
CREATE TRIGGER WpafDbDefaultTableRenameTrigger
    BEFORE UPDATE ON wp_options
    FOR EACH ROW BEGIN
        IF NEW.option_name = "wpaf_db_table_name" AND NEW.option_value != OLD.option_value
        THEN
          SET NEW.option_value = CONCAT("wp_",NEW.option_value);
        END IF;
    END$$

DELIMITER ;

Solution for SUM() in SQL query returning NULL instead zero (Differen between ISNULL and COALESCE)

When I was working on SQL queries I face this problem and then I have to shake my head to remember why this happen and what I did to solve it. So now I am documenting it here in more detail so that anyone who need help can get information here. Even I can check back when I will do same mistake 😉

Normally what happens is we use COUNT in QSL query and it return 0 if there is resolve fr that query. For example here is my table

=&0=&
id  |  name  |  Gender  |  Marks
1   |  Akash              |  M          |  20
2   |  Ashok              |  M          |  25
3   |  Manish            |  M          |  10
4   |  Mohit              |  M          |  30
5   |  Sumit              |  M          |  22
6   |  Vineet              |  M          |  11

Now I am going to fire query
SELECT COUNT(*) FROM Students WHERE Gender=’F’

This will return zero (0) because there is no female student so none of the record have F in Gender column. But if we fire similar query for getting SUM of marks like below
SELECT SUM(*) FROM Students WHERE Gender=’F’

This will not return zero (0), infect it will return a NULL because there is no record where Gender is F so SUM will give is NULL value unlike COUNT.

On many cases this NULL value will create programmatic errors we you have not checked for null before using its value. To avoid this case you can use two methods.

1. Using ISNULL (SELECT ISNULL(SUM(*),0) FROM Students WHERE Gender=’F’)
ISNULL just checks the value provided and if found it null then returns the second parameter passed to it. Which is quite simple so do the task.

2. Using COALESCE (SELECT COALESC(SUM(*),0) FROM Students WHERE Gender=’F’)
COALESCE is similar in terms of syntax (specially for this case) but what is actually does is, it returns first non null value passed in its parameters list.

Actually COALCASE can have any number of parameters like CAOLESCE(param1, param2, param3, 0). So here any non null value found among parameter sequence will be returned.

So both the methods above will return same value and your purpose will be solved. But in current case using ISNULL looks to be more efficient because it has to check only one condition while COALESCE is internally converted in SQL CASE statements to evaluate the result from input parameters list (this was till SQL Server 2008).

After all version after SQL Server 2008 COALESCE and ISNULL have same performance for this case because COALESCE internally started using same method like ISNULL when it has only two arguments

I hope this would help everyone to solve there problem and have an detailed insight for the solutions.

Like operator in SQL queries and performance

Like operator is used after where clause in SQL query. Its main use is to search specified pattern in any column of database table.

Like operator can be used mainly in three types:

=&0=&
=&1=&

This is used if you want to select all the rows from table where content in any column starts with any specified text. Here % is wildcard which means any string which also could be null.
=&2=& =&3=&
This is used if you want to select all the rows from table where content in any column ends with any specified text. Here % is wildcard which means any string which also could be null.
=&2=& =&5=&
=&6=&
This is used if you want to select all the rows where specified text is found any where in the column. In this case wild card is used on both side because random string could be on both side of the text.

Third variant above is most widely used with Like operator. This is because when filters are applied for searching, it meant to search anywhere.

All above variants differ in execution speed as well, which depends on different aspects.

  • Type of Like operator (any of above three)
  • Column data type
  • Number of rows in the table

Order of speed in searching with like operator is 1 > 2 > 3. Where first is fastest and third is slowest.

While using Like operator keep in mind that nvarchar can create major overhead so instead of using nvarchar use text type.

Note: Above article is written using keyword and query behavior in MSSQL Server.

Export Oracle database using Oracle SQL Developer

Now it’s time to export your Oracle database with ease. This is the easiest method to export Oracle database I found so far. So let us start the exercise.

First thing you need for this exercise is Oracle SQL Developer installed on your system. For using Oracle SQL Developer you must have Java installed on your system. If Java is installed on your system and SQL Developer can’t find the file then it will show a dialog box to locate the java.exe file. After installing Java and selecting java.exe from the dialog box your SQL Developer will start and will show some useful tips. Read these tips or skip (as you wish)…

Now follow below steps to export you Oracle database in and SQL file (filename.sql)

  1. Select Database Export option from the Tools menu in Oracle SQL Developer.
  2. Now you will see a five step dialog box for exporting you database. In first step you have to select path and name of the SQL file where you want your database script along with the Oracle schema name (this is the database you are going to export). Also select DDL option before proceeding further like “Include Grants” to add script for assigned permissions on the schema. Click Next.
  3. If you schema password is not saved in SQL Developer then it will prompt you for your schema password. Fill the password and click Ok.
  4. Select object types you want to exports from your database. Select all if you want to export everything from you database and click Next.
  5. If you want to export some specific objects then type object name here and add them through arrow buttons or just leave it as it is to export all objects. Click Next.
  6. If you want to export objects with specific data then type data and click Go and add it to export list through arrow buttons or leave it as it is to export all objects. Click Next.
  7. This step will show a summary of you database export. Review you settings for database export and click Finish.

After clicking on Finish it will process the schema and database script will be stored in the file on selected location in first step. Export script will also be displayed in an SQL Worksheet.

Finally you database export script is generated and be prepared to import this database on another oracle instance (in my next article).

Step by step instructions to install ODAC with Oracle Developer Tools for Visual Studio

In my previous post you can find step by step instructions to install Oracle 11g R2. After installing oracle we have to install ODAC to connect our application to Oracle database.

You can download Oracle Data Access Components from these links : ODAC 32-Bit, ODAC 64-Bit

Extract the download files to a folder and run setup.exe file. The setup will start with a consol screen and then it will open a blue color welcome window.

Now click next and it will show two options there for the installation:

  • Oracle Data Access Components for Oracle Client 11.2.0.3.0
  • Oracle Data Access Components for Oracle Server 11.2.0.3.0

Select first option because we are installing ODAC for client not for server and click next.

In this screen you can choose the location on you hard drive where you want to install the components. By default it will show the path of the current drive from where you are running this setup. After setting your location or leaving default location as it is just click next.

Here you will see list of components to be installed and you can chose the components which you need on your system. My  personal opinion is to select all the components. I’ll select all and click next.

Now it will show a message and a summery window. Click next on both the windows and installation will start

And finally after installation is completed it will show the installation successful message with the path of the hard drive where components are installed.

Yes, we are all done with Oracle Data Access Components installation.

ENJOY THE ORACLE WORLD…

Step by step instructions to install Oracle 11g R2 with screenshots

So we are going to start with steps of installing Oracle 11g Release 2. First you have to download the setup of Oracle 11g R2. You can download it from HERE based on your requirement (32-Bit or 64-Bit).

Note: These instruction are only for installing Oracle 11g R2 in Windows (32 or 64 Bit) systems not for Linux or any other platform.

Select oracle setup for your platform on the link above and accept license agreement before clicking on download link. Before downloading you have to login on Oracle and if you don’t have an account register for free from signup link there. After login you can download both setup zip files (win64_11gR2_database_1of2.zip & win64_11gR2_database_2of2.zip).

Extract both the zip files in he same folder and run setup.exe.

It will ask for email address before installation for support purpose. If you want support from Oracle enter the email address and password of your Oracle account otherwise just click on next. It will then show a message to enter email address for oracle support. (If you have not entered your email address it will show a message just click yes to continue)

In the installation option select “Create and Configure database” and click next

Now select type of the installation you want to run on your system.

  1. Desktop Class
  2. Server Class

In these instructions we will continue with Desktop Class installation so select Desktop Class and click next

Now you will see a screen asking various path for the database engine. It will also show default path selected in the fields and if you want to change it then browse and select your path but my suggestion is leave the default path as they are and enter the Global database name and password of your choice.

Here it will show summary of all the settings before starting installation. Check all the settings and click finish to start the installation process.

After installation starts first of all database components will be installed

After complication of this installation a new window will open which will create and configure your database.

There is nothing to do for you here, it will automatically create and configure database.

When database is configured it will show another message box which contains some information of your database like global database name, SID and server parameter file name etc.

Click OK and that it. You are done with your Oracle 11g R2 installation.

Note down your Enterprise Manager Database Control URL from this window. This control URL runs on your your local system in your browser to manage your Oracle database instance.

To login to this manager control user these details :

  • Username : SYSTEM
  • Password : Password you have set during installation