Structured Query Language

Integration Services

noreply@blogger.com (Murali) — Fri, 21 Jan 2011 20:36:00 PST

SQL Server Integration Services (SSIS) is Microsoft’s new enterprise class data Extract, Transform, and Load (ETL) tool. SSIS is a completely new product built from the ashes of SQL Server 2000’s Data Transformation Services (DTS). SSIS offers a much richer feature set and the ability to create much more powerful and flexible data transformations than its predecessor.

This huge improvement, however, is not without a cost. SSIS is a fairly complex tool and offers a completely different design paradigm than DTS. Database administrators adept at the former tool are very often intimidated and frustrated by the new SSIS. Their biggest mistake is in thinking that Integration Services would just be an upgrade of Data Transformation Services.

Reporting Services

noreply@blogger.com (Murali) — Fri, 21 Jan 2011 20:35:00 PST

Reporting Services is a Web service–based solution for designing, deploying, and managing flexible, dynamic Web-based reports, as well as traditional paper reports. These reports can contain information from virtually any data source.

Because Reporting Services is implemented as a Web service, it must be installed on a server with Internet Information Services (IIS). However, IIS does not have to be installed on a SQL Server. The Reporting Services databases are hosted on SQL Server 2008, but the Web service itself can be configured on a separate server.

Analysis Services

noreply@blogger.com (Murali) — Fri, 21 Jan 2011 20:35:00 PST

Analysis Services delivers Online Analytical Processing (OLAP) and Data Mining functionality for business intelligence applications. As its name suggests, Analysis Services provides a very robust environment for the detailed analysis of data.

It does this through user-created, multidimensional data structures that contain de-normalized and aggregated data from diverse data sources (such as relational databases, spreadsheets, flat files, and even other multidimensional sources).

SQL Server 2008’s Database Engine

noreply@blogger.com (Murali) — Thu, 13 Jan 2011 05:06:00 PST

The Database Engine is the primary component of SQL Server 2008. It is the Online Transaction Processing (OLTP) engine for SQL Server, and has been improved and enhanced tremendously in this version. The Database Engine is a high-performance component responsible for the efficient storage, retrieval, and manipulation of relational and Extensible Markup Language (XML) formatted data.

SQL Server 2008’s Database Engine is highly optimized for transaction processing, but offers exceptional performance in complex data retrieval operations. The Database Engine is also responsible for the controlled access and modification of data through its security subsystem. SQL Server 2008’s Database Engine has many major improvements to support scalability, availability, and advanced (and secure) programming objects.

What is SQL Server 2008/RDBMS?

noreply@blogger.com (Murali) — Thu, 13 Jan 2011 05:05:00 PST

As you most likely know, SQL Server 2008 is primarily thought of as a Relational Database Management System (RDBMS). It is certainly that, but it is also much more.

SQL Server 2008 can be more accurately described as an Enterprise Data Platform. It offers many new features and even more enhanced or improved features from previous editions of the product. In addition to traditional RDBMS duty, SQL Server 2008 also provides rich reporting capabilities, powerful data analysis, and data mining, as well as features that support asynchronous data applications, data-driven event notification, and more.

SQL Server Security

noreply@blogger.com (Murali) — Mon, 02 Aug 2010 01:32:00 PDT

There are several levels of security to SQL Server. At the a base level, in order to be granted get access to a SQL Server, a user is required to have a login account.

There are two modes of security to SQL Server, distinguished by which program provides the authentication. NT Authentication provides that Windows NT (and/or its successors) verify verifies the identity of a user logging in. In this mode, the SQL Server will ‘trust' that Windows NT has verified that the user is who he says he is. The second method, SQL Server authentication, it is SQL: Server that authenticates the identity of the user. SQL Server authentication is typically used for internet Internet connections, since not all internet Internet users have Windows NT, and not all NT domains are trusted.

In addition to identifying users, it is sometimes convenient to define roles at the server level. Since within an organization, there are groups of users with similar access needs and levels, it may be convenient to define roles with specific levels of permissions. Once you have defined a role, you can assign users to that role, and those users inherit all of the privileges assigned to the role.

There are two types of permissions assigned to individual users and roles. Statement permissions confer the right to execute certain types of T-SQL commands. Object permissions confer the right to access database objects directly.

Defining Logins Users, and Roles

Logins can be added either through the Database Properties Window (under Logins), or by invoking system stored procedures with appropriate parameters. The command
sp_addlogin loginname, password, databasename
will add a SQL Server authenticated login to the list;
sp_grantlogin ‘domainname/username'

will add an NT authenticated login to the list.

After the login has been allowed, you need to allow the user access to the database. This is done either through the Database Properties Window (under Users for that particular database) or with the command
sp_grantdbaccess loginname.

You can see the list of users using the command sp_helpuser or sp_helpuser username

To cancel a security account for a given database, use sp_revokedbaccess username

To remove an NT Login use sp_revokelogin ‘username' (note no domain), and to srop stop a SQL Server authenticated login, use sp_droplogin username .

To add a role to a database, use sp_addrole ‘clerical', and to add meAugust 16, 2005er ‘clerical', ‘username'. To delete a rolemember use sp_droprolemember username.

At the SQL Server level the rolenames are fixed, and cannot be changed, but users can be added using sp_addsrvrolemember username, dbcreator . To drop a rolmember, use sp_dropsrvmember username .

Granting permissions

The GRANT command is used to assign permissions to a security account. Statement permissions in this way: GRANT right TO username

where right can be any of the following:
CREATE DATABASE
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
DUMP DATABASE
DUMP TRANSACTION

For object permissions, GRANT is also the command to use, but the grammar differs slightly:
GRANT action ON table/view/procedure name TO username

where action can be one of the following:
INSERT, DELETE, EXECUTE, SELECT or UPDATE

One can also apply these this permission to groups or roles as well as usernames. And it is also possible that a role can have a permission, but a member of that role be denied permission for a task. See the next discussion.

In all of the above commands, you can also explicitly deny permission using DENY in place of GRANT.

Note however that DENY is not the negation of GRANT . DENY explicitly excludes the user from that capability. The REVOKE command is used to remove both the permission and refusal.
Resources

• Article: How To Identify Your SQL Server Service Pack Version and Edition
This is an insightful article that discusses about SQL server pack version and edition.
• Tutorial: Using Access or SQL Server to Write your ASP Code
This tutorial discusses how SQL server can be used to write the ASP code.

Top 7 Free Open Source Database Server

noreply@blogger.com (Murali) — Sun, 25 Jul 2010 21:51:00 PDT

Open Source Server based database management systems have become a common choice for organizations over the stand alone desktop databases. The server based databases or RDBMS such as MySQL, PostgreSQL, Microsoft SQL Server are designed to be used on servers and it can be easily shared by multiple users.

Most evidently, server-based DBMS are used in nonprofit sector for Web-based databases. Some of these organizations use server based DBMS for their interactive Websites, providing content and data to their sites. Server-based DBMS also hold a purpose to underlie large shared databases, such as financial packages and client management databases.

Now for those using Access in their organization may set up a Linux-based database and use an ODBC (Open Data Base Connectivity) connection between the desktop Access databases and the server database. This would imply that Access is used on the front end and make the back-end more robust and secure with server based database. After an extensive research we sorted out the top 7 Open Source Server based database management systems, have a look.

1. MySQL

For a web based use MySQL is the top choice, as it is the most often the only DBMS available in virtual hosting accounts. It runs on several platforms. It allows users to download the server and client software as well as a MySQL ODBC diver and a bunch of other MySQL tools from this page. MySQL is simpler and smaller, and therefore generally faster than other databases servers.

Link

2. PostgreSQL

PostgreSQL is an Open Source server-based database management system that runs well on UNIX based systems such as Linux. It is much more robust, scalable, and standards compliant than MySQL, and it can handle multiple transactions easily. However, the Postgres Plus Standard Server supports the most popular environments, including Linux 32, Linux 64, Windows, and Mac OS X.

Link

3. Firebird

This is an Open Source relational database offering many ANSI SQL-92 features that runs on Linux, Windows, and several Unix platforms. It features high SQL compliance, stored procedure and other advanceddatabase features.

Link

4. GNU SQL Server

SQL Server is a free portable multi-user relational database management system. GNU SQL Server supports the SQL89 dialect and some extensions from SQL92. It offers multiuser access and transaction based on predictive locks. Thedatabase system runs smoothly in UNIX OS and its working language is based on C. Moreover, it also uses RPC, shared memory and message queues.

Link

5. SAP MaxDB

It is one of the popular open source database management system developed and supported by SAP AG. It runs on Microsoft Windows, Linux and Unix, and for the other prominent hardware platforms. For those organizations undergoing SAP implementation MaxDB ensure that yourdatabase migration goes smoothly.

Link

6. ScimoreDB

It is a free distributed RDBMS designed for high load OLTP and Terabyte OLAP warehouses. The server is build to take advantage of parallelization over multipleservers. It helps to achieve a near linear scalability for OLTP applications. It can support up to 512 PC's in a single database cluster.

Link

7. DXstore database system

This is a database management system designed for Linux, FreeBSD, and other operating systems. The database system is distributed in embeddable C or C++ library and shares with them the model of a database as a collection of key/value pairs. It has an entirely new design and implementation. Moreover its functionality has been extended.

Link

How To Process MySQL Timestamp in PHP

noreply@blogger.com (Murali) — Thu, 22 Jul 2010 01:34:00 PDT

Many of us do not realize that MySQL's Timestamp is not the timestamp which PHP date() understands. In fact if you try to use MySQL timestamp directly in php date() function then you will get totally wrong dates. There is a simple way to use MySQL timestamp data in php.

First you need to modify the SQL query to use MySQL's UNIX_TIMESTAMP() function. So if your SQL looks like:
SELECT author, `when` …

then convert it to:
SELECT author, UNIX_TIMESTAMP(`when`) …

Then use PHP's date() function to convert the unix timestamp to appropriate display format like this:
date("F j, Y, g:i a", $row[1])

where $row[1] contains the timestamp column value.

MySQL 5.5 Milestone 2 improves replication and stored procedures

noreply@blogger.com (Murali) — Wed, 14 Jul 2010 00:30:00 PDT

The MySQL developers have released milestone 2 of MySQL 5.5, on the path to a general release in mid-2010. The new milestone release incorporates semi-synchronous replication in MySQL 5.5 based on patches to InnoDB developed by Google. This is designed to ensure that when a change is committed in the master database, at least one slave has confirmed that the data has arrived and been buffered before the commit is complete.

Developers of SQL procedures will find the addition of SQL standard exception handling, SIGNAL and RESIGNAL, should give them the ability to make stored procedures more robust and easier to debug.

Partitioning, which allows data within a table to be distributed over a cluster, has also been enhanced. An improved partitioning syntax lifts previous restrictions of only partitioning based on one INTEGER column, and allows for data to be partitioned based on multiple columns with dates, datetime, strings and integers.

An overview of the changes in MySQL is available online along with the change notes. MySQL 5.5 Milestone 2 is described as "not production ready" but "suitable for testing and feedback". It can be downloaded from the MySQL download site.

News source: h-online.com

MariaDB 5.1.44b Released

noreply@blogger.com (Murali) — Tue, 13 Jul 2010 00:30:00 PDT

MariaDB 5.1.44b Linux and Solaris binaries, Ubuntu/Debian/CentOS packages, and source are now available for download.

This is a bugfix/security release of MariaDB 5.1.44.

From the MariaDB 5.1.44b Release Notes:

MariaDB 5.1.44b fixes a buffer overflow that might potentially allow an authenticated user to run arbitrary code inside the server. It also has a fix for MySQL Bug #53371: “Security hole with bypassing grants using special path in db/table names.” In all other respects, it is the same as MariaDB 5.1.44.

See the MariaDB 5.1.44 Release Notes for a summary of the differences between MariaDB 5.1.42 and MariaDB 5.1.44.
A Windows binary is coming soon.

News source: askmonty.org/blog/mariadb-5-1-44b-released/

Microsoft links free SQL database with ASP .Net

noreply@blogger.com (Murali) — Mon, 12 Jul 2010 00:30:00 PDT

Microsoft is enabling its free SQL CE (SQL Server Compact Edition) database to work within ASP.Net Web applications, thus providing a lightweight database option for ASP.Net Web development, a Microsoft official said this week.

ASP.Net is a Web framework. SQL CE is an embedded database engine enabling easy database storage, said Scott Guthrie, corporate vice president in the Microsoft Developer Division, in a blog post this week.

"We will be releasing the first public beta of SQL CE Version 4 very shortly. Version 4 has been designed and tested to work within ASP.Net Web applications," Guthrie said.

"Applications you build can redistribute SQL CE as part of them. Just copy your Web application onto any server, and it will work," said Guthrie.

"SQL CE 4 provides an easy, lightweight database option that you'll now be able to use with ASP.Net applications. It will enable you to get started on projects quickly -- without having to install a full database on your local development box," Guthrie said.

SQL CE works with .Net-based data APIs and supports a query syntax similar to SQL Server. Developers can use existing data APIs, such as ADO.Net, as well as technologies like Entity Framework with SQL CE, Guthrie said.

SQL CE's database engine runs in-memory within an application; when an application shuts down, the database is automatically unloaded, he said. Version 4 can run in "medium trust" ASP.Net 4 Web-hosting scenarios without a hoster needed to install anything.

Visual Studio 2010 and Visual Web Developer 2010 Express will add SQL CE 4 tooling support for ASP.Net, in a planned update, Guthrie said.

"SQL CE does not require you to run a setup or install a database server in order to use it. You can now simply copy the SQL CE binaries into the \bin directory of your ASP.Net application, and then your Web application can run and use it as a database engine," Guthrie said. "No setup or extra security permissions are required for it to run. You do not need to have an administrator account on the machine. It just works."

News source: infoworld.com

Stored Procedures in MySQL 5.0

noreply@blogger.com (Murali) — Sun, 11 Jul 2010 00:30:00 PDT

MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.

As you get used to writing stored Procedures in MySQL 5.0, you will, as with any other programming language, want to generalize your stored procedures as much as possible. The more flexible your stored procedure is, the more tasks it can be used for -- and the less places you have to go searching for that elusive bug that just keeps giving you the wrong result. The day you end up making a copy of a stored procedure just to change a name or two is the day you need to think how tweaking the original procedure can accomplish what you want without breaking old functionality.

Most stored procedures that you will be writing for MySQL 5.0 will undoubtedly reference static table and column names. However, sometimes it is desirable to be able to pass these details to a stored procedure as parameters.

There is no direct support for dynamic declaration of details such as column or table names in MySQLs stored procedures. However, by utilizing user variables (which have been around for a while) and prepared statements (introduced in MySQL 4.1), we can do some trickery to get the results we want.

What follows is a step-by step guide to demonstrate how we can build a prepared statement in the command-line client, then use the ideas gathered from these examples and build a stored procedure which can manipulate data from any table and column that we specify. The samples are by nature simplistic, but you should quickly be able to gather how you can extend these to much more complex usage.

The examples below are shown as you would type them into the command-line client, but the input prompts have been omitted for easy cut-and-paste.

The base tables for the examples are those found it the world database, which you can download on the MySQL documentation page.

In this article, we explore how to find the average of a given column in some table. For example, we might want to find the average life expectancy in all countries:

SELECT AVG(LifeExpectancy) FROM Country;
+---------------------+
| AVG(LifeExpectancy) |
+---------------------+
| 66.48604            |
+---------------------+

Another example is the average population of the cities defined in the world database:

SELECT AVG(Population) FROM City;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236     |
+-----------------+

The goal is to build a stored procedure that will take a table name and a column name and display the average of the values in the column, just as the two examples above.

The first thing we need to understand is how user variables are used and assigned values. A user variable is distinguished by having a '@' symbol in front of it, and values are assigned using the SET statement:

SET @a := 'abc';
SELECT @a;
+------+
| @a   |
+------+
| abc  |
+------+

We can assign the value of any expression to a user variable, so the following also works:

SET @s := CONCAT('SELECT AVG(' , 'Population' , ') FROM ' , 'City');
SELECT @s;
+----------------------------------+
| @s                               |
+----------------------------------+
| SELECT AVG(Population) FROM City |
+----------------------------------+

...which is exactly one of the statements we'd like our stored procedure to build on-the-fly and execute.
Next in line to consider is prepared statements. Prepared statements are statements (such as queries) which are sent to the server, but the processing of the statement is halted as soon as the statement has been parsed, and the parsed statement is remembered by the server. We then have the option of telling the server to execute that statement one or more times as we wish.

We can prepare a statement from within the command-line client, and we will do much the same later on in our stored procedure:

PREPARE stmt FROM "SELECT AVG(Population) FROM City";
EXECUTE stmt;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236     |
+-----------------+

As you can see, we prepare the statement using a simple string expression. A statement can also be prepared from a user variable:

PREPARE stmt FROM @s;
EXECUTE stmt;
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236     |
+-----------------+

Note that a literal string expression or a user variable are the only ways you can specify the statement to be prepared. You cannot prepare a statement using an expression (which is also the reason why we're taking the seemingly roundabout way of using user variables to complete this task).

We're now a the stage where we can build our stored procedure colavg that will take a table and column name, and return the average of the values found in the column.

The first thing we do is to change the command-line client's command delimiter, to ensure that we can use semicolons inside the stored procedure without the client regarding them as end-of-statement. If you're using another client, you should of course skip the delimiter commands.

The input parameters are both of CHAR(64), which is the maximum size of these identifiers in MySQL. Since we are indeed reading from tables, we specify READS SQL DATA in the procedure declaration.

Inside the procedure, we concatenate the two static parts of the SELECT statement with the parameter values col and tbl into the user variable @s (remember, we can't use expressions in PREPARE statements). Finally, we execute the statement which will return the result to our client.

Finally, we reset the mysql client delimiter to the well-known semicolon.

delimiter //
DROP PROCEDURE IF EXISTS colavg//
CREATE PROCEDURE colavg(IN tbl CHAR(64), IN col CHAR(64))
READS SQL DATA
COMMENT 'Selects the average of column col in table tbl'
BEGIN
SET @s = CONCAT('SELECT AVG(' , col , ') FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END;
//
delimiter ;

We now have a stored procedure which will allow us to select the average of any column in any table:

CALL colavg('Country', 'LifeExpectancy');
+---------------------+
| AVG(LifeExpectancy) |
+---------------------+
| 66.48604            |
+---------------------+

CALL colavg('City', 'Population');
+-----------------+
| AVG(Population) |
+-----------------+
| 350468.2236     |
+-----------------+

One final note on compatibility with other SQL dialects: In the stored procedure, we execute simple SELECT statements which returns the output of the SELECT statement to the client. This behavior is a MySQL extention to the SQL standard, which is not likely to work in other RDBMSs. In most cases, you'll probably want to SELECT into one or more user variables, then pass the result back in an OUT or INOUT parameter.

MySQL 5.0 New Features: Triggers

noreply@blogger.com (Murali) — Sat, 10 Jul 2010 00:30:00 PDT

Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font.

For example:

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page.

For example:

mysql> CREATE PROCEDURE p ()
    -> BEGIN
    ->   /* This procedure does nothing */
    -> END;//
Query OK, 0 rows affected (0.00 sec)

Sometimes I will leave out the "mysql>" and "->" prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)

I tested all the examples with the publicly-available alpha version of MySQL 5.0.3 on Linux, SUSE 9.2. By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your computer. But if not, well, as an experienced MySQL user you know that help and support is always available.

Why Triggers

We are including support for triggers in MySQL 5.0 for these reasons:

Users of earlier MySQL versions kept on telling us they wanted triggers.
We have a commitment to support all ANSI-standard features.
You can use them to check for, and prevent, bad data entering the database.
You can change or negate the INSERT, UPDATE, and DELETE statements.
You can monitor data-change activity throughout a session.

I assume that you have read the first book in the "MySQL New Features" series already. In that book, "MySQL Stored Procedures", you (I hope) saw how MySQL supports stored procedures and functions. That's important knowledge, because you can use the same statements in triggers as you can use in functions. Specifically:

Compound statements (BEGIN / END) are legal.
Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are legal.
Variable declaration (DECLARE) and assignment (SET) are legal.
Condition declarations are legal.
Handler declarations are legal.

But remember that functions are subject to severe limitations: you cannot access tables from within a function. So these statements are illegal inside a function:

ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
  DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
  LOCK OPTIMIZE REPAIR REPLACE REVOKE
  ROLLBACK SAVEPOINT 'SELECT FROM table'
  'SET system variable' 'SET TRANSACTION'
  SHOW 'START TRANSACTION' TRUNCATE UPDATE

Precisely the same limitation applies for triggers.

MySQL 5.0 New Features: Data Dictionary

noreply@blogger.com (Murali) — Fri, 09 Jul 2010 00:30:00 PDT

mysql> CREATE TABLE table1 (column1 INT);
Query OK, 0 rows affected (0.00 sec)

When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page.

For example:

mysql> CREATE VIEW v AS
    -> SELECT column1 AS c /* view col name is c */
    -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

Sometimes I will leave out the mysql> and -> prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)

All of the examples in this book were tested with the publicly-available alpha version of MySQL 5.0.3 on the SuSE Linux operating system (version 9.1). By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your own computer. But if not, well, as an experienced MySQL user you know that help and support is always available.

A Definition and an Example

Standard SQL (SQL:2003) provides a method of accessing database metadata through a schema called INFORMATION_SCHEMA. Until now, MySQL has provided metadata only through a series of SHOW commands. SHOW, however, has two disadvantages:

SHOW commands are non-standard; they are specific to MySQL.
SHOW commands require that you learn an entire set of commands to be able to access the metadata you need.

In contrast:

Use of INFORMATION_SCHEMA is standard SQL; thus alleviating some problems that may occur in porting applications from one DBMS to another. For example, Microsoft SQL Server also supports INFORMATION_SCHEMA, while IBM DB2 supports a similar structure, albeit with different names.
The tables in INFORMATION_SCHEMA can be queried via a SELECT statement, just as regular tables can be queried; thus there is no need to learn a new set of commands to be able to access the metadata you need.

So MySQL AB made the decision to implement support for the INFORMATION_SCHEMA. Effective with MySQL 5.0.2, your MySQL installation will automatically contain a schema (usually called a database in MySQL parlance) called INFORMATION_SCHEMA; it contains a set of views that allow you to look at (but not change) the description of your database objects just as if the descriptions are regular SQL data. Here is an example:

mysql> SELECT table_name, table_type, engine
    -> FROM INFORMATION_SCHEMA.tables
    -> WHERE table_schema = 'tp'
    -> ORDER BY table_type ASC, table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| t2         | BASE TABLE | MyISAM |
| t1         | BASE TABLE | InnoDB |
| v1         | VIEW       | NULL   |
+------------+------------+--------+

Terminology Notes

Metadata refers to data about the data. For example, the name of a table and the data type of a column is metadata. There are two other terms that are often used as synonyms for metadata:

Data dictionary
System catalog

I won't be using those terms in this book.

Using INFORMATION_SCHEMA

MySQL now has a new "database" named INFORMATION_SCHEMA. It is a virtual database only; there will never be a need to create a file by that name, and the MySQL server itself creates and populates the tables therein. It is not possible to USE INFORMATION_SCHEMA; nor is it possible to UPDATE, INSERT, DELETE, or even REFERENCE the INFORMATION_SCHEMA tables. The only action possible is SELECT.

Privileges

Accessing the INFORMATION_SCHEMA tables does not require a special privilege: the SELECT privilege on each table is automatically granted to every user.

Thus, there is no difference between the current (SHOW) privilege requirement and the SELECT requirement. In either case, you have to have some privilege on an object in order to see the metadata information about that object.

MySQL 5.0 New Features

noreply@blogger.com (Murali) — Thu, 08 Jul 2010 00:30:00 PDT

This book is for the long-time MySQL user who wants to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, and information schema". The long answer is the MySQL 5.0 New Features series, and this book is the third in that series.

What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through the sample problems. To do this, I'll go through each little item, building up slowly. By the end, I'll be showing larger views that do something useful, as well as some things that you might have thought were tough.

Conventions and Styles:

Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font.

For example:

mysql> DROP VIEW v CASCADE;
Query OK, 0 rows affected (0.00 sec)

When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page. For example:

mysql> CREATE VIEW v AS
    -> SELECT column1 AS c /* view col name is c */
    -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

Sometimes I will leave out the mysql> and -> prompts, so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)

All of the examples in this book were tested with the publicly-available alpha version of MySQL 5.0.3 on the SUSE Linux operating system (version 9.1). By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your own computer. But if not, well, as an experienced MySQL user you know that help and support is always available.

MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases

noreply@blogger.com (Murali) — Fri, 25 Jun 2010 21:31:00 PDT

Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

mysqlcheck is available in MySQL 3.23.38 and later.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. The exact operations are determined by the underlying storage engine used.

You must execute it on running database. It is recommended that you kill other incoming queries (like from your website) before running this which makes it a lot faster. I simply stop my httpd server before running it. You may want to use a Site Unavailable message instead so that the MySQL server is left alone.

How to start/stop MySQL server on Linux

noreply@blogger.com (Murali) — Thu, 24 Jun 2010 21:27:00 PDT

To Start MySQL Server
/sbin/service mysqld start
To Stop MySQL Server
/sbin/service mysqld stop
To Restart MySQL Server
/sbin/service mysqld restart

And of course there is the brute force way to kill all the processes:
$ for i in `ps -ef |grep mysqld |awk '{print $2}'`; do `kill -9 $i`; done

Thanks to Anthony Eden for the comment (below) to remove the dot in front.

A simpler alternative is:
pkill mysqld

This will kill the mysqld process. However the best way to stop (because it stops properly) is obviously:
/sbin/service mysqld stop

How To Export MS Access Database To MySQL Database

noreply@blogger.com (Murali) — Tue, 22 Jun 2010 21:26:00 PDT

Here are few tips (with undocumented features) to help you export huge MS Access database (production database with real data) to MySQL.

Use mdbtools utilities as described below:

mdb-tables displays the list of tables in MS Access
mdb-scheme export the MS Access schema to MySQL database. The documentation doesn't mention that MySQL is supported. You should use the following command:
mdb-schema [-S] database mysql

You may need to replace column names if they collide with MySQL reserved keywords. few column names I needed to change were: GROUP, PRIMARY & CROSS. I changed the column names to GROUP_, PRIMARY_ & CROSS_ respectively. MySQL error messages are very cryptic (unhelpful).
The schema created contains drop tables. You can use grep -v to exclude them. Additionally you should add drop database followed by create database commands at the very top of the file when you are importing to a new database.

This is essential because you may have to go through multiple passes. Having drop table when a table is not there creates problem as also not having them creates problem when the table has already been loaded. So I remove the drop tables and instead drop and re-create the database for each run by the process described above.

mdb-export exports MS Access tables to CSV format. What is not emphasized is that you can directly create INSERT statements instead of a CSV file. Converting it directly to SQL INSERT statements is very helpful, especially for converting data types. Use it like shown in the sample:

mdb-export -I -R';\n' MSAccessDatabase.mdb Table > Table.sql

Note: Replace MSAccessDatabase.mdb with the full path of your MS Access Database, Table is Table name. You can save the output in any file, I just chose Tablename.sql for convenience.

The -R';\n' ensures that each SQL INSERT is followed by a semicolon and then a new line.

You may need to modify this to substitute column names, if you have previously changed them while importing the schema to prevent conflict with reserved MYSQL keywords.

MySQL SQL Delete Notes

noreply@blogger.com (Murali) — Tue, 22 Jun 2010 21:21:00 PDT

To delete duplicates from a MySQL database table I normally copy the duplicate data first to a temporary table and then use the copied id's to delete from the original table.

Delete using the temporary can take two forms. First is the slow way:



delete from target where id in (select id from temp);

This can be agonizingly slow for a large table. A much faster option is:



delete target from target, temp where target.id = temp.id;

BTW: The copying into temporary table part is simple but not that simple. Here's what I do:



insert into temp(id) select b.id from target a, target b where a.common_field1 = b.common_field1 and a.common_field2 = b.common_field2 ... and a.id < b.id;

Note: I am assuming all the tables have an unique row identifier (primary key).

To make SQL Queries Case Sensitive

noreply@blogger.com (Murali) — Mon, 14 Jun 2010 21:46:00 PDT

To make SQL Queries Case Sensitive using Collation.You can make SQL Queries Case sensitive at the column level or Table level or DataBase level.

Introduction

How to make SQL queries case sensitive.

How to do it?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable 
( 
mycolumn VARCHAR(10) 
) 
GO 
SET NOCOUNT ON 
INSERT mytable VALUES('Case') 
GO 
SELECT mycolumn FROM mytable WHERE
mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE
mycolumn='caSE' 
SELECT mycolumn FROM
mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 
SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 
SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 
-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case'

If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE):

And you can see the description from running the following query:

SELECT DATABASEPROPERTYEX('', 'Collation')

As changing this setting can impact applications and SQL queries, I
would isolate this test first. In SQL Server 2000, you can easily run
an ALTER TABLE statement to change the sort order of a specific column,
forcing it to be case sensitive. First, execute the following query to
determine what you need to change it back to:

EXEC sp_help
'mytable'

The second recordset should contain the following information,

in a default scenario:

 Column_Name Collation 

----------- ---------------------------------------------- 

mycolumn    SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you

need to change it back to after you make the following change,

which will forcecase sensitivity:

ALTER TABLE mytable
ALTER COLUMN mycolumn
VARCHAR(10)
COLLATE
Latin1_General_CS_AS
GO SELECT mycolumn FROM mytable WHERE
mycolumn='Case'
SELECT mycolumn FROM mytable WHERE
mycolumn='caSE'
SELECT mycolumn FROM
mytable WHERE mycolumn='case'
If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable 
ALTER COLUMN mycolumn VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS

Conclusion

This way to make sql querie Case Sensitive

Getting all stored procedures from a database

noreply@blogger.com (Murali) — Mon, 14 Jun 2010 21:12:00 PDT

Here how you can get all the stored procedure from a database. Hey not only the name but the stored procedures definitions also.

Get by using Cursor

declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)

declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc

FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc

Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.

Get from sysobjects

This is better option to get all stroed procedures from a database.

SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'

Difference between TOP clause in SQL 2000 and SQL 2005

noreply@blogger.com (Murali) — Mon, 14 Jun 2010 21:09:00 PDT

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE and DELETE statements.

Introduction

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE, and DELETE statements.

In SQL 2000

syntax: select Top N [Percent]

EX:

select Top 10 * from TableName
or
select Top 10 Percent * from TableName

n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return

Drawbacks:

1) We could not parameterize.
2) It will work only for select statements.

If we want to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this

set rowcount 10
delete from table where payratefieldname=1
set rowcount 0

It will work but the risk is if for some reason rowcount is not set to 0 then the other statements will also restricted to 10.

All these drawbacks are overcome in SQL 2005 by introducing Expression in syntax.

In SQL 2005

syntax: select Top (Expression) [Percent]

EX:

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Ex:

select Top 10 * from TableName
or
select Top 10 Percent * from TableName
or we can set at runtime as

Declare @ int
SET @topNum = 10
select TOP (@topNum) * from

For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional

Select Top (@topNum) * from TableName

Select Top 10 * from TableName

When doing an update or delete, you have to use the parentheses in both cases:

Delete Top (@topNum) from employeesDelete Top (10) from TableName

update Top (@topNum) TableName set fieldname = @fieldvalue

update Top (10) from employees set fieldname = @fieldvalue

SQL Keys

noreply@blogger.com (Murali) — Sun, 14 Feb 2010 05:10:00 PST

In SQL, keys are used to maintain referential integrity among relations. Put simply, this means keys allow tables to reference each other, and each reference will be “correct” every time. Referential integrity also prevents records from being “dangled” or “orphaned” by another record that has been deleted.

Primary Keys and Foreign Keys: The Basics

As an example, let’s consider an office employee roster with two tables.

The first table contains a list of departments and their IDs. Perhaps “billing” has ID #1, “marketing” has ID #2, and “accounting” has ID #3. The column for these unique IDs would be a PRIMARY KEY.

The second table, then, is a list of employees, with several columns: one each for the employee’s first and last names, one for their employee ID, and another for the ID of the employee’s department. The employee ID is another PRIMARY KEY. The column representing the employee’s department ID is called a FOREIGN KEY and links the values in that column to the department IDs in the department table. Let’s say Betty Smith works in billing, John Brown and Samantha Jones work inmarketing, and David Parker works in accounting. The creation of these tables is shown in Figure 1.

Please note PostgreSQL does not use the FOREIGN KEY constraint in the CREATE TABLE command, as shown in Figure 1, presumably because the developers consider mentioning both FOREIGN KEY and REFERENCES to be being redundant. However, many other SQL databases require the explicit use of the FOREIGN KEY keyword.

A simple JOIN operation could be used, then, to determine in which department each employee works, without having to manually interpret the department ID number. This is shown in Figure 2.

This is easy enough to do, but there are some problems that arise from having one table reference another table. The most immediate problem, in this case, would be to determine what would happen if, all of a sudden, the billing department were to be deleted? Would Betty Smith just vanish? Fortunately, the SQL:2003 standard defined five different ways by which this situation can be handled. These are called referential actions.

SQL Perl/PL Overview

noreply@blogger.com (Murali) — Sun, 14 Feb 2010 05:01:00 PST

Procedural languages provide the fundamental method for programming within an SQL database. In other articles in this series, we have covered the SQL-based procedural languages, but those are generally not the only procedural languages found in an SQL database. Procedural extensions for Perl and Java tend to be the most popular additions, but some database systems implement many more additional languages.

The following examples make use of PostgreSQL’s implementation of PL/Perl, but the examples should be relevant and similar to other procedural language extensions in most databases that support such additions.

Installing and Enabling PL/Perl

In an operating system that is bundled with numerous packages, like Red Hat Linux, the PL/Perl language should be available for immediate use. In other operating systems, it may need to be downloaded and installed as a package from that operating system’s package repository.

Debian Linux, for example, does not include PL/Perl in the base PostgreSQL package, and it must be downloaded and installed separately. In Debian and other APT-based Linux distributions, like Ubuntu, the command to do this for PostgreSQL 8.1 is apt-get install postgresql-plperl-8.1. The postgresql-plperl-8.1 package may also be installed via a package manager like dselect or aptitude. As of this writing, November, 2007, PL/Perl is available in Debian’s APT package repositories for the testing (etch) distribution for PostgreSQL versions 8.1 and 8.2.

Once PL/Perl is installed on the computer, it must be enabled for each database that will use PL/Perl functions. This may be accomplished by executing the CREATE LANGUAGE plperl command in the psql SQL shell.

“Hello, World!”

Naturally, the first task one should complete is the creation of the simple “Hello, World!” function. Figure 1 shows the creation and execution of this function.

The only real difference between this function and a procedural SQL “Hello, World!” function is that it is written in Perl, instead of SQL. As long as the developer knows how to program in Perl, writing PL/Perl code is quite simple. Also note the LANGUAGE plperl clause, which lets PostgreSQL know the function is written in PL/Perl. In Figure 1, we simply returned the “Hello, World!” string, and it was displayed in the result set of the SELECT hello_world() query.

Concatenation

Another simple example, the concatenate() function, is shown in Figure 2.

Figure 2 shows the creation of concatenate(), its use as a function, and its use as both a function and as a parameter passed to itself. In the function, the two TEXT parameters are shifted out of the parameter list, and Perl’s . operator is used to concatenate them. As Perl developers are wont to say, “there is more than one way to do it,” and that is certainly the case with a function like this. Another popular way of writing this function is shown in Figure 3.