Re: [Gusdev-gusdev] LoadNRDB newbie questions:

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hey Debbie
Thanks for the reply.  I tried to run the plugin with these arguments:

**********************************************************************
 ga GUS::Common::Plugin::LoadNRDB --testnumber1=10 --testnumber2=10
--nrdb=/scratch/NCBI/nr
--gitax=/scratch/luchtan/taxonomy/gi_taxid_prot.dmp --extDbRelId=135
--verbose --maketemp --temp_login=luchtan --temp_password=xxxxx
--dbi_str='dbi:Oracle:host=mango.ctegd.uga.edu;sid=GUS' --plugin --delete
--sourceDB 'GENBANK(NRDB):gb,EMBL DATA LIBRARY (NRDB):emb,DDBJ
(NRDB):dbj,NBRF PIR (NRDB):pir,PROTEIN RESEARCH FOUNDATION
(NRDB):prf,SWISS-PROT (NRDB):sp,BROOKHAVEN PROTEIN DATA BANK
(NRDB):pdb,PATENTS (NRDB):pat,GENINFO BACKBONE ID (NRDB):bbs,GENERAL
DATABASE IDENTIFIER (NRDB):gnl,NCBI REFERENCE SEQUENCE (NRDB):ref,LOCAL
SEQUENCE IDENTIFIER:lcl,GENPEPT:genpept'
***********************************************************************

I am a little confused about what user to set the select permissions to
instead of 'gusrw'.  I tried to code it as 'luchtan', but it said that I
couldn't grant/revoke priveleges to/from myself:

***********************************************************************
DBD::Oracle::db do failed: ORA-01749: you may not GRANT/REVOKE privileges
to/from yourself (DBD ERROR: OCIStmtExecute) at
/home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 222
***********************************************************************

and then I get all these errors like:

***********************************************************************
DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
("LUCHTAN"."NRDBTEMP"."EXTERNAL_DB_REL_ID") (DBD ERROR: OCIStmtExecute) at
/home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 271, <NRDB>
line 1.
DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
("LUCHTAN"."NRDBTEMP"."EXTERNAL_DB_REL_ID") (DBD ERROR: OCIStmtExecute) at
/home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 271, <NRDB>
line 11.
************************************************************************

Which I assume is a result of me not knowing who to grant permissions to.
In my gus config file there is a readOnlyDatabaseLogin.  Should it be that
user?

So the plugin still ends up crashing.  Here is the rest of the output:
(Any help would be much, much appreciate)
**********************************************************************

prepareAndExecute: select max(set_num) from luchtan.NRDBTemp
Thu May 29 16:08:30 2003	 entries in temp table

RetrieveFromDB: select * from DoTS.ExternalAASequence where  description =
? and  external_database_release_id = ? and  secondary_identifier = ? and
source_id = ?
  bindValues (unnamed protein product [Oryza sativa (japonica
cultivar-group)], 135, 7228451, BAA92411.1)

RetrieveFromDB: select * from DoTS.NRDBEntry where
external_database_release_id is NULL and  source_id = ?
  bindValues (BAA92411.1)

sqlExec:
    INSERT INTO DoTS.ExternalAASequence (  description, row_user_id,
user_write, group_write, secondary_identifier, row_project_id,
external_database_release_id, subclass_view, group_read, sequence,
aa_sequence_id, row_group_id, other_read, length, source_id,
modification_date, user_read, row_alg_invocation_id, other_write )
    VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, ?,
?, ? )
  bindValues (unnamed protein product [Oryza sativa (japonica
cultivar-group)], 6, 1, 1, 7228451, 2, 135, ExternalAASequence, 1,
MCSYIRYDTPKLFTHVTKTPPKNQVSNSINDVGSRRATDRSVASCSSEKSVGTMSVKNASSISFEDIEKSISNWKIPKVNIKEIYHVDTDIHKVLTLNLQTSGYELELGSENISVTYRVYYKAMTTLAPCAKHYTPKGLTTLLQTNPNNRCTTPKTLKWDEITLPEKWVLSQAVEPKSMDQSEVESLIETPDGDVEITFASKQKAFLQSRPSVSLDSRPRTKPQNVVYATYEDNSDEPSISDFDINVIELDVGFVIAIEEDEFEIDKDLLKKELRLQKNRPKMKRYFERVDEPFRLKIRELWHKEMREQRKNIFFFDWYESSQVRHFEEFFKGKNMMKKEQKSEAEDLTVIKKVSTEWETTSGNKSSSSQSVSPMFVPTIDPNIKLGKQKAFGPAISEELVSELALKLNNLKVNKNINEISDNEKYDMVNKIFKPSTLTSTTRNYYPRPTYADLQFEEMPQIQNMTYYNGKEIVEWNLDGFTEYQIFTLCHQMIMYANACIANGNKEREAANMIVIGFSGQLKGWWNNYLNETQRQEILCAVKRDDQGRPLPDRDGNGNPTELKEGFHMEEKDEPIQEDDQVVGTIQKYTKQKWYAEVMYRFIDGSYFQHITLIDSGADVNCIREDEILDQLVQTKREQVVNSIYLHDNSFPKSMDLPDQKITEKRAKLQDIPHHEERLLDYREKKSRDGQDKLPMEVEQSMATNKNTKILLRAWLLST,
2, 3, 1, 719, BAA92411.1, 1, 252, 0)
 DbiHandle:sqlExec:insert succeeded 1 row(s)

sqlExec:
    INSERT INTO DoTS.NRDBEntry (  description, row_user_id, user_write,
group_write, nrdb_entry_id, is_preferred, row_project_id, taxon_id,
external_database_release_id, group_read, aa_sequence_id, row_group_id,
other_read, gid, source_id, modification_date, user_read,
row_alg_invocation_id, other_write )
    VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, SYSDATE, ?,
?, ? )
  bindValues (unnamed protein product [Oryza sativa (japonica
cultivar-group)], 6, 1, 1, 2, 1, 2, 142988, 1, 2, 3, 1, 7228451,
BAA92411.1, 1, 252, 0)
DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
("DOTS"."NRDBENTRY"."EXTERNAL_DATABASE_RELEASE_ID") (DBD ERROR:
OCIStmtExecute) at /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line
144, <NRDB> line 11.

sqlExec:
    UPDATE  Core.AlgorithmInvocation
    SET
	end_time = SYSDATE,
	row_alg_invocation_id = ?,
	modification_date = SYSDATE
    WHERE   algorithm_invocation_id = ?
  bindValues (252, 252)
 DbiHandle:sqlExec:insert succeeded 1 row(s)

 SQL ERROR!! involving

    INSERT INTO DoTS.NRDBEntry (  description, row_user_id, user_write,
group_write, nrdb_entry_id, is_preferred, row_project_id, taxon_id,
external_database_release_id, group_read, aa_sequence_id, row_group_id,
other_read, gid, source_id, modification_date, user_read,
row_alg_invocation_id, other_write )
    VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, SYSDATE, ?,
?, ? )
 Values: unnamed protein product [Oryza sativa (japonica cultivar-group)],
6, 1, 1, 2, 1, 2, 142988, 1, 2, 3, 1, 7228451, BAA92411.1, 1, 252, 0 at
/home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 184

GUS::ObjRelP::DbiDbHandle::death('GUS::ObjRelP::DbiDbHandle=HASH(0x85e7ea4)',
'^J SQL ERROR!! involving^J ^J    INSERT INTO DoTS.NRDBEntry (  desc...')
called at /home/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147

GUS::ObjRelP::DbiDbHandle::sqlExec('GUS::ObjRelP::DbiDbHandle=HASH(0x85e7ea4)',
'GUS::ObjRelP::DbiDbHandle::st=HASH(0x417b0b58)', 'ARRAY(0x419d7d00)', '^J
INSERT INTO DoTS.NRDBEntry (  description, row_user_id, use...') called at
/home/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm line 674

GUS::ObjRelP::DbiRow::quote_and_insert('GUS::Model::DoTS::NRDBEntry=HASH(0x1043ae48)',
'HASH(0xfd8bd54)') called at /home/gus_home/lib/perl/GUS/ObjRelP/DbiRow.pm
line 621

GUS::ObjRelP::DbiRow::insert('GUS::Model::DoTS::NRDBEntry=HASH(0x1043ae48)')
called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1677

GUS::Model::GusRow::submit('GUS::Model::DoTS::NRDBEntry=HASH(0x1043ae48)',
undef, 1) called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1765

GUS::Model::GusRow::submitChildrenInClass('GUS::Model::DoTS::ExternalAASequence=HASH(0xf24a708)',
'GUS::Model::DoTS::NRDBEntry') called at
/home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1746

GUS::Model::GusRow::submitAllChildren('GUS::Model::DoTS::ExternalAASequence=HASH(0xf24a708)')
called at /home/gus_home/lib/perl/GUS/Model/GusRow.pm line 1684

GUS::Model::GusRow::submit('GUS::Model::DoTS::ExternalAASequence=HASH(0xf24a708)')
called at /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 345

GUS::Common::Plugin::LoadNRDB::makeNRDBAndExternalAASequence('GUS::Common::Plugin::LoadNRDB=HASH(0x84bf844)',
'HASH(0x84f5eb4)', 'HASH(0x84f5bf0)', 135) called at
/home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 127

GUS::Common::Plugin::LoadNRDB::run('GUS::Common::Plugin::LoadNRDB=HASH(0x84bf844)',
'HASH(0x8765564)') called at
/home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 389
	eval {...} called at
/home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 385

GUS::PluginMgr::GusApplication::doMajorMode_Run('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
'GUS::Common::Plugin::LoadNRDB') called at
/home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 284

GUS::PluginMgr::GusApplication::doMajorMode('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
'GUS::Common::Plugin::LoadNRDB') called at
/home/gus_home/lib/perl/GUS/PluginMgr/GusApplication.pm line 193

GUS::PluginMgr::GusApplication::parseAndRun('GUS::PluginMgr::GusApplication=HASH(0x80fbb0c)',
'ARRAY(0x8105184)') called at /home/gus_home/bin/ga line 11

Michael Luchtan
http://www.cs.uga.edu/~luchtan

On Thu, 29 May 2003, Deborah F. Pinney wrote:

>
> Hi Michael,
>
> These are the arguments that I used:
>
> --temp_login "pinney" --sourceDB 'GENBANK
> (NRDB):gb,EMBL DATA LIBRARY (NRDB):emb,DDBJ (NRDB):dbj,NBRF PIR
> (NRDB):pir,PROTEIN RESEARCH FOUNDATION (NRDB):prf,SWISS-PROT
> (NRDB):sp,BROOKHAVEN PROTEIN DATA BANK (NRDB):pdb,PATENTS
> (NRDB):pat,GENINFO BACKBONE ID (NRDB):bbs,GENERAL DATABASE IDENTIFIER
> (NRDB):gnl,NCBI REFERENCE SEQUENCE (NRDB):ref,LOCAL SEQUENCE
> IDENTIFIER:lcl,GENPEPT:genpept' --temp_password "xxxxxxx" --dbi_str
> 'dbi:Oracle:host=erebus.pcbi.upenn.edu;sid=gusdev'
> --gitax /ptolemy/gi_taxid_prot.dmp --nrdb /ptolemy/nr --extDbRelId 4194
> --maketemp --plugin --delete
>
>
> The temp table is necessary for updating and even though you are only
> filling the table for the first time, you will need to include this. I
> think Jonathan answered the general questions about temp tables and why
> you might need them. The plugin actually truncates, drops, creates the
> nrdbtemp table and grants select permission to gusrw. That last one is an
> error that will be fixed in the future but for now you may want to hard
> code the appropriate login. I included --dbi_str  because I thought that
> this could and might vary from your config file.
>
> The plugin runs in three sections, make the temp table, insert and update
> into NRDBEntry and ExternalAASequence, and delete obsolete entries in
> those two tables. Each section can be run separately and each is specified
> as --maketemp, --plugin, --delete. They should be run sequentially but
> for testing and in case of failure, they can be run at separate times. You
> could skip the --delete.
>
> I hope this helps.
>
>                                                         Debbie
>
> On Thu, 29 May 2003, MICHAEL LUCHTAN wrote:
>
> > Hello All:
> > I am trying to load the genbank nrdb database into GUS so that I will have
> > what I blast against in the db so that I can load blast similarities.
> > I have some questions regarding this plugin.
> > What is the temp table for?  Why is it necessary?  Why must I proide
> > another login to the database to create this table?  I would think that it
> > could just use the login/information provided in my gus config file.
> >
> > Can anyone clarify the args --plugin, and --maketemp?  i.e. what happens
> > if these are/are not provided?
> >
> > Which arguements are required?
> >
> >
> > Also, I have tried to run the plugin with the following command:
> > ga GUS::Common::Plugin::LoadNRDB --testnumber1=10 --testnumber2=10
> > --nrdb=/scratch/NCBI/nr
> > --gitax=/scratch/luchtan/taxonomy/gi_taxid_prot.dmp --extDbRelId=135
> > --verbose --maketemp --temp_login=luchtan --temp_password=*******
> > --dbi_str=dbi:Oracle:host=mango.ctegd.uga.edu;sid=GUS
> >
> > But I get an error like this:
> >
> > Thu May 29 12:01:35 2003	**COMMIT TURNED OFF**
> >
> > Thu May 29 12:01:35 2003	Testing on 10 insertions
> >                 into temp table
> >
> > Thu May 29 12:01:35 2003	Testing on 10 insertions
> >                 into NRDBEntry/ExternalAASequence
> >
> > There are 0 entries in the database hash
> > Thu May 29 12:02:40 2003	There are 2900500 gi to taxon_id pairs
> >
> > DBI->connect(host=mango.ctegd.uga.edu) failed: ORA-12154: TNS:could not
> > resolve service name (DBD ERROR: OCIServerAttach) at
> > /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 216
> >
> >
> > sqlExec:
> >     UPDATE  Core.AlgorithmInvocation
> >     SET
> > 	end_time = SYSDATE,
> > 	row_alg_invocation_id = ?,
> > 	modification_date = SYSDATE
> >     WHERE   algorithm_invocation_id = ?
> >   bindValues (245, 245)
> >  DbiHandle:sqlExec:insert succeeded 1 row(s)
> > Can't call method "do" on an undefined value at
> > /home/gus_home/lib/perl/GUS/Common/Plugin/LoadNRDB.pm line 219.
> >
> >
> >
> > I might be kind of "fresh" with regards to my interaction with Oracle, so
> > I am not sure if the dbi_str argument is in the correct format.  Can
> > anyone help me with this?
> >
> >
> > Michael Luchtan
> > http://www.cs.uga.edu/~luchtan
> >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.net email is sponsored by: eBay
> > Get office equipment for less on eBay!
> > http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> > _______________________________________________
> > Gusdev-gusdev mailing list
> > Gus...@li...
> > https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
> >
>
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: eBay
> Get office equipment for less on eBay!
> http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>