It's possible to write an external procedure that return a result set? Can  I receive some
hints to do this please?

Dan


My first though was yes since you can write an external procedure using sql. My second thought was no because sql will not be able to process the result. My third thought was "what do you want to do this for?"

It really depends on what you want to achieve what is the best way of going about this. A stored procedure or view will produce a result set so why not use one of those or even a standard query?

My guess is that what you want to do is some processing which isn't easily achievable using sql so you're thinking of using Delphi to do it and return the result set? If that's right then either look at using a script and ElevateDB's procedural language or developing a verb as an external procedure and returning a true/false.

I did post a TEXTSEARCH routine (for where CLOBs aren't indexed) into the extensions ng if that's the sort of thing you want - look under textsearch, text filters & word generators

Roy Lambert [Team Elevate]

I have an external full text search engine and I want to be able to include and use in
ElevateDB SQL, for further processing,  the result set generated by this external FTS for
a query expression (a list of row IDs as INTEGER and ranking info as FLOAT).

I need that in order to have a remote SQL server with an extension that allows to write
FTS queries using my own engine.

Roy Lambert wrote:

Dan


My first though was yes since you can write an external procedure using sql. My second
thought was no because sql will not be able to process the result. My third thought was
"what do you want to do this for?"

It really depends on what you want to achieve what is the best way of going about this. A
stored procedure or view will produce a result set so why not use one of those or even a
standard query?

My guess is that what you want to do is some processing which isn't easily achievable
using sql so you're thinking of using Delphi to do it and return the result set? If that's
right then either look at using a script and ElevateDB's procedural language or developing
a verb as an external procedure and returning a true/false.

I did post a TEXTSEARCH routine (for where CLOBs aren't indexed) into the extensions ng if
that's the sort of thing you want - look under textsearch, text filters & word generators

Roy Lambert [Team Elevate]

Dan


>I have an external full text search engine and I want to be able to include and use in
>ElevateDB SQL, for further processing, the result set generated by this external FTS for
>a query expression (a list of row IDs as INTEGER and ranking info as FLOAT).

I'm sure someone will correct me but I can't see an external function being of much use here. Lets see if I've got it right:

You call the external search engine and it returns a result set (in ElevateDB format??) and you want to do something else with it in ElevateDB. The only thing you can do is use it in a JOIN or sub SELECT expression since ElevateDB doesn't have anything in it yet to do anything else. You certainly don't want a function (sql verb) to be called on a row by row basis because this would just be a performance killer, you want to generate this result set at the start of a query and then just use it.

Writing an external function to shellexecute a command to this external full text search engine (which is the way I'd do it with my current understanding) is a doddle but I'm not sure where in a script (it would need to be a script) you could call it.

Roy Lambert [Team Elevate]

Dan,

<<I have an external full text search engine and I want to be able to
include and use in ElevateDB SQL, for further processing,  the result set
generated by this external FTS for a query expression (a list of row IDs as
INTEGER and ranking info as FLOAT).

I need that in order to have a remote SQL server with an extension that
allows to write FTS queries using my own engine. >>

The only way to return this information is via a BLOB OUT parameter.  Then
you'll need to parse it in the application and put it to use that way.

External procedures are most useful for doing things that aren't exactly
ElevateDB data manipulation.  The SQL procedures are most useful for data
manipulation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for your advice in the matter. I'm just beginning to build the project and I want
to use the right road for this. How fast can I expect to be the parsing of the blob data,
SQL/PSM is interpreted code?

Dan.

Dan,

Just an idea. It maybe too slow or not fitting your needs, but here it is: EDB can import
tables. Writing the data you want to return into the expected format could be one way to
do it.

An other more difficult but probably faster approach could be to write the returned data
into a stream and have EDB to read it from there. Of course you would need to know the
structure EDB uses to save result sets to a stream. (This is something I will probably
have to explore too.)

Regards, Leslie   

Dan,

<< Thanks for your advice in the matter. I'm just beginning to build the
project and I want to use the right road for this. How fast can I expect to
be the parsing of the blob data, SQL/PSM is interpreted code? >>

Well, it is compiled into tokens, so it isn't 100% interpreted.

However, I wasn't actually referring to SQL/PSM when I said "Then you'll
need to parse it in the application and put it to use that way."  I was
assuming that the application-level parsing would be done in Delphi code.
My reference to SQL/PSM was in the context of simply pointing out their its
purpose relative to the native external procedures.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dan

Can you post (at least) pseudocode to show just what you're trying to achieve. Unless I'm totally missing the point here you're going to

1. Call your external engine
2. Get the results from the external engine
3. Run a SELECT, UPDATE or DELETE in ElevateDB
    a) for each row in a WHERE clause use the output of the external engine

If I'm right I not sure of a way to do 1. or 2. in SQL in ElevateDB, I have some thoughts but I don't know if they'd work.

You'd probably be better off with a compound solution - interface to your external engine using Delphi - get the results and build SQL which would include a call to a custom function (SQL or Delphi) to which would be passed the results of your external text filter engine as two varchars (csv format) which you can then process.


Roy Lambert [Team Elevate]

A simple import in csv format is a good idea. Thanks Leslie. And Roy you're right:

1. Call the external engine
2. Get the results from the external engine
3. Run SQL in ElevateDB using the result set.

but all that is done on a remote server. All processing is going to take place in the
server. The FTS could return locally 100.000 records, but with dinamic SQL based on that,
the result set to be sent to the user shrinks down to 1000 records, perfectly ok for a
remote session.

I intend to use only the data transport layer provided by ElevateDB, and not to use
another connection to the server. That's why I want to find the best interface between EDB
and Delphi, to be able to do a speedy transfer of result sets generated by Delphi code
into EDB SQL enviroment for further processing and remote access.