Re: ADO.NET Provider returning null value in output parameter

Steve Naidamast

unread,

Aug 9, 2023, 9:43:06 AM8/9/23

to firebird-net-provider

Hello...

I have been wracking my head over an issue that concerns a single stored procedure being called by my application, which is the following...

>>>

CREATE PROCEDURE SP_UPDATE_EQUIP_REC(
PI_KEY_IN INTEGER NOT NULL,
PS_NAME_IN VARCHAR(50) NOT NULL,
PS_DESCRIPTION_IN VARCHAR(200) NOT NULL,
PI_EQUIPMENT_TYPE_KEY_IN INTEGER NOT NULL,
PI_EFFECTIVE_MOVEMENT_OFFSET_IN INTEGER NOT NULL,
PI_EFFECTIVE_RANGE_IN INTEGER NOT NULL,
PI_EFFECTIVE_MAX_RANGE_IN INTEGER NOT NULL,
PI_MUNITIONS_COUNT_IN INTEGER NOT NULL)
RETURNS(
PI_RECORDS_AFFECTED_OUT INTEGER)
AS
BEGIN
UPDATE EQUIPMENT
SET E_NAME = :PS_NAME_IN,
E_DESCRIPTION = :PS_DESCRIPTION_IN,
E_EQUIPMENT_TYPE_KEY = :PI_EQUIPMENT_TYPE_KEY_IN,
E_EFFECTIVE_MOVEMENT_OFFSET = :PI_EFFECTIVE_MOVEMENT_OFFSET_IN,
E_EFFECTIVE_RANGE = :PI_EFFECTIVE_RANGE_IN,
E_EFFECTIVE_MAX_RANGE = :PI_EFFECTIVE_MAX_RANGE_IN,
E_MUNITIONS_COUNT = :PI_MUNITIONS_COUNT_IN
WHERE E_KEY = :PI_KEY_IN
RETURNING ROW_COUNT INTO :PI_RECORDS_AFFECTED_OUT;
END;

<<<

If I execute the stored procedure in my database manager, it updates the table as expected, and returns a value of "1" into the output parameter, " PI_RECORDS_AFFECTED_OUT", as expected.

However, when I execute this stored procedure by calling it within my application, it fails to update the data in the table and returns a "DBNull" in the parameter set up to capture the returning value.

The code in my application is basically duplicate code of the same type that I use with all my update processes within the application, and every stored procedure works as expected with the updates except for this one.

Does anyone here have a clue as to what could be going wrong?

This makes no sense to me.

By the way, I am using the ADO.NET provider for the Firebird 2.5.9 version of the database and have never had a single problem with it.

Thank you...

Steve Naidamast

Sr. Software Engineer

Jiří Činčura

unread,

Aug 9, 2023, 9:45:18 AM8/9/23

to 'Mr. John' via firebird-net-provider

> Does anyone here have a clue as to what could be going wrong?

Without a code example, it's hard to guess...

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

Steve Naidamast

unread,

Aug 9, 2023, 9:51:28 AM8/9/23

to firebird-n...@googlegroups.com

Jiri...

Here is the method that executes the stored-procedure in question...

>>>

Friend Function Update_EquipmentRecord(ByVal poEquipmentStructure As DataStructures.EquipmentStructure) As Integer

Dim liRecordsAffected As Integer = 0

Dim loActionStringBuilder As New StringBuilder()

Dim loParameterArrayList As New ArrayList()

Dim loFbSqlHelper As New BlackFalconAB.Firebird.FbSqlHelper()

Dim loFbSqlHelperParameterStructure As BlackFalconAB.Firebird.FbSqlHelperParameterStructure

'CREATE PROCEDURE SP_UPDATE_EQUIP_REC(

' PI_KEY_IN INTEGER NOT NULL,

' PS_NAME_IN VARCHAR(50) NOT NULL,

' PS_DESCRIPTION_IN VARCHAR(200) NOT NULL,

' PI_EQUIPMENT_TYPE_KEY_IN INTEGER NOT NULL,

' PI_EFFECTIVE_MOVEMENT_OFFSET_IN INTEGER NOT NULL,

' PI_EFFECTIVE_RANGE_IN INTEGER NOT NULL,

' PI_EFFECTIVE_MAX_RANGE_IN INTEGER NOT NULL,

' PI_MUNITIONS_COUNT_IN INTEGER NOT NULL)

'RETURNS(

' PI_RECORDS_AFFECTED_OUT INTEGER)

'AS

'BEGIN

' UPDATE EQUIPMENT

' SET E_NAME = :PS_NAME_IN,

' E_DESCRIPTION = :PS_DESCRIPTION_IN,

' E_EQUIPMENT_TYPE_KEY = :PI_EQUIPMENT_TYPE_KEY_IN,

' E_EFFECTIVE_MOVEMENT_OFFSET = :PI_EFFECTIVE_MOVEMENT_OFFSET_IN,

' E_EFFECTIVE_RANGE = :PI_EFFECTIVE_RANGE_IN,

' E_EFFECTIVE_MAX_RANGE = :PI_EFFECTIVE_MAX_RANGE_IN,

' E_MUNITIONS_COUNT = :PI_MUNITIONS_COUNT_IN

' WHERE E_KEY = :PI_KEY_IN

' RETURNING ROW_COUNT INTO :PI_RECORDS_AFFECTED_OUT;

'END;

' create action

loActionStringBuilder.Append("SP_UPDATE_EQUIP_REC")

' add parameters

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_KEY_IN"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_TYPE_KEY

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.VarChar

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PS_NAME_IN"

loFbSqlHelperParameterStructure.PARAMETER_SIZE = 50

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_NAME

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.VarChar

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PS_DESCRIPTION_IN"

loFbSqlHelperParameterStructure.PARAMETER_SIZE = 200

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_DESCRIPTION

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_EQUIPMENT_TYPE_KEY_IN"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_TYPE_KEY

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_EFFECTIVE_MOVEMENT_OFFSET_IN"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_MOVEMENT_OFFSET

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_EFFECTIVE_RANGE_IN"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_EFFECTIVE_RANGE

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_EFFECTIVE_MAX_RANGE_IN"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_MAX_EFFECTIVE_RANGE

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_MUNITIONS_COUNT_IN"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Input

loFbSqlHelperParameterStructure.PARAMETER_VALUE = poEquipmentStructure.EQUIPMENT_MUNITIONS_COUNT

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure.PARAMETER_FBSQLDBTYPE = FirebirdSql.Data.FirebirdClient.FbDbType.Integer

loFbSqlHelperParameterStructure.PARAMETER_NAME = "PI_RECORDS_AFFECTED_OUT"

loFbSqlHelperParameterStructure.PARAMETER_DIRECTION = ParameterDirection.Output

loParameterArrayList.Add(loFbSqlHelperParameterStructure)

' execute insert

Try

liRecordsAffected = loFbSqlHelper.ExecuteNonQuery(csConnectionString, loActionStringBuilder.ToString(), loParameterArrayList)

loFbSqlHelperParameterStructure = New BlackFalconAB.Firebird.FbSqlHelperParameterStructure()

loFbSqlHelperParameterStructure = DirectCast(loParameterArrayList(8), BlackFalconAB.Firebird.FbSqlHelperParameterStructure)

liRecordsAffected = 1

'liRecordsAffected = Convert.ToInt32(loFbSqlHelperParameterStructure.PARAMETER_VALUE.ToString().Trim())

Catch loFbException As FirebirdSql.Data.FirebirdClient.FbException

Return (-2) ' firebird exception

Catch loException As Exception

Return (-1) ' general exception

End Try

Return (liRecordsAffected)

End Function

<<<

Please let me know if you require any further information.

Thank you...

Steve Naidamast

Sr. Software Engineer

blackfalc...@outlook.com

From: firebird-n...@googlegroups.com <firebird-n...@googlegroups.com> on behalf of Jiří Činčura <ji...@cincura.net>
Sent: Wednesday, August 9, 2023 9:45 AM
To: 'Mr. John' via firebird-net-provider <firebird-n...@googlegroups.com>
Subject: Re: [firebird-net-provider] Re: ADO.NET Provider returning null value in output parameter

> Does anyone here have a clue as to what could be going wrong?

Without a code example, it's hard to guess...

--
Mgr. Jiří Činčura

https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.tabsoverspaces.com%2F&data=05%7C01%7C%7Cd4384bcffbe74cb0200308db98dedfc6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638271855228053108%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=QBR9VXQZvuwOjH1l%2FY%2B5cXxE1gUWJN6KAj%2BKpAmhAww%3D&reserved=0

--
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To view this discussion on the web visit https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgroups.google.com%2Fd%2Fmsgid%2Ffirebird-net-provider%2Ffcf51882-17b8-4953-b466-98c79f8ac95d%2540app.fastmail.com&data=05%7C01%7C%7Cd4384bcffbe74cb0200308db98dedfc6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638271855228209345%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=JI3k01vY4iq3H5mQYesZTHNUl5PCC1oZ2ZXsrY4ymvE%3D&reserved=0.

Steve Naidamast

unread,

Aug 9, 2023, 10:28:40 AM8/9/23

to firebird-net-provider

I found this issue... :-)

The problem was that my search key ("PI_KEY_IN") was using the "Equipment Type Key", when it should have been "EQUIPMENT KEY".

Once I made this correction, everything started to work as expected.

All I needed was some time away from doing this research.

Thank you for your replies...

Steve Naidamast

Sr. Software Engineer

Reply all

Reply to author

Forward