Tools and other software Batch to update (or clean) my TV recordings folder (2 Viewers)

[mm1352000]

I don't understand what I can do further.

https://dev.mysql.com/doc/refman/5.7/en/show-warnings.html

 

[olli14]

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]

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.

 

[olli14]

@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?

 

[mm1352000]

Can you send me a PN in german and give me some hints?

Please do not use PM. There is nothing private in this conversation. If you go private, you prevent other people from following and learning.

http://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

 

[olli14]

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]

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]

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.

-- from CyberSimian in the UK

 

[pünktchen]

Try this in a batch file:

@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]

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.