Re: [Koha-devel] Losing biblio.author in 3.8.5
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@aandc.org>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: <mailto:koha-devel-bounces@lists.koha-community.org>koha-devel-bounces@lists.koha-community.org im Auftrag von Paul Gesendet: Mo 29.10.2012 19:55 An: <mailto:koha-devel@lists.koha-community.org>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 <mailto:Koha-devel@lists.koha-community.org>Koha-devel@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@lists.koha-community.org>Koha-devel@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@lists.koha-community.org>Koha-devel@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@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>
On Wed, Oct 31, 2012 at 8:12 PM, Paul <paul.a@aandc.org> wrote:
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'
IIRC you cannot use ExtractValue with UPDATE, INSERT, or SELECT INTO statements. Somewhere back in the list archives we discussed this, but neither Google nor I seem to be able to locate it at the moment. Kind Regards, Chris
Greetings, Read the manuals, please: http://dev.mysql.com/doc/refman/5.5/en/update.html http://dev.mysql.com/doc/refman/5.5/en/join.html (you could look at 5.6 or 5.1 too accordingly) mysql> select count(*) from biblio; +----------+ | count(*) | +----------+ | 38497 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from biblio where author is NULL; +----------+ | count(*) | +----------+ | 8967 | +----------+ 1 row in set (0.00 sec) mysql> select biblio.author,ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') from biblioitems left join biblio on biblio.biblionumber=biblioitems.biblionumber where biblio.author is NULL; +--------+----------------------------------------------------------------------+ | author | ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') | +--------+----------------------------------------------------------------------+ | NULL | | | NULL | | ... | NULL | | | NULL | | +--------+----------------------------------------------------------------------+ 8967 rows in set (0.89 sec) mysql> update biblio right join biblioitems on biblio.biblionumber=biblioitems.biblionumber SET biblio.author=ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') where biblio.author is NULL; Query OK, 8967 rows affected (1.68 sec) Rows matched: 8967 Changed: 8967 Warnings: 0 mysql> select count(*) from biblio where author is NULL; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) Granted, they are all blank still, because all my records had nothing in the 100$a column for those records. I don’t know if that will or will not affect any logic in Koha, as NULL!=space(s). Though you could go back and replace with NULL for things with no printable characters in it, I’m sure. GPML, Mark Tompsett
At 10:55 AM 11/1/2012 +0800, Mark Tompsett wrote:
Greetings, Read the manuals, please: <http://dev.mysql.com/doc/refman/5.5/en/update.html>http://dev.mysql.com/doc/refman/5.5/en/update.html http://dev.mysql.com/doc/refman/5.5/en/join.html (you could look at 5.6 or 5.1 too accordingly)
Many thanks Mark. I had in fact tried that (my previous email: "I have tried various combinations of LEFT|RIGHT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) but with no success so far" but your "join biblioitems on biblio.biblionumber=biblioitems.biblionumber" is good. My last 3-day course with Oracle was 10 years ago :/ For the record (Koha 3.8.5, MySQL 5.5.24): mysql> UPDATE biblio RIGHT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber SET biblio.author=ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') WHERE biblio.author IS NULL; is fully functional. So far I have only done it on the sandbox; all previous "reports" that relied on biblio.author are now working. All that remains is to work out why our cataloguers cannot get the 100$a into the correct column, so that I don't have to go through this exercise every day. I'll also look into whether this has cured our authority problem, and whether or not NULL!=space(s) affects anything (or replace space[s] with NULLs.) Again many thanks, Paul --- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>
participants (3)
-
Christopher Nighswonger -
Mark Tompsett -
Paul