As part of rewriting my query generator I'm working on the design time editor.

Stage 1 - select database
Stage 2 - select table(s) - and configure
Stage 3 select fields - and configure and enable for user access

At Stage 1 I wanted to present a list of available databases and descriptions and I wanted to use my RCF sql function to format the info


Qry.SQL.Text := 'SELECT LIST(RCF(Description, Name, ''-''),#13) AS DB FROM Databases WHERE InMemory = FALSE';

But the function RCF doesn't exist in the default database.

Is there anyway to access it?

Roy Lambert

>But the function RCF doesn't exist in the default database.
Is there anyway to access it?<

Can you create an "empty" database called "MyGlobal" and define the functions you want to be global like RCF, then connect to MyGlobal and run your query from that?

Barry

Barry

>>But the function RCF doesn't exist in the default database.
>Is there anyway to access it?<
>
>Can you create an "empty" database called "MyGlobal" and define the functions you want to be global like RCF, then connect to MyGlobal and run your query from that?

Interesting idea but I don't think its feasible. This is for my query generator  - pass the session across and use that info to get a list of databases. I'd need to create a MyGlobal database accessible to r every session. Doable but a right pain in the backside.

I have a workround because in this instance I can use a simple COALESCE but I'd love to be able to define and access functions at session level rather than database.

Roy

>>I have a workround because in this instance I can use a simple COALESCE but I'd love to be able to define and >>access functions at session level rather than database.

It would be really useful Roy.

A way to make this work (just talking about the semantics of the thing) would be for the following SQL to be allowable:

SELECT
 Name,
 MyDatabase.MyFunction(PublishedOn)

FROM Information.Tables

Or to allow User-defined functions on the Default DB.

--

Both these options come with issues (like the security of the default DB) ... but maybe Tim will consider them & add them??

Is it possible to add a user-defined Module to the default DB? This is a bit heavy-duty but if it was you could use a custom-module to provide a range of useful base-functions at the default-db level.

Roy,

<< But the function RCF doesn't exist in the default database.  Is there
anyway to access it? >>

Yes, make sure the DatabaseName for the Qry component is set to point to the
database that contains the function, like this:

Qry.DatabaseName:='MyDatabase';
Qry.SQL.Text := 'SELECT LIST(RCF(Description, Name, ''-''),#13) AS DB FROM
Configuration.Databases WHERE InMemory = FALSE';

Tim Young
Elevate Software
www.elevatesoft.com

Adam,

<< Is it possible to add a user-defined Module to the default DB? >>

No, not currently.

<< This is a bit heavy-duty but if it was you could use a custom-module to
provide a range of useful base-functions at the default-db level. >>

I've tried adding this in the past, but there are some dependency issues
that I haven't been able to resolve.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Yes, make sure the DatabaseName for the Qry component is set to point to the
>database that contains the function, like this:
>
> Qry.DatabaseName:='MyDatabase';
> Qry.SQL.Text := 'SELECT LIST(RCF(Description, Name, ''-''),#13) AS DB FROM
>Configuration.Databases WHERE InMemory = FALSE';

OK, but not viable in this case - see my reply to Barry above.

Roy Lambert

Roy,

<< OK, but not viable in this case - see my reply to Barry above. >>

How so ?  If the database that contains the RCF function is accessible (at
all) by the current session, then you can use it in that fashion.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< OK, but not viable in this case - see my reply to Barry above. >>
>
>How so ? If the database that contains the RCF function is accessible (at
>all) by the current session, then you can use it in that fashion.

Hmm. After switching my brain back on, adding fresh oil and changing the spark plugs I realise I can get a list of the databases, query each of then for the function, switch the context to that database, then get a list of the available databases to present to the user.

Doable but yuck

Roy Lambert