I have some Delphi code that is tried and tested.
I now want that functionality in ElevateDB.

Pseudo code :-
  MyFunction(const str1, str2 : String) : Real;

    Nested ProcedureA
      Nested ProcedureAwithinA
      Nested ProcedureBwithinA

    Nested ProcedureB
      Calls ProcedureA.
      Also calls itself recursively.

  Begin
    Result := ProcedureB;
  End;

Can a database Function have nested procedures in it?

Will the recursive calls work?

Is this best made into an external Function?

Does someone who where I can find documentation for setting up and
calling an external Function?

Regards,
  Peter Evans

Peter

>Can a database Function have nested procedures in it?
>
>Will the recursive calls work?

I think the answer is yes, never tried it and I wouldn't even if it is possible simply because it would be a nightmare for debugging.

>Is this best made into an external Function?

In my view YES

>Does someone who where I can find documentation for setting up and
>calling an external Function?

Nope - you can load one of Tim's templates (pick the lightweight ones) and they're pretty easy to follow. You can also look in the extensions ng for a number of examples.

The process is essentially:

1. load template
2. code function
3. compile to dll
4. stuff dll where ElevateDB can see it
5. tell ElevateDB where you put it
6. create function pointing at external module

If you ever used DBISAM's UDF engine customisation its similar (but different)

Roy Lambert

Peter,

<< Is this best made into an external Function? >>

Yes, Roy is correct - this is best dealt with as an external function.  You won't have to touch the existing Delphi code, and then you can use it anywhere you would use a built-in EDB SQL function.

<< Does someone who where I can find documentation for setting up and calling an external Function? >>

Roy's description of the steps are essentially what is involved.  Using the external module template in Delphi is the key, and the rest is just hooking up the plumbing:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=External_Modules

Tim Young
Elevate Software
www.elevatesoft.com

On 7/08/2015 3:33 AM, Tim Young [Elevate Software] wrote:

Thanks for the link.

Regards,
  Peter Evans

On 7/08/2015 3:33 AM, Tim Young [Elevate Software] wrote:
> Peter,

Using the external module template in Delphi is the key, and the rest is
just hooking up the plumbing:
>

What I did was look at the example EDBHash submitted back on 30 Sep 2009
in the Extensions area.

I made a few changes because I am using Unicode. It compiled OK.

Then I created my own routine. I know it was installed because I
ran :-

  SELECT * FROM Modules

as advised in the manual for External Modules.

The problem was when I ran some SQL. I didn't get the expected results.
Thinking there was some error I ran SQL like :-

SELECT
 GreatFunction('DEF', 'ABC') IS NULL
FROM MyTable

I got one result for each row in MyTable.
Seems like something has gone wrong?

When I tried adding a parameter I got an error. This was expected, so I
believe the function has been set up in the database OK.

How do I narrow down where the error arises?

Regards,
  Peter Evans

Peter

It is possible to output "Events" to a "LogEvents" in EDB from within SQL Functions and Procedures.

The best thing I can suggest is adding some lines within the SQL of your functions:

SET LOG MESSAGE TO 'Message XXX Variable value: ' + CAST(aVariable AS VARCHAR);

You can then use the OnLogMessage event of the script component in your Delphi applciation to see where the script stops.

This will allow you a measure of "debugging" ...

Peter,

<< The problem was when I ran some SQL. I didn't get the expected results. Thinking there was some error I ran SQL like :-

SELECT
 GreatFunction('DEF', 'ABC') IS NULL
FROM MyTable

I got one result for each row in MyTable. Seems like something has gone wrong? >>

Why would you think that ?  Did you, instead, want this:

SELECT
 GreatFunction('DEF', 'ABC') IS NULL

?

That will generate a single result row.

Tim Young
Elevate Software
www.elevatesoft.com

On 10/08/2015 11:44 PM, Tim Young [Elevate Software] wrote:
> Peter,
>
> << The problem was when I ran some SQL. I didn't get the expected results.

I am about to place in Extensions a ZIP file with the external function
GreatFunction.

This is a very simple external function. I can not get it to work.

Also, have placed the SQL that sets up the Module and Function. Perhaps
someone will be able to assist in finding out what is going wrong.

Regards,
  Peter Evans

Peter


I've had a look - first a telling off - please don't put test code into the extensions newsgroup - that should be reserved for actual working (or thought to be working) extensions - use the binaries newsgroup instead.

<<SELECT
 GreatFunction('DEF', 'ABC') IS NULL
FROM MyTable>>

Looking at your code I would expect you to get a False for every row in your table. The only possible results from your function are 1.0, 0.0 or -1 and none of those are a null.

<<I got one result for each row in MyTable.
Seems like something has gone wrong?>>

Why? What do you get and what are you expecting?


Roy Lambert