Database | The Final Guide

phpMyAdmin 3.4.3.1 (Freeware)

Wednesday, July 6th, 2011

phpMyAdmin is a free software tool written in PHP for administering of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations on MySQL. Operations like managing databases, tables, fields, relations, indexes, users, permissions, etc are supported by user interface. Don’t think there is no chance to execute SQL statements. Its still there to write your own SQL statements and execute.

Features

Intuitive web interface (you can open phpMyAdmin on your PC by typing http://localhost/phpmyadmin/)
Supports MySQL features like browse and drop databases, tables, views, fields and indexes, create, copy, drop, rename and alter databases, tables, fields and indexes and many other features.
Supports importing data from CSV and SQL
Supports exporting data to various formats: CSV, SQL, XML, PDF, ISO/IEC 26300 - Open Document Text and Spreadsheet, Word, Excel and others
Administering multiple servers
Creating PDF graphics of your database layout
Creating complex queries using Query-by-example (QBE)
Searching globally in a database or a subset of it
Transforming stored data into any format using a set of predefined functions, like displaying BLOB data as image or download-link
and many more features available

You can download phpMyAdmin from phpMyAdmin website.

For more information, visit: http://www.phpmyadmin.net/

06.07.2011 Database, MySql No Comments

Fetch the incremented Identity value from MS-SQL Server

Wednesday, June 10th, 2009

The Microsoft SQL Server Database provides an Identity value which is generated automatically when a record is added in such tables which contains IDENTITY. We can fetch the auto incremented value from MS-SQL Server database to our applications using built in function @@IDENTITY( ) (used in MS-Access) or SCOPE_IDENTITY( ) which is the recommended method for obtaining identity value on SQL Server. Scope_Identity( ) retrieves the last identity value created in the current scope which is a single module that can be a stored procedure, batch, function or trigger. We can implement the coding to retrieve the Scope_Identity in many ways.
1. Using batch command with two sql queries seperated with a semicolor, with ExecuteScalar( ) against the batch command to return single value.

[C#]
string strSQL = "INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES(@PRODUCT_NAME); SELECT SCOPE_IDENTITY()";
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open();
      using(SqlCommand Cmd = new SqlCommand())
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.Text;
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           intID = (int)Cmd.ExecuteScalar( );
      }
      dbCon.Close( );
}

2. We can use output parameter from a stored procedure and ExecuteNonQuery( ).
Stored Procedure declaration

CREATE PROCEDURE dbo.SP_PRODUCT_ADD
(@PRODUCT_NAME VARCHAR(250), @PRODUCT_ID INT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES (@PRODUCT_NAME);
    SET @PRODUCT_ID = SCOPE_IDENTITY( );
END

Our Application Coding…

[C#]
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open( );
      using(SqlCommand Cmd = new SqlCommand( ))
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.StoredProcedure;
           Cmd.CommandText = "SP_PRODUCT_ADD";
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           SqlParameter parm1 = new SqlParameter( );
           param1.ParameterName = "@PRODUCT_ID";
           param1.SqlDbType = SqlDbType.BigInt;
           param1.Direction = ParameterDirection.Output;
           Cmd.Parameters.Add(param1);
           Cmd.ExecuteNonQuery( );
           intID = Convert.ToInt32(param1.Value);
      }
      dbCon.Close( );
}

10.06.2009 Database, SQL Server No Comments

SQL Server 2005 Common Table Expressions

Sunday, May 31st, 2009

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

USE AdventureWorks;
GO

;WITH CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT * FROM CTE_Orders;
GO

We can use multiple CTE definitions in one statement as follows.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

31.05.2009 Database, SQL Server No Comments

Show all tables having identity columns

Monday, May 18th, 2009

The following query will be very much helpful when your project manager asks you to write a query to show all the tables in a database (SQL Server 2000/2005/2008) which are having identity columns. Its very simple and straight forward.

Query for SQL Server 2005 / 2008

USE Northwind
GO

SELECT object_name(o.object_id) AS TableName, c.name as IdentityColumn
FROM sys.columns c JOIN sys.objects  o
ON c.object_id = o.object_id
WHERE c.is_identity = 1
AND o.type IN ('U')

Results

Query for SQL Server 2000

USE Northwind
GO

SELECT object_name(o.id) AS TableName, c.name as IdentityColumn
FROM syscolumns c JOIN sysobjects  o
ON c.id = o.id
WHERE c.colstat = 1
AND o.type IN ('U')

18.05.2009 Database, SQL Server 1 Comment

Change sa password for SQL Server Express

Tuesday, May 12th, 2009

While installing Visual Studio 2005, it shows like SQL Server 2005 Express edition also installing but you can’t access it. Actually SQL Server 2005 Express edition will be installed on the pc as an instance but it wont appear in the programs list to access. Inorder to access it, we must need a Management Studio express to access and use SQL Server 2005 Express which is enough for medium scale applications. You can download it from here.

Once you install Management Studio express, you can connect to the SQL Server 2005 Express database using windows authentication mode only which is the default connectivity set by the Visual Studio 2005 installer. Inorder to use SQL Authentication mode, you need to configure the SQL Server 2005 Express to work for Mixed Authentication mode (Windows Authentication & SQL Authentication). You can do it by connecting to the SQL Server 2005 Express instance in Windows Authentication mode and configuring as follows.

Right click on the server instance in the object explorer (left side) and click properties
You can see Properties screen for the SQL Server 2005 Express isntance, select Security Option from the “Select a Page” (left side) which looks as follows which shows Windows Authentication Mode as the default
You change the Server Authentication mode to Mixed Authentication (SQL Server and Windows Authentication mode) and click OK to close the properties window.
Now drill down to Security from the Object Explorer and drill down Logins
You can see the list of users registered in the SQL Server 2005 Express instance to access from which you right click on the user sa and click Properties which looks as follows
By Default password for the user sa will be kept null when SQL Server 2005 Express is installed by Visual Studio 2005. Even though the password is null it’ll be displayed like ********* in the above screen. Change the password to your desired in both Password & Confirm Password text boxes and click OK.
You can achieve the above password changing by executing the stored procedure as shown below.

use master
sp_password @old = null, @new = 'sapassword',  @loginame ='sa'
go

Now you disconnect, close the SQL Server Management Studio Express and you open and connect to SQL Server 2005 Express using SQL Server Authentication mode with your own password for the sa account.

You can use this change password method for SQL Server 2000, SQL Server 2005 provided sufficient privileges are assigned for the windows account you use.

12.05.2009 Database, SQL Server No Comments

Configure SQL Server for ASP.Net session state

Monday, May 11th, 2009

Session state: A session is defined as the period of time that a unique user interacts with a web application. Session state is a collection of objects, tied to a session are stored on a server.

Using SQL Server for ASP.Net session state: Once you start running multiple web servers for the same web site, the default asp.net session state, InProc, is no longer useful, as you cannot guarantee that each page request goes to the same server. It becomes necessary to have a central state store that every web server accesses.
SQL Server offers you centralized storage of a session state in a Web farm. It also offers the transactional capabilities that provide reliability to most relational database systems. You can use SQL Server to save a session. This process is not as efficient as InProc and StateServer mode, because you must store the information in a different process or on a different server. However, this option may be more efficient than using the aspnet_state service, depending on the actual workload and the database configuration. Once you start saving session state to a SQL database it will also persist through web server restarts and reboots.

Installing session state: Open ASP.Net 2.0 Command prompt located at Start–>Programs–>Microsoft .Net Framework SDK v2.0–>SDK Command Prompt and type the following command

For Trusted Connection to SQL Server which will be authenticated with windows credentials

“aspnet_regsql -S MyPcServer1 -E -ssadd -sstype p”

For Untrusted connection to SQL Server with user id and password

“aspnet_regsql -S MyPcServer1 -U sa -P sa -ssadd -sstype p”

Configuring ASP.Net Web application: To switch ASP.Net to use SQL you must update the <sessionState> element of your application’s Web.config file as follows;

Set the mode attribute of the <sessionState> element to SQLServer.
Set the sqlConnectionString attribute to specify the connection string to your SQL Server

For example

<sessionState

mode=”SQLServer”

sqlConnectionString=”data source=server;user id=uid;password=pwd”

cookieless=”false” timeout=”20″ />

11.05.2009 ASP.Net, Database, Programming, SQL Server, Web No Comments

Install and Use PHP & MySql on Windows Machine

Tuesday, May 5th, 2009

AppServ package is a full-featured package which can be installed in 1 minute.
AppServ package contains
- Apache webserver
- PHP
- MySQL
- phpMyAdmin

You can download AppServ Pagackage from http://www.appservnetwork.com/

AppServ Package installation step by step

Double click appserv-win32-x.x.x.exe to install AppServ on your computer which shows welcome screen and click Next to go to License Agreement Screen.
License Agreement: AppServ distribution under GNU?GPL License. You must read license agreement before install. If you agree for this license click Next to go to next step. If you do not agree click Cancel to exit installation.
Click Next and choose Install Location (Default Location: C:AppServ)
Click Next and choose Components : AppServ default package components are checked.
You can choose the components by check (install) or uncheck (not to install).
- Apache HTTP Server is a Web Server.
- MySQL Database is a Database Server.
- PHP Hypertext Preprocessor is a PHP Programming processor.
- phpMyAdmin is a MySQL Database control via WWW.
If you complete choosing it click Next to go next step.
Apache Configuration : This is the screen where you can configure Apache Server on your pc.
Server Name: Specify Server Name e.g. localhost
Admin Email Specify Admin Email e.g. admin@myserver.com
HTTP Port Specify HTTP port for Apache Web Server (Default Port is 80 but if iis is already installed then select different port ex: 8080)
Click Next to MySQL Configuration :
Default User: root for MySQL Database.
Password: root (or your own password)
Character Sets Specify for data storage language and collations.
Old Password If you have problem when you coding PHP code with Old MySQL API. And found error Client does not support authentication protocol requested by server; consider upgrading MySQL client. You must check this option to avoid error. (Enable InnoDB If you use InnoDB must check this )option.
Click install and Finish installation and it’ll run Apache webserver monitor and Apache service automatically even after you restart your pc.
Directory Structure of Apache, PHP and MySql are shown as follows.
To test PHP, open notepad and type <? phpinfo(); ?> and save as test.php in location C:\AppServwww
Open internet explorer or other web browser and type http://localhost:8080/test.php and click go which will display the default PHP configuration page.
To access the default configuration pages of Apache webserver installed on your local pc, type http://localhost:8080 in the address bar of your browser and Apache default configuration page will be displayed.
You can access PhpMyAdmin interface to configure the database in MySql for your PHP project or any other front end project through the link phpMyAdmin Database Manager Version 2.10.2