[Koha-devel] Losing biblio.author in 3.8.5

[Mike Hafen]

Would an SQL Update statement be more effective here?  Like:

UPDATE biblio SET author =
ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') WHERE
author IS NULL

On Wed, Oct 31, 2012 at 1:00 PM, Paul <paul.a at aandc.org> wrote:

> At 08:09 PM 10/29/2012 +0100, you wrote:
>
>> Hi Paul,
>>>
>> [snip]
>>
>>  For your reports you always have the option to query the MARCXML data
>>> directly, nothing should be lost.
>>>
>>
>> I agree -- in fact for staff, I've already written a new report replacing
>> biblio.author with ExtractValue(marcxml,'//**datafield[@tag="100"]/**subfield[@code>="a"]')
>> and it works perfectly.
>>
>> But this doesn't "correct" the MySQL db.  Where/why on earth has it gone
>> wrong, and how to correct it?
>>
>
> Could some kind soul with knowledge of using XML in MySQL assist?
>
> I'm trying to copy the XML 100$a to biblio.author where this latter is
> NULL, but:
>
> mysql> INSERT INTO biblio (biblio.author)
>     -> SELECT ExtractValue(marcxml,'//**datafield[@tag="100"]/**subfield[@code>="a"]')
> FROM biblioitems
>     -> WHERE biblio.author IS NULL;
> ERROR 1054 (42S22): Unknown column 'biblio.author' in 'where clause'
>
> so I tried to be more explicit for biblio.author and now the XML has an
> error
>
> mysql> INSERT INTO biblio (biblio.author)
>     -> SELECT author FROM biblio, ExtractValue(marcxml,'//**
> datafield[@tag="100"]/**subfield[@code>="a"]') FROM biblioitems
>     -> WHERE biblio.author IS NULL;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near '(marcxml,'//datafield[@tag="**100"]/subfield[@code>="a"]') FROM
> biblioitems
> WHERE' at line 2
>
> I have tried various combinations of LEFT|RIGHT JOIN biblio ON (
> biblioitems.biblionumber = biblio.biblionumber) but with no success so far.
>
>
> Thanks and regards -- Paul
>
>
>
>
>
>
>
>  Hope that helps,
>>
>> Katrin
>>
>>
>> -----Ursprüngliche Nachricht-----
>> Von: koha-devel-bounces at lists.koha-**community.org<koha-devel-bounces at lists.koha-community.org>im Auftrag von Paul
>> Gesendet: Mo 29.10.2012 19:55
>> An: koha-devel at lists.koha-**community.org<koha-devel at lists.koha-community.org>
>> Betreff: [Koha-devel] Losing biblio.author in 3.8.5
>>
>> We have a [rather important, it's for donors' tax receipts] report that
>> includes:
>>
>> SELECT
>> items.barcode AS Barcode,items.dateaccessioned AS 'Acc Date',
>> biblio.title AS Title, biblio.author AS Author,biblioitems.**publishercode
>> AS
>> Publisher,biblioitems.**publicationyear AS Year,
>> biblioitems.editionstatement AS Edition,
>> items.price as FMV, etc etc etc
>>
>> It worked perfectly from 3.6.1 up to and including 3.8.4
>>
>> Since we upgraded to 3.8.5 (24 Sep, to fix bug 8520), something has
>> changed
>> -- biblio.author systematically comes up empty (all 245$a fields are
>> present) while *all* the other field are perfect. (added later: just
>> checked some other reports that use biblio.author -- same result, other
>> fields are good, author fails.)
>>
>> I've looked around release notes and bugs without finding anything
>> relevant, and am now at a loss as to what I should look for.
>>
>> Any suggestions warmly accepted.
>>
>> Thanks and regards,
>> Paul
>>
>> ______________________________**_________________
>> Koha-devel mailing list
>> Koha-devel at lists.koha-**community.org<Koha-devel at lists.koha-community.org>
>> http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel>
>> website : http://www.koha-community.org/
>> git : http://git.koha-community.org/
>> bugs : http://bugs.koha-community.**org/<http://bugs.koha-community.org/>
>>
>
> ---
> Maritime heritage and history, preservation and conservation,
> research and education through the written word and the arts.
> <http://NavalMarineArchive.com**> and <http://UltraMarine.ca>
>
> ______________________________**_________________
> Koha-devel mailing list
> Koha-devel at lists.koha-**community.org<Koha-devel at lists.koha-community.org>
> http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel>
> website : http://www.koha-community.org/
> git : http://git.koha-community.org/
> bugs : http://bugs.koha-community.**org/ <http://bugs.koha-community.org/>
>
>
> ______________________________**_________________
> Koha-devel mailing list
> Koha-devel at lists.koha-**community.org<Koha-devel at lists.koha-community.org>
> http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel>
> website : http://www.koha-community.org/
> git : http://git.koha-community.org/
> bugs : http://bugs.koha-community.**org/ <http://bugs.koha-community.org/>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-devel/attachments/20121031/2bd0c128/attachment-0001.htm>