how to access gbpvr.db3 file using SQLite

[Wakalaka]

Using Finisar.SQLite DLLs here.

I have a simple C# program to output the capture sources

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Finisar.SQLite;

namespace sqltest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SQLiteConnection sqconn;
            string dbpath = "c:\\temp\\gbpvr.db3";
            string strconn = "Data Source=" + dbpath + ";Version=3;";
            sqconn = new SQLiteConnection(strconn);
            sqconn.Open();

            int i = 1;
            bool result = false;
            do
            {
                string strSQL = "SELECT * FROM CAPTURE_SOURCE WHERE oid='" + i.ToString() + "'";
                SQLiteCommand sqc = new SQLiteCommand(strSQL, sqconn);
                SQLiteDataReader sqr = sqc.ExecuteReader();
                result = sqr.Read();
                if (result)
                {
                    int alloc_order = (int) sqr["allocation_order"];
                    listBox1.Items.Add(alloc_order.ToString() + " " + sqr["name"]);
                    i++;
                }
            } while (result);
            sqconn.Close();
        }
    }
}

I added a reference to SQLite.Net.dll. When I run the program, I get an exception on sqconn.Open(). Any idea what I have wrong?

[Wakalaka]

Solved the problem. A search on the SourceForge forums for Finisar.SQLite revealed this. I grabbed the latest SQLite3.dll from sqlite.org and my program now works (with a slight mod).

Code:

if (result)
{
   int alloc_order = (int) ((Int64) sqr["allocation_order"]);
   listBox1.Items.Add("alloc[" + alloc_order.ToString() + "] oid[" + i.ToString() + "] " + sqr["name"]);
   i++;
}

[HydroChronic]

Can this same package be used to read Recording information from gbpvr.db3? I have been unable to get it to work using the Common GBPVR Utilities dll and the feature would be extremely helpful for those people who don't rename their recording files.

[Jeff]

You can use the dll that gbpvr itself uses, System.Data.SQLite.dll.

Documentation is available at http://sqlite.phxsoftware.com/

Just be careful that you don't install a newer version of the dll on your machine. It can break things if you are not familiar with how to manage multiple versions of .dot assemblies.

As to you other comment, what problem are you having using the commongbpvrutilites.dll?

Jeff

[Wakalaka]

You can use the dll that gbpvr itself uses, System.Data.SQLite.dll.

Documentation is available at http://sqlite.phxsoftware.com/

Just be careful that you don't install a newer version of the dll on your machine. It can break things if you are not familiar with how to manage multiple versions of .dot assemblies.

ok, will try it out.

[zehd]

ok, will try it out.

I have also discovered a really good dll that lets one use VB6. That appeals to us old farts...

[HydroChronic]

As to you other comment, what problem are you having using the commongbpvrutilites.dll?

Jeff

My ultimate goal was being able to replace the file names in my file list with the Recording info (ie. Show Name, Episode Name and channel number) for files recorded by GBPVR. I tried to do this when I first started writing the plugin using the ArchiveDB datatype, but I got error messages saying that I was missing the file "Video Archive.mdb" (or something along those lines). I was also unable to find any documentation pertaining to how to change the database file which is referenced by that datatype. I had put this problem to the side in order to finish the functionality of the plugin, but now that is done and I am back to this problem again.

[zehd]

My ultimate goal was being able to replace the file names in my file list with the Recording info (ie. Show Name, Episode Name and channel number) for files recorded by GBPVR. I tried to do this when I first started writing the plugin using the ArchiveDB datatype, but I got error messages saying that I was missing the file "Video Archive.mdb" (or something along those lines). I was also unable to find any documentation pertaining to how to change the database file which is referenced by that datatype. I had put this problem to the side in order to finish the functionality of the plugin, but now that is done and I am back to this problem again.

Interesting how there are a few of us working on the same type of program.

Originally there was renamerecording.exe. And then there was addepisode.exe.

I've just completed one that, after getting the info from the database, also gets season info from the net (epguides.com) and uses that to build the new file name...

I may not have worked on it, if I had known there were others doing the same... But it's all good :-)

[zehd]

For anyone still using VB6, I have some source and the wrapper dll that makes accessing the gb-pvr db3 file very easy to access...

I've modified the source a bit to also handle Unicode to UTF-8 and back encoding/decoding so that other languages besides English can also be referenced...

If you interested check it out...

[HydroChronic]

Interesting how there are a few of us working on the same type of program.

Originally there was renamerecording.exe. And then there was addepisode.exe.

I've just completed one that, after getting the info from the database, also gets season info from the net (epguides.com) and uses that to build the new file name...

I may not have worked on it, if I had known there were others doing the same... But it's all good :-)

The plugin I am currently working on deals with manually editing commercials in a comskip txt file. But since I expect the user to include their Recordings folder as one of the folders where files are selected from, I would like to be able to give the user more than just the file name with the show name and various numbers.

You say that you just completed a tool which gets information from GBPVR's database. How did you go about doing that, since that is basically what I am trying to do.