Current program details, from batch file!

[pBS]

here's a batch file i wrote to look up the current program details from sqlite3.exe so i could pipe it into my lcd..
all that is needed is sqlite3.exe and your database location..
[edit batch file accordingly] can be modified to get any info in the db! very fast too..
Boy,i wish someone would have helped me find these silly connect strings..
so i share them here with hope others would find it usefull..
enjoy!
[use: "whatever.bat channel"]

Code:

@echo off
set ch=
rem **convert real channel# to oid channel# [internal ch# scheme]
for /f "usebackq tokens=1,2 delims=, " %%v in (`sqlite3 -separator "," "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " SELECT oid FROM CHANNEL where channel_number=%1; " `) do @set ch=%%v
rem calculate and reorder current date
for /f "usebackq tokens=1,2,3,4 delims=/ " %%a in (`date /t`) do (@set nowdate=%%d-%%b-%%c)
rem convert pm 12hr time to 24hr time
for /f "usebackq tokens=1,2,3,4 delims=: " %%f in (`time /t`) do call :24hr %%h %%f
rem calculate time and concactenate date and time together
for /f "usebackq tokens=1,2,3,4 delims=: " %%p in (`time /t`) do (@set now='%nowdate% %hr1%:%%q:55.0000000')
rem run sqlite with the parameters we built
sqlite3 -separator ": " "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " SELECT name, sub_title, description FROM PROGRAMME where start_Time < %now% and end_time > %now% and channel_oid = '%ch%' ; "

rem function to convert time format
:24hr
if "%1"=="PM" (set /a hr1=%2+12) else (set hr1=%2)
goto :eof

it just outputs to console, but add a ">filename.txt " at end of second sqlite command and it will write that file with info...

[Jim_]

That is neat!
And yes please keep sharing as you go. I haven’t dabbled in sql yet, but your example makes it look very simple and strait forward.

[Jim_]

Your topic is a nice place to share all the little sql utilities people come up with in the future, if that's ok?


I did some reading on SQL syntax at this site and found that it was very easy to log and delete "conflict" pending recordings from the database before the epg is updated.
Used in UpdateEPG.bat, this should eliminate part of the listing overlap problem.

Code:

echo . >>DeleteConflicts.log
echo %Date% %Time% >>DeleteConflicts.log
sqlite3 -separator ": " "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " SELECT status, filename, manual_start_time FROM RECORDING_SCHEDULE where status = 5" >>DeleteConflicts.log

sqlite3 "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " DELETE FROM RECORDING_SCHEDULE where status = 5"

I'm also going to clear the Prompt-For-Resume "PLAYBACK_POSITION" fields using UpdateEPG.bat.

Code:

sqlite3 "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " DELETE FROM PLAYBACK_POSITION"

Triggers look interesting!

[pBS]

yep....that's where i found my info also!
i found auto-it modules to interface but without the sql queries, it's not much use.....and this way feels very simple and powerfull and fast.. very low resource usage..
i modified mt batch to allow specifying what info you want returned and allowing for looking up any show with channel, time and info returned, so you can look up any show playing anytime...[or use "now" instead of time for current time..]
still need to add date specification ability but anytime today is working..
can also be used to get details for a filename, building 'what's next' lists or looking up shows based on criteria, checking recordings or import/export of data lists... [like say, re-occurring save and restore!]
the syntax on that site is a bit cryptic but it's a start!
lots of possibilities, but be carefull on the writing to database,not sure how locking will affect it..reading should be fine anytime..
Cheers!

[dark_half]

Can this be modified to give me the description of a recorded show. I currently have a batch file that move the mpg to another directory after recording, creates a xml and imports them into an app that will allow me to stream them to my ReplayTV's. If I could include the description it would improve my wifes acceptance.

[Jim_]

yep....that's where i found my info also!
i found auto-it modules to interface but without the sql queries, it's not much use.....and this way feels very simple and powerfull and fast.. very low resource usage..
i modified mt batch to allow specifying what info you want returned and allowing for looking up any show with channel, time and info returned, so you can look up any show playing anytime...[or use "now" instead of time for current time..]
still need to add date specification ability but anytime today is working..
can also be used to get details for a filename, building 'what's next' lists or looking up shows based on criteria, checking recordings or import/export of data lists... [like say, re-occurring save and restore!]
the syntax on that site is a bit cryptic but it's a start!
lots of possibilities, but be carefull on the writing to database,not sure how locking will affect it..reading should be fine anytime..
Cheers!

That sounds awesome! Looking forward to seeing what you come up with, and yes this could get dangerous but that’s what backups are for.
I looked at that site a while ago, but like most sites it assumes you already know everything to begin with. A few working examples sure makes it easier when someone’s just starting out.
Jim

[pBS]

Can this be modified to give me the description of a recorded show. I currently have a batch file that move the mpg to another directory after recording, creates a xml and imports them into an app that will allow me to stream them to my ReplayTV's. If I could include the description it would improve my wifes acceptance.

yep...just search on the filename field in the recordings table for the oid number, and then search on that oid# in programme and return the description field...

just use my batch in first post as template, as it also has to convert channel# to oid# then use that number to look up info from guide...
just change the table name in first call, like:

Code:

for /f "usebackq tokens=1,2 delims=, " %%v in (`sqlite3 -separator "," "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " SELECT oid FROM RECORDING_SCHEDULE where filename=%1; " `) do @set file_oid=%%v

and further down...

Code:

sqlite3 -separator ": " "C:\Program Files\DEVNZ\Gbpvr\GBPVR.DB3" " SELECT description FROM PROGRAMME where oid = '%file_oid%' ; "

come to think of it, since you don't need the time functions, just those two lines in a batch will suffice! lol


and run the batch file with filename as paramter and it will return description

P.S. JIM:
do you know what each of the recording status numbers mean?

[pBS]

i'm going to try to save and import recordings info from a remote database!..
as long as it opens it from a unc path it should be fine..
so even looking stuff up should be possible if it works..

SUCCESS!!!
now that is going to be usefull..lol
i have 2 machines with gbpvr installed, and now i can share the recordings info automatically...
plus i'm going to work on a reoccuring recordings import/export..
too cool...hehe

[pBS]

to use a script with multiple commands, use:
sqlite3.exe "database.db3" ".read commands.sql"
with commands being inside the file commands.sql..
that way you can do many ops and use the .X commands..
just thought yall might wanna know..
[i have to, to .import my data back in, can't do from one cmd]

[Jim_]

P.S. JIM:
do you know what each of the recording status numbers mean?

Yes, but I don’t recall the name of the thread they came from.

STATUS_PENDING = 0
STATUS_IN_PROGRESS = 1
STATUS_COMPLETED = 2
STATUS_COMPLETED_WITH_ERROR = 3
STATUS_PLACE_HOLDER = 4
STATUS_CONFLICT = 5
STATUS_DELETED = 6