Issue Details (XML | Word | Printable)

Key: DNET-770
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Remigiusz Babicz
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Problem with INSERT (...) RETURNING (...) and using DataReader

Created: 14/Jun/17 08:55 AM   Updated: 09/Oct/17 10:12 AM
Component/s: ADO.NET Provider
Affects Version/s: 5.9.1.0
Fix Version/s: 6.0.0.0

Environment: Windows x64 ; Firebird database 2.5.4 ; FirebirdSql.Data.FirebirdClient 5.9.1
Issue Links:
Duplicate
 


 Description  « Hide
INSERT (...) RETURNING (...) statement:
* works fine when using ExecuteScalar()
* doesn't work when using Executereader()


Full Code:

using System;
using FirebirdSql.Data.FirebirdClient;

namespace FirebirdClient
{
   class Program
   {
      static void Main(string[] args)
      {
         object generatedID;
         string command = @"
INSERT INTO CUSTOMER(CUSTOMER, CONTACT_FIRST, CONTACT_LAST, PHONE_NO, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE_PROVINCE, COUNTRY, POSTAL_CODE, ON_HOLD)
VALUES('aaa', 'Jan','Kowalski', '1256', 'ulica', 'miasto', 'city', 'province', 'USA', '123', '*' ) RETURNING CUST_NO";
         string connectionString = @"DataSource = localhost;Database = ""localhost:C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB""; User = sysdba; Password = masterkey";
         using (FbConnection con = new FbConnection(connectionString))
         {
            con.Open();
            using (FbCommand fbc = new FbCommand(command, con))
            {
               if (false)
               {
                  generatedID = fbc.ExecuteScalar(); //this works fine!
               }
               else
               {
                  //this doesn't work
                  FbDataReader dr = fbc.ExecuteReader();
                  int fielsCount = dr.FieldCount; //return 1
                  string namse = dr.GetName(0); //return CUST_NO
                  Type type = dr.GetFieldType(0); //return int
                  bool rows = dr.HasRows; //return false
                  bool read = dr.Read(); //return false
                  generatedID = dr.GetValue(0); //throws exception
               }
            }
         }
      }
   }
}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Andre Litfin added a comment - 14/Jun/17 10:15 AM
You can change Method Fetch() in GdsStatement.cs and FesStatement and write

            if (this.StatementType == DbStatementType.StoredProcedure && !_allRowsFetched)
            {
                _allRowsFetched = true;
                return this.GetOutputParameters();
            }
            else if (_statementType == DbStatementType.Insert && _allRowsFetched)
            {
                return null;
            }
            else if (_statementType != DbStatementType.Select && _statementType != DbStatementType.SelectForUpdate)
            {
                return null;
            }

instead of

            //Original
            /*
            if (_statementType != DbStatementType.Select &&
_statementType != DbStatementType.SelectForUpdate)
{
return null;
}*/

to make it work :-)

Rafael Almeida added a comment - 01/Sep/17 01:31 AM
Very good suggestion Andre Litfin , these days I had this need.

I'll be trying to help Cincura. I did some testing here and everything went well!