How to use MSDE(SQL Server) as GBPVR Database

[cginzel]

Quote[/b] ]Really, the best solution in the long run is moving the items to a Access Stored Procedure.  I like stored procedures for several reasons.  Seperates the Data from the Business Logic, and the Presentation Logic.  Plus allows for easier support of multiple databases.

Separation of business logic from presentation is a good thing, but the same is true on the back end.  Ideally, I never put any business logic in the database, unless performance dictates...

Stored procedures are not portable!  Try migrating from SQL Server to Oracle!

All of my development now is strictly multi-tiered and the database does one thing and that is to hold and index data...

The logic goes in the middle.  Even in a client/server architecture, I separate out the business logic so even though technically the business logic is compiled into the client, internally it is still acting as though it were a separate tier...

Since sub is using .Net, a better solution (although more work ) would be to abstract all database access into a separate assembly.  Then he could publish the API and we could implement a MS SQL Server assembly in it's place... or any other database...

Technically, with this solution, the database wouldn't even have to have the same tables or names!

-Charles

[KingArgyle]

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">
Stored procedures are not portable! Try migrating from SQL Server to Oracle!
[/QUOTE]

Yeah, I work a lot in Java, and the thing there is to use EJB's for retrieval of data from a database. I agree the Stored Procedures themselves are not necessarily syntax portable, but I still say that if you have a large application (like a .NET or JAVA J2EE) application, it is easier to change a stored procedure logic and deploy it, then it is to redeploy a whole application. It's a pain to have to redeploy an entire application when just recompiling a stored procedure takes less time.

For the most part, GBPVR doesn't use any complex logic that would require complex Stored Procedures, just move the Prepared SELECTs to equivalnt stored procedures, and there would be minor migration issues. The problem comes when you start getting into PL/SQL or T-SQL programs that include complex logic. Those are a pain to port. Same goes for DB2 as each database contains it's own way of implementing stored procedures.

Anyway, we are getting off the main topic, which is getting GBPVR to work with MSDE. Some minor tweaking in the plugins, and also the main source code would make using GBPVR with another database easier. We are close, just a few minor (or non-minor depending on your needs) errors.

I took a look at Jorm's Find Fast plugin, and with a minor tweak it can be easily be made to work with MSDE. That would give you the ability to Search the Episode guide from within GBPVR. I may take a look at this tonight. If I get it coded, I'll post it here, and send it to Jorm also.

[cginzel]

KingArgyle,

I've just discovered a new wrinkle when using MSDE as the backend... GB-PVR/MediaMVP does not respond to the Back/Exit or Stop buttons while viewing a recording. And if I press the Menu button, it returns to the menu, but seems to still be playing the video because I still hear the audio!

I don't recall this being so when I first switched to using the MSDE backend, but when I change the connection string back to the default Access DB, the buttons work as expected!

Explain how that can be...

-Charles

[KingArgyle]

Not sure, this is probably something I'll have to ask Sub.

I don't use GBPVR for watching stuff, just for recording so haven't run into that problem. (Still have a few ActiveX Windows that pop up when watching recordings).

I know depending on the remote control you use, GBPVR stores some information in the databases. But if other buttons on your remote are working correctly, then not sure why those specific ones wouldn't be working.

[sub]

I cant think what would cause that. I dont have MSDE installed these days, so cant check it myself, but you can email me a gbpvr.exe.log file if you want. This would probably contain an error message.

[DaveParker]

I've set up my GB-PVR to use SQL Server for its data and most things seem to be working fine. The only problem I've found so far is I no longer seem to be able to set reoccurring recordings. When I try to do this they appear in the reoccurring list, but never appear in the pending list. They also don't highlight in red (not even on the program in the TV Guide I clicked to set-up the recording from).

In the log files, I've come across the following error.
13/01/2005 20:34:13.968 ERROR [2] Unabled to save ScheduledRecording: Specified cast is not valid.
13/01/2005 20:34:13.968 ERROR [2] at ba.e(ScheduledRecording A_0)
13/01/2005 20:34:13.984 ERROR [2] ScheduleSeasonRecordings() encounter a problem: IErrorInfo.GetDescription failed with DB_E_NOLOCALE(0x80040E41).

I used the upsizing wizard in Access 2000 to create the SQL Server database. Are there any solutions to this such as changing the datatype of a field etc?

Also a bit off topic but is it possible to make the description field for each program longer? I tried this in the Access database by changing it as a Text (255) to Memo, but I think the executable was still trimming it to 255.

[DaveParker]

Ooops! Just re-read through the whole topic again and it sounds like you're all already aware of this issue. So, umm, anyone worked out a fix yet?

[KingArgyle]

DaveParker, you might want to try the External Recording Manager, as I wrote this to work with either MSDE or Access as the datasource. You can use it as a replacement or as an add on for the built in Recording Manager.

You can setup your ReOccuring recordings with the External Recording Manager, and it should correctly add your recordings to the Schedule.

Also, if you run into the Search issue discussed earlier in this thread. Jorm has fixed his Fast Find plugin to work with MSDE, so that should take care of that issue. Sub is also fixing it in a future release of GBPVR.

[cginzel]

Is anyone using the v908 GBPVR with MSDE? I had switched back to the access database because of the now() function reference problem, but supposedly sub has fixed that. So now I'm trying to get MSDE back up and running to test with the new version and I can't seem to get GBPVR or the config program to connect to my database...

I used DTS to import the database. I created a gbpvr user with the password gbpvr. I can connect to the database using this gbpvr/gbpvr pair using SQL Query Analyzer so I'm fairly sure this is setup correctly, but each time I try and run the config program I get a &quot;Cannot open database requested in login'gbpvr'. Login fails.&quot; crash...

Here is my connection string from the config.xml file:

&lt;DatabaseConnectionString&gt;Provider=sqloledb;Data Source=base\gbpvr;User Id=gbpvr;Password=gbpvr;Initial Catalog=gbpvr;&lt;/DatabaseConnectionString&gt;

Has someting changed in the login for the v908 release or could this be caused by the .Net update I installed the other day which is apprently affecting my web access to GBPVR as well?

-Charles

[KingArgyle]

I think your problem is your Data Source attribute.

Provider=sqloledb;Data Source=(local);User Id=gbpvr;Password=gbpvr;Initial Catalog=gbpvr;

Try chaning to either (local) or the msde server name.

I haven't tried it yet with .908 too many little quirks yet.