Bit confused at the moment.  I've been through the ElevateDb manuals and found out how to create, alter, and drop a stored procedure.  
I even managed to add a stored procedure to my test database (using a create procedure... statement)

I can run the stored proc from the manager tool by right clicking on it.  All good so far. it even produces a result set (So I know the stored logic is
reasonable)

Buggered if I can work out how to execute the procedure interactively in the manager tool.  

I thought the following statment would do it

EXECUTE PROCEDURE <PROCEDURENAME>(<PARAMETERS>);

Instead, I get this error message
ElevateDB Error #700 An error was found in the statement at line 1 and column 1 (Expected INSERT, UPDATE, DELETE, CREATE, ALTER, DROP,
GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE but instead found execute)

Any help would be greatly appreciated

Thanks

Karl,

if you want to execute a procedure within the EDBManager just use the
context-menu or the "Execute procedure" link. Then you can enter your
parameters and execute it.

What you try is to execute the procedure from an sql-statement.

In your application you have to use a EDBStoredProc component; you can
execute the procedure by using this code:

with EDBStoredProc1 do
begin
  StoredProcName := 'MyProcedure';
  prepare;
  try
    ParamByName('Param1').asInteger := myID;
    ExecProc;
  finally
    unprepare;
  end;
end;

If you use the procedure more than once you should assign the
StoredProcName to the component and prepare it once. Then you just need
this code:

with EDBStoredProc1 do
begin
    ParamByName('Param1').asInteger := myID;
    ExecProc;
end;

Hope that helps.
Regards Uli

I'm new to EDB, so sorry for that question, but does this mean, that we
can't call another procedure inside another procedure?

Regards
Rolf

"Uli Becker" <test@test.com> schrieb im Newsbeitrag
news:791650D4-27DD-4450-AC7C-5462338260DD@news.elevatesoft.com...
> Karl,
>
> if you want to execute a procedure within the EDBManager just use the
> context-menu or the "Execute procedure" link. Then you can enter your
> parameters and execute it.
>
> What you try is to execute the procedure from an sql-statement.
>
> In your application you have to use a EDBStoredProc component; you can
> execute the procedure by using this code:
>
> with EDBStoredProc1 do
> begin
>   StoredProcName := 'MyProcedure';
>   prepare;
>   try
>     ParamByName('Param1').asInteger := myID;
>     ExecProc;
>   finally
>     unprepare;
>   end;
> end;
>
> If you use the procedure more than once you should assign the
> StoredProcName to the component and prepare it once. Then you just need
> this code:
>
> with EDBStoredProc1 do
> begin
>     ParamByName('Param1').asInteger := myID;
>     ExecProc;
> end;
>
> Hope that helps.
> Regards Uli
>
>
>

Rolf,

> I'm new to EDB, so sorry for that question, but does this mean, that we
> can't call another procedure inside another procedure?

No, from a procedure you can use "call" to execute another procedure.

Look at the chapter on SQL/PSM (persistent stored module) statements in
the manual:

7.14 CALL
Calls a procedure.
Syntax
CALL <ProcedureName>([<Value>[,<Value>]])
Usage
Use this statement to call a procedure, passing parameter values if the
procedure being called has been defined
with parameters.
Examples
-- This trigger calls the external
-- SendMail procedure with which group to
-- send the email to along with the new
-- value of the Notes column for the customer
-- being updated
CREATE TRIGGER "NotesUpdate" AFTER UPDATE OF "Notes"
ON "Customer"
BEGIN
CALL SendEmail('CustomerReps',NEWROW.Notes);
END

Regards Uli

Karl,

<< I thought the following statment would do it >>

The only way to execute a procedure in the EDB Manager via SQL is inside of
another procedure, function, or script via the SQL/PSM CALL statement:

http://www.elevatesoft.com/manual?action=mantopic&id=edb1sql&category=13&topic=233

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I am connecting to EDB v1.07 from a website using ASP and ODBC and am yet to find a way to execute/call a stored procedure.

I created a simple procedure that returns a recordset (i.e. returning and open cursor).

All that is returned is "ElevateDB Error #700 An error was found in the statement at line 1 and column 1 (Expected INSERT, UPDATE, DELETE, CREATE, ALTER,
DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE but instead found CALL)".

Are there ways to call a stored procedure remotly via ODBC or indeed by any method?

Regards

Greg

Greg,

<< I am connecting to EDB v1.07 from a website using ASP and ODBC and am yet
to find a way to execute/call a stored procedure.

I created a simple procedure that returns a recordset (i.e. returning and
open cursor).

All that is returned is "ElevateDB Error #700 An error was found in the
statement at line 1 and column 1 (Expected INSERT, UPDATE, DELETE, CREATE,
ALTER,
DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT,
DISCONNECT, REMOVE but instead found CALL)".

Are there ways to call a stored procedure remotly via ODBC or indeed by any
method? >>

Unfortunately, calling a stored procedure via ODBC is not available at this
time.  I will see what I can do for getting this into the next 1.09 Build 2
release, which should be available very soon.

--
Tim Young
Elevate Software
www.elevatesoft.com

Guys

Thanks for taking the time to answer the question.  You explained very clearly what is available in ElevateDB, and I appreciate that.  

How do I handle the following in this database then?

Lets say I have related data stored across a couple of tables, and have created a stored proc to get me that data back in one step.  The body of it
is somewhat equivalent to "select x,y,z from a left join b on (a.h = b.h)". It's done as a stored proc (MYPROC), and there is actually some fairly
heavy logic in the body of the proc.  

Now I also have a case where I want to retreive the rows where x = 1. I would have expected to be able to write a query something like "select *
from MYPROC where x = 1".  

Karl,

<< Now I also have a case where I want to retreive the rows where x = 1. I
would have expected to be able to write a query something like "select *
from MYPROC where x = 1".   >>

No, you can't use a stored procedure as a table expression in ElevateDB due
to the fact that ElevateDB cannot project what the result set structure will
look like due to the dynamic nature of the statement execution in a stored
procedure.  The way to do this would be to have the stored procedure create
a temporary table with the result set that you further query using the
temporary table name.

See the front page of our web site for an example of a script that does
this:

http://www.elevatesoft.com/

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


How come no announcement about the super-dooper new web site?

Roy Lambert