check is table exists in mysql 4.x

show tables like 'tbl name'

but you can not use this in t-sql openquery. in order to check the status from T-SQL openquery, you can use this statement instead.

show table status like 'tbl name'

sample code in T-SQL:
declare @rowCnt int
select @rowCnt = count(*) from OPENQUERY(PPC_DB_MYSQL,'show table status like "tbl name"')
if (@rowCnt=1)
BEGIN
END

you can also use exists function to see if there's record returned.

Classify of Indian Gamings

until today, I find a clear explaination on the classification of Indian games.
it's from http://500nations.com/Indian_Casinos.asp.

Class I Gaming
Defined as "traditional tribal gaming and social gaming" with minimal prizes.
There is no regulation outside of the tribal government.

Class II Gaming
Defined as gambling played exclusively against other players and not the house.
Examples are bingo, poker, and other “non-banked” card games.
These games are permitted on Indian land as long as they are legal elsewhere in the state.
.
Class III Gaming
Defined as gambling played against the casino.
Includes slot machines, blackjack, craps, roulette, and "all forms of gaming that are not class I gaming or class II gaming."
Requires a compact with the state.

Put SQL Server to single user mode

In order to have exclusive access to the database, the following command can be issued, it will kill all connections to the specified database except for the connection that we currently have to the database.

alter database xxx
set single_user
with rollback immediate

the following command bring it back to multiple user access.
alter database xxx
set multi_user

cross database ownership in sqlserver 2000

this works on sqlserver 2000 since sp3.

(ATER DATABASE db SET DB_CHAINING ON is not working)
-- when I listed the options,the option is displayed as "DB CHAINING", no underscore is included.
--it also works with 'on' and 'off'
sp_dboption 'xxxdb','DB CHAINING',true
sp_dboption 'xxxdb','DB_CHAINING',true

after enabling the cross database ownership, as long as the creator of the stored procedure in one database has corresponding ownership in another database, the user in the calling database does not have to has db access to the second database as long as it's granted the privilege to execute the stored procedure.

Note: by default, in sqlserver 2000 sp3, the stored procedure is executed as it's creator's privileges.

Change default setting of TCP ports allowed in Windows

This happens again so that it maybe a good time to record it here.

If you try to set up TCP connections from ports that are greater than 5000, the local computer responds with the following WSAENOBUFS (10055) error message:

An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.

The default maximum number of ephemeral TCP ports is 5000,
To increase the maximum number of ephemeral ports, follow these steps:

Start Registry Editor.
Locate the following subkey in the registry, and then click Parameters:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
On the Edit menu, click New, and then add the following registry entry:
Value Name: MaxUserPort
Value Type: DWORD
Value data: 65534
Valid Range: 5000-65534 (decimal)
Default: 0x1388 (5000 decimal)

Description: This parameter controls the maximum port number that is used when a program requests any available user port from the system. Typically , ephemeral (short-lived) ports are allocated between the values of 1024 and 5000 inclusive.

Exit Registry Editor, and then restart the computer.

Note: An additional TCPTimedWaitDelay registry parameter determines how long a closed port waits until the closed port can be reused.

Mysql transaction log restoration

almost all the documents you can find on the Net are telling you to use mysqlbinlog to read the binary logs and pipe the output to mysql client, but this method simply will fail on windows system if the database contains binary data such as blob and binvarchar, due to escaping and piping problem in mysqlbinlog utility. the mysql utility simply can not read the file or output stream created by mysqlbinlog.

here's a workaround though, we can tell mysql to use --execute "source xx" option to parse the log files from mysqlbinlog.

E.g.
C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
C:\> mysql --user=root --execute "source /tmp/bin.sql"

ref 1: http://bugs.mysql.com/bug.php?id=33048
ref 2: http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/windows-vs-unix.html

Basic MS SQLServer backup and restore commands

1. backup
1.1 backup database
BACKUP DATABASE [_db] TO DISK = N'C:\dbBackupTest\MC\db\test' WITH NOINIT , NOUNLOAD , NAME = N'_db backup', NOSKIP , STATS = 10, NOFORMAT
1.2 backup transaction log
BACKUP LOG [db] TO DISK = N'C:\dbBackupTest\MC\db\testlog' WITH NOINIT , NOUNLOAD , NAME = N'_db backup', NOSKIP , STATS = 10, NOFORMAT
--restore
--allow transaction log restore after database restore
RESTORE DATABASE [_db] FROM DISK = N'C:\dbBackupTest\MC\cdsdb\_db_200905090700.BAK' WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY
--allow more transaction log restore after transaction log restore
RESTORE LOG [_db] FROM DISK = N'C:\dbBackupTest\MC\db\_db_tlog_200905091000.TRN' WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY
--last transaction log, put the db operational
RESTORE LOG [_db] FROM DISK = N'C:\dbBackupTest\MC\db\_db_tlog_200905100600.TRN' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY

Setting master slave relationship between MySQL DBs

1.creating a user for replication.
Each slave must connect to the master using a standard MySQL user name and password, so there must be a user account on the master that the slave can use to connect. privilege REPLICATION SLAVE must be granted to the selected replication user.

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

2. configure a master
2.1 must enable binary logging on master.
log-bin=c:\xxx\log file name
server-id=x
#specify the db replication is for
binlog-do-db=exampledb

3. configure a slave
server-id=2
master-host=192.168.0.100
master-connect-retry=60
replicate-do-db=exampledb
#these two parameters can be defined in script
master-user=slave_user
master-password=secret

you do not need to enable binary log on a slave.

or you can do by mysql command:
CHANGE MASTER TO MASTER_HOST='master_host_name',MASTER_USER='replication_user_name',MASTER_PASSWORD='replication_password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;

usually, it's not necessary to specify the log file and log position information. as a good practice, the following is a suggested sequence on setting slave:
- set slave server-id and master-host in mysql configure file
- stop slave
- reset slave
- change master to xxxxx
- start slave

4. to load data from master if the source db is myisam type and there's no db created on slave yet. in order to run that command successfully, RELOAD,SUPER and SELECt privileges on the tables you want to load have to be granted to the replication user.

load data from master;

5. replicate to another database
eg. db1 to db2 on another server
replicate-rewrite-db=db2->db2
replicate-do-db=db2

Misc:
A better method to ensure exact replication would go like the below:
1) start master
2) grant replication to slave
3) run "show master status" and record the index file and it's offset position
4) start the slave
5) run "stop slave"
6) run "change master to master_host='[MASTER HOST IP]', master_user='[USER]', master_password='[PASSWORD]', master_log_file='[NAME]', master_log_pos=[POSITION]" (from #3)
7) run "start slave"


misc:
1. get master replication information
-flush tables with read lock;
-show master status;
-unlock tables;
2. get slave replication information
-show slave status;
3.enforce log switch
flush logs;
4.retrieve log information
-show master logs
-show slave logs

Backup and Restore MySQL Database

1. Configure mysql to generate binlog.
example:
in my.ini, define these two lines
log-bin=c:\mysql_blog\mysqlblog
max_binlog_size=10m

2.make scheduled task that runs script to make full backup specified database.
the command used can be mysqldump

3. make scheduled task that runs script to make back up of binlog files. an easy way to back up only the newly created bin log files is to remove the binlog files have been backed up.

the backup scripts can rotate after a certain period.

upon the time to restore the database from disaster such as hard disk failure, the following steps can be followed.

1. restore the database from recent full database backup
mysql -u -p db 2. restore all the bin logs since the recent full database backup
mysqlbinlog binlogfiles binlogfile2 ... >onebig file
mysql -u -p db <onebig file

IO redirection can also be used here

Always successful Grant statement in MySQL

If you do not want to fail a grant statement in Mysql, you can always include "Create" privilege in the statement. In this way, you can grant privilege to a table that is not existing yet. The table can be a table even in a non-existing database.

E.g

Grant create, select on db.tbl to user@localhost identified by 'TEST'

This statement will alway successfully executed on any mysql server.

Regular Expressions in Java and C#

This is a summary from reading "Introduction to Regular Expressions" written by Larry Mak

The basic idea of using regular expression is to match and to replace. The former determines if the pattern is in the string, and if so, find it. Replace changes the string according to the pattern to another pattern.

In C#, Regex in System.Text.RegularExpressions is used to do the match. In Java, Pattern and Matcher in package java.util.regex are used do the work.

C#:

Regex.IsMatch( string data, "Hello" )

Java:
Pattern pat = Pattern.compile("Hello");
Matcher m = pat.matcher( data );
if ( m.find() )

To define a boundary in regular expression, you use "\b"

To match more than once in same string, in C#, you write something like below to loop all the matches:
   for ( Match m = Regex.Match( str, patternToMatch); m.Success; m = m.NextMatch() )
In Java, you use matcher's find method:
   Pattern pat = Pattern.compile(pattern);
Matcher m = pat.matcher( s );
         while ( m.find() ){
             System.out.println( m.group() );
         }
Capturing groups are numbered by counting their opening parentheses from left to right.
E.g. $(\d+)\.(\d\d). For each matching, it can be further detailed to capturing groups.
In C#, GroupCollection gc = m.Groups;
In Java, matcher.group(i)

Replacement
In C#, use Regex.Replace( str, search, replace );
In Java, use matcher.replaceAll() method

One powerful operation you can do with regular expression is to change the
string with what you captured. you define the groups in order from 1,
and you can rearrange the groups by reorganize the order or groups,
for example, $1$3$2.

Refresh on GridBagLayout

I have to work on a GUI in Java recently. Since it works with JDK1.5 so that the most convinient and flexiable GroupLayout can't be a candidate. The NetBean 6.5 does not provide automatic conversion from GroupLayout to GridBagLayout as what has been provided in JBuilder3.x back to more than 10 years in 1998, and the visual Editor for eclipse does not fully support free GUI design, so that I have to code it by hand. Once again it reminds me that Borland's tools are the best.

This is a good chance for me to refresh the knowledge on layout managers in Java. Most of the content below is from http://java.sun.com/docs/books/tutorial/uiswing/layout/gridbag.html.

GridBagLayout is the second most flexiable and powerful layout manager next to GroupLayout. It places components in a grid of rows and columns, allowing specified components to span multiple rows or columns. Not all rows necessarily have the same height. Similarly, not all columns necessarily have the same width. Essentially, GridBagLayout places components in rectangles (cells) in a grid, and then uses the components' preferred sizes to determine how big the cells should be.

This resizing behavior is based on weights the program assigns to individual components in the GridBagLayout. The way the program specifies the size and position characteristics of its components is by specifying constraints for each component. The preferred approach to set constraints on a component is to use the Container.add variant, passing it a GridBagConstraints object.

It is possible to reuse the same GridBagConstraints instance for multiple components, even if the components have different constraints. However, it is recommended that you do not reuse GridBagConstraints, as this can very easily lead to you introducing subtle bugs if you forget to reset the fields for each new instance

Other attributes are easy to understand, below are the ones need to pay more attention to.

weightx, weighty

Specifying weights is an art that can have a significant impact on the appearance of the components a GridBagLayout controls. Weights are used to determine how to distribute space among columns (weightx) and among rows (weighty); this is important for specifying resizing behavior.

Unless you specify at least one non-zero value for weightx or weighty, all the components clump together in the center of their container. This is because when the weight is 0.0 (the default), the GridBagLayout puts any extra space between its grid of cells and the edges of the container.
Generally weights are specified with 0.0 and 1.0 as the extremes: the numbers in between are used as necessary. Larger numbers indicate that the component's row or column should get more space. For each column, the weight is related to the highest weightx specified for a component within that column, with each multicolumn component's weight being split somehow between the columns the component is in. Similarly, each row's weight is related to the highest weighty specified for a component within that row. Extra space tends to go toward the rightmost column and bottom row.

Mouse problem in Word 2007

Symptom:

the mouse simply does not select any thing or locate to any position in work document.
keyboard is still working.

Related tries:
- remove HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Data
- disable add-ins under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Word\Addins

Failed in my case.

Final solution:
-start word in secure mode:winword /a. this is because under normal start, the mouse does not work in the option dialog.
-work options-->add-ins
disable suspected add-ins will get the problem fixed. in my case, the expired power designer add-in caused the failure of mouse function in word 2007.

Applications that run on Samsung Jack i616

I just find it is too frustrated to play with Windows Mobile backed handsets. There're a lot of applications for Windows Mobile based PDAs, but most of them are just not that portable as they do on desktop windows systems. Very frequently, you will find the application you get is not working with your handset, especially if that is running a standard version of the system, which is used on entry level smart phones, such as a smart phones without a touch screen.

Here's a list of third party applications working on my Samsung Jack i616, which is an entry level smart phone nowadays on the market.

GPS Navagator:
Garmin Mobile XT for Windows Mobile v.5.00.20w

Browser:
skyfire

Media Player:
TCPMP

Connect Samsung Jack i616 to Internet via computer with bluetooth

Bluetooth itself can share network connections to other paired device, but this does not work on standard windows mobile 6.1 installed on samsung jack. my computers have provided connection services via bluetooth. I thought this would be a solution for me to follow, but the bluetooth on WM6 simply does not list that out as one of the usable service on my computers.

I've tried realy hard for more than 10 hours to connect Samsung Jack to Internet via bluetooth by using my desktop or laptop computers by trying to change the bluetooth settings. then I found this really simple sulution, which reminds me again that I should get familiar with WM6.1 the first before trying things blindly.

The solution? you just need to build activesync connection via bluetooth. the detailed steps to follow is in the activesync's help. Go to activesync on pc and go to help> microsfot activsync help> and click on “Connecting a mobile device to PC” > “connect activesync using bluetooth”.
following the steps in the hlep, you should be able to connect your WM6.1 system to Internet via bluetooth by using your computer's internet connection.

Run bunch of scripts in a batch

simple example for reference.

in batch file:
FOR %%q IN (*.sql) DO isql -d db -U sa -P xxxx -i %%q -o %%q.log
in command line directly:
FOR %q IN (*.sql) DO isql -d db -U sa -P xxxx -i %q -o %q.log

Valid Data Vs Correct Data

A constraint makes sure the data is valid, but it can not guaranty it is correct.

For example, if you use a foreign key to enforce data in child table should have a reference in parent table. you can guaranty the data in child table is valid, but you can not tell if the data is correct. if the primary key is composed of data "male" and "female", as long as data in child table is one of these valid values, it passes the checking of constraint. But you might mistakenly assigned a male a gender of female.

Using foreign key is better than using trigger to validating the data, because foreign key checks the validity before data is entered. In contrast, DML triggers works on data after data has been entered. Trigger can roll the change back, but that requires double work since the data is entered, then undone.

Modify foreign key

In Oracle, you can really modify it with ALTER TABLE MODIFY CONSTRAINT.

In SQL Server or most of other DBs, there's no such statement. you have to drop the constrain the first, recreate it the next.

Oracle might do the same two steps way beneath the scene.

In or inner join vs exists

This article is based on Oracle database.

IN is similiar to Inner join, "exists" is similiar to a loop statement.

generally say, "exists" works better in most of situation since it requires you to specify a join condition, which can invoke an INDEX scan.

But it's not always true. when the result set of subquery is small enough, inner join or IN works better. first of all, subquery can also use index scan as no exception. sencondly, using IN or inner join, you're telling the rule-based optimizer that you want the inner query to drive the outer query. the engine will run out the driving query that returns small result set the first and that will make filtering rows in outer query faster.

which way is better? checking the execution plan will give you final answer.

please note that in Oracle, the query parser will always change subquery to be "select 0" whatever value you specify in the select list(common ones are 0,1,X,null).

find parent rows not having a child

1.select * from parent where not exists(select 1 from child where parent.pk=child.fk)
this is better since no join is involved

2.select * from parent where parent.pk not in (select fk from child)
this is most natural query. a little bit worse than first method since join operation is heavier

3.out join
select * from parent left (outer) join child on parent.pk=child.fk where child.fk is null

this utilize the feature of outer join. you make a result from join the first, then filter out the record wanted. might be worst performance.

select 1 vs select * in exists function

in SQL server, select 1 is better than select *.

if you check the execution plan, you will find they have exactly same plan and performance. but during the stage of query plan, * is expanded to bind to all the columns in the underlying table. you can imagine this will have to retieve meta data from database that can be avoided in "select 1" syntax.

so the advantage of "select 1" is to avoid parsing uncessary information that will discarded during the execution.

SQLServer refresh

T-SQL With Check view option

it's used to prevent the data from being modified in such a way that it falls out of the view. simply, it forbids update on the column used in the view's where clause.

Table Relationship

one-to-one,one-to-many,many-to-many

for one to one, they can always be merged into one table. they are divided into two or more tables under special concern such as efficient storage,security and temporary data management. to implement this relationship, make PK a FK to each other.

for one to many relationship, it's usually implemented by FK and PK.

For many-to-many relationship, no such constrain can be defined. they are implemented by defining a junction table.

When two tables need a many-to-many relationship, create a third table, also know as junction table. this junction table will have a composite PK created from PK from each of the two tables.

Normalization

the process of ensuring that your database complies with normal forms is referred to as normalizing a database.

1st normal form
1NF requires that all column values in a table be divided to their smallest element. on other wards, all non-pk fields are atomic(can not be divided further). additionally, it should have no repeating groups.

2NF applies to table that have composite PKs. a table is said to be in 2NF if it is in 1NF and every non-pk attribute is completely dependent on the whole pk. in other words, if an attribute within the table is dependent on only one column of the composite pk then it is not 2NF.

3NF means fields within the table are only dependent on pk and not dependent on non-pk fields. to ensure that a table complies with 3NF, review each column and ask "is this column dependent on the PK?"

De-normalization

do it more for better performance.

online transaction processing OLTP databases are mostly normalized because they are focused on inserts and modifications, which perform better in a normalized db.

online analytic processing OLAP databases are commonly normalized because they are focused on querying, which perform better in denormalized db.

Generalization

similar to concept in OOD, is a process of combing similar entities together to form a single entity. e.g. all kind of people can be organized in contact table.

Order By

used to guarantee the order of your result set. columns in order by clause don't have to be in the select column list. the exception is if we use distinct clause to eliminate duplicate rows. in this case, the column in order by clause must be in the select column list.

Aggregation Function

NULL value will not be counted in the calculation of aggregation function.
eg. count(*) will count all the rows. count(column) will only count on column with not null values.

Identity column

you can define only one identity column in a table. the position of it can be any where. it's not necessarily put it in the left most or right most of the column list. while insert statement has column list missed, sql server can figure out if there's a value provided for identity column automatically.

be aware that the identity is similar to sequence in Oracle, even an insert statement fails execution, as long as this is not identity column related problemm, the identity will still be increased 1 step forward, and that is irreversible.

N in Front of String

it means the following string is in uni-code

Coalesce vs case statement

Coalesce choose the first non-null value of the list. this is similar with case statement, which choose the first satisfied branch to use.

Case. when..else..End statement is fully fledged statement that evaluate express as condition. Coalesce is much simpler and only tells if the value is null.

Len
Be careful that len function does not count trailing blanks

Tracing the execution time
here's an example code I can use in everyday life while it comes to performance turning.

declare @start datetime,@end datetime
set @start=getdate()
.....do something
set @end=getdate()
print 'time:'+str(datediff(ms,@start,@end))+' ms'

Remove vs Alter
The reason why you do alter instead remove statement is to preserve the permissions on the original object.

Select record with max xxx
use exists that basically makes an inner join to itself.
select * from tbl a where not exists(select 1 from tbl b where a.increamentalCol <>

Storing different language set in MSSQL

if data is to be stored within a column in a different collation than the database, they should be defined as Unicode column. this will double the storage space, but it ensures that data can be moved smoothly between columns.

typically, a computed column can be created on this kind of column with different collation to facilitate data access in different collation without explicit collation conversion.

First Rule on Database Design

Keep the row size as small as possible.

This is regarding to efficient physical storage and also efficient access to the data in a table. the smaller a row is, the more rows can be stored in one physical IO access unit, the faster the data can be found.

How to make "No Data Found" in Jasper report

Problem:

There's no record found for report, and you want to print "No Data Found" in the report.

Solution:

Solution 3 is the preferred one.

1. Make a dummy empty record for the report so that report display a line in its detail. you can use one of the column to have value of "No Data Found" to solve the problem. you can also make a field that is printed only when dummy record is used.

2. Define a column footer that contains a static text of "No Data Found". make the column footer print only when report count is equal to zero.
Note: in version 2, the column footer is printed at bottom of page. newer version might make it float with column header if there's no body available.

3. Define a report group that groups on nothing. hide the group header. define a static box of "No Data Found" in group footer. make group footer print only when report count is equal to zero.

Enable SQL Trace in MySQL

In Windows, in the configuration file, for instance, my.ini, add a line like this:
log=c:\mysql.log

same thing in linux or unix, adding same "log" entry in configuration file will turn on the sql trace.

remark the log entry will turn the trace off.