I've created a perl script to run a query to an Oracle DB and then dump the results to a MySQL DB, but I'm getting errors because of the syntax in the perl script. Can anyone help me out with this problem?
Error msg -
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ticket_number', 'company_name', ,'problem_category', 'problem_s at dashboard_active_voip_tickets.pl line 81.
Values coming from Oracle query -
Ticket_Number, Company_Name, Problem_Category, Problem_Subcategory, Team, Assignee, Ticket_State, Priority, Creation_Date, Modified_Date, Ticket_Duration, Ticket_Relationship, Modified_By
Values in MySQL Table -
ticket_number, company_name, ,problem_category, problem_subcategory, team, assignee, ticket_state, priority, creation_date, modified_date, ticket_duration, ticket_relationship, modified_by
************PERL SCRIPT************
#!/usr/local/bin/perl
use strict;
use lib "/usr/local/lib/perl5/site_perl/5.6.1";
$ENV{ORACLE_HOME}="/oracle/8.1.6";
$ENV{"LD_LIBRARY_PATH"} .= "/usr/lib:/usr/local/lib:/opt/gnu/lib:/opt/j2sdk1_3_1_01/jre/lib/sparc:/oracle/8.1.6/lib:/usr/local/ssl/lib";
use DBI;
use DBD::Oracle;
# Database Variables
my $osso_db_name="OSSO";
my $osso_userid="userid";
my $osso_password="password";
my $mysql_db_name="reporting";
my $hostname="server";
my $mysql_db_table="active_tickets";
my $mysql_userid="userid";
my $mysql_password="password";
# Connect to OSSO Database
my $osso_dbh = DBI->connect("DBI:Oracle:$osso_db_name", "$osso_userid", "$osso_password")
or die "Could not connect to database: " . DBI->errstr;
# Build SQL Query
my $osso_sql = qq{ select tta.problem_id "Ticket_Number"
,replace(ttc.gobeam_company_name,',',' ') "Company_Name"
,tpc.description "Problem_Category"
,NVL(tpcc.description,'None Provided') "Problem_Subcategory"
,decode(tta.problem_category,96,'Team 1',896,'Team 1',101,'Team 1',75,'Team 1',15,'Team 1',823,'Team 1',38,'Team 2',61,'Team 2',67,'Team 2',20,'Team 2',30,'Team 2',784,'Team 2',46,'Team 3',82,'Team 3',87,'Team 3',1,'Team 3',7,'Team 3',12,'Team 3',714,'Team 3',826,'Team 3',811,'Resolve','Team 1') "Team"
,tta.assigned_to "Assignee"
,tta.ticket_state "Ticket_State"
,tta.priority "Priority"
,to_char(tta.creation_date,'yyyy-mm-dd hh:mm:ss') "Creation_Date"
,to_char(tta.modified_date,'yyyy-mm-dd hh:mm:ss') "Modified_Date"
,trunc(sysdate - tta.creation_date) "Ticket_Duration"
,decode(tta.tt_relationship_id,1,'Master',2,'Child',3,'Unassociated') "Ticket_Relationship"
,ttt.last_modified_by "Modified_By"
from ticket.tt_activeticket tta
,ticket.tt_customerinfo ttc
,ticket.tt_troubletickets ttt
,ticket.problem_category tpc
,ticket.problem_category tpcc
where tta.problem_id = ttt.problem_id
and tta.problem_id = ttc.problem_id(+)
and tta.problem_category = tpc.problem_category_id
and tta.problem_subcategory = tpcc.problem_category_id(+)
and tta.assigned_to_dept_id = 105
};
my $osso_sth = $osso_dbh->prepare( $osso_sql );
$osso_sth->execute();
# Declare all osso variables
my ($Ticket_Number, $Company_Name, $Problem_Category, $Problem_Subcategory, $Team, $Assignee, $Ticket_State, $Priority, $Creation_Date, $Modified_Date, $Ticket_Duration, $Ticket_Relationship, $Modified_By);
# Bind the columns to variables per row
$osso_sth->bind_columns ( undef, \$Ticket_Number, \$Company_Name, \$Problem_Category, \$Problem_Subcategory, \$Team, \$Assignee, \$Ticket_State, \$Priority, \$Creation_Date, \$Modified_Date, \$Ticket_Duration, \$Ticket_Relationship, \$Modified_By);
# make connection to database
my $mysql_dbh = DBI->connect("DBI:mysql:$mysql_db_name:$hostname", "$mysql_userid", "$mysql_password")
or die "Could not connect to database: ". DBI->errstr;
# Declare new mysql variables
my ($mysql_sql, $res);
# While loop over the returning data from OSSO preparing it to insert into MySQL database on Utopia
while( $osso_sth->fetch() ) {
# Chomp all the data
chomp($Ticket_Number, $Company_Name, $Problem_Category, $Problem_Subcategory, $Team, $Assignee, $Ticket_State, $Priority, $Creation_Date, $Modified_Date, $Ticket_Duration, $Ticket_Relationship, $Modified_By);
# Insert into MySQL Database query
$mysql_sql = qq { insert into $mysql_db_table ( 'ticket_number', 'company_name', 'problem_category', 'problem_subcategory', 'team', 'assignee', 'ticket_state', 'priority', 'creation_date', 'modified_date', 'ticket_duration', 'ticket_relationship', 'modified_by' ) values('$Ticket_Number', '$Company_Name', '$Problem_Category', '$Problem_Subcategory', '$Team', '$Assignee', '$Ticket_State', '$Priority', '$Creation_Date', '$Modified_Date', '$Ticket_Duration', '$Ticket_Relationship', '$Modified_By') };
# Actual insert into Utopia's MySQL
$res = $mysql_dbh->do($mysql_sql)
}
# Finish the OSSO query, and disconnect from both databases.
$osso_sth->finish();
$osso_dbh->disconnect();
$mysql_dbh->disconnect();
exit;
eg replace 'ticket_number'
with `ticket_number`,`company_name`,
leave the values as is though
values('$Ticket_Number', ....
