How to convert parameters substring to the column type in C#?

Question

I have an INSERT with one VARCHAR parameter. SQL looks like this:

INSERT INTO FLAGS (TIME_STAMP, FLAG) 
SELECT SUBSTRING(@s1 FROM 1 FOR 23), SUBSTRING(@s1 FROM 24 FOR 2) 
FROM RDB$DATABASE;

And sending method like this:

using (FbConnection connection = new FbConnection(ConnectionString))
{
    try
    {
        connection.Open();
        var transaction = connection.BeginTransaction();
        var command = new FbCommand(sqlCommand, connection, transaction);
        command.Parameters.Add("@s1", "2018-09-12 08:37:51.00083");
        command.ExecuteNonQuery();
        transaction.Commit();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

Column TIME_STAMP is TIMESTAMP and column FLAG is INTEGER. In the real application there will be several rows in the INSERT, not just one. There will be also more columns so I would like to keep number of parameters for one row as low as possible.
If I run this code, it gives me:
Exception thrown: 'System.FormatException' in FirebirdSql.Data.FirebirdClient.dll

If I change the command like this:

INSERT INTO FLAGS (TIME_STAMP, FLAG) 
SELECT SUBSTRING(@s1 FROM 1 FOR 23), CAST(SUBSTRING(@s1 FROM 24 FOR 2) AS INTEGER) 
FROM RDB$DATABASE;

It gives me this error:

Exception thrown: 'FirebirdSql.Data.FirebirdClient.FbException' in 
FirebirdSql.Data.FirebirdClient.dll
Dynamic SQL Error
SQL error code = -804
Data type unknown

FWIR this should be possible. At least this works in FlameRobin (version with CAST also works):

SET TERM ^ ; 
EXECUTE BLOCK AS 
DECLARE s1 VARCHAR(25) = '2018-09-12 08:37:51.00083'; 
BEGIN 
INSERT INTO FLAGS (TIME_STAMP, FLAG) 
SELECT SUBSTRING(:s1 FROM 1 FOR 23), SUBSTRING(:s1 FROM 24 FOR 2) 
FROM RDB$DATABASE;
END^ 
SET TERM ; ^

Can someone point me in the right direction?

EDIT
Result should be:

TIME_STAMP              | FLAG
------------------------+-----
2018-09-12 08:37:51.000 | 83

I would suggest considering something like https://stackoverflow.com/questions/17882366/firebird-embedded-multiple-inserts-using-net-provider and specifying each parameter independently. — mjwills, Sep 18 '18 at 13:51
Actually I had multiple inserts in `EXECUTE BLOCK` but have problems with speed and memory consumption, so I am looking for some other solutions that could be more efficient. — Artholl, Sep 18 '18 at 14:00
@CaiqueCPereira The problem is that there could be more data types in one parameter and it looks like I need to specify the correct data type after substring and I am not able to do that. — Artholl, Sep 18 '18 at 14:10
"There will be also more columns so I would like to keep number of parameters for one row as low as possible" - a very weird idea. You want to remove type safety and deterministic behavior for the sake of fuzzy heuristics and no warrants. For obscure reason you want to shoot yourself into the foot. Well, you just did. — Arioch 'The, Sep 18 '18 at 16:27
Get rid from `SELECT` - it does not belong there. `INSERT INTO FLAGS (TIME_STAMP, FLAG) VALUES (@S1, @S2)`. Also it seems you should not add "@" to the parameter names - see http://bobby-tables.com/adodotnet and also see https://www.ibprovider.com/eng/examples/lcpi_oledb_net__c001__example_0005.html — Arioch 'The, Sep 18 '18 at 16:30
@Arioch'The Linking to IBProvider documentation is not right when the OP is using FirebirdSql.Data.FirebirdClient, and that driver definitely does support using `@` in parameter names (as does System.Data.SqlClient for SQL Server) — Mark Rotteveel, Sep 18 '18 at 16:39
@MarkRotteveel that documentation also uses Ado.Net framework in C#, so while it may not be applied verbatim - it demonstrates the relevant concepts and one example of their implementation. Is there equally extensive set of examples published on FirebirdClient site ? — Arioch 'The, Sep 19 '18 at 09:54
@Arioch'The That is not my point. You are making claims that are not correct based on documentation of a different driver. — Mark Rotteveel, Sep 19 '18 at 10:09
@MarkRotteveel I base them upon many examples I have seen around net and upon BT site first and IP site only second. Frankly, if there would had been INSERT/VALUES example on BT I would not even look into IP site here. And there was a reason I made it only comment not answer and even after that I wrote >>Also(1) it seems(2) you should not add "@"....."< — Arioch 'The, Sep 19 '18 at 13:14
@Arioch'The Both standard ADO.net documentation and the [examples on the Firebird site](https://www.firebirdsql.org/en/net-examples-of-use/) suggest it is perfectly fine. — Mark Rotteveel, Sep 19 '18 at 13:32

Mark Rotteveel · Accepted Answer · 2018-09-18T16:42:27.847

1

Firebird cannot infer the datatype of a parameter in the select list in most situations (there are exceptions, but this is not one of them). You will need to explicitly cast it to define the data type and length. Note that this only works with Firebird 2.5 or higher.

You'll need to use:

INSERT INTO FLAGS (TIME_STAMP, FLAG) 
SELECT SUBSTRING(cast(@s1 as varchar(25)) FROM 1 FOR 23), CAST(SUBSTRING(cast(@s1 as varchar(25)) FROM 24 FOR 2) AS INTEGER) 
FROM RDB$DATABASE

It would however be far better to do this client side and just use a normal insert into .. values (..,..) using appropriate types than using stringly typed things.

The reason it works in the execute block, is because there you have already defined s1 to be a varchar(25)

edited Sep 18 '18 at 16:42

answered Sep 18 '18 at 16:35

Mark Rotteveel

100,966
191
140
197

It works, thank you. I would like to try this approach, because I am trying to reduce memory consumption (and increase speed) for periodically inserting many rows (around 400k every time) and according to [this article](https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/) this solution could help me with that. – Artholl Sep 19 '18 at 06:29
I also tried the recommended way (set of `INSERT INTO` in `EXECUTE BLOCK` with parameters), but it gives me -901 undefined message number - even with just one row which works well if I remove `EXECUTE BLOCK AS BEGIN` from the beginning and `END` from an end. – Artholl Sep 19 '18 at 08:41
@Artholl That depends exactly on how you used the execute block; parameters need to be passed to the execute block correctly. And really, don't rely on articles written for different database systems and assume it will translate directly to another database system. – Mark Rotteveel Sep 19 '18 at 10:08
I just wanted to test if that solution gives me some improvement also in Firebird, I definitely don't rely on that. So far it looks like I will not use it. But thanks to you I learned something about Firebird which could help me somehow in the future, so it was not complete waste of time :-) – Artholl Sep 19 '18 at 12:04

How to convert parameters substring to the column type in C#?

1 Answers1