Client SQL dialect 1 does not support reference to BIGINT datatype

[thelostcode]

The issue

Alright I'll give my best to explain it.

In file FbModificationCommandBatch.cs there is the GetCommandText() method. It generates a special line to the executed block

sbExecuteBlock.AppendLine("RETURNS (AffectedRows BIGINT) AS BEGIN");

This is not always correct and may cause a server error.

If you have a database in dialect 1 and we take aside the reason why someone has this in 2018 and you execute this statement with that BIGINT it results in an error.

The fix is easy. Change this to int.

But I have no idea where in this code we have access to the Database dialect (not the connected client dialect!!) to differentiate between both cases.

Steps to reproduce

1. Create an database with dialect 1.
2. Execute this one in isql or any other Firebird SQL tool and you can easily check the error - which is also caused by that line above.

set term^;
execute block
    returns (AffectedRows bigint)
as 
begin
    AffectedRows=0;
    AffectedRows = row_count;
end^

[ralmsdeveloper]

Yes would be a good suggestion, but only for those who use DIALECT 1.

Another important thing is to know that the 1 is marked for obsolete.

Look at this link: https://www.firebirdsql.org/pdfmanual/html/isql-dialects.html

I'll analyze how to solve this! I can not just switch to int, because those who use the long type will have problems!

[thelostcode]

Yeah I know this. Just switching it is wrong. Also I know it is obsolete but I know companies that use Firebird 2.5 and an super old school database ( > 20 years ), ported from Interbase which is still Dialect 1. Though they connect with Dialect 3 nowdays the database file itself is Dialect 1.

FlameRobin for example shows the Database Dialect. If you have the chance to do something like:

if Database.Dialect == 3:
   use the big int
else:
  use the int 

Thaaaaaaat would be awesome.

[thelostcode]

The BIGINT is used for counting affected rows in update calls, right? Is it even possible to update more than 2.147.483.647 rows? I am not really sure if BIGINT is really needed there when I start thinking about this.

[ralmsdeveloper]

No, it returns the table id! But I've marked this as I analyze, I'll try to make this implementation soon.

[thelostcode]

Oh, I was not aware of this. Thank you for letting me know. If you let me know how I can access the database options inside of that code without doing hackish stuff I can make it happen and do a PR. 🔢

[ralmsdeveloper]

@thelostcode I made a small adjustment for the use of DIALECT 1

[ralmsdeveloper]

Fixed in version 2.0.11.4, available in nuget!