Adding a scheduled recording by using plain SQL: Solved (1 Viewer)

[Aquarius]

Hi all,
I tried to circumvent the official introduction of recordings into the DB by just applying some SQL.

Insertion works just fine, but the TVServer throws out my inserted record with the following message:
2012-06-14 21:40:33.265625 [scheduler thread(30)]: Scheduler: Orphaned once schedule found 333 - removing

The SQL statement looks like this.

insert into schedule (idChannel, scheduleType, programName, startTime, endTime, ... )
select idChannel, '0' , title, startTime, endTime, ...
from program where description like ... ;

My suspicion is the field "schedule.id..." , which is an autoincrement. Does TVServer perhaps remember this value internally ?

Background: I wanted to create a very lightweight recording filter using simple search mechanisms in the programs database without the hassle of plugins (like "ForTheRecord") or whatever.

If somebody knows another pragmatic, simple way to insert recordings, I would be thankful.

Thanks
/Gerd

 

[mm1352000]

Hi Gerd

Please provide a full example of the record values.

mm

 

[Aquarius]

@mm
Thanks for taking care.

Now, ... I have a problem ... It works !

What I did ? I stopped TvService to make a DB-dump and restarted and TVService did not complain about anything.
The upmentioned error message " Orphaned once schedule found " did not appear any more. The recording was scheduled and executed.

Thanks for your support, perhaps some magic behind.

For those who like to do the same, here is my little SQl-statement to insert a scheduled recording without any add-on. Just with what MP already supplies. This SQL statement is inserts a one time schedule not bound to any TV-card, searching in programs title or - descriptiontext for keywords or sentence.

Add this line to a file e.g. UpdateEPG.sql

-- Start with :
-- mysql.exe -u root -pMediaPortal -h localhost --verbose <UpdateEPG.sql 1>UpdateEPG.log
-- MySQL :: MySQL 5.5 Reference Manual :: 4.5.1 mysql — The MySQL Command-Line Tool
-- http://dev.mysql.com/doc/refman//5.5/en/batch-commands.html
USE mptvdb
insert into schedule (idChannel, scheduleType, programName, startTime, endTime, maxAirings, priority, directory, quality, keepMethod, keepDate, preRecordInterval, postRecordInterval, canceled, recommendedCard, series, idParentSchedule)
            select idChannel,  '0', title, startTime, endTime, 2147483647, 0, '', 73, 0, '2000-01-01 00:00:00.0', 0,5,"2000-01-01 00:00:00.0",-1,0,-1
                  from program  where
                        title like  '%your search pattern here%'
                    or description    like  '%your search pattern here%'
                    or title like  '%another earch pattern%'
                    or description    like  '%another search pattern%'
                    ;
commit;

Feel fee to enhance SQL.

And execute it it with this line:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" -u root -pMediaPortal -h localhost --verbose < UpdateEPG.sql >UpdateEPG.log

Add this line to a CMD - file (adjust the path to MySQL) and add it to Windows- Scheduler.
That's it. Easy and flexible.

Good luck !

Regards
Gerd

 

[mm1352000]

Now, ... I have a problem ... It works !

Great!
Thanks for the info - I'm sure other people will appreciate it.

 

[DoXer]

Is there also a way to do the same with MS SQL 2005/2008?

 

[Lehmden]

Hi.
Haven't tested jet but usually the SQL statements are working in both environments so this part should do it in Ms SQL Client (hopefully )

USE mptvdb
insert into schedule (idChannel, scheduleType, programName, startTime, endTime, maxAirings, priority, directory, quality, keepMethod, keepDate, preRecordInterval, postRecordInterval, canceled, recommendedCard, series, idParentSchedule)
            select idChannel,  '0', title, startTime, endTime, 2147483647, 0, '', 73, 0, '2000-01-01 00:00:00.0', 0,5,"2000-01-01 00:00:00.0",-1,0,-1
                  from program  where
                        title like  '%your search pattern here%'
                    or description    like  '%your search pattern here%'
                    or title like  '%another earch pattern%'
                    or description    like  '%another search pattern%'
                    ;
commit;