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:
Need help with perl script using DBI

 



terrykhatri531
User

Aug 14, 2014, 1:05 AM

Post #1 of 19 (1963 views)
Need help with perl script using DBI Can't Post

Hi,

What I am trying to do is to get the rows from postgresql table and insert them to an oracle table, but I am not getting the syntax right, here is the sample snippet of my script :


Code
  
use strict;
use DBI;
use warnings FATAL => qw(all);



my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle
my @rows;



$pgh->do("SET search_path to northwind") or die;



my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
while (@rows = $sth->fetchrow_hashref()) {
$ora->do("INSERT INTO employees VALUES(@rows)");

}




#connect to postgres
sub pgh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd = 'postgres';
my $pgh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $pgh;
}



#connect to oracle
sub ora {
my $dsn = 'dbi:Oracle:host=localhost;sid=orcl';
my $user = 'nwind';
my $pwd = 'nwind';
my $ora = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $ora;
}


its throwing the following error :


Code
  
DBD::Oracle::db do failed: ORA-00907: missing right parenthesis (DBD ERROR: error possibly near <*> indicator at char 35 in

'INSERT INTO employees VALUES(HASH(0<*>x18eae60))') [for Statement "INSERT INTO employees VALUES(HASH(0x18eae60))"]

at /usr/share/perlproj/cgi-bin/scripts/nwind_pg2ora.pl line 16.


Many thanks for your help !!

Terry


(This post was edited by terrykhatri531 on Aug 14, 2014, 1:09 AM)


FishMonger
Veteran / Moderator

Aug 14, 2014, 6:08 AM

Post #2 of 19 (1955 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

Why are you fetching the row as a hashref but assigning it to an array? Change it to use fetchrow_array.


terrykhatri531
User

Aug 14, 2014, 9:15 AM

Post #3 of 19 (1948 views)
Re: [FishMonger] Need help with perl script using DBI [In reply to] Can't Post

Thanks Ron,

I made changes to the script as under :


Code
 
my @rows;
my $rows =[] ;


my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
while (@rows = $sth->fetchrow_array()) {
$ora->do("INSERT INTO employees VALUES(@rows)");
}


Now I am getting the error :


Code
 
Use of uninitialized value $rows[9] in join or string at /usr/share/perlproj/cgi-bin/scripts/nwind_pg2ora.pl line 17.




There is something else needs to be done to get the @row data without the brackets i.e. [], like in the form of 'value1','value2',...'value n'

Many thanks !!

Terry


FishMonger
Veteran / Moderator

Aug 14, 2014, 9:37 AM

Post #4 of 19 (1944 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

Use Data::Dumper to see what is in @rows to make sure it hold what you expect.

If some fields are undef, then you may need to assign them the '' empty string before doing the insert. Or, if passing undef as a field value in oracle is acceptable, then you could turn of that warning inside the while loop.


Zhris
Enthusiast

Aug 14, 2014, 11:48 AM

Post #5 of 19 (1934 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

Also, I am not entirely certain, but in Oracle I thought insert "strings" must be quoted, and each value comma separated.

I.e. this snippet:

Code
my @row = qw(one two three four 5); 
my $query = "INSERT INTO employees VALUES(@row)";
print $query;


Outputs:

Code
INSERT INTO employees VALUES(one two three four 5)


When it probably needs to output:

Code
INSERT INTO employees VALUES('one', 'two', 'three', 'four', 5)


Chris


terrykhatri531
User

Aug 14, 2014, 11:55 AM

Post #6 of 19 (1932 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

OK I modified my script as under :


Code
 
my @rows;
my $rows;
my $placeholders;

$pgh->do("SET search_path to northwind") or die;

my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
while (@rows = $sth->fetchrow_array()) {
$placeholders = join ", ", ("?") x @rows;
my $ith = $ora->prepare("INSERT INTO employees VALUES ($placeholders)");
print Data::Dumper->Dump([$placeholders]), $/;
$ith->execute(@rows);

}


Now I am getting the following error :


Code
 
DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for insert into a LONG column

(DBD ERROR: error possibly near <*> indicator at char 12 in 'INSERT INTO <*>employees VALUES

(:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18)')

[for Statement "INSERT INTO employees VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

with ParamValues: :p1=7, :p10=undef, :p11="RG1 9SP", :p12="UK", :p13="(71) 555-5598", :p14="465",

:p15='.&#65533;.&#65533;..JFIF.....`.`...&#65533;."Exif..MM.*.......................&#65533;.C....................................

etc ...




To me it looks like my routines are not behaving properly, there are no LONG datatypes in either of the tables and if you look at the error it prints <*> I don't know where its coming from !!



Chris, Can you please have a look at it - Many thanks !!!

Terry


Zhris
Enthusiast

Aug 14, 2014, 12:29 PM

Post #7 of 19 (1922 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

I assume you have set the type of these columns to varchar2, make sure you set the char limit to a suitable value, this error can occur when its exceeded, otherwise change the type to be clob.

Chris


(This post was edited by Zhris on Aug 14, 2014, 12:31 PM)


terrykhatri531
User

Aug 14, 2014, 12:50 PM

Post #8 of 19 (1914 views)
Re: [Zhris] Need help with perl script using DBI [In reply to] Can't Post

Hi Chris,

The problem is with the column PHOTO which in postgres has a datatype of BYTEA and in oracle its BLOB, my script is trying to insert raw data of bytea format to a BLOB column and its resulting in this error.



If can think of a solution from your experience, please do help me get around this issue.

Rgds

Terry


Zhris
Enthusiast

Aug 14, 2014, 1:10 PM

Post #9 of 19 (1908 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

In the DBD::Oracle documentation, there is information with regards to issues when inserting *LOBS via placeholders:

http://search.cpan.org/~pythian/DBD-Oracle-1.17/Oracle.pm#Handling_LOBs

Code
use DBD::Oracle qw(:ora_types); 
$sth->bind_param($idx, $value, { ora_type=>ORA_BLOB, ora_field=>'PHOTO' });


Also, the following information is in the DBI documentation:

http://search.cpan.org/~timb/DBI-1.631/DBI.pm#execute

Quote
If any arguments are given, then execute will effectively call "bind_param" for each value before executing the statement. Values bound in this way are usually treated as SQL_VARCHAR types unless the driver can determine the correct type (which is rare), or unless bind_param (or bind_param_inout) has already been used to specify the type.


This might be the solution. Note if you do need to explicitly define the data type then you will have to bind the params via bind_param* as it won't be possible via execute.

Chris


(This post was edited by Zhris on Aug 14, 2014, 1:32 PM)


terrykhatri531
User

Aug 14, 2014, 1:57 PM

Post #10 of 19 (1884 views)
Re: [Zhris] Need help with perl script using DBI [In reply to] Can't Post

Thanks Chris,

I find it a bit difficult, I will appreciate if you can append my script with what you think should work, below I am giving you the full definition of employees tables both in postgres and oracle, but first my script :


Code
 
#!/usr/local/bin/perl

use strict;
use DBI;
use warnings FATAL => qw(all);
use Data::Dumper;
use DBD::Oracle qw(:ora_types);

my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle
my @rows;
my $rows;

$pgh->do("SET search_path to northwind") or die;
$ora->do("alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd HH24:MI:SS'") or die;

my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();

my $sth_insert;

while (my @cols = $sth->fetchrow_array()) {
$sth_insert ||= do {
my $placeholders = join ", ", ("?") x @cols;
$ora->prepare("INSERT INTO employees VALUES ($placeholders)");
};

$sth_insert->execute(@cols);
}

#connect to postgres
sub pgh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd = 'postgres';
my $pgh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $pgh;
}

#connect to oracle
sub ora {
my $dsn = 'dbi:Oracle:host=localhost;sid=orcl';
#my $dsn = 'DBI:Oracle:local';
my $user = 'nwind';
my $pwd = 'nwind';
my $ora = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $ora;
}


Employees table in postgres :


Code
 
EmployeeID | integer | not null default nextval('"Employees_EmployeeID_
seq"'::regclass)
LastName | character varying(20) | not null
FirstName | character varying(10) | not null
Title | character varying(30) |
TitleOfCourtesy | character varying(25) |
BirthDate | timestamp without time zone |
HireDate | timestamp without time zone |
Address | character varying(60) |
City | character varying(15) |
Region | character varying(15) |
PostalCode | character varying(10) |
Country | character varying(15) |
HomePhone | character varying(24) |
Extension | character varying(4) |
Photo | bytea |
Notes | text |

ReportsTo | integer |
Password | character varying(20) | not null


Employees table in Oracle


Code
 
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEEID NOT NULL NUMBER(38)
LASTNAME NOT NULL VARCHAR2(20)
FIRSTNAME NOT NULL VARCHAR2(10)
TITLE VARCHAR2(30)
TITLEOFCOURTESY VARCHAR2(25)
BIRTHDATE TIMESTAMP(6)
HIREDATE TIMESTAMP(6)
ADDRESS VARCHAR2(60)
CITY VARCHAR2(15)
REGION VARCHAR2(15)
POSTALCODE VARCHAR2(10)
COUNTRY VARCHAR2(15)
HOMEPHONE VARCHAR2(24)
EXTENSION VARCHAR2(4)
PHOTO BLOB
NOTES CLOB

REPORTSTO NUMBER(38)
PASSWORD NOT NULL VARCHAR2(20)


Please do help me out on this, thank you very much !!

Rgds.

Terry


Zhris
Enthusiast

Aug 14, 2014, 3:27 PM

Post #11 of 19 (1871 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

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)


terrykhatri531
User

Aug 14, 2014, 5:39 PM

Post #12 of 19 (1859 views)
Re: [Zhris] Need help with perl script using DBI [In reply to] Can't Post

Chris, thank you very much, trust me I don't have words to thank you enough, you're one great person !!

Rgds.

Terry


Zhris
Enthusiast

Aug 14, 2014, 8:00 PM

Post #13 of 19 (1852 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

No problem, just mentioning that I have just updated my code with a more refined version.

Chris


terrykhatri531
User

Aug 20, 2014, 10:28 AM

Post #14 of 19 (1620 views)
Re: [Zhris] Need help with perl script using DBI [In reply to] Can't Post

Hi Chris,

I just want to check with you if the following script will work or not, I am not in a position to exexute it yet however I want to make sure it does work when I run it - Thank you !!


Code
  
#!/usr/local/bin/perl

use CGI;
use strict;
use DBI;
use warnings FATAL => qw(all);

my $q = new CGI;
my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle
my $sth;
my $dth;
my $placeholders;my @rows;

my @data=();
my @tables = qw!departments employees dept_emp dept_manager titles salaries!;
for my $t (@tables){
push @data,$q->param($t) || '' ;

push @data;

$sth = $pgh->prepare("SELECT * FROM @data");
$sth->execute();

while (my @rows = $sth->fetchrow_array()) {
$placeholders = join ", ", ("?") x @rows;
$dth = $ora->prepare("INSERT INTO @data VALUES($placeholders)");
$dth->execute(@rows);
}
}


#connect to postgres
sub pgh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd = 'postgres';
my $pgh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $pgh;
}


#connect to oracle
sub ora {
my $dsn = 'dbi:Oracle:host=localhost;sid=orcl';
my $user = 'emp';
my $pwd = 'emp';
my $ora = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $ora;
}


Many thanks !!

Terry


(This post was edited by terrykhatri531 on Aug 20, 2014, 10:42 AM)


Zhris
Enthusiast

Aug 20, 2014, 1:30 PM

Post #15 of 19 (1608 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

Its difficult to say whether it will work as desired or not, but my best guess is no.

1) I have no idea what the value of $q->param($t) is per iteration.
2) push @data should result in the error "Useless use of push with no values".
3) As a result of 1), I have no idea what value(s) @data holds. You use @data in both your postg and oracle queries, but I believe there should be differences in syntax.
4) Are you sure the order the columns are returned when selected correlates with the order they need to be inserted. My guess is this is reliant on the order you created the columns, which you have probably done equally for both databases, therefore will be ok.
5) Will you encounter issues with inserting into date / clob / blob type columns again.

Chris


terrykhatri531
User

Aug 20, 2014, 2:09 PM

Post #16 of 19 (1604 views)
Re: [Zhris] Need help with perl script using DBI [In reply to] Can't Post


In Reply To
Its difficult to say whether it will work as desired or not, but my best guess is no.

1) I have no idea what the value of $q->param($t) is per iteration.
2) push @data should result in the error "Useless use of push with no values".
3) As a result of 1), I have no idea what value(s) @data holds. You use @data in both your postg and oracle queries, but I believe there should be differences in syntax.
4) Are you sure the order the columns are returned when selected correlates with the order they need to be inserted. My guess is this is reliant on the order you created the columns, which you have probably done equally for both databases, therefore will be ok.
5) Will you encounter issues with inserting into date / clob / blob type columns again.

Chris


Hi Chris, what I am trying to do is to select one table at a time with @data and read from that postgresql table and write to the coressonding oracle table, looks like I am not doing it right Unsure, can you please have another look at it :

my @data=();
my @tables = qw!departments employees dept_emp dept_manager titles salaries!;
for my $t (@tables){
push @data,$q->param($t) || '' ;

push @data;


To your question no. 5 - no there are no BLOB or CLOB types in this schema.

Many thanks !!
Terry


Zhris
Enthusiast

Aug 20, 2014, 3:16 PM

Post #17 of 19 (1587 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

Whats the purpose of $q->param($t)?

You could just extend the code I provided earlier so that the definitions hash supports multiple tables / iterate through.

Chris


(This post was edited by Zhris on Aug 20, 2014, 3:16 PM)


terrykhatri531
User

Aug 21, 2014, 7:37 AM

Post #18 of 19 (1503 views)
Re: [Zhris] Need help with perl script using DBI [In reply to] Can't Post

Ok I got your point - made the changes and it works now !




Code
 
my @tables = qw!departments employees dept_emp dept_manager titles salaries!;
for my $t (@tables){
$datestring = localtime();
print "[$datestring] Starting to process table $t\n";
$sth = $pgh->prepare("SELECT * FROM $t");
$sth->execute();
while (my @rows = $sth->fetchrow_array()) {
$placeholders = join ", ", ("?") x @rows;
$dth = $ora->prepare("INSERT INTO $t VALUES($placeholders)");
$dth->execute(@rows);
}
$datestring = localtime();
print "[$datestring] Finished processing table $t\n\n";
}


Thanks.

Terry


Zhris
Enthusiast

Aug 21, 2014, 8:33 AM

Post #19 of 19 (1492 views)
Re: [terrykhatri531] Need help with perl script using DBI [In reply to] Can't Post

Nice one, you fixed yourself with very little help! Based on your new script I see that there are no necessary differences in syntax for table name between postg and oracle, which I had previously assumed based on your previous code snippets i.e. upper cased first letter and quote wrap, having never used postg.

Just a few pointers you may find useful:

1) If you want a higher resolution timer, look into Time::HiRes, it will give seconds and milliseconds.
2) There is no need to prepare your insert statement for each row being inserted, construct the placeholders and prepare the insert statement before the while loop, this will improve performance. You will however have to know how many columns there are per table beforehand in order to construct the appropriate number of placeholders.
3) Just been nit picky now but your array namespace @rows is not technically correct, since the data held represents a single row, therefore would be better named @row.

Chris


(This post was edited by Zhris on Aug 21, 2014, 8:41 AM)

 
 


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

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