Hello Mike,

Database Workbench won't enable the Commit/Rollback buttons on a plain
SELECT,
because this will require you to use them for every SELECT you perform.

In the case of a SELECT-able Stored Procedure, this means it won't be able
to detect
your procedure is also modifying data.

In the SQL Editor, you can actively start a transaction by yourself and the
commit/rollback
buttons will always be enabled.

Hope this helps, if not, feel free to write to me personally.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!





I’m sorry I should have been a little bit more concise on what I’m doing.



I use Database Workbench v5 for all of my development needs.



Running the stored procedure setting the V_REPORT = 1 in Database Workbench,
I do get a results set. So far, so good.



However, if I set V_REPORT = 0 which should cause the UPDATE to be processed
instead, I’m not able to commit as the “Commit” and “Rollback” buttons are
not enabled.



However, If I comment out the first portion (as shone below) leaving just
the UPDATE clause it works fine and the “Commit” and “Rollback” buttons
are not enabled.



/*

IF (V_REPORT = 1) THEN

SUSPEND;

ELSE */

UPDATE ACCT_CASE

SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE

WHERE ACCT_ID = :ACCT_ID

AND CASE_ID = :CASE_ID;



Any ideas why?



Thanks,

Mike





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, November 03, 2015 9:29 AM
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Why can't I have a SUSPEND or UPDATE in the
same Stored Procedure?





hi,



you got an error or what?







regards,

Karol Bieniaszewski



-------- Oryginalna wiadomość --------
Od: "'stwizard' stwizard@... [firebird-support]"
<firebird-support@yahoogroups.com>
Data: 03.11.2015 14:59 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same
Stored Procedure?



Greetings All,



Firebird v 2.5.4



Many times I would like to run a report before I do an update. Why can’t I
allow for both in one stored procedure? Look at the end of this stored
procedure where I use V_REPORT.



Thanks,

Mike



SET TERM ^^ ;

CREATE PROCEDURE X_CHK_LEGAL_CASE_DATE (

V_REPORT SmallInt)

returns (

ACCT_ID Integer,

CASE_ID SmallInt,

LEGAL_CASE_DATE Date,

CASE_LEGAL_CASE_DATE Date,

ACCH_LEGAL_CASE_DATE Date,

ACCH_NOTE VarChar(200))

AS

DECLARE VARIABLE iAcctCaseCourtID Integer;

begin

FOR SELECT ACCT_CASE_COURT_ID,

CAST(CREATE_DATE AS DATE),

ACCT_ID,

CASE_ID

FROM ACCT_CASE_COURT

WHERE STATUS_CODE = 'A'

ORDER BY ACCT_ID, CASE_ID

INTO :iAcctCaseCourtID, :LEGAL_CASE_DATE, :ACCT_ID, :CASE_ID DO

BEGIN

SELECT LEGAL_CASE_DATE

FROM ACCT_CASE

WHERE ACCT_ID = :ACCT_ID

AND CASE_ID = :CASE_ID

INTO :CASE_LEGAL_CASE_DATE;



IF (CASE_LEGAL_CASE_DATE IS NULL) THEN

BEGIN

SELECT FIRST 1 CAST(CREATE_DATE AS DATE), NOTE

FROM ACCT_CASE_COURT_HIST

WHERE ACCT_CASE_COURT_ID = :iAcctCaseCourtID

ORDER BY ACCT_CASE_COURT_HIST_ID

INTO :ACCH_LEGAL_CASE_DATE, ACCH_NOTE;



IF (V_REPORT = 1) THEN

SUSPEND;

ELSE

UPDATE ACCT_CASE

SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE

WHERE ACCT_ID = :ACCT_ID

AND CASE_ID = :CASE_ID;

END

END

end ^^

SET TERM ; ^^





[Non-text portions of this message have been removed]



------------------------------------
Posted by: "stwizard" <stwizard@...>
------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu
there.

Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links