Subject Re: [firebird-support] Multiple Rows in Singleton Select
Author Helen Borrie
At 03:40 AM 25/02/2010, you wrote:
>Can anyone suggest what I may have done wrong?
>Firebird 1.5
>I'm using a SQL insert into a table which returns the error 'multiple rows in singleton select'.
>The table has a primary key, the values being inserted are unique.
>I am formatting the SQL statement for use via an IBObjects Query and the debugger shows a valid statement prior to the ExecSQL() statement.
>There are no duplicate entries in the table and I'm confused.
>To make matters worse, the code used to work fine so I'm leaning towards a database problem. Does anyone have any thoughts?

Show the insert statement.

Generically speaking, you will get this error whenever you try to do something that needs a scalar, that is instead receiving a set of multiple values, e.g., you are including a subquery expression in an input field where the subquery picks up values from more than one row.

If there is a "database problem" where the same code used to work but now gives this error, possibly you have a subquery expression that involves a join or where clause in which a searched table (child table, perhaps) that used to have 0 or 1 child now has > 1 children.

Off-topic: The ExecSQL() statement in IBO is for executing statements that either return no output (such as DDL, executable SPs, or other DML statements that don't return a dataset.) You can use it for an executable SP that returns one single row of output and pick up those values in the app by reading the values in the return params structure (tibo* components) or fields (tib_ components).

If you try to execute a selectable SP with ExecSQL() you will get this error, too.

BTW, also off-topic, if you are using IB_SQL to test your statements, you will get this message if you use the DSQL window to try to execute a statement that returns multiple rows. Use the Cursor window when your statement returns a set of 0 to many rows. Also watch that you have the Params checkbox correctly set for what you want your statement to do.

If you are stuck about what to use for what purpose in IBO, please use the IBO list.