Firebird_sdbc error: *Cursor is not open caused by 'isc_dsql_fetch'

[libreofficer]

hello,

I tried to call this stored procedure in LibreOffice Base:

CREATE OR ALTER PROCEDURE myproc
RETURNS (val CHAR(100))
AS BEGIN
	FOR SELECT "customer" FROM "test" INTO :val
	DO
	BEGIN
		SUSPEND;
	END
END

but I have the following error when I try to execute it:

*firebird_sdbc error: Cursor is not open caused by ‘isc_dsql_fetch’

I tested with this one:

CREATE OR ALTER PROCEDURE DUMMY
 RETURNS (TXT VARCHAR(10))
 AS
 BEGIN
   TXT='DOG';
   SUSPEND;
   TXT='CAT';
   SUSPEND;
   TXT='MOUSE';
   SUSPEND;
 END

I got the same error!

This is my LibreOffice Base file:
dbfirebird.odb

Any idea on how to solve this problem?

Configurations:

LibreOffice Version: 6.4.1.2 (x86)

Build ID: 4d224e95b98b138af42a64d84056446d09082932

CPU threads: 4; OS: Windows 10.0 Build 17763; UI render: default; VCL: win; Locale: fr-CH (fr_FR);

UI-Language: en-GB

Calc: CL

[Ratslinger]

Hello,

Have only looked at this briefly. You cannot execute from Query window. Go to Tools->SQL.
Will attempt to look further at your example later. Have done little with Firebird Procedures in past. It would also help to know what you are attempting to accomplish.

[libreofficer]

I want to list my data when I call my stored procedures (i.e. from a form).
I tried with Tools->SQL : I got nothing from the output when I use EXECUTE PROCEDURE myproc

[Ratslinger]

@libreofficer,

Thanks for the info & post (+1). Did find one Firebird Procedure I had worked on (question dealt with MySQL) and it also ran from a macro. If interested it is in my comments in this post → Using SQL cursor and loop in LO base macro.. In that example, when the procedure is executed, records are created in the table for students grades to be applied.

Also curious as to the purpose of your procedure as this seems nothing more than a query to get data from another query.

[libreofficer]

Yes, I want to make a teacher daily lessons from a database, and I try to discover the possible use of stored procedures, because I need to put in a weekly timetable the lessons of that week.

And thank you for your last year post which I will keep to learn from.
The problem is that I have been using VBA and have difficulties using LibreOffice Basic (documentation does not contains very much examples), especially to convert between different calendars (solar, lunar, etc.)

[Ratslinger]

@libreofficer,

You should actually remove your solution from the question and post as an answer. Then accept your own answer as the correct answer.

Also note it is not necessary to mark questions as [Solved] especially if there is an accepted answer.

[libreofficer]

I just did it, thank you.

[libreofficer]

After many complicated search I found this solution:

I have to execute my stored procedure through SELECT to make it selectable, and be sure to activate Run SQL command directly before running the query (see picture).

I didn’t find this in the official documentation of FireBird 2.5 and I don’t know what is the purpose of EXECUTE PROCEDURE !

Thank you for your assistance