Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

[Kevin M. <mu...@ge...>]

Elisabetta Manduchi wrote:

> Yes, as far as I know. If the primary_key is in the xml, an update 
> should occur. 


Hmm, this record, which specifies a value for the table's primary key 
(PROTOCOL_ID), experiences a "duplicate column name" error after an 
attempted insert:

<RAD3::Protocol>
  <PROTOCOL_TYPE_ID>12</PROTOCOL_TYPE_ID>
  <PROTOCOL_ID>1</PROTOCOL_ID>
  <NAME>Spot quantification</NAME>
  <PROTOCOL_DESCRIPTION>null</PROTOCOL_DESCRIPTION>
  <URI>http://experimental.act.cmis.csiro.au/Spot/index.php</URI>
  <SOFTWARE_TYPE_ID>637</SOFTWARE_TYPE_ID>
  <SOFTWARE_DESCRIPTION>Spot</SOFTWARE_DESCRIPTION>
  <HARDWARE_TYPE_ID>null</HARDWARE_TYPE_ID>
  <HARDWARE_DESCRIPTION>null</HARDWARE_DESCRIPTION>
  <BIBLIOGRAPHIC_REFERENCE_ID>15</BIBLIOGRAPHIC_REFERENCE_ID>
</RAD3::Protocol>

but this one, which doesn't try to specify a protocol_id, succeeds:

<RAD3::Protocol>
  <PROTOCOL_TYPE_ID>1650</PROTOCOL_TYPE_ID>
  <NAME>Affymetrix GeneArray Scanner</NAME>
  <PROTOCOL_DESCRIPTION>null</PROTOCOL_DESCRIPTION>
  <URI>http://www.affymetrix.com</URI>
  <SOFTWARE_TYPE_ID>null</SOFTWARE_TYPE_ID>
  <SOFTWARE_DESCRIPTION>null</SOFTWARE_DESCRIPTION>
  <HARDWARE_TYPE_ID>609</HARDWARE_TYPE_ID>
  <HARDWARE_DESCRIPTION>"Designed by Agilent Technologies. Monochrome, 
20 5m resolution. The GeneArray Scanner is designed to measure the 
intensity of fluorescence of lab\
eled sample nucleic acid (RNA and DNA) bound to GeneChip probe arrays. 
The scanner has the ability to focus the 488nm argon laser beam to a 
spot size of less than 4 micro\
ns."</HARDWARE_DESCRIPTION>
  
<BIBLIOGRAPHIC_REFERENCE_ID>null</BIBLIOGRAPHIC_REFERENCE_ID>                                                                                                            

</RAD3::Protocol>

Here's the full error output:

sqlExec:
    INSERT INTO RAD3.Protocol (  SOFTWARE_TYPE_ID, row_user_id, 
user_write, group_write, NAME, PROTOCOL_DESCRIPTION, row_project_id, 
group_read, PROTOCOL_ID, row_group_id\
, other_read, PROTOCOL_TYPE_ID, URI, BIBLIOGRAPHIC_REFERENCE_ID, 
SOFTWARE_DESCRIPTION, HARDWARE_TYPE_ID, HARDWARE_DESCRIPTION, 
modification_date, protocol_id, user_read, \
row_alg_invocation_id, other_write )
    VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
SYSDATE, ?, ?, ?, ? )
  bindValues (637, 23, 1, 1, Spot quantification, , 1, 1, 1, 1, 1, 12, 
http://experimental.act.cmis.csiro.au/Spot/index.php, 15, Spot, , , 101, 
1, 306, 0)
DBD::Oracle::st execute failed: ORA-00957: duplicate column name (DBD 
ERROR: error possibly near <*> indicator at char 146 in '
    INSERT INTO RAD3.Protocol (  SOFTWARE_TYPE_ID, row_user_id, 
user_write, group_write, NAME, PROTOCOL_DESCRIPTION, row_project_id, 
group_read, <*>PROTOCOL_ID, row_group\
_id, other_read, PROTOCOL_TYPE_ID, URI, BIBLIOGRAPHIC_REFERENCE_ID, 
SOFTWARE_DESCRIPTION, HARDWARE_TYPE_ID, HARDWARE_DESCRIPTION, 
modification_date, protocol_id, user_rea\
d, row_alg_invocation_id, other_write )
    VALUES   (  :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, 
:p12, :p13, :p14, :p15, :p16, :p17, SYSDATE, :p18, :p19, :p20, :p21 ) ') 
[for Statement "
    INSERT INTO RAD3.Protocol (  SOFTWARE_TYPE_ID, row_user_id, 
user_write, group_write, NAME, PROTOCOL_DESCRIPTION, row_project_id, 
group_read, PROTOCOL_ID, row_group_id\
, other_read, PROTOCOL_TYPE_ID, URI, BIBLIOGRAPHIC_REFERENCE_ID, 
SOFTWARE_DESCRIPTION, HARDWARE_TYPE_ID, HARDWARE_DESCRIPTION, 
modification_date, protocol_id, user_read, \
row_alg_invocation_id, other_write )
    VALUES   (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
SYSDATE, ?, ?, ?, ? ) " with ParamValues: :p1='637', :p2='23', :p3=1, 
:p4=1, :p5='Spot quantification',\
 :p6=undef, :p7='1', :p8=1, :p9='1', :p10='1', :p11=1, :p20='306', 
:p12='12', :p21=0, 
:p13='http://experimental.act.cmis.csiro.au/Spot/index.php', :p14='15', 
:p15='Spot',\
 :p16=undef, :p17=undef, :p18='101', :p19=1] at 
/checkout/GUS/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 146, <GEN0> line 432.

I'm continuing to investigate.  My version of UpdateGusFromXML.pm seems 
to be 1.11, but I realize that it is dependent on a bunch of other modules.

Thanks,
Kevin Murphy

> ---
>
> On Tue, 7 Dec 2004, Kevin Murphy wrote:
>
>> Can UpdateGusFromXML be used for updating as well as inserting records?
>>
>> I was hoping that merely by including the primary key in the XML that 
>> the plugin would take the hint and do an update on the relevant 
>> record.  However, it doesn't seem to like this.
>>
>> Thanks,
>> Kevin Murphy
>>
>>
>>