Database | edwin's journey

How to Inserting 0 To an AutoIncreament Field on MySql

By edwin / 6 January 2014 6 January 2014 / Database, Programming / database, mysql, util, utility

Let say i have this table,

CREATE TABLE `refproductscale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scale` varchar(255) NOT NULL,
  `del` set('y','n') NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`)
)

I dont know why, but everytime im inserting ‘zero’ as its id, suddenly it changes into an increament number.
But with this script, im able to insert ‘zero’ as its id. This is my script,

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
insert into refproductscale values(0, 'none', 'n');

And this is the result,

All i have to do is use NO_AUTO_VALUE_ON_ZERO before i do insert query. 😉

[Oracle] How to Truncate and Clean All Your Database’s Tables

By edwin / 9 January 2013 9 January 2013 / Database, Programming / database, misc, oracle, Programming

I worked with Oracle for this project, and sometime i need to truncate and clean all my oracle database’s content. So instead of truncate the tables one by one, i found a simple query to generate a simple truncate query.

select 'truncate table ' || table_name || ';' from user_tables

Hope it helped others 🙂

How to Count Number of Tables and Views from Oracle and DB2

By edwin / 17 October 2012 17 October 2012 / Database, Programming / basic, database, db2, oracle, Programming, utility

Several days ago i had a project that made me had to do data migration from DB2 to Oracle.
For validity check, i had to count number of tables and views from DB2 before migrated and number of tables and views on Oracle after ive migrate it.

Okay enough chit-chat, this is my queries.
Count DB2’s number of tables

Select Count(*) from syscat.tables
where tabschema = 'YOUR_SCHEMA_NAME' 
AND type = 'T'

And this is how to count DB2’s View numbers

Select Count(*) from syscat.tables
where tabschema = 'YOUR_SCHEMA_NAME' 
AND type = 'V'

This is how to count number of tables from Oracle

select owner, count(*) from dba_tables
where owner = 'YOUR_OWNER_NAME'
group by owner

And this is how to count number of views from Oracle

select owner, count(*) from dba_views
where owner = 'YOUR_OWNER_NAME'
group by owner

Hope it will help others,
cheers (D)

A Weird PostgreSQL Error : “could not rename file global/pg_database.*** to global/pg_database”

By edwin / 5 July 2012 5 July 2012 / Database, Programming / util, utility

I found a very weird error when starting my PostgreSQL, this is what written in my error log file,

could not rename file global/pg_database.1160 to global/pg_database

very weird, but somehow it happen because of my hard disk is corrupt. I fixed it using windows’ CHKDSK command

CHKDSK C: /r

Weird eh?

Enabling Remote Connection on PostgreSQL

By edwin / 20 May 2012 20 May 2012 / Database, Programming / database, utility

Default Postgresql configuration only allow connection from localhost, if you want your PostgreSQL to accepting connection from other ip, there are some changes that you need to do.

First, you need to open file pg_hba.conf and edit “IPv4 local connections” part. Add other ip that you want to be connected to your database.

# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host 	all 		all 		192.168.1.0/16 		  trust

After that, open file postgresql.conf and do some editing in “listen_addresses” part.

#listen_addresses = 'localhost'		# what IP address(es) to listen on;
listen_addresses = '*'
					# comma-separated list of addresses;
					# defaults to 'localhost', '*' = all
					# (change requires restart)

After restarting your postgresql , it can accept connection from other ip. 😉