SQL

FETCH_STATUS function in sql server

noreply@blogger.com (Anonymous) — Mon, 04 Mar 2013 11:29:00 +0000

@@FETCH_STATUS function determines whether FETCH keyword has successfully retrieved a row from the current cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.

This function can have one of the three values:

Syntax of @@FETCH_STATUS Function :

@@FETCH_STATUS

Return type of @@FETCH_STATUS function is integer.

Examples of @@FETCH_STATUS Function :

Example 1 : Use of @@FETCH_STATUS function

DECLARE Customer_Cursor CURSOR FOR
SELECT ContactName FROM Customers
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Customer_Cursor
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Above cursor displays each customer name one by one.

CURSOR_ROWS function in Sql Server

noreply@blogger.com (Anonymous) — Mon, 04 Mar 2013 11:02:00 +0000

@@CURSOR_ROWS function returns number of rows currently in the last opened cursor. The number returned by @@CURSOR_ROWS is negative if the last cursor was opened asynchronously. Keyset-driver or static cursors are opened asynchronously if the value for sp_configurecursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.

Below is table that describes cursor status based on return type.

Syntax of @@CURSOR_ROWS Function :

@@CURSOR_ROWS

Return type of @@CURSOR_ROWS function is integer.

Examples of @@CURSOR_ROWS Function :

Example 1 : Use of @@CURSOR_ROWS function in select clause

SELECT @@CURSOR_ROWS

Output
0

Above example returns 0 means currently cursor is not opened.
Now we will execute @@CURSOR_ROWS after cursor is opened.

Below is the code to create, open and execute cursor.

DECLARE Product_Cursor CURSOR FOR
SELECT ProductName FROM Products
OPEN Product_Cursor
FETCH NEXT FROM Product_Cursor

Output ProductName
Tea

Above example returns output of executed cursor.

Below is the code to execute @@CURSOR_ROWS after cursor is executed. After that cursor is closed and deallocated.

SELECT @@CURSOR_ROWS
CLOSE Product_Cursor
DEALLOCATE Product_Cursor

Output
-1 Now

@@CURSOR_ROWS function returns -1 i.e. cursor is dynamic.

REPLICATE Function

noreply@blogger.com (Anonymous) — Thu, 09 Aug 2012 03:36:00 +0000

REPLICATE function of sql server with examples.

REPLICATE is used to repeat a string with specified number of times.

Syntax of REPLICATE Function :

REPLICATE ( character_expression ,integer_expression )

character_expression is a string to repeat.

integer_expression is number of times the character_expression to be repeated.

Return type of REPLICATE function is varchar data type.

Examples of REPLICATE Function :

Example 1 : Use of REPLICATE function in select clause

SELECT REPLICATE('Syntax-Example', 3)

Output

Syntax-ExampleSyntax-ExampleSyntax-Example

Above example returns repeated string 3 times by specified string.

Example 2 : Use of REPLICATE function to display field value of table in a select clause

SELECT ContactName, REPLICATE(ContactName,2) 'Repeated Name'

FROM Customers

Output

ContactName Repeated Name

Clickson Andrew Clickson Andrew Clickson Andrew

Adie Addison Adie AddisonAdie Addison

Christopher Cole Christopher ColeChristopher Cole

Above example repeats customers name 2 times from customers table.

NULLIF Function

noreply@blogger.com (Anonymous) — Thu, 09 Aug 2012 03:27:00 +0000

NULLIF function in sql server with examples.

NULLIF function returns null value if the two specified expressions are equivalent.

NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.

Syntax of NULLIF Function :

NULLIF (expression1, expression2)

expression1 and expression2 are any valid sql server expression. It can be constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

Return type of NULLIF function is same as expression1.

Examples of NULLIF Function :

Example 1 : Use of NULLIF function in select clause

SELECT ProductName, NULLIF(UnitsInStock,ReorderLevel) AS Stock

FROM Products

Output

ProductName Stock

Rogide soild NULL

Speagesild 95

Zanse koeken 36

Cho colade 15

Scottish NULL

Above example displays Null where both columns UnitsInStock and ReorderLevel values are same otherwise it displays value of UnitsInStock column.

CASE Expression

noreply@blogger.com (Anonymous) — Tue, 07 Aug 2012 09:21:00 +0000

CASE expression in sql server with examples.

CASE expression enables many forms of conditional processing to be placed into a SQL statement. By using CASE, more logic can be placed into SQL statements instead of being expressed in a host language or 4GL program.

CASE is a deterministic i.e They return same value every time they are called with a specific set of values.

CASE expression has 2 formates as listed below.

Simple CASE function compares an expression to a set of simple expressions.
Searched CASE function evaluates a set of boolean expression .

Syntax of Simple CASE Expression :

CASE input_expression

WHEN when_expression THEN result_expression

[ ...n ]

[

ELSE else_result_expression

]

END

input_expression is any valid sql server expression evaluated when using the simple .

WHEN Boolean_expression is a Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

n is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression is a expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid SQL Server expression.

ELSE else_result_expression is a expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL.

else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

Examples of CASE Expression :

Example 1 : Use of simple CASE expression function in select clause

SELECT

CASE SUBSTRING('Sintax-Example',1,2)

WHEN 'Si' THEN 'Please correct your spelling. Don''t use si instead of sy. It is Syntax- Example.'

WHEN 'Se' THEN 'Please correct your spelling. Don''t use se instead of sy. It is Syntax- Example.'

ELSE 'Syntax-Example'

END

Output

Please correct your spelling. Don't use si instead of sy. It is Syntax-Example.

Above example compares first 2 characters specified string and displays output based on comparision expression evaluates to true.

Example 2 : Use of searched CASE expression function in select clause

SELECT

CASE

WHEN SUBSTRING('Sintax-Example',1,2) = 'Si' THEN 'Please correct your spelling. Don''t use si instead of sy. It is Syntax-Example.'

WHEN SUBSTRING('Sintax-Example',1,2) = 'Se' THEN 'Please correct your spelling. Don''t use se instead of sy. It is Syntax-Example.'

ELSE 'Syntax-Example'

END

Output

Please correct your spelling. Don't use si instead of sy. It is Syntax-Example.

Above example compares first 2 characters specified string and displays output based on comparision expression evaluates to true.

FILE_ID Function

noreply@blogger.com (Anonymous) — Tue, 07 Aug 2012 09:05:00 +0000

FILE_ID function in sql server with examples.

FILE_ID function is used to get file identification number for logical file name in the current database.

Syntax of FILE_ID Function :

FILE_ID ( 'file_name' )

file_name is a name of the file for which to return the file ID. It corresponds to the name column in sysfiles. It is of type nchar(128).

Return type of FILE_ID function is smallint.

Examples of FILE_ID Function :

Example 1 : Use of FILE_ID function in select clause

SELECT FILE_ID('Southwind')

Output

Above example returns id for Soundwind file.

SQL Wildcards

noreply@blogger.com (Anonymous) — Wed, 13 Jun 2012 16:43:00 +0000

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be use Wildcard are :

%,_,[^charlist] or [!charlist]

% : A substitute for zero or more characters

_ : A substitute for exactly one character

[^charlist] or [!charlist] : Any single character not in charlist

SQL Wildcard Examples

We have the following "Persons" table:

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE 'Chi%'

The result-set will look like this:

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE '%ago%'

The result-set will look like this:

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName LIKE '_om'

The result-set will look like this:

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE 'K_end_en'

The result-set will look like this:

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "a" or "k" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[ak]%'

The result-set will look like this:

Next, we want to select the persons with a last name that do not start with "a" or "k" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[!ak]%'

The result-set will look like this:

SQL TOP Clause

noreply@blogger.com (Anonymous) — Fri, 02 Mar 2012 05:37:00 +0000

The TOP clause is used to specify the number of records to return.The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

SQL Server Syntax

SELECT TOP number|percent column_name(s)
FROM table_name

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

SELECT column_name(s)
FROM table_name
LIMIT number

Example

SELECT *
FROM Persons
LIMIT 6

Oracle Syntax

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number

Example

SELECT *
FROM Persons
WHERE ROWNUM <=6

SQL TOP Example

The "Persons" table:

Now we want to select only the two first records in the table above.

We use the following SELECT statement:

SELECT TOP 2 * FROM Persons

SQL TOP PERCENT Example

The "Persons" table:

Now we want to select only 50% of the records in the table above.

We use the following SELECT statement:

SELECT TOP 50 PERCENT * FROM Persons

The result-set will look like this:

Note: Not all database systems support the TOP clause.

Integration Services

noreply@blogger.com (Anonymous) — Sat, 22 Jan 2011 04:36:00 +0000

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 (Anonymous) — Sat, 22 Jan 2011 04:35:00 +0000

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 (Anonymous) — Sat, 22 Jan 2011 04:35:00 +0000

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 (Anonymous) — Thu, 13 Jan 2011 13:06:00 +0000

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 (Anonymous) — Thu, 13 Jan 2011 13:05:00 +0000

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 (Anonymous) — Mon, 02 Aug 2010 08:32:00 +0000

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 (Anonymous) — Mon, 26 Jul 2010 04:51:00 +0000

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 (Anonymous) — Thu, 22 Jul 2010 08:34:00 +0000

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 (Anonymous) — Wed, 14 Jul 2010 07:30:00 +0000

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 (Anonymous) — Tue, 13 Jul 2010 07:30:00 +0000

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 (Anonymous) — Mon, 12 Jul 2010 07:30:00 +0000

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 (Anonymous) — Sun, 11 Jul 2010 07:30:00 +0000

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 (Anonymous) — Sat, 10 Jul 2010 07:30:00 +0000

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 (Anonymous) — Fri, 09 Jul 2010 07:30:00 +0000

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 (Anonymous) — Thu, 08 Jul 2010 07:30:00 +0000

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 (Anonymous) — Sat, 26 Jun 2010 04:31:00 +0000

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.

SQL

FETCH_STATUS function in sql server

CURSOR_ROWS function in Sql Server

REPLICATE Function

Syntax of REPLICATE Function :

Examples of REPLICATE Function :

Example 1 : Use of REPLICATE function in select clause

Example 2 : Use of REPLICATE function to display field value of table in a select clause

NULLIF Function

Syntax of NULLIF Function :

Examples of NULLIF Function :

Example 1 : Use of NULLIF function in select clause

CASE Expression

Examples of CASE Expression :

Example 1 : Use of simple CASE expression function in select clause

Example 2 : Use of searched CASE expression function in select clause

FILE_ID Function

Syntax of FILE_ID Function :

Examples of FILE_ID Function :

Example 1 : Use of FILE_ID function in select clause

SQL Wildcards

SQL Wildcard Examples

Using the % Wildcard

SQL TOP Clause

SQL Server Syntax

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

Example

Oracle Syntax

Example

SQL TOP Example

SQL TOP PERCENT Example

Integration Services

Reporting Services

Analysis Services

SQL Server 2008’s Database Engine

What is SQL Server 2008/RDBMS?

SQL Server Security

Top 7 Free Open Source Database Server

Top 10 PostgreSQL Performance Optimization Tips

How To Process MySQL Timestamp in PHP

MySQL 5.5 Milestone 2 improves replication and stored procedures

MariaDB 5.1.44b Released

Microsoft links free SQL database with ASP .Net

Stored Procedures in MySQL 5.0

MySQL 5.0 New Features: Triggers

Why Triggers

MySQL 5.0 New Features: Data Dictionary

A Definition and an Example

Terminology Notes

Using INFORMATION_SCHEMA

Privileges

MySQL 5.0 New Features

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