Tools and other software Batch to update (or clean) my TV recordings folder (1 Viewer)

olli14

Portal Pro
December 17, 2008
740
35
Home Country
Germany Germany
I tested again and I found out that I can't use "load_file(filename)". It doesn't work.
E.g. I have the files "heute.ts" and "heute.txt" in the folder "c:\test". In the file "heute.txt" is a simple HELLO.
Now when I type
select load_file('c:/test/heute.ts');
I get as result NULL

WHen I type in
select load_file('c:/test/heute1.ts');
I get as result NULL

When I type in
select load_file('c:/test/heute.txt');
I get as result HELLO

Though the file heute.ts exists and the file heute1.ts doesn't exist the result is everytime NULL. So I can't use load_file command. Is there another command that is working?
 

pünktchen

Portal Pro
October 26, 2010
537
201
Home Country
Germany Germany
There's no "file exists" function in MySQL, because MySQL doesn't know anything about physical files. Load_File tries to read strings from a file, that's why it doesn't work for video files.But you could substitute the ts extension of "filename" with xml (replace funktion) and run Load_File against your xml's instead.

Or you could output the filename table as a list and run a real batch file against it to check for file existance and delete the non existend entries from the sql table.
 
Last edited:

olli14

Portal Pro
December 17, 2008
740
35
Home Country
Germany Germany
@pünktchen
I Don't understand what you mean with this "...But you could substitute the ts extension of "filename" with xml (replace funktion) and run Load_File against your xml's instead..." Can you send me a PN in german and give me some hints?
 

olli14

Portal Pro
December 17, 2008
740
35
Home Country
Germany Germany
The leading thought to make this conversation private was that no one has interest to my speciale problem. I thought. But you are right. Maybe someone is out there :)

maybe you had the same idea as I have now. I have a batch file that can delete all XML files, jpg files, txt files and so on where no corresponding ts files exists.

So I will test this:
1. I delete all XML files where no corresponding ts file exists.
2. I open the recording table and use this command: select replace('filename','.ts','.xml');
3. Then I use this command: DELETE FROM Recording WHERE LOAD_FILE(fileName) IS NULL;
4. Then I use this command: select replace('filename','.xml','.ts');
I will tell you about the result.
Or did you had another idea, mm1352000?
 

pünktchen

Portal Pro
October 26, 2010
537
201
Home Country
Germany Germany
The leading thought to make this conversation private was that no one has interest to my speciale problem. I thought. But you are right. Maybe someone is out there :)

maybe you had the same idea as I have now. I have a batch file that can delete all XML files, jpg files, txt files and so on where no corresponding ts files exists.

So I will test this:
1. I delete all XML files where no corresponding ts file exists.
2. I open the recording table and use this command: select replace('filename','.ts','.xml');
3. Then I use this command: DELETE FROM Recording WHERE LOAD_FILE(fileName) IS NULL;
4. Then I use this command: select replace('filename','.xml','.ts');
I will tell you about the result.
Or did you had another idea, mm1352000?
That was my idea. Unfortunately "load_file" seems to have a problem if "fileName" has special characters like german Umlauts.
 

CyberSimian

Test Group
  • Team MediaPortal
  • June 10, 2013
    2,849
    1,771
    Southampton
    Home Country
    United Kingdom United Kingdom
    The leading thought to make this conversation private was that no one has interest to my speciale problem. I thought. But you are right. Maybe someone is out there :)
    Yes, I am interested in a batch script to do this. Like you, I run my own batch script to re-arrange recordings (move them to appropriate locations in the "Videos" section), but I have to remember to start MP and manually use the "Clean Up" function. A neater solution would be for my script to issue some command to perform the clean up.

    Unfortunately, I have absolutely no knowledge of SQL, so cannot offer you any help. But I am interested in the outcome. (y)

    -- from CyberSimian in the UK
     

    pünktchen

    Portal Pro
    October 26, 2010
    537
    201
    Home Country
    Germany Germany
    Try this in a batch file:

    Code:
    @echo off
    chcp 1252
    
    set RECORDINGS=D:\Aufnahmen
    set MYSQL=C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe
    
    setlocal enabledelayedexpansion
    
    for /R %RECORDINGS% %%i in (*.xml) do (
        if not exist "%%~dpi%%~ni.ts" (
            set FILENAME=%%~dpi%%~ni.ts
            set FILENAME=!FILENAME:\=\\!
            echo SET CHARACTER SET 'latin1';> cleanup.sql
            echo DELETE FROM `mptvdb`.`recording` WHERE '!FILENAME!' = `fileName`>> Cleanup.sql
            timeout /t 5 /nobreak
            "%MYSQL%" --user=root --password=MediaPortal < Cleanup.sql
            del /f /q "%%~dpi%%~ni.xml"
            del /f /q "%%~dpi%%~ni.jpg"
            del /f /q Cleanup.sql
        )
    )
    
    exit
     

    olli14

    Portal Pro
    December 17, 2008
    740
    35
    Home Country
    Germany Germany
    Hi Pünktchen,
    I tested yesterday evening the Batch file. It did nothing. After starting the Batch I got a black small window for less than a second and that was all.
     

    Users who are viewing this thread

    Top Bottom