13 Replies to “Media Metadata, Read, Write, Compare with Picasa #media #metadata #picasa

  1. So I tried this verbatim with RM7 and had some problems: I was just doing the first step, the generation of the compare of image metadata to RM caption/description data.

    Specifically the issues I encountered are:
    1. When I run the batch file alias, I always get an error:: Invalid TAG name: “execute ”
    (But the script still runs ok.)

    2. I get a bunch of “Error: File not found …” errors (for about 16 files): I am guessing it is because of the characters in the file name (Unicode characters in filename not supported??)

    I can and do continue after the above errors: the script of course is programmed to tell you what to do, it says this (this is verbatim except I have changed a couple strings to anonymize the text):

    Open RM-Media.db3 with your SQLite Manager, copy and run the following statements
    ATTACH DATABASE ‘”C:\Users\MYUSERNAME\Documents\06.Genealogy\0.RootsMagic Genealogy DB-MYSURNAME\MYSURNAME Genealogy.rmgc”‘ ;
    SELECT RM.MediaFile COLLATE NOCASE, Thumbnail, RM.Caption COLLATE NOCASE AS RMcaption,
    CAST(RM.Description COLLATE NOCASE AS TEXT) AS RMdescription, Pic.Caption AS MetaCaption,
    Pic.Description AS MetaDescription, Pic.MediaPath FROM MultiMediaTable AS RM
    INNER JOIN PicasaTable AS Pic ON RM.MediaFile COLLATE NOCASE LIKE Pic.MediaFile ;

    So I run next SQLiteExpert Personal 5.3.5.476 64 bit and open the RM-Media.db3 file, select PicasaTable in RM-Media database on left side of SQLiteExpert and in the SQL tab on the right paste the text as instructed (“ATTACH DATABASE …LIKE Pic.MediaFile ;”):
    The result is a popup error window titled “SQLiteExpertPers64” indicating this error text:

    near “;”: syntax error
    SQL Statement:
    ATTACH DATABASE ‘”C:\Users\myusername\Documents\…path_to_my…\RootsMagicDB.rmgc” ;.

    Any ideas on what the issue could be? (I am totally new to SQL: no idea what the syntax issue could be.)

    (FYI, I have unifuzz64.dll downloaded, but did not load that in this case since RMNOCASE sorting does not appear to be used, so I assume it is not required.)

    • Wow, 8 years have gone since that was published and we have transferred thepage from WikiSpaces to WordPress. I can see that transition has corrupted code blocks on the page (but should have had no effect on the attached files), notably the loss of the backslash character in the quoted filepaths. It will take me some time to fix those errors but somehow you seem to have progressed further than where they appear.

      “-execute” is an argument for ExifTool so I assume the “Invalid Tag name” error report is from it. If that is systemic, then there should be no point in proceeding beyond that error.

      Are you saying the “file not found” error is only for a subset of the files processed and that the rest are ok?

      The “near “;”: syntax error” looks to be the imbalanced single quotation mark around the path in the ATTACH statement; needs the closing ‘.

      This script predates my discovery of unifuzz so it used the COLLATE NOCASE workaround. You could delete all those and load unifuzz – might have a bearing on the “file not found” errors.

  2. FYI the error is not about “-execute” but “-execute ” (not the added space, if that makes a difference).
    The “file not found” is only on a subset of file, which I noticed all happen to have unicode in the names…
    So the 1st thing I want to try is unifuzz:
    However I always get “Error: The specified module could not be found.”
    I have tried all obvious syntaxes…any ideas on how to get this to load?
    I have changed a line in the batch file thus:
    FROM:

    ECHO .mode tabs > GetImageListFromRM.sql

    TO:

    ECHO .load “unifuzz64.dll” > GetImageListFromRM.sql
    ECHO .mode tabs >> GetImageListFromRM.sql

    I have tried all different kinds of syntax in the .load line with no luck (with and without quotes/path/.dll extension…!): any ideas how to get this to work?
    (All files in the same c:\bin directory!)

  3. I’ve gone through the page and corrected visible errors which proved to be a learning curve trying to use the newish block editor introduced to this site when we upgraded recently from WordPress 4 to WP5. Haven’t yet tried running the procedures and may not have done so since first publishing them. Since then, SQLite and SQLite managers have gone through many changes and we now have Windows PowerShell. And ExifTool now has its own site and may also have changed. If it all works as originally written will be a testament to backwards compatibility!

  4. OK thanks for the help: the 32/64 bit thing helped solve the unifuzz error!
    The missing files error was indeed due to unicode: I solved this by adding an exiftool argument up front (after -T) : “-charset filename=utf8 “.

    Still having I think minor issues, but will work on this further now to get all the way done … I think I am almost there.

    • Good work. I’m now working through the instructions and encountering what you describe.

      There are trivial errors that you have found you can ignore, arising from the 3
      ERASE commands in the batch file to remove the files from a previous run. If those files do not exist, you get the “file cannot be found” error. Next time you run it, no such error. I didn’t learn enough Windows Command syntax to figure out how to test the files exist and make the erase conditional.

      The “Invalid TAG name: “execute “” I have not looked into but it seems to be only a warning.

      The “near “;”: syntax error” stemming from the ATTACH statement seems to be the result of two changes in SQLite, I think. One is that it now seems that the filepath must be enclosed in either single quotes or double quotes but not both. 8 years ago, I think if the filepath had a space character in it, the ‘”filepath”‘ syntax was necessary for ATTACH. Double quotes are necessary in the shortcut to pass the whole string to %1 and the batch command.

      The other error around the ATTACH statement is that it must now end with “AS schema-name”. So RM5comparePicasa.bat now reads:
      ECHO ATTACH DATABASE %1 AS RM; –enclose unquoted filepath in either single or double quotes if none

  5. OK, so I seem to be mostly OK until I try to execute SQL on the new RM-Media database: I still get an SQL error.
    Please refer to this image:
    https://photos.smugmug.com/photos/i-bZww3xG/0/4b274092/X5/i-bZww3xG-X5.png
    (1) Batch file executes OK except for error about “execute” – that seems exiftool related but the exiftool “ImageDescriptions.txt” file is created and seems OK for content
    (2) I copy the SQL that batch file displays
    (3) In SQLite Personal 64 bit I do two things: load 64 bit unifuzz, then paste and exceute the SQL code: this gives an SQL error as shown in the image. 🙁

    Any idea what the issue is?

  6. I know nothing really of SQL, but according to this page:
    https://www.techonthenet.com/sqlite/attach_database.php
    The ATTACH command syntax is this:
    ATTACH [DATABASE] ‘filename’ AS database_name;
    Is that a change from when you first wrote this page? Your ATTACH command has no “AS database_name”, and uses double AND single quotes around the filename of the database.
    So I changed that first line of SQL to the following:
    FROM:
    ATTACH DATABASE ‘”C:\bin2\cdb.rmgc”‘ ;
    TO:
    ATTACH DATABASE ‘C:\bin2\cdb.rmgc’ as CDB;

    Ta-da! That seemed to fix it.
    The only issue is the output just shows “(blob)” in the thumnail column, so maybe SQLite Expert Personal no longer supports display of JPG thumbnails?
    Also the text does not wrap as shown in your screen shot: every item is on a a single-height line: text is simply cut-off if the column is too narrow, it does not wrap like in your screenshot.

    • Ah, you posted while I was still composing. We’re on the same page, now.

      I was surprised to see just (blob) using SQLite Expert Personal 5 but it is simply a setting. Tools > Options > Grids > Show images

      • And wordwrap is an option in the same area called “Cell auto-height”.

        That -execute warning is a mystery to me. The line:
        ECHO -execute >> RMimagefiles.args
        appends -execute to that file. Commenting it out results in an error message for every line in ImageDescriptions.txt being imported into the PicasaTable and a null result.

        This pc doesn’t have Picasa so I’m hazy on what metadata tags it used and whether I can see them with Windows File Explorer. I’m seeing nothing in the PicasaTable for its set of media files.

        • I’ve just uncovered another change in the way things work. I couldn’t understand why running the Compare procedure across JPG files that included some from my Picasa era it was retrieving nought from the metadata. Back then, according to what I wrote, Picasa stored its caption in either or both Caption-Abstract and Description and those are the names that once worked as arguments for exiftool.exe. I’m beginning to think my files were stripped of the Picasa metadata.

          This page provides some useful clues: http://metadatadeluxe.pbworks.com/w/page/47662311/Top%2010%20List%20of%20Embedded%20Metadata%20Properties

          I can add IPTC:Caption-Abstract value using the exifToolGUI app and retrieve it with the exiftool statement as written. I tried to write to EXIF:Description using exifToolGUI but it was rejected. The above reference for Description shows EXIF:ImageDescription under Description for Picasa (along with IPTC:Caption-Abstract) and I could add that tag. To retrieve it, the exiftool argument has to be changed from -Description to -ImageDescription.

          Because you are starting from a different metadata management software, perhaps you have used other tags for your captions. In any case, there will be revisions needed in the arguments passed to exiftool.

          Meanwhile, I may try revisiting my old computer with Picasa (not sure if that’s one or two ago) and see if there are still JPGs on it with Picasa metadata.

          • I need to correct some misinformation in that previous comment. The -Description argument for exiftool.exe was and still is valid. In the ExifToolGUI shell for ExifTool.exe, I thought I had to enter it in the Workspace Definition as -exif:Description and that was rejected so, based on that reference I cited which showed Picasa using exif:ImageDescription I tried it and it was accepted. I’ve now found that all I had to enter is -Description and can now retrieve values from both iptc:Caption-Abstract and a Description tag of unknown family.

            What’s got me even more unsettled (because these procedures did work) is that my working set of image files associated with my RootsMagic databases is now devoid of either Caption-Abstract or Description values. Yet I have found (browsing with ExifToolGUI) copies of the files with that metadata in an old RM6 style website generated in 2016 (each image in three sizes, all with the metadata copied from the RM caption and description by my procedure). And another set that may have gone through an even more convoluted process involving FTM and Ancestry.ca before coming back to RM as renamed files in one folder but with the metadata intact. The latest image file was 2014 and the latest with the caption from RM 2013.

            Somewhere along the way, not only did I change computers (twice) but I also consolidated the media under a Google Drive folder. I must have had duplicated files and overwritten or discarded those that had caption+description written from RM. Bummer. That’s one of the risks with the metadata tucked away out of site in the usual user interfaces.

Leave a Reply