Tag Archives: Database

24 May

Java termwork connect Access Database and operations

Prologue:
Long back there were golden days of post graduation in the “great” DDIT (Now Dharamsinh Desai University), Nadiad.
Assignments, termwork and journals or everything similar was in demand – I attempted a java code as my termwork on Monday, October 03, 2005. [That’s created date of .java file, I don’t remember anything.]
A “variable check” became very famous, notorious actually, for some unmentionable reasons. It was recently mentioned by Utkarsh AKA Bapu.
Later I decided to scan each bit of my harddisk and get that 8582 code.
So here is that java code and it was working 5 years back.
I don’t remember / have termwork’s definition but code is here. Seems a simple java program to connect DB and do regular activities.

Now,
This post is a remembrance for my colleagues and the great 8582 check.

For the rest go ahead and test Java-Access-Database connectivity code.
Download TermWork.

[ad#ad-2-300×250]


Epilogue:
I’m no more working with Java, I understand logic and nothing else. So if atall you face any errors, go through it – you can solve it, post it here too.
Enjoy.
04 May

Steps to Move Copy Rename MySQL Database

Moving, copying or renaming database is a very basic activity. I have just noted a few commands for reference to quickly follow the required operation.

1. Rename database on Linux Machine:

A. Use RENAME DATABASE Command [MySQL 5.1].

RENAME DATABASE db_name TO new_db_name;

B.

mysql -uroot -pPASSWORD -e “drop database if exists NEW-DB-NAME; create database NEW-DB-NAME” && mysqldump -uroot -pPASSWORD SOURCE-DB-NAME | mysql -uroot -pPASSWORD NEW-DB-NAME && mysql -uroot -pPASSWORD -e “drop database SOURCE-DB-NAME”

2. Create Duplicate of a database:

mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword DB_TO_BE_CREATED

3. Copy MySQL Database to Remote MySQL Server:

3A. By using -h (hostname) option.

Create MySQL Dump:

mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Load MySQL Dump:

mysql -uroot -ppassword -hHOSTNAME < DB_TO_BE_COPIED.sql OR Using Single Command: mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword -hHOSTNAME DB_TO_BE_CREATED 3B. By moving dump file to remote server. Create MySQL Dump: mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Copy SQL file to remote MySQL Server:

scp DB_TO_BE_COPIED.sql username@remote-machin:/path/to/copy

Login to remote-machin.

Load MySQL Dump:

mysql -uroot -ppassword DB_TO_BE_CREATED < /path/to/copy/DB_TO_BE_COPIED.sql

Here, I haven’t considered copying and moving data-files for MyISAM databases as these are just the commands-way.

05 Apr

In the world of NoSQL-Hands on Mongodb-1

I’m hearing a lot of “NoSQL” these days. To really understand how (and) does it works, I decided to give a try on MongoDB.

MongoDB (hu*mongo*us) is an open source, scalable, high-performance, schema-free, document-oriented database written in the C++ programming language.

MongoDB is not a Relational Database Management System. The database manages collections of JSON (JavaScript Object Notation)-like documents which are stored in a binary format referred to as BSON (Binary JSON).

This post will surely get you started with the MongoDB-NoSQL.

Installing MongoDB:

  • Download Mongodb from www.mongodb.org
  • Extract binaries
  • Create data directory – default for windows C:\data\db
  • Install service: Full\path\to\mongod.exe –install

If you want to change default data directory location:
Full\path\to\mongod.exe –dbpath=”\”full\path\to\”” –install

Troubleshooting Mongodb Installtion Error:
I faced while installing, if you get error like:
dbexit:
shutdown: going to close listening sockets.
shutdown: going to flush oplog…
shutdown: going to close sockets…
shutdown: waiting for fs preallocator…
shutdown: closing all files…
closeAllFiles() finished
dbexit: really exiting now

Go to: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/
Check for MongoDB
Verify the value of ImagePath is proper

This seems to be caused by not giving full-path while installing.

Add Environment variable Path, add path/upto/mongo-bin-dir;

Okay, now we’re all set to checkout the MongoDB-NoSQL world.

MongoDB GUI Administration Tool:

There is a GUI Admin tool available phpmoadmin to simplify the MongoDB Operations.
Signle php script: phpmoadmin
Download PHP extension from php.net/manual/en/mongo.installation.php
Extract in ext directory of WAMP & Restart.

In case of error you may install Microsoft Visual C++ 2008 SP1 Redistributable Package (x86) [www.microsoft.com/downloads/details.aspx?familyid=A5C84275-3B97-4AB7-A40D-3802B2AF5FC2&displaylang=en]

MySQL to MongoDB Database Migration:

Create CSV from MySQL:
mysql> select * into outfile “c:/TABLENAME.csv” FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ from TABLENAME;
Query OK, 293911 rows affected (1 min 8.17 sec)


Import CSV to mongodb using mongoimport:
C:\>mongoimport -d DATABASENAME -c TABLENAME –file “c:\TABLENAME.csv” –type csv -f FIELD1,FIELD2…

Using MongoDB on Windows:

On Command Prompt:
C:\MongoDB\mongodb-win32-i386-1.4.0\bin>mongo.exe
MongoDB shell version: 1.4.0
url: test
connecting to: test
type “exit” to exit
type “help” for help

By default it’s connecting to test database.
Default mongodb port is 9999.

Basic Commands

  • show dbs : show database names
  • show collections : show collections in current database
  • show users : show users in current database
  • show profile : show most recent system.profile entries with time >= 1ms
  • use : set curent database to
  • db.help() : help on DB methods
  • db.foo.help() : help on collection methods
  • db.foo.find() : list objects in collection foo
  • db.foo.find( { a : 1 } ) : list objects in foo where a == 1 it result of the last line evaluated; use to further iterate

Simple Queries with MongoDB:

Set Current Database:
>use DATABASENAME


See Present Database:
> db
DATABASENAME


View collections (tables) under present db:
> db.getCollectionNames()
[ “TABLENAME”, “system.indexes” ]


SQL>Select count(*) from TABLENAME:
NoSQL> db.TABLENAME.count()
238735


SQL>select * from TABLENAME where field1 regexp ‘enrique’:
NoSQL>db.TABLENAME.find( { field1 : /enriqu/i} );


For simple find:
NoSQL>db.TABLENAME.find( { field1 : “value” } );


SQL>Select field1 from table where field2=”%search%”:
NoSQL>db.TABLENAME.find( { field2 : /search/i} , {field1 : 1 } );


SQL>Select All but one (field1) column:
NoSQL>db.TABLENAME.find( { field2 : /search/i} , {field1 : 0 } );


SQL>Select with sort order ascending and descending (1,-1):
NoSQL>db.TABLENAME.find( { ar_name : /enrique/i }, {field1 : 1 } ).sort({ field1 : -1})

MongoDB now seems easy to follow, but I’m still having hard time in taking on the Map/Reduce.
More in the next post; ofcourse documentation home is a good place, else you may export the pdf / html / xml documentation here.

-- Kedar Vaijanapurkar --