I need to create SP in flamerobin

Question

0.00/5 (No votes)

See more:

I was trying to create store procedure in flamerobin as below

CREATE PROCEDURE Test_SP 
RETURNS (staff character)
AS BEGIN

SELECT distinct
COLLOQUIAL_NAME
FROM STAFF1

END

GRANT EXECUTE
 ON PROCEDURE Test_SP TO  OPUSER;

GRANT EXECUTE
 ON PROCEDURE Test_SP TO ROLE OP_USER;

GRANT EXECUTE
 ON PROCEDURE Test_SP TO  SYSDBA;

What I have tried:

I try to just create simple SP but still no result

CREATE PROCEDURE Test1_SP (
TAB_NAME VARCHAR(31) )
RETURNS (status integer)
AS
DECLARE temp integer;

begin
status=1;
    execute statement 
      ('select * from "' || tab_name)
      into :temp;
END

Posted 3-Jan-22 23:45pm

Member 11776570

Updated 5-Jan-22 0:52am

Add a Solution

Comments

0x01AA 4-Jan-22 8:02am

And no error message? Simply unexpected return value?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

0x01AA · Answer 1 · 2022-01-04T02:31:00

Solution 1

You don't give a lot of information in your question. Therefore this is only a guess.

The SP:

CREATE PROCEDURE TEST_SP
RETURNS (STAFF CHAR(50))
AS BEGIN
   SELECT DISTINCT COLLOQUIAL_NAME
      FROM STAFF1
      INTO :STAFF
      DO SUSPEND;
END

Test Usage:

SELECT * FROM TEST_SP

Posted 4-Jan-22 2:31am

0x01AA

Comments

Member 11776570 5-Jan-22 5:04am

Nope it is not working, it gives me this error.

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, column 7
DO

0x01AA 5-Jan-22 5:19am

Does it give the error while you try to create the SP or while executing the SP?

0x01AA 5-Jan-22 5:43am

Look like you work with BDE? In case yes, we need to use ::STAFF instead of :STAFF

SET TERM ^;
CREATE PROCEDURE TEST_SP
RETURNS (STAFF CHAR(50))
AS BEGIN
SELECT DISTINCT COLLOQUIAL_NAME
FROM STAFF1
INTO ::STAFF
DO SUSPEND;
END
^

Member 11776570 5-Jan-22 5:57am

Below is working now but it has still error message of "multiple rows in singleton select", please can u let me know how it will resolved

SET TERM ^ ;

CREATE PROCEDURE STAFF_Test
RETURNS (personid varchar(500),
legacyrowid varchar(500),
lastname varchar(500),
firstname varchar(500),
minit varchar(100),
ACTIVESTATUS varchar(100),
COLLOQUIAL_NAME varchar(500)
)
AS BEGIN
SELECT distinct
STAFFID AS personid,
STAFFID AS legacyrowid,
replace (STAFFNAME, substring( STAFFNAME from 1 for (position(' ' in STAFFNAME))), '') as lastname,
substring( STAFFNAME from 1 for (position(' ' in STAFFNAME))) as firstname,
Staffinit as minit
,ACTIVESTATUS
,COLLOQUIAL_NAME
FROM STAFF1
INTO :personid, :legacyrowid,:lastname,:firstname,:minit,:ACTIVESTATUS, :COLLOQUIAL_NAME ;

DO SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE STAFF_Test TO SYSDBA;

0x01AA 5-Jan-22 6:11am

I think you have a semicolon to much:
INTO :personid, :legacyrowid,:lastname,:firstname,:minit,:ACTIVESTATUS, :COLLOQUIAL_NAME ;
should be
INTO :personid, :legacyrowid,:lastname,:firstname,:minit,:ACTIVESTATUS, :COLLOQUIAL_NAME

Member 11776570 5-Jan-22 6:19am

Nope still same issue is there look, but there is empty in result and i gives same error of multiple selecton

Preparing statement: select * from STAFF_Test
Statement prepared (elapsed time: 0.203s).
Field #01: STAFF_TEST.PERSONID Alias:PERSONID Type:STRING(500)
Field #02: STAFF_TEST.LEGACYROWID Alias:LEGACYROWID Type:STRING(500)
Field #03: STAFF_TEST.LASTNAME Alias:LASTNAME Type:STRING(500)
Field #04: STAFF_TEST.FIRSTNAME Alias:FIRSTNAME Type:STRING(500)
Field #05: STAFF_TEST.MINIT Alias:MINIT Type:STRING(100)
Field #06: STAFF_TEST.ACTIVESTATUS Alias:ACTIVESTATUS Type:STRING(100)
Field #07: STAFF_TEST.COLLOQUIAL_NAME Alias:COLLOQUIAL_NAME Type:STRING(500)
PLAN SORT ((STAFF1 NATURAL))
Parametros: 0

Executing statement...
Statement executed (elapsed time: 0.000s).
258 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 29 index, 69 seq.
Delta memory: -232 bytes.
Total execution time: 59.065s
Script execution finished.

0x01AA 5-Jan-22 6:26am

~~A 'GRANT' on the table itself issue maybe?~~
Does simply SELECT * FROM STAFF_Test gives the error "multiple rows in singleton select"? I can hardly believe that...

Member 11776570 5-Jan-22 6:34am

Nope there is no issue with table.
I can run simple below query and it provide me result for it

select * from STAFF1

when i below SP select query it gives me popup with this below msg

select * from STAFF_Test

Context : Statement :: Fetch
Message: isc_dsql_fetch failed.

SQL Message : -811
multiple rows in singleton select

Engine Code : 335544652
Engine Message :
multiple rows in singleton select
At procedure 'STAFF_Test' line : 15, col: 1

0x01AA 5-Jan-22 6:50am

Sorry, I don't see what the problem here is :(

Btw: In case you make a VIEW instead of SP, does it give the same error?

Member 11776570 5-Jan-22 6:52am

done now. I don't know is server is slow.
But now it is done.
Thanks for all the reply.

0x01AA 5-Jan-22 6:58am

Thank you for accepting the answer ;)

Member 11776570 · Answer 2 · 2022-01-05T00:52:00

SET TERM ^ ;





ALTER PROCEDURE STAFF_Test
RETURNS (personid varchar(500),
legacyrowid varchar(500),
lastname varchar(500),
firstname varchar(500),
minit varchar(100),
ACTIVESTATUS varchar(100),
COLLOQUIAL_NAME varchar(500)
)
AS BEGIN
FOR SELECT distinct
STAFFID AS personid,
STAFFID AS legacyrowid,
replace (STAFFNAME, substring( STAFFNAME from 1 for (position(' ' in STAFFNAME))), '') as lastname,
substring( STAFFNAME from 1 for (position(' ' in STAFFNAME))) as firstname,
Staffinit as minit
,ACTIVESTATUS
,COLLOQUIAL_NAME
FROM STAFF1
INTO :personid, :legacyrowid,:lastname,:firstname,:minit,:ACTIVESTATUS, :COLLOQUIAL_NAME



DO SUSPEND;



END^
SET TERM ; ^



GRANT EXECUTE
ON PROCEDURE STAFF_Test TO SYSDBA;