Category Archives: Database

Trigger for backup and restore data before deleting

Copy data(record) into backup table before delete data.
and restore (delete from backup table) deleted data.

Working Table : note
Backup Table : note_del

CREATE TABLE `note` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` text NOT NULL,
`date` datetime NOT NULL,
`userId` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `userId` (`userId`)
)

CREATE TABLE `note_del` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` text NOT NULL,
`date` datetime NOT NULL,
`userId` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `userId` (`userId`)
)

For backup before deleting

DELIMITER $$

USE `memoboard`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER note_delete
BEFORE DELETE ON note
FOR EACH ROW
BEGIN
INSERT INTO note_del (SELECT * FROM note WHERE id = OLD.id);
END
$$

For restore (delete from note_del table, then the record will be restore into note table)

DELIMITER $$

USE `memoboard`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER note_undelete
BEFORE DELETE ON note_del
FOR EACH ROW
BEGIN
INSERT INTO note (SELECT * FROM note_del WHERE id = OLD.id);
END
$$
Posted in Database, Tech Note | Tagged , , | Leave a comment

To set up for Advantage Database Server(v.11) and Code First

Server : Advantage Database Server 11

Path : \ADS_DB:6262\DB\DBB.ADD (Port : 6262)

Test Access Table : WEBORDERLN (ADT Type)

Web.config


Controller, (For use, we need to make this separate to model and controller. I mention this, because someone will say about this /..\ )

public class Ads_Context : DbContext
{
    public Ads_Context(): base("name=Ads_Context")
{
}
public DbSet webOrderLns { get; set; }
}

public class HomeController : Controller
{
    //
    // GET: /Home/

    public string Index()
    {
        var context = new Ads_Context().webOrderLns.ToList();
        return "A";
    }

}
[Table( "WEBORDERLN", Schema = "::this" )]
public class WebOrderLN
{
    [Key]
    public string PK {get; set;}
    public string FK { get; set; }
    public string pickno { get; set; }
}

The key point is,

[Table( "WEBORDERLN", Schema = "::this" )]

Advantage .NET Data Provider 11 for window download

Posted in ASP.NET, Database | Tagged , , , , | Leave a comment

Asp.net (C#) Lock Table and Record (Advantage Database Server)

Lock Table

[HttpGet]
public ActionResult lockTable()
{
    string conn_string = System.Configuration.ConfigurationManager.ConnectionStrings["Ads_Context"].ConnectionString;
    using (AdsConnection conn = new AdsConnection(conn_string))
    {
	Ads_Connection.Open();

	AdsCommand cmd = new AdsCommand("misys01", conn);
	cmd.CommandType = CommandType.TableDirect;

	AdsExtendedReader reader = cmd.ExecuteExtendedReader();

	reader.LockTable();

	System.Threading.Thread.Sleep(5000); // Lock table about 5 Sec

	if (reader.IsTableLocked())
	{
	    return Content("Table is locked");
	}
	else
	{
	    return Content("Table is not locked");
	}
    }

    // Locking will be released when finished this method.
}

Update Locking Table

[HttpGet]
public ActionResult UpdateLockingTable()
{
    string conn_string = System.Configuration.ConfigurationManager.ConnectionStrings["Ads_Context"].ConnectionString;
    using (AdsConnection conn = new AdsConnection(conn_string))
    {
	Ads_Connection.Open();

	bool processing = true;

	while (processing)
	{
	    try
	    {
		string query = "SELECT finvno FROM misys01";
		AdsCommand cmd = conn.CreateCommand();
		cmd.CommandText = query;

		AdsDataReader reader = cmd.ExecuteReader();

		reader.Read();

		string invno = reader["finvno"].ToString();
		invno = "290002";

		AdsCommand cm = new AdsCommand("UPDATE misys01 SET finvno = '" + invno + "'", conn);
		cm.ExecuteNonQuery();

		processing = false;
					
	    }
	    catch (Exception e)
	    {
		processing = true; //back to loop
		//return Content(e.Message);
	    }
	}

	return Content("Updated");
    }
}

Lock Record

[HttpGet]
public ActionResult LockRecord()
{
    string conn_string = System.Configuration.ConfigurationManager.ConnectionStrings["Ads_Context"].ConnectionString;
    using (AdsConnection conn = new AdsConnection(conn_string))
    {
	Ads_Connection.Open();

	string query = "SELECT finvno FROM misys01";
	AdsCommand cmd = conn.CreateCommand();
	cmd.CommandText = query;

	AdsExtendedReader reader = cmd.ExecuteExtendedReader();
	reader.Read();
	
	int recordNo = reader.RecordNumber;
	reader.LockRecord(recordNo);

	System.Threading.Thread.Sleep(10000); // Lock record about 10 sec

	if (reader.IsRecordLocked())
	{
	    return Content("Record is locked");
	}
	else
	{
	    return Content("Record is not locked");
	}
    }
    // Locking will be released when finished this method.
}
Posted in ASP.NET, Database, Tech Note | Tagged , , , , , | 1 Comment

[ADS] Unable to find the requested .Net Framework Data Provider. It may not be installed.

Ref No:
100525-2319
Last Modified:
Wednesday, May 26, 2010
Product:
Advantage .NET Data Provider
Category:
Configuration
Title:
Unable to find the requested .Net Framework Data Provider. It may not be installed.
Problem Description:
The problem affects the ADO .NET data provider for ADS 9.1 as ADS 9.1 was the first to ship with 64-bit capability.

In Windows environments where IIS defaults to 64-bit, an install of the ADO .NET data provider will result in the error,
Unable to find the requested .Net Framework Data Provider. It may not be installed., in the web application. This is because the ace32.dll file supplied with the provider is 32-bit.

Solution:

This problem has two solutions.

  1. The web application can be configured to run as 32-bit
    To do this follow these steps:

    1. Open the IIS Manager
    2. Select the web server
    3. Select Application Pools
    4. Create a new Application Pool, or select “DefaultAppPool”
    5. Go to advanced settings
    6. Change “Enable 32-bit Applications” to true

    If you created a new Application Pool, you can set the Pool to be used to a site or an Application by changing its properties.

  2. The 64-bit dlls can be copied in from a 64-bit server installation.
Posted in Database, Tech Note | Tagged , , , , | Leave a comment

Replace specific character or trimming

REPLACE :

UPDATE [TABLE_NAME] SET [FILED_NAME] = REPLACE([FIELD_NAME],[TARGET_STR],[REPLACE_WITH]);

TRIMMING :

UPDATE [TABLE_NAME] SET [FILED_NAME] = TRIM([FIELD_NAME]);

Posted in Database, Tech Note | Tagged , | Leave a comment

Import large csv file into PostgreSql (pgsql)

> psql -d [DB_NAME]

> \copy [TABLE_NAME] from ‘[FILE_NAME]’ delimiter ‘,’

ex )

> \copy location from ‘/tmp/location.csv’ delimiter ‘,’

Posted in Database, Tech Note | Tagged , , | Leave a comment

Import large csv file into mysql

mysql > LOAD DATA LOCAL INFILE ‘/tmp/blocks.csv’ INTO TABLE `ip_blocks` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’;

Note : http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Posted in Database, Tech Note | Tagged , , , , | Leave a comment

How to search the field which has duplicated value

SELECT id,count(*) AS total FROM mytable GROUP BY `name` ORDER BY `total` DESC

Posted in Database, Tech Note | Tagged , | Leave a comment

Let’s play with information_schema (Mysql5)

To see quick result, open to mysql console window.

In my case, I’m using WAMP, so I can open mysql console like this,

cmd -> c:/> -> cd wamp -> cd bin -> cd mysql -> cd mysql5.1.36 -> cd bin -> mysql -u root -> mysql>

Now we are ready to shoot queries ^^;

To see all information about tables,

SELECT * FROM information_schema.tables WHERE table_schema = ‘[DB Name]’ AND table_name = ‘[Table Name]’

Ex)

SELECT * FROM information_schema.tables

\g (after state query, you need to type ‘\g’ to make run your query) OR semi-colon (;)

It show toooo  much information that we can not regonize.

Now let’s make to show information only selected database,

SELECT * FROM information_schema.tables where table_schema=’test1′    //of cause you need to have test1 database

we can see the table and information for slected database. ioi

Now, let’s see the only selected table information,

SELECT * FROM information_schema.tables where table_schema=’student’

we can see the table information.

Let’s see only table names in selected database,

SELECT table_name FROM information_schema.tables WHERE table_schema=’test1′

Um.. what else…..^^

Bonus,

Let’s create store procedure! why? just..^^k

Good night~3

Posted in Database | Tagged , , , | 4 Comments

Let’s play with SQL join~!! (inner join,outer join,left,right… @.@)

I have one hour left to go home ^^; So, I will talk about SQL Joins.
(I just want to post something on my blog..^^)

Firstly, let’s create 2 tables for testing.

Table 1.
CREATE TABLE `student` (
`id` INT NOT NULL,
`name` VARCHAR( 50 ) NOT NULL,
`address` VARCHAR( 30 ),
`phone` varchar(50),
`email` varchar(100),
PRIMARY KEY(`id`))
Table 2
CREATE TABLE `credit` (
`id` INT NOT NULL,
`subject` varchar(50),
`score` decimal(5,2),
`date` date)

Secondly, let’s put some data in that tables.

INSERT INTO `student` (`id`,`name`,`address`,`phone`,`email`) VALUES(1,’Mark Song’,’NY,NY 10001′,’222-222-2222′,’mark@uniapple.net’);
INSERT INTO `student` (`id`,`name`,`address`,`phone`,`email`) VALUES(2,’Amy Kim’,’LA,CA 10001′,’333-333-3333′,’amy@uniapple.net’);
INSERT INTO `student` (`id`,`name`,`address`,`phone`,`email`) VALUES(3,’James Jung’,’NY,NY 10001′,’444-444-4444′,’james@uniappl.net’);
INSERT INTO `student` (`id`,`name`,`address`,`phone`,`email`) VALUES(4,’Nakamura Na’,’NY,NY 10001′,’555-555-5555′,’nakamura@uniapple.net’);
INSERT INTO `student` (`id`,`name`,`address`,`phone`,`email`) VALUES(5,’Alice M’,’LA,CA 10001′,’777-777-7777′,’alice@uniapple.net’);
INSERT INTO `student` (`id`,`name`,`address`,`phone`,`email`) VALUES(6,’Hippo George’,’NY,NY 10001′,’888-888-8888′,’hippo@uniappl.net’);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(1,’Math’,’100.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(1,’English’,’50.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(1,’Science’,’80.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(1,’History’,’90.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(2,’Math’,’70.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(2,’English’,’90.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(2,’Science’,’60.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(2,’History’,’70.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(3,’Math’,’90.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(3,’English’,’50.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(3,’Science’,’50.00′,’2011-01-01′);
INSERT INTO `credit` (`id`,`subject`,`score`,`date`) VALUES(3,’History’,’60.00′,’2011-01-01′);

Now we have like this schema ::

Are you ready to play? ^^

let’s play with this,

Round 1.

I want to get all student information and all the subject score. (GET A and C)

SELECT * FROM `student` LEFT JOIN `credit` ON student.id = credit.id
Or
SELECT * FROM `student` A LEFT JOIN `credit` B ON A.id = B.id
or
SELECT A.id,A.name,A.phone,A.email,B.subject,B.score,B.date FROM `student` A LEFT JOIN `credit` B ON A.id = B.id (*Recommend, but in this posting, I will use the first one for convenience^^)

Result :

Round 2.

I want to get all the subject score and related student information. (get B,C)

SELECT * FROM `student` RIGHT JOIN `credit` ON student.id = credit.id

Round 3.

I want to get all the student information who has credit, also need to get the credit information too. (Get C)

SELECT * FROM `student` INNER JOIN `credit` ON student.id = credit.id

Understand? ^^

Bonus Round

Now I want to all the student information and only Math score.

SELECT *,(SELECT `score` FROM `credit` WHERE `subject`=’math’ AND id=student.id) as math_score FROM `student`


Um.. what else………………..
???
Posted in Database | Tagged , , , , , | 30 Comments