Menu

SQLite export fails

Help
2024-02-09
2025-04-30
  • Rene van Hout

    Rene van Hout - 2024-02-09

    Hi Tom, it's me again.
    I read on Facebook the new possibility to export to the SQLite format. That's a great feature for use on Android I guess.
    I created a profile but unfortunately I get some SQL errors and I can't figure out what's wrong. I hope you have a hint or tip what to change or what to look for.

    I attach the error log
    Thanks in advance.

    Groet,
    René

     
  • Tom van Breukelen

    Hallo Rene,
    The error message tells me that SQLite couldn't create the table. The reason why however is not cleat. Could you send me a screenshot of the profile that you've created, so that I could try to reproduce the problem?

    Bij voorbaat hartelijke dank,
    Tom

     
  • Tom van Breukelen

    Hi Rene,

    Table names should not have any blanks in them. Table "SoftCat 17 W10" could not be created. For the next FNProg2PDA version, I'll add a validation routine to check for invalid table names. For the moment however, just leave out the blanks and it should work

    Cheers, Tom

     
  • Rene van Hout

    Rene van Hout - 2024-02-10

    Hi Tom,
    So the naming convention for tables differs from these for files, good to know.
    I changed the table name and the export completed.

    Thanks again for your support!
    René

     

    Last edit: Rene van Hout 2024-02-10
  • Tom van Breukelen

    Hi René,

    I'm glad that you managed to complete the export. Naming conventions do indeed differ between SQL tables and other files (as well as FNProg2PDA profile names). Basically one should avoid table names which conflict with SQL reserved keywords. Here is an article that of SQLite reserved keywords. There are also a number of other naming conventions which are enforced by the SQL engine. Though these vary somewhat between SQL “flavours”, SQL columns and table names should begin with a letter, not end in an underscore, and should contain only alphanumeric characters. Column and table names should not contain spaces. In general, these rules must be followed explicitly or code will confuse the SQL interpreter and not execute.

    Cheers, Tom

     
  • Rene van Hout

    Rene van Hout - 2024-02-12

    Hi Tom,
    Thanks for the link. There's a lot of interesting information on that site!
    I managed to have my SoftCAT inventory on Android now, using the app SQLite Browser Pro, perfect, exactly as I wanted.

    No attacking CATraxx :-)
    One thing I encountered (a little of topic): the information in some fields is truncated. Is that something within FNProg2PDA or do I have to look elsewhere?

    Thanks a lot for this new feature!
    René

     
  • Tom van Breukelen

    Hi René,
    The truncation of some fields is a Java programming issue. Sometimes the Java programming language makes it very hard to "calculate" the right length dynamically. I'll have a look at it.

    Cheers, Tom

     
  • Arve Skogen

    Arve Skogen - 2024-09-17

    Hi Tom! Excellent program you have created. Exporting to update my webpage regularly.
    I'm using the SQLite export and really hoping the "truncated" in some fields can get fixed.

    Best wishes for your health.

    Arve

     
  • Tom van Breukelen

    Hi Arve,

    I'm glad that you find my program useful. Thanks for pointing out the truncate problem. I found the cause of the problem and will fix it asap. As default the maximum number of characters allowed in a memo filed was set to 255 for SQLite. That would have been correct for a standard text field but not for memo fields. I've removed that limitation and now allows an unlimited numbers of characters.

    Cheers, Tom

     
  • Tom van Breukelen

    Hi Arve,
    Version 10.7.1 has been released to fix the "truncated" problem.
    Best regards, Tom

     
  • Arve Skogen

    Arve Skogen - 2024-09-19

    Hi Tom!
    That worked excellent. Thank you so much.

    I have one more problem and a wish.
    Problem is when I try to export the "Index" column from tracks I get an error message. (SQLITE_ERROR) SQL error or missing database (near "Index": syntax error).

    My wish (if it is not there already?)
    I can find writers and performers, but I would really like to get Producers as well. Is that possible?

    Best regards, Arve

     
  • Tom van Breukelen

    Hi Arve,

    Index is a reserved word in SQLite (and many other SQL databases). These words differ from database to database and the number of them can be quite substantial (like 249 words for MariaDB). In my program I only check a small subset of known reserved words and Index was not included.

    In the next release I'll make sure that most, if not all, reserved words are properly checked. Maybe I issue a warning in case a reserved word is used with a recommendation to use an alternative word as fieldname instead.

    In the meanwhile you could export "Index" for example as "IndexNo" as a workaround solution.

    Regarding the Producers, I'll put is on my to do list as well for the next release. That release would be ready around the middle or end of October (after my next 2 weeks vacation).

    Best regards, Tom

     
    • Tom van Breukelen

      Hi Arve,

      I've just released FNProg2PDA version 10.7.2.

      This version contains new fields added to the CATraxx tracks export:
      * ProductionPerson
      * PublishingPerson
      * Engineers
      * Mastering
      * Mixing
      * Programming
      * Publishers
      * Copyright
      * Producers

      as well as:
      Issue a warning in case the SQL field name you are exporting to is a reserved word

      I hope you like it.

      Best regards, Tom

       
  • Rene van Hout

    Rene van Hout - 2024-09-26

    Hi Tom.

    This is great news, all the information is now indeed exported. No more truncations!
    Thanks for the hard work on this great program (I use it all the time).

    I read in the other thread that you suffered some serious health issues. Hope you are well.
    All the best.

    Regards,
    René

     
  • Arve Skogen

    Arve Skogen - 2024-10-21

    This was really great. Thank you

    Best regards, Arve

     
  • Arve Skogen

    Arve Skogen - 2024-10-24

    Hi again!
    One thing that came up. When I export to SQLite from album, I try to include "Writers", but all the fields are empty?

    One more wish. Is it possible to include "ArtistID" in the artist export?

    Best regards
    Arve

     
  • Tom van Breukelen

    Hi Arne,

    I've fixed the Writer issue (cause: missing join statement) and I'm now working on the ArtistID export.

    Cheers, Tom

     
  • Tom van Breukelen

    Hi Arne,
    I've released version 10.8 last night which fixes the Writers issue and adds the ArtistID.

    Cheers, Tom

     
  • Arve Skogen

    Arve Skogen - 2024-10-30

    Just tested it, and it works excellent. Thank you very much!

    Cheers, Arve

     
  • Arve Skogen

    Arve Skogen - 2025-04-29

    Hi!
    I'm really enjoying the possibility to export data from Catraxx. I regularly use it. Recently I have started to use the "New and modified records" option when exporting.
    That works like a charm on Albums and Tracks, but on Artists it exports the complete file.

    Is it possible to adjust that?

    Best regards,
    Arve

     
  • Tom van Breukelen

    Hi Arve,
    Both Albums and Tracks tables contain a Last Modification date field. I use this to find the new and modified records. This field isn't available for Artists, which makes it far more difficult to find new records and nearly impossible to find modified records.

    Best regards,
    Tom

     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.