Using Decimal parameters in Firebird stored procedures

Question

I have a Firebird stored procedure that accepts Decimal(9,6) values for Latitude and Longitude parameters. It's used to create a contact profile for a user.

When I try to create and use these decimal parameters, I get a conversion error:

Value was too large or too small for an Int32.

Here's how I create the ADO.Net connection:

FbConnection dbConn = new FbConnection(ConnectionString);

And further on, here's where I set up the command:

        IDbCommand command = _Connection.CreateCommand();
        command.Connection = _Connection;
        command.CommandText = "CREATECONTACT";
        command.CommandType = CommandType.StoredProcedure;

The code I'm using to create the ADO.Net parameters looks like this:

IDataParameter param21 = command.CreateParameter();
param21.ParameterName = "GEOLAT";
param21.DbType = DbType.Decimal;
param21.Value = 3.14m;

After all the parameters are added I call command.ExecuteScalar() to get the value of the ContactId that was (hopefully) just created.

int contactId = Convert.ToInt32(command.ExecuteScalar(), CultureInfo.InvariantCulture);

It isn't the Convert.ToInt32 that causes the problem. The exception is thrown from ExecuteScalar(). I get the same error if the command looks like this:

object result = command.ExecuteScalar();

The procedure works fine if the values for GeoLat and GeoLong are integer values (such as 72 or 0), but if I try to pass a decimal value, it fails. For example:

IDataParameter param21 = command.CreateParameter();
param21.ParameterName = "GEOLAT";
param21.DbType = DbType.Int32;
param21.Value = 12;

Am I doing something wrong here?

I'm using the Firebird .NET data provider 2.5.1 against a Firebird 2.1.3 database.

UPDATE: At @bluecoder's request, I tried to build the .NET data provider from the source code. But I get a compile error at line 731 of GdsStatement.cs.

int processedItems = (rowDescs[part] != null ? rowDescs[part].Count : 0);

The error is:

There exist both implicit conversions from "short" to "int" and from "int" to "short".

Changing line 731 to this:

int processedItems = (rowDescs[part] != null ? (int) rowDescs[part].Count : 0);

Allows the code to compile on my machine.

After making this code change, I was able to run my tests and use decimals in my stored procedure successfully.

It appears that the trigger for the error is that I return the number of rows affected by my update statement as part of the procedure.

UPDATE2: Here's the full source for the SQL stored procedure. It's used to create an hCard record in my database.

SET TERM ^ ;
RECREATE PROCEDURE CREATECONTACT (
 TEMPLATECODE     SMALLINT,
 CREATEUSERID     INTEGER,
 CREATEDATE       TIMESTAMP,
 SECURITYCODE     SMALLINT,
 LINKTEXT         VARCHAR(255),
 GUID             VARCHAR(200),
 GIVENNAME        VARCHAR(200),
 FAMILYNAME       VARCHAR(200),
 ADDITIONALNAME   VARCHAR(200),
 HONORIFICPREFIX  VARCHAR(200),
 HONORIFICSUFFIX  VARCHAR(200),
 NICKNAME         VARCHAR(200),
 PHOTOLOCALFILEID INTEGER,
 LOGOLOCALFILEID  INTEGER,
 BIRTHDAY         TIMESTAMP,
 JOBTITLE         VARCHAR(200),
 "ROLE"           VARCHAR(200),
 ORGANIZATION     VARCHAR(255),
 NOTE             BLOB SUB_TYPE TEXT,
 GEOLAT           DECIMAL(9,6),
 GEOLONG          DECIMAL(9,6))
RETURNS (
 ENTITYID         INTEGER)
AS 
BEGIN
EXECUTE PROCEDURE CreateEntity :TEMPLATECODE, :CREATEUSERID, :CREATEDATE, :SECURITYCODE, :LinkText 
    RETURNING_VALUES EntityId;

INSERT INTO CONTACT (EntityId, GUID, GIVENNAME, FAMILYNAME, ADDITIONALNAME, 
HONORIFICPREFIX, HONORIFICSUFFIX, NICKNAME, PHOTOLOCALFILEID, LOGOLOCALFILEID, 
BIRTHDAY, JOBTITLE, "ROLE", ORGANIZATION, NOTE, GEOLAT, GEOLONG)
VALUES (:EntityId, :GUID, :GIVENNAME, :FAMILYNAME, :ADDITIONALNAME, 
:HONORIFICPREFIX, :HONORIFICSUFFIX, :NICKNAME, :PHOTOLOCALFILEID, 
:LOGOLOCALFILEID, :BIRTHDAY, :JOBTITLE, :ROLE, :ORGANIZATION, :NOTE, 
:GEOLAT, :GEOLONG);
SUSPEND;
END^
SET TERM ; ^

UPDATE3: I just tried this with the new Firebird Client 2.5.2 release and it's still having a problem. I think this is a bug. I'm going to report it on the Firebird Tracker.

UPDATE4: Oops. I discovered I had a stale reference to Firebird Client 2.5.1 in my GAC. Firebird Client 2.5.2 does fix the problem.

Answer 1

Checking the obvious first:

1) Are you sure none of your Parameter.DbType are set to DbType.Int32?

2) Can you post the actual code where your debugger is encountering the error? (I'm assuming that is pseudocode above)

3) Did you verify that failure is at the Parameter.Value assignment line? (Not at the command execution, for example)

---

Source
Download .Net Provider Source - Provider
7zip to extract source (If you don't have it) - 7Zip

Build
[Backup your current source being modifying anything]
1) Right-click on your C# solution and select Add->Existing solution
2) select the *.csproj from the directory you extracted the Provider source to
[..\NETProvider\source\FirebirdSql\Data\FirebirdSql.Data.FirebirdClient.csproj]
3) Right-click on the FirebirdClient project and build it
4) Change the FirebirdSql.Data.FirebirdClient reference in your app to the version you just compiled
[..\Data\bin\Debug\FirebirdSql.Data.FirebirdClient.dll]

Now you should be able to step into (F11) your ExecuteScalar statement and see where the conversion exception is being raised.

Let me know if any of that doesn't made sense. I'm interested to see what you find

Answer 2

I just discovered that I had a stale reference to Firebird Client 2.5.1 in my GAC. After removing this reference, I was able to run my test cases successfully.

The .NET Provider for Firebird version 2.5.2 does not have the problem I described in my question. The solution is to upgrade to the latest version of the .NET Provider for Firebird.