Board index » delphi » Stored procedure calling DLL or EXE?

Stored procedure calling DLL or EXE?

I'd like to do something like this on MS SQL Server. I don't know the area
well enough so maybe some I am not saying something properly...

The end user on a client machine would call a stored procedure on the
server. The procedure would then call something on the server (what? - dll,
exe?) written in a programming language (Delphi or C++). This would produce
a temporary table on the server, and the user would get back the name of
this table as a result of that stored procedure (how?).

As a variation, instead of a temporary table I would like the procedure to
return a view and the end user would operate on this view. Is working with a
view faster than with a temporary table?

Is it possible? If yes, is it reasonably easy to implement? Where do I look
for details? (I know, MSDN and SQL Server docs, but what are the right words
to search for?)

If not, what are the alternatives?

TIA,

Igor

 

Re:Stored procedure calling DLL or EXE?


Quote
"Igor Ivanov" <igiva...@infoserve.net.deletetoomuchspam> wrote:
>As a variation, instead of a temporary table I would like the procedure to
>return a view and the end user would operate on this view. Is working with a
>view faster than with a temporary table?
>Is it possible? If yes, is it reasonably easy to implement?

create procedure view_tab
as
select f1,f2,f3.... from t1,t2..
where...

and it returns data set to you

Re:Stored procedure calling DLL or EXE?


Why not just let the stored procedure return the result set. All you
would have to do is "Select * from xxxxxxxxxx" at the end of the
stored procedure and it will return the result set just like any other
query. The only thing is the stored procedure is not "updatable" so
you would need to exec the stored procedure using a TQuery and then
add a TUpdateSql object that could apply what changes might be needed
back to the proper tables.

Dennis Passmore
Ultimate Software, Inc

"If you cannot conceive the idea you
 will never achieve the desired results"

Re:Stored procedure calling DLL or EXE?


Thank you for your answers Mike and Dennis.

I think I understood the part about returning a data set from the stored
proc.

As I suspected, I didn't explain the rest well enough <g>. The step of
calling at some point something written in a regular programming language is
essential. I need it because it must make some complex calculations that
can't be done in SQL. But functionally it looks like an SQL, in the sense
that it takes recordsets and parameters and returns recordset(s). What I
want to achieve is that this step would be completely transparent to the
caller of the stored proc. IOW, this could look like an extension to the SQL
Server. Plugin could be the word. The stored proc is supposed to be a
wrapper around those calculations plus it will do some regular SQL job of
its own.

Is such a thing possible?

Igor

Re:Stored procedure calling DLL or EXE?


Quote

>As I suspected, I didn't explain the rest well enough <g>. The step of
>calling at some point something written in a regular programming language is
>essential. I need it because it must make some complex calculations that
>can't be done in SQL. But functionally it looks like an SQL, in the sense
>that it takes recordsets and parameters and returns recordset(s).

Then just write your custom code in Delphi as a ActiveX.dll (COM
object) and use it in the stored procedure just as you would in any
other script.

Look up the "sp_OACreate()" functions in the MsSql books online.

I use it all the time and it works great.

Dennis Passmore
Ultimate Software, Inc

"If you cannot conceive the idea you
 will never achieve the desired results"

Re:Stored procedure calling DLL or EXE?


Thank you, I'll try it.

Igor

Quote
"Dennis Passmore" <dennisp@nospam_dpassmore.com> wrote in message

news:nq03eu0cd9c1ctsfa3a474ev9hveo9g8ol@4ax.com...
[...]
Quote
> Then just write your custom code in Delphi as a ActiveX.dll (COM
> object) and use it in the stored procedure just as you would in any
> other script.

> Look up the "sp_OACreate()" functions in the MsSql books online.

Other Threads