[rt-users] How to update CF via scripted mySQL commands

[scott.dalzell]

i have got abit further with this issue and i have got a perl script that
does what i need it to do and edit the mysl database, however when i add
this code into RT as a scrip i get the following error in the error.log.

[Fri Jul 19 12:38:40 2013] [error]: Scrip 491 Prepare failed: Global symbol
"@row" requires explicit package name at (eval 646) line 74.
Global symbol "$sqlQuery" requires explicit package name at (eval 646) line
74.
Global symbol "@row" requires explicit package name at (eval 646) line 75.
Global symbol "$dbh" requires explicit package name at (eval 646) line 87.
Global symbol "$query_master2" requires explicit package name at (eval 646)
line 92.
Global symbol "$sqlQuery" requires explicit package name at (eval 646) line
98. (/opt/rt4/sbin/../lib/RT/Action/UserDefined.pm:65)
[Fri Jul 19 12:38:40 2013] [warning]: Use of uninitialized value $Default in
join or string at /opt/rt4/share/html/Elements/EditCustomFieldWikitext line
52. (/opt/rt4/share/html/Elements/EditCustomFieldWikitext:52)

i prosume this means i am missing some "USE xyz"'s at the start of the
code(see below) to allow RT to understand all the variables like perl can
via the command line


---CODE-START ---

#!/usr/bin/perl -w
use DBI;
 
print "Content-type: text/html\n\n";

#################
###Set variables
#################

my $db ="[database name]";
my $table ="objectcustomfieldvalues";
my $ticket = "16552";
my $cf_RT = "12"; #Bugzilla No in RT (Bug_No)
my $cf_Bug = "13"; #RT no in Bugzilla(Ticket_No)
my $user = "[username]";
my $pass = "[password]";
my $host="[hostname]";


########################################################
###Setting all lines of code used to query mysql database
########################################################
###Copy CF_RT (Bugzilla_NO) and update them in a temp table###

my $query_master1 ="CREATE TEMPORARY TABLE temp_tbl SELECT * FROM
objectcustomfieldvalues WHERE objectid=$ticket and customfield = $cf_RT and
disabled=0";

my $query_master2 ="UPDATE temp_tbl SET Customfield = $cf_Bug";

my $query_master3 ="ALTER TABLE temp_tbl CHANGE ObjectId Content
CHAR(100),CHANGE Content ObjectId CHAR(100)";

###Add updated values to new table
my $query_master4 ="insert into objectcustomfieldvalues
(CustomField,ObjectType,ObjectId,SortOrder,Content,LargeContent,ContentType,ContentEncoding,Creator,Created,LastUpdatedBy,LastUpdated,Disabled)
Select
CustomField,ObjectType,ObjectId,SortOrder,Content,LargeContent,ContentType,ContentEncoding,Creator,Created,LastUpdatedBy,LastUpdated,Disabled
from temp_tbl";

my $query_master5 ="DROP tABLE temp_tbl";


#################
###Set Functions
#################

sub querymysql 
{
#prapare then runs passed mysql commands via perl code
$sqlQuery  = $dbh->prepare($_[0])
or die "Can't prepare $_[0]: $dbh->errstr\n";
$sqlQuery->execute
or die "can't execute the query: $sqlQuery->errstr";
}

sub showtable 
{
#displays tables of previous query
while (@row= $sqlQuery->fetchrow_array()) {
my $tables = $row[0];
print "$tables\n";
print "\n\n";
}
}


####################################################
#Code
####################################################

###Connect to mysql database
$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);


###Update CF (RT_no) on bugzilla bug 
querymysql($query_master1);
querymysql($query_master2);
querymysql($query_master3);
querymysql($query_master4);
querymysql($query_master5);

$sqlQuery->finish;

exit(0);

---CODE-END---

thank you in advance

Scott



--
View this message in context: http://requesttracker.8502.n7.nabble.com/How-to-update-CF-via-scripted-mySQL-commands-tp54656p54666.html
Sent from the Request Tracker - User mailing list archive at Nabble.com.