transfer data dari mysql ke oracle guna php

[man_anna]

a'kum.. saya nak mintak tolong banyak kat kawan- kawan nie..saya nak buat thesis pasal data conversion. saya nak guna php sebagai GUI untuk transfer data dari mysql to oracle ..harap dapat bantu sangat - sangat

[ayohmang]

data conversion .. ? lehhh .. but kena ada pengeras nya .. $$$$ .. mata duitan ke aku ni ? hheheheh .. (private message or email me your specs' and requirements ... rsktt@yahoo.com.sg)

[man_anna]

a'kum..

berapa pengerasnya...murah sudahlah.kalau ajar boleh

[mnajem]

masukkan juga pengasingan business logic. coding tak perlu database dependent.

kalau database independent senang,kalo nak migrate apa2 pun,tak sampai 1 jam,dah siap.

[ayohmang]

tak semua sql yg digunakan oleh mysql sama dengan oracle ... (walaupun mysql bertungkus lumus untuk mencapai standard sql mcm oracle .. ) kalooo 1 jam aku tak caya lahhhh .. very hype ... statement tu .. :-) ..

mann_anna .. tak lah mahal sangat ... kebetulan aku pun kena buat satu skrip php yg convert mysql ke oracle .. meh saya emel skrip tu ke awak ..

[mnajem]

I hate triggers just because I am not used to it :p
(oracle guna triggers)

[mnajem]

tools commercial utk database migration:
http://www.realsoftstudio.com/screenshots.php
http://www.realsoftstudio.com/overview.php

kampeni aku reseller benda ni.

[man_anna]

ayohmang

nie email saya zman_0577@hotmail.com...tolong hantar skrip tu ye...tima kasih lah ayohmang kerana membantu. ayohmang keje kata mane?

[man_anna]

mnajem

apa nama kampeni kau.. kampenikau x buat migration tool ke

[mnajem]

ada buat,kitaorang penah buat Informix->Postgresql

[apairudin]

Kiriman asal oleh mnajem
I hate triggers just because I am not used to it :p
(oracle guna triggers)

Kebanyakan RDBMS modern ada trigger (termasuk mysql, postgresql, firebird, etc) bukan oracle je.

[man_anna]

boleh terand detail sikit , x faham le

[mnajem]

http://www.onlamp.com/pub/a/php/2004/08/05/dataobjects.html sebagai contoh. Java pun boleh jugak. Haritu discuss tender ngan bro Hamdi dia pun memang practise style ni.

[apairudin]

Please don't confuse the topic.


Kiriman asal oleh mnajem
http://www.onlamp.com/pub/a/php/2004/08/05/dataobjects.html sebagai contoh. Java pun boleh jugak. Haritu discuss tender ngan bro Hamdi dia pun memang practise style ni.
This article is about database abstraction. Discussion pasal tender dengan sapa-sapa pun is not an interest to anyone as far as this topic is concerned, at least for me.

Database abstraction salah satu cara/kaedah/coding style utk buat migration tool, tapi it DOES NOT answer the question:

man_anna
saya nak guna php sebagai GUI untuk transfer data dari mysql to oracle

Anyhow, objective task ni adalah transfer 'data' dari mysql ke oracle, menggunakan PHP.
So you'll need:
1) PHP
2) php-mysql
3) php-oci8 (utk oracle)

Nak ceritakan secara penuh memang impossible la, sebab strucktur database memang tak diberitahu langsung. Tapi secara ringkasnya code tu sepatutnya:
a) Buat connection ke mysql, baca data dari database/table yang dikehendaki, dan simpan dalam satu variable/array. (Hint: SELECT)
b) Buat satu lagi connection ke oracle, dan masukkan data dari variable/array tadi ke database/table dalam oracle pulak (Hint: INSERT)

Note: Kalau struktur database mysql dan oracle tak sama, mungkin kena 'massage' variable/array tadi supaya memenuhi syarat DB structure yang terlibat sebelum (b).

[ayohmang]

'massage' pakai minyak ubat regular expression ..

contohnya , nama field seperti 'access','date','user' adalah reserve word dlm oracle ..

betul spt apa yg apai kata .. sebenarnya byk keje kena buat masa parsing mysql punya sql supaya boleh pakai kat oracle ..

datatype juga perlu diambil kira.

contoh skrip (tp dlm perl) ..



#!/usr/bin/perl
#
# $Header: /home/abbey/m2o/RCS/m2o.pl,v 1.91 2000/12/05 18:26:30 abbey Exp abbey $
#
# Copyright (c) 2000 The Pythian Group, Inc.
# Distributed under the Gnu GPL - see COPYING or www.gnu.org for details.
# usage: m2o.pl < mysqldumpfile.sql > oraclescript.sql
# Contributions solicited! :-)
# Cheers, Paul Vallee
# vallee@pythian.com
# ------------------------------------------------------------------
# 2001.04.20 (upped to ver 1.91)
# Modified to convert some more datatypes Oracle doesnt like.
# binint, tinyint, and timestamp (including sysdate trigger)
# Artem Litvinovich
# al867@columbia.edu
# ------------------------------------------------------------------
# 2000.12.05
# I have modified this script to handle an auto-increment statement
# the code changes occur on the following lines:
# 149-158 and 200-228
# Christian Ullman
# cullman@verio.net

# ===========Some setup here.============
# Rename columns named "access" (illegal in Oracle) to
$rename_access='access_level';

# Rename columns named "date" (illegal in Oracle) to
$rename_date='sys_date';

# Rename tables named "user" (illegal in Oracle) to
$rename_user='users';

# Map columns of mysql type 'text' to the following type
$map_text='varchar2(4000)';

# Default type for enums
$enum_datatype='varchar2(40)';

# Make mySql BIGINT into Oracle INTEGER
$map_bigint='integer';

# Make mySql TINYINT into ANSI SMALLINT
$map_tinyint='smallint';

# ===========End setup section===========

# The following was automatically generated for me by s2p. Yes, this started
# life as a lowly sed script. :-)

eval 'exec /usr/bin/perl -S $0 ${1+"$@"}'
if $running_under_some_shell;
# this emulates #! processing on NIH machines.
# (remove #! line above if indigestible)

eval '$'.$1.'$2;' while $ARGV[0] =~ /^([A-Za-z_0-9]+=)(.*)/ && shift;
# process any FOO=bar switches

$[ = 1; # set array base to 1
$, = ' '; # set output field separator
$\ = "\n"; # set output record separator


# Most of the rest is not s2p now.


while (<>) {
chomp; # strip record separator

if (/^CREATE TABLE/i) {
s/ user / $rename_user /;
@Fld = split(' ', $_, 9999);
$tablename = $Fld[3];
$tablename_length=length($tablename);

}

s/^\s+//;

if (/^PRIMARY KEY/i) {
s/\([0-9]+\)//g;
}

s/,access\)/,$rename_access\)/i;

if (/^ *KEY/) {
@Fld = split(' ', $_, 9999);
s/ user / $rename_user /i;
$indexname_length=length($Fld[2]);

$total_length = $tablename_length + $indexname_length + 1;
$indexname=$tablename.'_'.$Fld[2];
$indexname=substr($indexname,1,30);

$Fld[3] =~ s/\(date\)/\($rename_date\)/i;
$Fld[3] =~ s/\(date,/\($rename_date,/i;
$Fld[3] =~ s/,date,/,$rename_date,/i;
$Fld[3] =~ s/,date\)/,$rename_date\)/i;

$Fld[3] =~ s/\([0-9]+\)//;
$indcount{$indexname} = $indcount{$indexname} + 1;

$append=$indcount{$indexname};

if ( $indcount{$indexname} eq 1 ) {
$localkey="create index " . $indexname . " \non $tablename $Fld[3]\n";
} else {
if ( length($indexname) > 27 ) {
$indexname=substr($indexname,1,29);
}
$indexname=$indexname.$append;
$localkey="create index " . $indexname . " \non $tablename $Fld[3]\n";
}
$localkey =~ s/,$//;
$localkey =~ s/$/\;/;
$key.=$localkey;
s/^/--/g;
$_='';
}
if (/^ *UNIQUE/) {
@Fld = split(' ', $_, 9999);
s/ user / $rename_user /i;
$indexname_length=length($Fld[2]);

$Fld[3] =~ s/\(date\)/\($rename_date\)/i;
$Fld[3] =~ s/\(date,/\($rename_date,/i;
$Fld[3] =~ s/,date,/,$rename_date,/i;
$Fld[3] =~ s/,date\)/,$rename_date\)/i;

$Fld[3] =~ s/\([0-9]+\)//;

$total_length = $tablename_length + $indexname_length + 1;
$indexname=$tablename.'_'.$Fld[2];
$indexname=substr($indexname,1,30);
$indcount{$indexname} = $indcount{$indexname} + 1;

$append=$indcount{$indexname};

if (/^ *UNIQUE/) {
if ( $indcount{$indexname} eq 1 ) {
$localkey="create unique index " . $indexname . " \non $tablename $Fld[3]\n";
} else {
if ( length($indexname) > 27 ) {
$indexname=substr($indexname,1,29);
}

$indexname=$indexname.$append;
$localkey="create unique index " . $indexname. " \non $tablename $Fld[3]\n"; }
}
$localkey =~ s/,$//;
$localkey =~ s/$/\;/;
$key.=$localkey;
s/^/--/g;
$_='';
}
s/int\(/number\(/ig;
s/tinynumber\(/number\(/ig;
s/float/number/g;
s/smallnumber\(/number\(/ig;
s/^#/-- /g;

# Oracle uses a triger and a sequence to accomplish the same task as auto-inc in mySQL
# Here we will catch the name of the col that has an auto inc and push it in to an array
# where we will construct the syntax later

if (/auto_increment/i) {
# s/$/ --auto-increment/;
@fields = split(' ', $_, 9999);
push(@autoincs,"$fields[1],$tablename");
}

if (/timestamp/i) {
@fields = split(' ', $_, 9999);
push(@timestamps,"$fields[1],$tablename");
}

s/auto_increment//ig;
s/unsigned//ig;
s/datetime/date/i;
s/date date/$rename_date date/i;
s/timestamp\(8\)/date/i;
s/timestamp/date/i;
s/varchar\(/varchar2\(/i;
s/date datetime/$rename_date date/i;
s/0000-00-00 00:00:00/0001-01-01 00:00:00/ig;
s/0000-00-00\'/0001-01-01\'/ig;
### s/default \'00000\'/default 0/i;
### s/default \'0\.000000\'/default 0/i;
s/\\\'/\\\'\'/g;
s/^access /$rename_access /i;
s/ access\(/ $rename_access\(/i;
s/ text / $map_text /ig;
s/ bigint / $map_bigint /ig;
s/ tinyint / $map_tinyint /ig;
s/zerofill//i;
s/ text,/ $map_text,/ig;
s/ bigint,/ $map_bigint,/ig;
s/ tinyint,/ $map_tinyint,/ig;
s/DEFAULT ''//ig;
s/^size/att_size/ig;
s/timestamp\(14\)/date/i;
if (/enum\(/i) {
s/DEFAULT /DEFAULT-/ig;
@Fld = split(' ', $_, 9999);
$_ = $Fld[1] . ' ' . $enum_datatype . ' ' . $Fld[3] . ' ' . $Fld[2] . ' ' .
$Fld[4] . ' ' . $Fld[5] . ' ' . $Fld[6] . ' ' . $Fld[7];
s/DEFAULT-/DEFAULT /ig;
s/ enum\(/ check \($Fld[1] in \(/ig;
s/check \(([^\)]+)/check \($1\)/i;
}
if ( $_ ) {
$tblstatement.=$_."\n";
}
if ( /\)\;/) {
$tblstatement =~ s/,\n\)/\n\)/ig;
print $tblstatement;
print "\n";
print $key;
print $unique;

# if the $tblstatment has the 'CREATE TABLE' string, then run through
# the triggers array
if($tblstatement =~ /CREATE TABLE/i ){
srand;
trig_autoinc(@autoincs);
trig_timestamp(@timestamps);
}
$tblstatement='';
$key='';
$unique='';
}
}

sub trig_timestamp {
my @triggers = @_;
my ($trig,$col,$table) = ("","","");
while ($trig = pop(@triggers)){
($col,$table) = split(/\,/,$trig);
# to ensure unique trigger names and sequence names
# we will grab the num of seconds and drop that in the middle
$sec = int(rand 10000);
$trig_name = "stamp_" . $col . "_" . $sec;
$trig_syntax =<<END_OF_ME;
CREATE OR REPLACE TRIGGER $trig_name
BEFORE INSERT ON $table
FOR EACH ROW WHEN (new.$col is null)
BEGIN
SELECT sysdate
INTO :new.$col
FROM dual;
END $trig_name;
/
END_OF_ME
print "$trig_syntax\n";
}
}

sub trig_autoinc {
my @triggers = @_;
my ($trig,$col,$table) = ("","","");
while ($trig = pop(@triggers)){
($col,$table) = split(/\,/,$trig);
# to ensure unique trigger names and sequence names
# we will grab the num of seconds and drop that in the middle
$sec = int(rand 10000);
$trig_seq = "seq_" . $col . "_" . $sec;
$trig_name = "autoinc_" . $col . "_" . $sec;
$trig_syntax =<<END_OF_ME;
CREATE SEQUENCE $trig_seq;
CREATE OR REPLACE TRIGGER $trig_name
BEFORE INSERT ON $table
FOR EACH ROW WHEN (new.$col is null)
BEGIN
SELECT $trig_seq.nextval
INTO :new.$col
FROM dual;
END $trig_name;
/
END_OF_ME
print "$trig_syntax\n";
}
}