Discovering Databases

How to determine if the Table exists in the Database or not

2010-01-13T10:48:00.000-08:00

Oracle - select table_name from user_tables where table_name = upper('table_name_to_be_searched');

SQL Server - select name from sys.tables where name = upper('table_name_to_be_searched');

How to determine URL and Port of Oracle database web based Management Console

2010-01-10T05:55:00.000-08:00

To determine URL and Port of Oracle database web based Management Console, use -
emctl status dbconsole

EG:

C:\Documents and Settings\Administrator>emctl status dbconsole
Environment variable ORACLE_SID not defined. Please define it.

C:\Documents and Settings\Administrator>set ORACLE_SID=orcl

C:\Documents and Settings\Administrator>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory D:\oracle\product\10.2.0\db_2/localhost_orcl/sysman/log

C:\Documents and Settings\Administrator>

Connecting to MySQL Database through PERL

2009-04-16T12:41:00.000-07:00

FAQ: How to connect to MySQL database through PERL.

SOLUTION:
1. Install MySQL database. MySQL community server comes free of cost and is distributed under GNU General Public License.

2. I would instead recommend installing a package called WAMP server which provides MySQL community server, Apache server and PHP. It also provides many effective tools like PHPMyAdmin which can be used for database administration. Also WAMP server comes free of cost and is distributed under GNU General Public License.

3. To check if the MySQL server is running, execute:
telnet 3306
E.G: # telnet 127.0.0.1 3306

4. I would also recommend you to also install SQLYog. It is a very light and effective tool to monitor and manage MySQL database. Again it also comes free of cost and is distributed under GNU General Public License.

5. Create a database in MySQL, a non-root user and give it a password.

6. Install ‘DBI’ perl module through ‘PPM’

7. Install ‘DBD::Mysqlpp” perl module through ‘PPM’

8. You are all set to connect to MySQL database. While connecting make sure that database is already created and you are connecting through a valid user and with correct credentials. To see a sample script execute following on command prompt:
# perldoc DBD::Mysqlpp

REFERENCES:
1. http://dbi.perl.org/
2. # perldoc DBD::Mysqlpp

AUTHOR: Parag Kalra
Email-ID: paragkalra@gmail.com

Displaying "non-English" characters using PHP & MySQL

2009-01-23T22:23:00.000-08:00

Many times we intend to display "non-English" characters say Hindi language on the web-page using PHP & MySQL. Here's the trick:

1. Set column, table & database collation to "utf8_unicode_ci". You can use PHPMyAdmin for that purpose.

2. Use following meta tag in the php page:

meta http-equiv="Content-Type" content="text/html; charset=UTF-8"

3. And while establishing connection with the database make sure you do the following:
$chs1="SET NAMES utf8";
myquery($chs1);

$chs2="SET CHARACTER SET utf8";
myquery($chs2);

function myquery($var) {
$rval=mysql_query($var);
return $rval;
}

Resource:
http://dev.mysql.com/doc/refman/5.0/en/charset.html
http://www.phpfreaks.com/forums/index.php/topic,235410.msg1095028.html#msg1095028

Important commands:
mysql> show character set;
mysql> show collation ;
mysql> show global variables;

MySQL script to create & drop databases !!!

2009-01-18T09:35:00.000-08:00

#===============================================================================
# FILE: alter_dbs.sql
# DESCRIPTION: To drop and recreate the databases
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# CREATED: Sunday 18 January 2009 07:00:29 IST IST
#===============================================================================

#-------------------------------------------------------------------------------
# Dropping the databases if they exists
#-------------------------------------------------------------------------------
DROP DATABASE IF EXISTS `some_database `;

#-------------------------------------------------------------------------------
# Creating databases
#-------------------------------------------------------------------------------
CREATE DATABASE `some_database `;

MySQL Error 2003 - Can't connect MySQL on'IP Address'

2008-12-28T05:33:00.000-08:00

1. Try to telnet the remote mysql server.
e.g. telnet 192.168.1.2 3306 (Windows)
e.g. telnet 192.168.1.2 3306 (Linux)
2. If not being able to telnet, check the config file of mysql (my.cnf). "bind-address" parameter should be set to ip address and not to localhost.
3. Restart the mysql service.
4. Now you should be able to telnet and also connect to the mysql server remotely.

Resources used:
http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
http://ubuntuforums.org/showthread.php?t=687294