How to use MSDE(SQL Server) as GBPVR Database

[KingArgyle]

Yep, I tried that as well.

Also, tried, creating a View that just had getdate() in the select statement. But SQL Server doesn't allow Scalar functions not to have a parameter, all scalar functions must have a parameter.

Only way to get around this is to handle it outside the application.

[KingArgyle]

Actually, it might work doing something like this:

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">
CREATE VIEW CurTimeView
AS
SELECT RAND() as curTime
GO

CREATE FUNCTION dbo.now() RETURNS datetime
AS
BEGIN
RETURN (SELECT curTime from CurTimeView)
END
GO
[/QUOTE]

I tried something similar last night but SQL Server kept complaining that I didn't have a parameter for the scalar function. I found syntax examples on the web for these formats, and all are supposed to work on SQL Server 2000. Unfortunately I don't have MSDE or Enterprise Manager to test these out at work.

Here is an article that shows how to do this as well:
http://www.extremeexperts.com/sql/articl...tions.aspx

[cginzel]

Quote[/b] ]Easiest way to do this, is to just run the Import Data wizard, and it'll set it up for you.

I suspect a Identity column is not matching up, and causing the storechannel to fail.

Okay, I used the import wizard and GBPVR worked great except for the Search Guide which apparently also uses the Now() function so it fails to show anything when selecting a letter.

Then I went to the configuration and tried to update the EPG.  This time I got a different error regarding a null oid on the PROGRAMME table, so made that column an identity and did the refresh again and it completed without an error message, however the log still has the follwoing line:

12/22/2004 11:23:41 AM.468 WARNING [2134] Unexpected error removing old shows from EPG: Line 1: Incorrect syntax near 'p'.

So something is not completely correct...

The import translated the text fields into nvarchar and they seem to be working.  Are you sure these need to be converted to a simple varchar?

Also, should I make oid an identity in all of the tables?

-Charles

[cginzel]

Okay, I ran the following DDL:

CREATE VIEW CurrentDate AS SELECT Date = GETDATE()
GO
CREATE FUNCTION now () RETURNS DATETIME AS
BEGIN
DECLARE @myDateTime DATETIME
SELECT @myDateTime = Date FROM CurrentDate
RETURN (@myDateTime)
END
GO
GRANT EXECUTE ON now TO gbpvr
GO

But I still see the following in the log:

12/22/2004 11:44:24 AM.640 ERROR [75] Failure in ChannelFactory::getCurrentProgrammeList(): 'now' is not a recognized function name.

The reason is the function name is really dbo.now... In Oracle I would just define a synonym and be done with it!

Isn't there a way to alias the gbpvr userid as dbo in SQL Server?

-Charles

[KingArgyle]

You can probably get away with nvarchar fields.

What you'll notice on the last error message, is that it is trying to remove old programs from the program table. I suspect the code that needs to be executed is something like:

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">
SQL Server:
DELETE FROM PROGRAMME WHERE end_time < getdate()

Access:
DELETE FROM PROGRAMME WHERE end_time < now()

[/QUOTE]

If that code doesn't execute correctly, you'll just end with up old guide information in the database.

To clean up the guide, you could execute an OSQL command line command in the PostEPGUpdate.bat file, and have it clean up the guide after it has finished.

As for the Search on the Guide, have you tried Jorm's Fast Find plugin? I don't know exactly how he is searching that information. I primarily use the Web Admin for scheduling and searching for recordings, so I don't use the Guide built into the main GBPVR application.

If you can get the NOW() udf function implemented, that might take care of your guide problems.

[cginzel]

According to this post:

http://groups-beta.google.com/group....0f492c2

Scaler UDFs require the database owner prefix...

And apparently even if you are logged in as sa!

Ultimately, maybe sub can find a way to make his queries more db egnostic... or maybe getdate() can be defined within Access... so getdate() can be used thoughout.

-Charles

[KingArgyle]

Quote[/b] ]

12/22/2004 11:44:24 AM.640 ERROR [75] Failure in ChannelFactory::getCurrentProgrammeList(): 'now' is not a recognized function name.

The reason is the function name is really dbo.now... In Oracle I would just define a synonym and be done with it!

Might be able to specify the owner of the function, doing

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">
create function gbpvr.now()
[/QUOTE]

You can also just try changing the owner of the function with the Enterprise Manager.

This article shows that you can execute a UDF without the owner qualification on it.
http://www.devarticles.com/c....3

[KingArgyle]

Yeah, you are correct, I was looking at a table function.

I'm going to check with Jorm and see if he is using the now() function in his fast find plugin.

[cginzel]

Quote[/b] ]Yeah, you are correct, I was looking at a table function.

Yup... I tried defining the function as gbpvr.now(), but then all references must be done like:

select gbpvr.now()

so that's still a non-starter...

-Charles

[KingArgyle]

A couple of solutions come to mind, both require reworking existing code though:

Create a SQLFunctionHelper class file, that contains the following definitions:

SQLFunctionHelper.Today
SQLFunctionHelper.Now

When it is initialized, the Provider string for connecting to the database is passed to it. This string is queried, and if the SQL Server provider is specified:

SQLFunctionHelper.Today = &quot;getdate()&quot;
SQLFunctionHelper.Now = &quot;getdate()&quot;

For Access:
SQLFunctionHelper.Today = &quot;now()&quot;
SQLFunctionHelper.Now = &quot;now()&quot;

And when the inline SQL is created for accessing the data, something like this can be coded:

string sqlString = &quot;DELETE FROM PROGRAMME WHERE end_time &lt; &quot; + SQLFunctionHelper.Now

Which would generate:

SQL Server:
DELETE FROM PROGRAMME WHERE end_time &lt; getDate()

Access:
DELETE FROM PRPGRAMME WHERE end_time &lt; now()

Thus making the same code function on either Access or SQL Server. A simple modificaton could be made to the SQLFunctionHelper class to support other Databases as well.

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.

If the SQL was stored in a stored procedure it would be a small matter of porting it to SQL Server, and creating the same stored procedure there as exists in Access. Plus on Enterprise level databases these are pre-compiled so the access paths are already calculated, and data is returned quicker.