Add list of IDs as parameter for Firebird query in C#

Question

I would like to update a list of rows given by a list of IDs. Normally, the parameters of the query can be passed via the parameter property. But if I pass a list of IDs, Firebird will not accept the query. I'm using the FirebirdSql.Data.FirebirdClient from NuGet.

Code (simplified):

List<string> ids = someList.Select(_ => _.Id).ToList();
using (var fbCommand = new FbCommand("UPDATE someTable SET NAME='foo' WHERE ID IN (@ids)", fbConnection))
{
    fbCommand.Parameters.Add("ids", ids);
    fbCommand.ExecuteNonQuery();
}

The Table "someTable" is defined like this:

CREATE TABLE someTable (
    ID CHAR(36),
    NAME CHAR(20)
);

This is the exception:

Exception thrown: 'FirebirdSql.Data.FirebirdClient.FbException' in FirebirdSql.Data.FirebirdClient.dll An exception of type 'FirebirdSql.Data.FirebirdClient.FbException' occurred in >FirebirdSql.Data.FirebirdClient.dll but was not handled in user code arithmetic exception, numeric overflow, or string truncation string right truncation

I still get the same exception using "@ids" as parameter name... — Jan B., Nov 13 '20 at 15:22
The ID in the database is has max size of 36 characters. Is the ID in c# exceeding 36 characters? — jdweng, Nov 13 '20 at 15:26
there are some tricks, but in general a parameter is not scalar, not value. Is your ID only integer and nothoing else ? — Arioch 'The, Nov 13 '20 at 15:36
You should delimit a list of id with comma to use it as `IN ()` argument. — Miamy, Nov 13 '20 at 15:39
@Miamy you mean one can write like `int i = 1,2,3,4,5,6;` in C++ if using comma to delimit? SQL paramerer is kind of a variable! It is scalar, at least in Firebird. You can NOT pass a vector or matrix into a scalar variable, neither in C nor in SQL. — Arioch 'The, Nov 13 '20 at 15:41
@Arioch'The, as I remember, FB use syntax like `WHERE ID IN (1,2,3,4)`. I'm not sure `fbCommand.Parameters` will translate input list like this. — Miamy, Nov 13 '20 at 15:44
@Miamy True, and you can make it parametrized like `where ID IN (@Param1, @Param2, @Param3, @Param4)`. You can have many params, indeed. But each param will be one scalar value. Equally in C+ you can have `int i1=1, i2=2, i3=3, i4=4;` but it will be not one variable holding four numbers, it would be four variables holding one number each. Same with params. There is no text-to-text translation, there is not "string splicing" or "stirng interpolation" or what is the name. There really is a creation of variables and putting values into variables OUTSIDE of SQL query text. And that is intention! — Arioch 'The, Nov 13 '20 at 15:47
@Arioch'The, yes, of course. I mean TS should try to translate a list of strings to a comma-separated string and to pass it as a parameter. — Miamy, Nov 13 '20 at 15:51
@Miamy and then his query would return nothing! because it would still contain ONE value not many values. `Select ...... where 1 in ( "1,1,1,1,1" , 2, 3, 4)` - would this return any rows ? — Arioch 'The, Nov 13 '20 at 15:53
To clarify, I will have many IDs in the list, like more than 5000 or so. I'm not sure if this can cause problems also. But in the case I was testing, I just added 4 IDs to the list. — Jan B., Nov 13 '20 at 15:56
@JanB. see the question i linked, yours is duplicate. You would have to make a GTT and populate it. Also, making selection by 5K+ aribtrary numbers feels just wrong. There should be something simple among them. Imagine you would show humans 5K+ rows. He would only have attention to read 100 or 200, never more. So there should be some underlying and relatively simple criterion you select those 5000 out of others. And then you can hopefully put THAT primary criterion into WHERE, not the flattened list. — Arioch 'The, Nov 13 '20 at 15:58
@Arioch'The the problem is, that I must mark rows that my software has processed as "processed". There is no simple WHERE criterion that I can use there, because the criterion is really the rows that are processed. Using a GTT just feels wrong for a simple query like this. Maybe I should put the Update query into a foreach loop? — Jan B., Nov 13 '20 at 16:07
You can only pass individual values to a parameter. In theory Firebird has arrays, but they are hardly supported (not sure if the Firebird ADO.net driver does at all), and even if the driver would support it, arrays in Firebird don't work with `IN`. — Mark Rotteveel, Nov 13 '20 at 17:06
Duplicates: [Filter sql based on C# List instead of a filter table](https://stackoverflow.com/questions/11508240/how-can-i-supply-a-listint-to-a-sql-parameter), [Pass List Into SQL Parameter](https://stackoverflow.com/questions/22176471/pass-liststring-into-sql-parameter), [How do I translate a List into a SqlParameter for a Sql In statement?](https://stackoverflow.com/questions/4502821/how-do-i-translate-a-liststring-into-a-sqlparameter-for-a-sql-in-statement), [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) — Mark Rotteveel, Nov 13 '20 at 17:09
More so, you'd better start with marking rows as "taken to be processed by agent X" before acvtually processing. Because your processor may crash (software failure, network failure, hardware failure...) and because you may have race ocnditions with other procesros working in parallel. Only after successful savign data you would re-mark those rows as "processed". There were other questions whether organizing multi-processing of rows was discussed. I think on SO but not sure. — Arioch 'The, Nov 13 '20 at 17:52

score 0 · Accepted Answer · answered Nov 13 '20 at 17:27

0

I got kind of around the problem by simply using a foreach loop to update all the rows:

List<string> ids = someList.Select(_ => _.Id).ToList();
foreach (string id in ids) {
    using (var fbCommand = new FbCommand("UPDATE someTable SET NAME='foo' WHERE ID = @id", fbConnection))
    {
        fbCommand.Parameters.Add("id", id);
        fbCommand.ExecuteNonQuery();
    }
}

answered Nov 13 '20 at 17:27

Jan B.

104
8

Bad structure: the query is loop invariant. `using` should be outside the `for` not inside. Creating the query, adding the parameter and PREPARING the query ("compiling" it in SQL lingo) should be done before the loop. Then inside the loop you have to change parameter value and execute the previously prepared query. – Arioch 'The Nov 13 '20 at 17:48

Add list of IDs as parameter for Firebird query in C#

1 Answers1