How can I call a procedure which returns a resultset from a normale SQL
Statement? In Informix I can use this SQL statement:

Execute Procedure XY(x,b)

This will return a resultset. How can I do the same in EDB without to use
the special Delphi component for procedures, but instead with a normale
TEDBQuery. How can I write a SQL statement in EDB Manager to get the
resultset?

How can we debug a Procedure in EDB Manager? I can't find any debugging
features like breakpoints or inspection any values in there at all.

Regards
Rolf

Rolf,

> How can I call a procedure which returns a resultset from a normale SQL
> Statement? In Informix I can use this SQL statement:

You can't. Use a script instead. Then you can exceute any procedure by
using "CALL MyProcedure".

> How can we debug a Procedure in EDB Manager? I can't find any debugging
> features like breakpoints or inspection any values in there at all.

While testing and debugging use a script with the same code instead.
Within the script you can set breakpoints and inspect values.

Uli

Uli,

>>You can't. Use a script instead. Then you can exceute any procedure by
using "CALL MyProcedure".<<

Using a Script will not show me the result set.

>>While testing and debugging use a script with the same code instead.
Within the script you can set breakpoints and inspect values.<<

In my case this would not work, as I must debug two procedures and one of
them calls the second one which is a recursive procedure, which calls it
self. I can't see how I can debug something like that, than with Log Message
calls.

Rolf

Rolf,

> Using a Script will not show me the result set.

It's not clear what you really want. If you just need the result set of
a procedure, why not use a TEDBStoredProc component and passing params
to the procedure?

Uli

Rolf

>Using a Script will not show me the result set.

I'm pretty sure it can Check out DECLARE - look for WITH RETURN in the description

>>>While testing and debugging use a script with the same code instead.
>Within the script you can set breakpoints and inspect values.<<
>
>In my case this would not work, as I must debug two procedures and one of
>them calls the second one which is a recursive procedure, which calls it
>self. I can't see how I can debug something like that, than with Log Message
>calls.

Can't you debug the recursive script by itself? Then move onto debugging the combined script secure in the knowledge you don't have to bother about the inside script.

Roy Lambert [Team Elevate]

Uli,

I want to do this inside EDB Manager, whithout the need to execute the
Procedure again and again via the Execute Procedure Dialog, as it is much to
complicated to enter all the paramters again and again. As such I only wants
to start the procedure inside the SQL window where I can write the statement
one time and than I can execute it again and again without to rewrite
anything at all. The procedure returns a results set an as such this can't
be used inside a script.

I search a simpler and more comfortable way to debug a Procedure, than with
the Execute Procedure Dialog, where I must reneter all my paramters all the
time again, if I have recreated the Procedures. Not very comfortable!

Regards
Rolf

"Uli Becker"  schrieb im Newsbeitrag
news:53BF96EE-6C7F-434C-ADAA-81D613A22827@news.elevatesoft.com...

Rolf,

> Using a Script will not show me the result set.

It's not clear what you really want. If you just need the result set of
a procedure, why not use a TEDBStoredProc component and passing params
to the procedure?

Uli

Roy,

This doesen't work. I have tried this Script to call my Procedure with
displaying the resultset:

SCRIPT
BEGIN
  DECLARE ReturnCursor SENSITIVE CURSOR WITH RETURN FOR TreeStmt;

  PREPARE TreeStmt FROM
     'CALL GroupsTree(554, 1)';

  OPEN ReturnCursor;
END

I can't so this, as it throws this error:
ElevateDB Error #700 An error was found in the statement at line 6 and
column 7 (Expected SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP,
GRANT, REVOKE, BACKUP, RESTORE, SET BACKUPS, MIGRATE, REPAIR, VERIFY,
OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH, UNPUBLISH, SAVE,
LOAD, SET UPDATES, COPY FILE, RENAME FILE, DELETE FILE but instead found
CALL)

Regards
Rolf

"Roy Lambert"  schrieb im Newsbeitrag
news:44D9BC86-A8F4-4F00-B095-4733E3F9264E@news.elevatesoft.com...

Rolf

>Using a Script will not show me the result set.

I'm pretty sure it can Check out DECLARE - look for WITH RETURN in the
description

>>>While testing and debugging use a script with the same code instead.
>Within the script you can set breakpoints and inspect values.<<
>
>In my case this would not work, as I must debug two procedures and one of
>them calls the second one which is a recursive procedure, which calls it
>self. I can't see how I can debug something like that, than with Log
>Message
>calls.

Can't you debug the recursive script by itself? Then move onto debugging the
combined script secure in the knowledge you don't have to bother about the
inside script.

Roy Lambert [Team Elevate]

Rolf,

You can't use the result set of a stored procedure called with CALL from a PSM, no matter if it is a procedure or script. It's been in the wish list, though

--
Fernando Dias
[Team Elevate]

Rolf


<<   PREPARE TreeStmt FROM
     'CALL GroupsTree(554, 1)';>>

Can you post GroupsTree here please.

It looks as though you're expecting GroupsTree to return a string. I've never tried that so no idea about it working or not. However, you've wrapped the statement in quotes so you're preparing a statement from the string CALL... rather than the result of the operation. What happens if you remove the quotes?

Roy Lambert [Team Elevate]

Roy

PREPARE TreeStmt FROM CALL GroupsTree(554, 1);

Removing the quotes results in this error:
ElevateDB Error #700 An error was found in the script at line 6 and column
31 (Expected ; but instead found GroupsTree)


PREPARE TreeStmt FROM GroupsTree(554, 1);

This gives this error:
ElevateDB Error #700 An error was found in the script at line 6 and column
26 (ElevateDB Error #401 The function GroupsTree does not exist in the
schema Default)

The procedure is for 100% sure there. What is here meant with schema
"Default"? I don't use the default Session. In the status bar of EDB Manager
is the correct Sessions and DB selected and if I call the procedure with
CALL ... it works without an error. As such I wonder, why it doesn't find
the procedure in this case.

If I look at sample code for the PREPARE statemnt call, there is everywhere
quotes around the FROM-Statement.

Regards
Rolf