Re: [rt-users] How to update CF via scripted mySQL commands
On 07/22/2013 09:40 PM, scott.dalzell wrote: 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 Description: Perl program
Re: [rt-users] How to update CF via scripted mySQL commands
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 Sent from the Request Tracker - User mailing list archive at Nabble.com.
Re: [rt-users] How to update CF via scripted mySQL commands
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.
Re: [rt-users] How to update CF via scripted mySQL commands
On 07/19/2013 06:10 AM, scott.dalzell wrote: 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.
[rt-users] 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 Sent from the Request Tracker - User mailing list archive at Nabble.com.
