How to use MSDE(SQL Server) as GBPVR Database

[KingArgyle]

I've been meaning to get back to testing this as the performance of Access is starting to bug me, but I haven't had the time to retest. If I can get some time between all the other projects and work (which is crazy right now), I'll see if I can figure out anything. I don't expect to have this as a high priority as it isn't something that he uses.

[Darryl]

I'm willing to work with the developers to test and make GBPVR & MSDE work together. I have a fair bit of experience writing VB/.Net code to work with the MSDE and converting to/from Access.

I've fired up the SQL Profiler and I'll work out which SQL commands need to be modified to make it work. (with suggested solutions)

I'd suggest a switch in the config file to toggle between MSDE and Access and change the SQL statement according to that switch.

Cheers,
Darryl

[cheval]

Would it not make sense to support mysql rather than a database that probably most people do not really have easy access to.

[gruskada]

There's no need for the full-blown version of SQL Server, which I doubt very few people here have access to, but to the best of my knowledge, MSDE and SQL Server 2005 Express can be freely redistributed in applications. And, for developers, SQL Server 2k5 Express is a breeze to work with with .NET 2.0 (using Visual Studio 2005, at least). Also, the first post in this thread shows how to download and set up MSDE yourself.

gruskada

[Darryl]

First up. I'm trying to update the EPG by right click on the icon on the tray.
It runs for a short while, then stops with these log entries

ERROR [3] Failure in ChannelFactory:toreChannels(): Specified cast is not valid.

ERROR [3] Failure in ChannelFactory:toreChannels(): at xd426863f4d052758.x645bf0ede6e7454f.x091b0bb67b0c21bd(ICollection xb539b3782652833f, Int32 xeba3e20435eb0e32)



The last 2 statements in the SQL trace are

"insert into PROGRAMME (name, sub_title, description, start_time, end_time, channel_oid, unique_identifier, rating) values ( 'Billy the Cat', 'Airborne Hijinks', 'Billy is a boy in cat''s clothing! After Billy teased one cat too many a magician decided it was time to teach him a lesson - so he turned Billy into a cat. At last, he gets a taste of his own medicine.', 'Jan 16 2006 6:00:00:000AM', 'Jan 16 2006 6:25:00:000AM', 1, '', '')"

select @@identity


Now, that record appears in the PROGRAMME table, so the insert statement has worked. The next statement should return the OID field from the last inserted record. Unfortunately I cant see what was returned,only the statement, but when I try the same two statements in the Query Analyser, they work, so I'll assume the select statement worked.

Whatever comes next in the code could be the problem.

Does this help find where the error lies?

Regards,
Darryl

[Darryl]

I'm also seeing this SQL statement

delete from PROGRAMME p where end_time < 'Jan 17 2006 7:43:22:000PM' and not exists (select 1 from RECORDING_SCHEDULE rs where rs.programme_oid = p.oid)'


I think this is the error referenced back in July, so its still there. If I understand the statement, delete any records from PROGRAMME table that end prior to a date, that dont have a corrosponding entry in the RECORDING_SCHEDULE table.

Is that right?


Also, I understand why you're using table aliases to make the statements shorter and easier to read, however SQL doesnt understand the syntax and consequently generates an error.
The solution is to leave out the alias and use the full table name whenever it is referenced.

eg

delete from PROGRAMME p where end_time < 'Jan 17 2006 7:43:22:000PM' and not exists (select 1 from RECORDING_SCHEDULE rs where rs.programme_oid = p.oid)'

becomes

delete from PROGRAMME where end_time < 'Jan 17 2006 7:43:22:000PM' and not exists (select 1 from RECORDING_SCHEDULE where RECORDING_SCHEDULE.programme_oid = PROGRAMME.oid)'



One last thing.. dates should be in the format of 'YYYY-MM-DD HH:mms' when in SQL statements then there is no ambiguity, so that delete statment should really be

delete from PROGRAMME where end_time < '2006-01-17 19:43:22' and not exists (select 1 from RECORDING_SCHEDULE where RECORDING_SCHEDULE.programme_oid = PROGRAMME.oid)'


Regards,
Darryl

[sub]

"insert into PROGRAMME (name, sub_title, description, start_time, end_time, channel_oid, unique_identifier, rating) values ( 'Billy the Cat', 'Airborne Hijinks', 'Billy is a boy in cat''s clothing! After Billy teased one cat too many a magician decided it was time to teach him a lesson - so he turned Billy into a cat. At last, he gets a taste of his own medicine.', 'Jan 16 2006 6:00:00:000AM', 'Jan 16 2006 6:25:00:000AM', 1, '', '')"

select @@identity


Now, that record appears in the PROGRAMME table, so the insert statement has worked. The next statement should return the OID field from the last inserted record. Unfortunately I cant see what was returned,only the statement, but when I try the same two statements in the Query Analyser, they work, so I'll assume the select statement worked.

Whatever comes next in the code could be the problem.

The only database related thing that happens next is:

Object returnObject = indentityQuery.ExecuteScalar();
int programOID = (int)System.Decimal.ToInt32(returnObject);

To me this looks correct, but if it isnt, then tell me what it should be. It obviously works fine with access.

[sub]

I'm also seeing this SQL statement

delete from PROGRAMME p where end_time < 'Jan 17 2006 7:43:22:000PM' and not exists (select 1 from RECORDING_SCHEDULE rs where rs.programme_oid = p.oid)'


I think this is the error referenced back in July, so its still there. If I understand the statement, delete any records from PROGRAMME table that end prior to a date, that dont have a corrosponding entry in the RECORDING_SCHEDULE table.

Is that right?


Also, I understand why you're using table aliases to make the statements shorter and easier to read, however SQL doesnt understand the syntax and consequently generates an error.
The solution is to leave out the alias and use the full table name whenever it is referenced.

eg

delete from PROGRAMME p where end_time < 'Jan 17 2006 7:43:22:000PM' and not exists (select 1 from RECORDING_SCHEDULE rs where rs.programme_oid = p.oid)'

becomes

delete from PROGRAMME where end_time < 'Jan 17 2006 7:43:22:000PM' and not exists (select 1 from RECORDING_SCHEDULE where RECORDING_SCHEDULE.programme_oid = PROGRAMME.oid)'

To be honest, I use this everywhere, and the idea of changing doesnt sound like fun. I originally picked up this syntax from SQL Server 6.5, so I'm surprised its no longer used. It also works in fine Oracle. It was particularly useful for when the subquery contained the same table as the outer query - using the alias would make it clear exactly which one you were talking about.

[sub]

One last thing.. dates should be in the format of 'YYYY-MM-DD HH:mms' when in SQL statements then there is no ambiguity, so that delete statment should really be

delete from PROGRAMME where end_time < '2006-01-17 19:43:22' and not exists (select 1 from RECORDING_SCHEDULE where RECORDING_SCHEDULE.programme_oid = PROGRAMME.oid)'

I dont format dates as strings myself, instead I use paremeters and the .net or oledb layer must be doing the conversion of my datetime objects into strings of this format.

[Darryl]

The only database related thing that happens next is:

Object returnObject = indentityQuery.ExecuteScalar();
int programOID = (int)System.Decimal.ToInt32(returnObject);

To me this looks correct, but if it isnt, then tell me what it should be. It obviously works fine with access.

http://msdn.microsoft.com/library/defaul...s_50u1.asp

The @@IDENTITY returns a 'numeric', not an int, even though the table field contains a integer, you'll get the value in a numeric data type..

So its probably this line

> int programOID = (int)System.Decimal.ToInt32(returnObject);

trying to shoehorn a numeric into an int variable.



http://msdn.microsoft.com/library/defaul...b_7msw.asp

> decimal
> Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.

> numeric
> Functionally equivalent to decimal.

You could change programOID to be an System.Int32 or System.Int64 or even System.Double, or add an extra line to accept the numeric into a System.Double and then convert to an integer and assign to programOID.

Darryl