0

I'm trying to get multiple rows with a like condition.

EXECUTE BLOCK 
   RETURNS (ID Varchar(128), SCRIPT  BLOB SUB_TYPE TEXT)
AS
BEGIN
FOR SELECT Col1.EID, Col1.Script 
FROM Col1
WHERE Col1.Script Like %test12%
INTO :ID, :SCRIPT
  DO
   BEGIN
     SUSPEND;
   END
END

Col1 looks like:

EID Script
1 "Hello test12, this is a Test"
2 "Hello test12, this is a second Test"

I get always error -811: multiple rows in singleton select. If I search for the problem everyone says make a for select, but it's not working.

Example in dbfiddle.

scotti8
  • 21
  • 1
  • 3
  • Your code as shown has a syntax error. Please provide a valid [mre]. That said, I can't reproduce it, see [this (Firebird 3.0) dbfiddle](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=1eb81ce78429490a8988bb4d275e54c5), so also show how you execute this. – Mark Rotteveel Sep 22 '21 at 09:00
  • If I try this on dbfiddle it works maybe its a Problem of Firebird 2.5? In the Live System there runs a 2.5 – scotti8 Sep 22 '21 at 09:20
  • It is very unlikely, but I don't have a Firebird 2.5 install at hand. But please specify your exact Firebird version, and show the code used to execute this statement. – Mark Rotteveel Sep 22 '21 at 09:23
  • Yes Sorry, The exact Firebird Version is 2.5.9 and here is the code to execute the statement [link](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=c34ebbef5c6f4c209e4955b13647f50a) – scotti8 Sep 22 '21 at 09:35
  • Ok, but how is that statement executed in your application? It is possible that the error is not produced inside the execute block, but by the execution of the execute block (for example because it produces multiple rows, and your method of execution only expects a single row). – Mark Rotteveel Sep 22 '21 at 10:42
  • 1
    Also if I try with multiple Lines in dbfiddle it works, but in my local database is a error [dbfidlle](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=d8bdf97e5e3128a4dd8d950cbd01fe27) – scotti8 Sep 22 '21 at 10:54
  • 1
    Shown query cannot produce "multiple rows" error by itself. The only way to get the error from this query is using API call that is not supposed to handle result set, such as isc_dsql_exec_immed2(). – user13964273 Sep 22 '21 at 11:19
  • You've established what you execute, and that should work, **if** it is executed in the right way. There are methods of execution in the Firebird API that expect zero or one rows, and attempting to execute something that produces multiple rows (like your execute block) using one of those API methods will produce the same error. So, I repeat again, show **how** your application executes the statement. – Mark Rotteveel Sep 22 '21 at 11:50
  • Now I saved a [Log](https://pastebin.com/YGPbpGkM), and I also tried it with a DB Viewer an that worked. But probably it dont work with my main Application . – scotti8 Sep 22 '21 at 14:58
  • 1
    Executing your example in Firebird 2.5.9 ISQL works just fine, so the problem is with how your application executes the statement. – Mark Rotteveel Sep 22 '21 at 15:43
  • with Firebird 2.5+ you can use TraceAPI (for example http://fbprofile.sf.net ) to see low-level events on the server. It seems that your program (language's client library) executes the code as "sp call" (like `insert ... returning...`) instead of `open cursor` (like `select ...`), or even executes somehow different query – Arioch 'The Sep 22 '21 at 19:28

1 Answers1

0

From your log:

2021-09-22 14:53:38 D [00000a3a] Execute

You must use "Open" function instead of "Execute".

user13964273
  • 1,012
  • 1
  • 4
  • 7