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 <paul.a@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@lists.koha-community.org im Auftrag von Paul
Gesendet: Mo 29.10.2012 19:55
An: koha-devel@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@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>

_______________________________________________
Koha-devel mailing list
Koha-devel@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/

_______________________________________________
Koha-devel mailing list
Koha-devel@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/

_______________________________________________
Koha-devel mailing list
Koha-devel@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>