Parameterized query

Hey,
I learned parametrized query in ASP.net C#.

The following link was very helpful.
http://geekswithblogs.net/dotNETvinz/archive/2009/04/30/creating-a-simple-registration-form-in-asp.net.aspx
Adding part of code for your quick reference.
The code is basically a registration form:

private void ExecuteInsert(string name, string username, string password, string gender, string age, string address)

{

SqlConnection conn = new SqlConnection(GetConnectionString());

string sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age) VALUES "

+ " (@Name,@UserName,@Password,@Gender,@Age)";

try

{

conn.Open();

SqlCommand cmd = new SqlCommand(sql, conn);

SqlParameter[] param = new SqlParameter[5];

param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);

param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);

param[2] = new SqlParameter("@Password", SqlDbType.VarChar, 50);

param[3] = new SqlParameter("@Gender", SqlDbType.Char, 10);

param[4] = new SqlParameter("@Age", SqlDbType.Int, 100);

param[0].Value = name;

param[1].Value = username;

param[2].Value = password;

param[3].Value = gender;

param[4].Value = age;

for (int i = 0; i < param.Length; i++)

{

cmd.Parameters.Add(param[i]);

}

cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Insert Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

conn.Close();

}

}


The code works perfect if we are using MSSQL DB Server.

But in case you are using Mysql DB server then you need to modify the code little bit as follows:

private void ExecuteInsert(string name, string username, string password, string gender, string age)
{
MySqlConnection conn = new MySqlConnection(GetConnectionString());

string sql = "INSERT INTO test_user (first_nm, last_nm, pword, age ,gender) VALUES "

+ "(?Name,?UserName,?Password,?Age,?Gender)";

try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(sql, conn);

MySqlParameter[] param = new MySqlParameter[5];

param[0] = new MySqlParameter("?Name", MySqlDbType.VarChar, 50);
param[1] = new MySqlParameter("?UserName", MySqlDbType.VarChar, 50);
param[2] = new MySqlParameter("?Password", MySqlDbType.VarChar, 50);
param[3] = new MySqlParameter("?Age", MySqlDbType.VarChar, 12);
param[4] = new MySqlParameter("?Gender", MySqlDbType.VarChar, 6);

param[0].Value = name;
param[1].Value = username;
param[2].Value = password;
param[3].Value = age;
param[4].Value = gender;

for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}

//Response.Write(CommandType.Text+ "
");
cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

}
catch (Exception ex)
{

string msg = "Insert Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally
{

conn.Close();

}

}


This example shows you how to run a parameterized query against MySQL.
The trick is to remember the ? instad of @ – which is the case of SQL Server.

Sorting result explicitely in MySQL

Hello,
I you want to sort the result explicity & specify the order of which ‘order by’ comes back in,
like if you had a priority field that had the values “Low” “High” or “Medium” .. do this:

select * from tablename order by priority=’High’ DESC, priority=’Medium’ DESC, priority=’Low” DESC;

Another Smart way is :

SELECT * FROM tickets ORDER BY FIELD(priority, ‘High’, ‘Normal’, ‘Low’, ‘The Abyss’);

I observed that its not necessary to mention all the possible values.

e.g. The query we built was quit complex
SELECT b.mf_cocode,b.mf_schcode,b.mf_schname,b.grpcode,b.moptionname,b.mplanname
FROM
(SELECT c.mf_cocode,c.mf_schcode,c.mf_schname,c.grpcode,c.moptionname,c.mplanname
FROM mutual_category c
ORDER BY c.moptionname=’growth’ DESC,c.mplanname=’regular’ DESC) b
GROUP BY b.grpcode

MySQL Query Optimization – Indexing

Indexing is the most important tool you have for speeding up queries. Other techniques are available to you, too, but generally the one thing that makes the most difference is the proper use of indexes.

MySQL uses indexes in several ways:

* As just described, indexes are used to speed up searches for rows matching terms of a WHERE clause or rows that match rows in other tables when performing joins.
* For queries that use the MIN() or MAX() functions, the smallest or largest value in an indexed column can be found quickly without examining every row.
* MySQL can often use indexes to perform sorting and grouping operations quickly for ORDER BY and GROUP BY clauses.
* Sometimes MySQL can use an index to reading all the information required for a query. Suppose that you’re selecting values from an indexed numeric column in a MyISAM table, and you’re not selecting other columns from the table. In this case, when MySQL reads an index value from the index file, it obtains the same value that it would get by reading the data file. There’s no reason to read values twice, so the data file need not even be consulted.

Ref:- http://www.informit.com/articles/article.aspx?p=377652

Mysql Query Cache – Current Cache Size

HI Buddies,
I leaned something new about Mysql Query caching.
Indeed it would be good to be able to see query cache contents.
Unfortunately it is not possible in current MySQL version. So you’ve just got to guess.

SESSION shows the values for the current connection.
mysql> SHOW GLOBAL STATUS;
+———————————–+————+
| Variable_name | Value |
+———————————–+————+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
…
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 2 |
…
| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+———————————–+————+

#Qcache_free_memory –
The amount of free memory for the query cache.
#Qcache_hits –
The number of query cache hits.
#Qcache_inserts –
The number of queries added to the query cache.
#Qcache_queries_in_cache –
The number of queries registered in the query cache.

For Details Ref :- http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Qcache_free_memory

Byte to MB relation (16777216 bytes
16384.000 kilobytes (abbreviated as KB or Kb*)
16.0000 megabytes (abbreviated as M or MB))

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache.
When you set query_cache_size to a nonzero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures.
mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| query_cache_size | 41984 |
+——————+——-+

For the query cache to actually be able to hold any query results, its size must be set larger:

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+——–+
| Variable_name | Value |
+——————+——–+
| query_cache_size | 999424 |
+——————+——–+
1 row in set (0.00 sec)

The query_cache_size value is aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you assign.

To control the maximum size of individual query results that can be cached, set the query_cache_limit system variable. The default value is 1MB.

Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this:

mysql> SET SESSION query_cache_type = OFF;

If you set query_cache_type at server startup (rather than at runtime with a SET statement), only the numeric values are allowed.

query_cache_type = 2; means You can cache query on defmand using
SELECT SQL_CACHE id, name FROM customer;
SQL_CACHE

The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
vice versa,
SELECT SQL_NO_CACHE id, name FROM customer;

The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.
For details Ref : http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

To check whether the query cache is present in your MySQL server, use the following statement:

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

mysql> SHOW STATUS LIKE ‘Qcache%’;
+————————-+——–+
| Variable_name | Value |
+————————-+——–+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+————————-+——–+

The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache
The optimal value of Qcache_lowmem_prunes is 0.
For details Ref : http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html

Join in SQLite

Hi frds,
I need to perform JOIN in SQLite , that time i come to know ,
RIGHT OUTER JOIN and FULL OUTER JOIN is not implemented.

SQL Features That SQLite Does Not Implement:
FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers. The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.
Complete trigger support There is some support for triggers but it is not complete. Missing subfeatures include FOR EACH STATEMENT triggers (currently all triggers must be FOR EACH ROW), INSTEAD OF triggers on tables (currently INSTEAD OF triggers are only allowed on views), and recursive triggers – triggers that trigger themselves.
Complete ALTER TABLE support Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
RIGHT and FULL OUTER JOIN LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
Writing to VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
GRANT and REVOKE Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

Ref: http://www.sqlite.org/omitted.html
http://www.geocities.com/colinpriley/sql/sqlitepg06.htm

TEXT TYPES – Max Size in MySQL

Choosing the column types specifies what information can or can’t be stored in a table cell. Using the most correct option for each column is important as it may affect the database’s overall performance.

TEXT TYPES
CHAR( ) A fixed section from 0 to 255 characters long.
VARCHAR( ) A variable section from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
BLOB A string with a maximum length of 65535 characters.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.

Updating foreign keys in a multiuser environment.

Updating foreign keys in a multiuser environment. Using LAST_INSERT_ID().

The LAST_INSERT_ID() is unique to the login session. This allows updating
of foreign keys.

CREATE TABLE keytest (
pkey int(11) NOT NULL auto_increment,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);

CREATE TABLE foreignkeytest (
pkey int(11) NOT NULL auto_increment,
pkeykeytest int(11) NOT NULL,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);

mysql> insert into keytest(ptext,ptype) values (‘one’,1);

mysql> select LAST_INSERT_ID() from keytest;
+——————+
| last_insert_id() |
+——————+
| 1 |
+——————+
1 row in set (0.03 sec)

mysql> insert into foreignkeytest (ptext,pkeykeytest) values (‘one’,LAST_INSERT_ID());

Note: If your session didn’t update any records, LAST_INSERT_ID() will be zero. Never
assume LAST_INSERT_ID()+1 will be the next record. If another session inserts a record,
this value may be taken. You are assured that this value will be unique to the “session”.

**SPECIAL NOTE: MySQL 4.1.2. supports UUID.

mysql> select uuid();
+————————————–+
| uuid() |
+————————————–+
| 167c1afe-0a0f-1027-891e-0004e222b485 |
+————————————–+
1 row in set (0.00 sec)

“A UUID is designed as a number that is globally unique in space and time.
Two calls to UUID() are expected to generate two different values, even if
these calls are performed on two separate computers that are not
connected to each other.”

So in the future, UUID() could be used as a better primary key. The advantage
being tables from a one server could be up-loaded to a second server without
worrying about duplicate keys.

Select the nth Highest Record in a Database Table using MySQL

If you want to select 2nd Highest Date from a recored set :
Then You have 2 options:

1. SUB-QUERY:
SELECT datetobefetch
FROM table1
WHERE datetobefetch < (
SELECT max(datetobefetch)
FROM table1
WHERE datetobefetch
)

But if have fetch 5th record then, the better approach is as follows:
2.Using LIMIT:

SELECT datetobefetch
FROM table1
ORDER BY datetobefetch
DESC LIMIT 4,1

The query just returns the first row after 4 row(s) so you get the nth highest record.

The Rows Holding the Group-wise Maximum of a Certain FieldRef:

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

+———+——–+——-+
| article | dealer | price |
+———+——–+——-+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+———+——–+——-+

Use an uncorrelated subquery in the FROM clause or a LEFT JOIN:

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

In my problem i found first query more efficient.

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and the s2 rows values will be NULL.

Ref : http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

How do I check how much disk space my database is using?

The simple way in PHP is as follows:
A quick script to show you how much disk space all your tables in a
database are using:

<?

mysql_connect("db.modwest.com", "username", "password");
mysql_select_db("yourdb");

$result = mysql_query("show table status");

$size = 0;
$out = "";
while($row = mysql_fetch_array($result)) {
$size += $row["Data_length"];
$out .= $row["Name"] .": ".
round(($row["Data_length"]/1024)/1024, 2) ."
\n”;
}

$size = round(($size/1024)/1024, 1);

echo $out .”
\n”;
echo “Total MySQL db size: $size”;
?>

If you simply want to know the total size, the query below will do the
trick (in bytes):

To get the total allocated space to a database:
SUM(data_length) + SUM(index_length)

Allocated but unused:
SUM(data_free)

select sum(data_length+index_length) from information_schema.tables where table_type = ‘BASE TABLE’;

Ref: http://lists.mysql.com/mysql/197563
http://www.modwest.com/help/kb6-199.html

Using Linux command also you can find out exact statistics:

Command is : df -h;

Reference Byte Converter:
1 Byte = 8 Bit
1 Kilobyte = 1024 Bytes
1 Megabyte = 1048576 Bytes
1 Gigabyte = 1073741824 Bytes