Thread: [Gusdev-gusdev] UpdateGusFromXML and updating records

Brought to you by: delagoya, jcrabtree, msaffitz, stevefischer, stoeckert

gusdev-gusdev

[Gusdev-gusdev] UpdateGusFromXML and updating records

From: Kevin M. <mu...@ge...> - 2004-12-07 17:16:37

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

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Elisabetta M. <man...@pc...> - 2004-12-07 17:41:29

Yes, as far as I know. If the primary_key is in the xml, an update should 
occur. We do use this feature in the RAD StudyAnnotator for the Image 
Files adn Raw Data Files forms.
Elisabetta

---

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
>
>
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now. 
> http://productguide.itmanagersjournal.com/
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Kevin M. <mu...@ge...> - 2004-12-07 18:04:55

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
>>
>>
>>

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Angel P. <an...@pc...> - 2004-12-07 18:23:09

The GUS XML parser is not a real XML parser. The order of the columns 
matter. Put <PROTOCOL_ID> before <PROTOCOL_TYPE_ID>

angel
Kevin Murphy wrote:

> 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
>>>
>>>
>>>
>
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now. 
> http://productguide.itmanagersjournal.com/
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Kevin M. <mu...@ge...> - 2004-12-07 19:55:28

Angel Pizarro wrote:

> The GUS XML parser is not a real XML parser. The order of the columns 
> matter. Put <PROTOCOL_ID> before <PROTOCOL_TYPE_ID>

Grrr, but thanks.

Now I have a problem where the number of columns in RAD3Ver.ProtocolVer 
(24) is apparently not large enough (number of rows in RAD3.Protocol is 
23).   Hmm.

Here is the error:

sqlExec: INSERT INTO RAD3Ver.ProtocolVer select v.*,312,SYSDATE,11 from 
RAD3.Protocol v where v.protocol_id = ?
  bindValues (1)
DBD::Oracle::st execute failed: ORA-00913: too many values (DBD ERROR: 
error possibly near <*> indicator at char 20 in 'INSERT INTO 
RAD3Ver.<*>ProtocolVer select v.*,312,\
SYSDATE,11 from RAD3.Protocol v where v.protocol_id = :p1') [for 
Statement "INSERT INTO RAD3Ver.ProtocolVer select v.*,312,SYSDATE,11 
from RAD3.Protocol v where v.protoco\
l_id = ?" with ParamValues: :p1='1'] at 
/checkout/GUS/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 146, <GEN0> line 12.

There's nothing in RAD3Ver.ProtocolVer right now, BTW.  If I don't have 
the right definition for it for some reason, it would be straightforward 
to recreate it.

Kevin Murphy

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Kevin M. <mu...@ge...> - 2004-12-07 20:05:43

Kevin Murphy wrote:

> Now I have a problem where the number of columns in 
> RAD3Ver.ProtocolVer (24) is apparently not large enough

These two columns are missing from RAD3Ver.ProtocolVer:

 SOFTWARE_TYPE_ID                                   NUMBER(10)
 HARDWARE_TYPE_ID                                   NUMBER(10)

I confirmed this by looking at rad3ver-tables.sql.   Are there more 
recent versions of the rad3ver-*.sql files, or should I just fake these 
columns in (i.e. recreate the table) and keep moving?

-Kevin

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Kevin M. <mu...@ge...> - 2004-12-07 21:31:02

Angel Pizarro wrote:

> The GUS XML parser is not a real XML parser. The order of the columns 
> matter. Put <PROTOCOL_ID> before <PROTOCOL_TYPE_ID> 

Here is another important note on updating records using UpdateGusFromXML:

If you specify the primary key, that column name must be in lower case.  
The other column names are not case-sensitive.

Kevin Murphy

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Angel P. <an...@pc...> - 2004-12-07 21:51:58

I was actually pretty surprised that you did not run into that error 
previously. All of the GUS XML that I have dealt with had lower case 
elements for the table columns

So now we know that:
1) Elements that are table column name must be lowercase and in the 
order they appear in the view/table
2) A column must be all on one line with no leading/trailing whitespace
3) Updates are triggered by settig the PK column

angel

Kevin Murphy wrote:

> Angel Pizarro wrote:
>
>> The GUS XML parser is not a real XML parser. The order of the columns 
>> matter. Put <PROTOCOL_ID> before <PROTOCOL_TYPE_ID> 
>
>
> Here is another important note on updating records using 
> UpdateGusFromXML:
>
> If you specify the primary key, that column name must be in lower 
> case.  The other column names are not case-sensitive.
>
> Kevin Murphy
>

Re: [Gusdev-gusdev] UpdateGusFromXML and updating records

From: Elisabetta M. <man...@pc...> - 2004-12-07 23:42:34

Angel,

> So now we know that:
> 1) Elements that are table column name must be lowercase and in the order 
> they appear in the view/table

is that true or it's only for the primary_key? So far I've never paid 
attention to the order of the xml fields when *inserting* (so I imagine I 
might have not followed it all the times) and I don't recall having 
problems with that.
Elisabetta