CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner: Re: [terrykhatri531] Need help with perl script using DBI: Edit Log



Zhris
Enthusiast

Aug 14, 2014, 3:27 PM


Views: 9019
Re: [terrykhatri531] Need help with perl script using DBI

It looks as though your table has a variety of data types to have to deal with. You'd be best off defining your columns in a Perl data structure, hardcoded or possibly via DBI's column_info method, before generating the select and insert statements then binding the values. I think you will also need to consider using Oracles to_date function to convert your string dates into Oracle internal dates.

I cannot test the below, but it roughly represents the approach above:


Code
my $definitions = 
{
EMPLOYEEID => undef, # NOT NULL NUMBER(38)
LASTNAME => undef, # NOT NULL VARCHAR2(20)
FIRSTNAME => undef, # NOT NULL VARCHAR2(10)
TITLE => undef, # VARCHAR2(30)
TITLEOFCOURTESY => undef, # VARCHAR2(25)
BIRTHDATE => { value => "TO_DATE(:BIRTHDATE, 'YYYY-MM-DD HH24:MI:SS')" }, # TIMESTAMP(6)
HIREDATE => { value => "TO_DATE(:HIREDATE, 'YYYY-MM-DD HH24:MI:SS')" }, # TIMESTAMP(6)
ADDRESS => undef, # VARCHAR2(60)
CITY => undef, # VARCHAR2(15)
REGION => undef, # VARCHAR2(15)
POSTALCODE => undef, # VARCHAR2(10)
COUNTRY => undef, # VARCHAR2(15)
HOMEPHONE => undef, # VARCHAR2(24)
EXTENSION => undef, # VARCHAR2(4)
PHOTO => { type => { ora_type=>ORA_BLOB, ora_field=>'PHOTO' } }, # BLOB
NOTES => { type => { ora_type=>ORA_CLOB, ora_field=>'NOTES' } }, # CLOB
REPORTSTO => undef, # NUMBER(38)
PASSWORD => undef, # NOT NULL VARCHAR2(20)
};

my @columns = keys %$definitions;
my @values = map { $definitions->{$_}->{value} // ":$_" } @columns;

$" = ", ";
my $select_sth = $pgh->prepare('SELECT * FROM "Employees"');
my $insert_sth = $ora->prepare("INSERT INTO employees (@columns) VALUES (@values)");

$select_sth->execute();
while (my $row = $select_sth->fetchrow_hashref('NAME_uc'))
{
$insert_sth->bind_param(":$_", $row->{$_}, $definitions->{$_}->{type} // { }) for (@columns);
$insert_sth->execute();
}


Chris


(This post was edited by Zhris on Aug 14, 2014, 7:57 PM)


Edit Log:
Post edited by Zhris (Enthusiast) on Aug 14, 2014, 3:29 PM
Post edited by Zhris (Enthusiast) on Aug 14, 2014, 3:30 PM
Post edited by Zhris (Enthusiast) on Aug 14, 2014, 3:42 PM
Post edited by Zhris (Enthusiast) on Aug 14, 2014, 3:43 PM
Post edited by Zhris (Enthusiast) on Aug 14, 2014, 3:48 PM
Post edited by Zhris (Enthusiast) on Aug 14, 2014, 7:57 PM


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives