>Hi Set > >I just upgraded to FB 2.5.2.26540 and tried the EXECUTE BLOCK again but had >the same issues: >Starting transaction... >Preparing statement: EXECUTE BLOCK returns (Mystatement varchar(10000)) AS >DECLARE VARIABLE S VARCHAR(256) >Error: *** IBPP::SQLException *** >Context: Statement::Prepare( EXECUTE BLOCK returns (Mystatement >varchar(10000)) AS >DECLARE VARIABLE S VARCHAR(256) ) >Message: isc_dsql_prepare failed > >SQL Message : -104 >Invalid token > >Engine Code : 335544569 >Engine Message : >Dynamic SQL Error >SQL error code = -104 >Unexpected end of command - line 2, column 31 > >Total execution time: 0.015s
Seems like whatever components you use, thinks that the first semicolon terminates the statement, even if it is within EXECUTE BLOCK. Maybe you need to write SET TERM ^^ ; before EXECUTE BLOCK and then terminate the EXECUTE BLOCK with ^^ and have SET TERM ; ^^ at the end. But I'm just guessing, I've no such problems with IB Workbench. I expect the database to be DIALECT 3 and not an old database still in DIALECT 1? >So as per your advice I changed it to CREATE PROCEDURE and created an SP >called MyProc then I used the command SELECT * FROM MyProc >and I do get a string back (BTW I had to increase 4096 to 10000 else got an >overflow) > >Now I can execute the string if I copy and paste it, but is there any way to >directly execute the string returned from the SELECT * FROM MyProc? In a program, this is of course very simple, just add the result to a cursor or query component and open it. In a stored procedure, you can use EXECUTE STATEMENT, but the problem is that to return the rows from the stored procedure to the user, you need to know the name, type and (maximum) number of columns when you create the procedure, and this is what we try to avoid. Hence, I would recommend to return the statement to the caller (a program or similar) and let the caller thereafter call what is returned. Set
