[Koha-devel] Losing biblio.author in 3.8.5

Thu Nov 1 01:12:52 CET 2012

At 02:27 PM 10/31/2012 -0600, Mike Hafen wrote:
>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

Many thanks for the suggestion, but I ran into problems -- I think it's to 
do with mixing a straightforward 'table/column' as in biblio.author with an 
'ExtractValue' from a different table.

Here's your suggestion:

mysql> UPDATE biblio SET author = 
ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') WHERE 
author IS NULL;
ERROR 1054 (42S22): Unknown column 'marcxml' in 'field list'

So I tried a second FROM to point to the 'table/column' with marcxml FROM 
biblioitems:

mysql> UPDATE biblio SET author = 
ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') FROM 
biblioitems WHERE 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 'FROM biblioitems WHERE author IS NULL' at line 1

No joy; so bracket the 'ExtractValue' with the table in front of it:

mysql> UPDATE biblio SET author = 
(biblioitems(ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]')) 
WHERE author IS NULL;
ERROR 1305 (42000): FUNCTION koha384.biblioitems does not exist

MySQL reads that as a FUNCTION, so put biblioitems. inside the bracket:

mysql> UPDATE biblio SET author = 
ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') 
WHERE author IS NULL;
ERROR 1054 (42S22): Unknown column 'biblioitems.marcxml' in 'field list'

MySQL is really touchy ;={

I hate bothering you, but do you have thoughts on this?

Thanks - Paul

>On Wed, Oct 31, 2012 at 1:00 PM, Paul 
><<mailto:paul.a at aandc.org>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: 
><mailto: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: 
><mailto: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
><mailto: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
>website : <http://www.koha-community.org/>http://www.koha-community.org/
>git : <http://git.koha-community.org/>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>http://NavalMarineArchive.com> and 
><<http://UltraMarine.ca>http://UltraMarine.ca>
>
>_______________________________________________
>Koha-devel mailing list
><mailto: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
>website : <http://www.koha-community.org/>http://www.koha-community.org/
>git : <http://git.koha-community.org/>http://git.koha-community.org/
>bugs : <http://bugs.koha-community.org/>http://bugs.koha-community.org/
>
>
>_______________________________________________
>Koha-devel mailing list
><mailto: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
>website : <http://www.koha-community.org/>http://www.koha-community.org/
>git : <http://git.koha-community.org/>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
>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/

---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
<http://NavalMarineArchive.com> and <http://UltraMarine.ca>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-devel/attachments/20121031/0cda9cac/attachment.htm>