Archive | Database RSS feed for this section

Migrating UTF8 data from MySQL 5.0 to 4.x (with latin charset)

27 Sep

Some of old PHP application use ANSI SQL to create table and insert data. So when I use those application with UTF8 content in MySQL 5.0, the default character-set of those table is latin1. The problems come when I want to move data to an older MySQL, the encoded content goes wrong.

So this is the solution:

mysqldump -u username –opt –default-character-set=latin1 –skip-set-charset –skip-tz-utc –skip-comments –single-transaction %1 %2 %3 %4 %5

This post will be updated later with full description.

Paging in SQL Server 2000

15 Jul

Oh man, working with M$ is hell. Yeah, they do very big things but they just forgot small but essential things.

M$ maybe the most stupid when not implementing paging functionality in T-SQL. Most of other DBMSs do that. Paging from server is a vital point, especially in web development. If you just do paging from client-side, you are wasting most of network traffics for unused records.

After searching thouroughly on Internet, I finally found out some ways to get arround this flaw. One uses dynamic SQL query to refine the query and add some ORDER BY + TOP clauses to get the result. Another way is to use a temporary table with an identity column used as a row number. I prefer the 2nd way. At least, it’s more secured.

alter procedure GetAuthors
(
@orderby 	nvarchar(100),
@recordsPerPage int,
@pageNumber 	int
)
AS
--  the amount of rows actually needed
declare @rowsNeeded int
--  the position where we need to start retrieving records
declare @startRecord int
-- initialize @rowsNeeded
set @rowsNeeded = (@pageNumber * @recordsPerPage)
-- initalize @startRecord
set @startRecord = ((@pageNumber-1) * @recordsPerPage) + 1

create table #temptable
(
 row		 int identity(1,1),
 au_id 		 varchar(20),
 au_lname	 varchar(40),
 au_fname	 varchar(20),
 phone	 	 char(12),
 address 	 varchar(40),
 city		 varchar(20),
 state	  	 char(2),
 zip		 char(5),
 contract	 bit
) 

-- set the rowcount to only the rows we need.
set rowcount @rowsNeeded

-- select data into a temp table,  I choose to
-- select all data, not just the key into the
-- temp table to avoid another join if the table is large.
insert into #temptable
(au_id,au_lname,au_fname,phone,address,city,state,zip,contract)
select au_id,
       au_lname,
       au_fname,
       phone,
       address,
       city,
       state,
       zip,
       contract
FROM authors
ORDER BY
CASE
WHEN @orderby=’au_lname’ THEN au_lname
WHEN @orderby=’au_fname’ THEN au_fname
WHEN @orderby=’au_id’    THEN au_id
END 
ASC
select row,
       au_id,
       au_lname,
       au_fname,
       phone,
       address,
       city,
       state,
       zip,
       contract
from #temptable
where row >=@startRecord

drop table #temptable

If you use SQL Server 2005, the life must be easier. You can use ROW_NUMBER to do paging:

WITH OrderedOrders AS
(select SalesOrderID, OrderDate,
Row_Number() OVER (order by OrderDate)as RN
from SalesOrderHeader )
SELECT * from OrderedOrders WHERE RN between 50 and 60

Well… At least, M$ have done something to improve the situation, but not all. It takes me all night to find out the way. I have even thought about switching to MySQL with a very simplier paging syntax, but this is my thesis :P, so I can’t.Thanks to the communities.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31741

Okey, time to continue my work.