How to update CF via scripted mySQL commands

Hey guys

What i am trying to do is link 1 ticket CF (Bug_No) to another Ticket CF
(Ticket_No) by writing a script that will update the CF (Ticket_No) via the
mysql database when the CF (Bug_No) is updated

The reason i need it done in this manor is becasue this is a trial before
doing the same thing but between our RT and bugzilla systems where i will
have to update the CF(Ticket_No) in bugzilla via its mysql database on a
different server.

i already know how to do the custom donditions and manual update the mysql
databases for the action but how do i turn something like the following into
a script for mysql

where
objectid=16552 - test ticket used in example
customfield=12 - CF(Bug_No)
customfield=13 - CF(Ticket_No)

manual mysql commands
CREATE TEMPORARY TABLE temp_tbl SELECT * FROM objectcustomfieldvalues WHERE
objectid=16552 and customfield = ‘12’;
UPDATE objectcustomfieldvalues set disabled=1 where objectid=16552 and
customfield=13;
UPDATE temp_tbl SET Customfield = 13;
select * from temp_tbl;
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;
DROP tABLE temp_tbl;

thank you in advanced

Scott

View this message in context: http://requesttracker.8502.n7.nabble.com/How-to-update-CF-via-scripted-mySQL-commands-tp54656.html

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

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.

Updating the RT database via raw SQL statements is a recipe for
disaster. Do not do it. Use the Perl API RT provides. If you don’t,
you WILL corrupt your RT database.

[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

No, those errors are because RT runs under strict. Put “use strict;” at
the top of your command-line code and fix the errors first.

Thanks for pointing that out. i think ill try to do the API route as i cant
afford to corrupt the database.

Can the RT API be used to modify a database that is not being used by RT

Just that my final goal is to modify a field in our Bugzilla database (also
MYsql) when an RT CF is given a bug number

View this message in context: http://requesttracker.8502.n7.nabble.com/How-to-update-CF-via-scripted-mySQL-commands-tp54656p54676.html

Thanks for pointing that out. i think ill try to do the API route as i cant
afford to corrupt the database.

Can the RT API be used to modify a database that is not being used by RT.

When you load the RT perl modules they’ll read RT_SiteConfig.pm for
database details and use that database.

It isn’t really clear what you want here, though. Do you want to update
more than one database at a time? If so, nothing stops you from loading
DBI and opening direct DBI connections to other databases from within a
Perl script that also uses the RT APIs.

Just that my final goal is to modify a field in our Bugzilla database (also
MYsql) when an RT CF is given a bug number

You should do that by adding a scrip in RT.

Write a condition that checks whether the CF has been changed. I’ve
attached a condition I’m using that you can adapt if you want.

Then write an action that opens a Perl DBI connection to your Bugzilla
and issues the appropriate UPDATEs against Bugzilla. Or, better, use any
HTTP web service API bugzilla provides to do the update via Bugzilla
from within the RT scrip as a simple web service call - usually done
with LWP::Simple.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

CustomFieldSetTo.pm (2.77 KB)