firebird-net-provider — All about development of the MS .Net provider

Re: [Firebird-net-provider] Detect current connection is local or network

[Mr. J. <mr_...@ya...>]

Hi,thanks Alexander,

I've put that condition:

If ",localhost,127.0.0.1,".Contains("," + My.Settings.Server_Name.Trim.ToLower + ",")


  ..................

EndIf           

I don't know if that is the best way

thanks

      



On Saturday, December 14, 2013 1:55 PM, Alexander Muylaert-Gelein <amu...@ho...> wrote:
 
Hi 

This parses a firebird path and scans for "loopback" or computername.  You just need to add all possible IP's from you local PC and localhost.

good luck

a


        private static void ReplaceLocalHost(ref string aServer)
        {
            s_OriginalDatabaseServer = aServer;
            if (System.Environment.MachineName.Equals(aServer, StringComparison.OrdinalIgnoreCase) || ("127.0.0.1".Equals(aServer, StringComparison.Ordinal)))
            {
                aServer = "localhost";
            }
        }


        private static char[] s_Splitter = new char[] { ':' };

        private static bool SplitServerAndPort(string aPart, out string aServer, out int aPort)
        {
            var p = aPart.IndexOf('/');
            if (p >= 0)
            {
                aServer = aPart.Substring(0, p);
                if (int.TryParse(aPart.Substring(p + 1), out aPort))
                {
                    ReplaceLocalHost(ref aServer);
                    return true;
                }
                return false;
            }
            else
            {
                aServer = aPart;
                ReplaceLocalHost(ref aServer);
                aPort = 3050;
                return true;
            }
        }

        public static bool SplitDatabaseTarget(string aDatabaseTarget, out string aServer, out int aPort,
                                               out string aPath)
        {
            aServer = "localhost";
            aPort = 3050;
            aPath = null;

            if (string.IsNullOrWhiteSpace(aDatabaseTarget))
            {
                return false;
            }

            var parts = aDatabaseTarget.Split(s_Splitter, StringSplitOptions.RemoveEmptyEntries);

            switch (parts.Length)
            {
                case 0:
                    return false;
                case 1:
                    // "webdb"
                    aPath = parts[0];
                    return true;
                case 2:
                    // "c:\fast.fdb"
                    // "fast-server:webdb
                    // "fast-server/3052:webdb"
                    if (parts[0].Length == 1 && parts[1].StartsWith("\\"))
                    {
                        aPath = parts[0] + ":" + parts[1];
                    }
                    else
                    {
                        if (!SplitServerAndPort(parts[0], out aServer, out aPort))
                        {
                            return false;
                        }
                        aPath = parts[1];
                    }
                    return true;
                case 3:
                    // "fast-server:c:\fast\fast.fdb
                    // "fast-server/3052:c:\fast\fast.fdb"
                    if (!SplitServerAndPort(parts[0], out aServer, out aPort))
                    {
                        return false;
                    }
                    if (parts[1].Length == 1 && parts[2].StartsWith("\\"))
                    {
                        aPath = parts[1] + ":" + parts[2];
                        return true;
                    }
                    return false;
                default:
                    return false;
            }
        }



________________________________
Date: Fri, 13 Dec 2013 23:27:55 -0800
From: mr_...@ya...
To: fir...@li...
Subject: Re: [Firebird-net-provider] Detect current connection is local or	network


Hi ! 
"local"  = database is on the same machine as application is
thanks.



On Friday, December 13, 2013 3:10 PM, Jiri Cincura <di...@ci...> wrote:
>Depends on what you think "local" (your original question) is.



On Fri, Dec 13, 2013 at 12:40 PM, Mr. John <mr_...@ya...> wrote:
> solution is to search in connecton string  for  'localhost' or '127.0.0.1'
> ?








On Friday, December 13, 2013 3:10 PM, Jiri Cincura <di...@ci...> wrote:
 
On Fri, Dec 13, 2013 at 12:40 PM, Mr. John <mr_...@ya...> wrote:
> solution is to search in connecton string  for   'localhost' or '127.0.0.1'
> ?

Depends on what you think "local" (your original question) is.


-- 
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com



------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT 
organizations don't have a clear picture of how application performance 
affects their revenue. With AppDynamics, you get 100% visibility into your 
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

Re: [Firebird-net-provider] Detect current connection is local or network

[Mr. J. <mr_...@ya...>]

Hi ! 
"local"  = database is on the same machine as application is
thanks.



On Friday, December 13, 2013 3:10 PM, Jiri Cincura <di...@ci...> wrote:
>Depends on what you think "local" (your original question) is.



On Fri, Dec 13, 2013 at 12:40 PM, Mr. John <mr_...@ya...> wrote:
> solution is to search in connecton string  for  'localhost' or '127.0.0.1'
> ?








On Friday, December 13, 2013 3:10 PM, Jiri Cincura <di...@ci...> wrote:
 
On Fri, Dec 13, 2013 at 12:40 PM, Mr. John <mr_...@ya...> wrote:
> solution is to search in connecton string  for   'localhost' or '127.0.0.1'
> ?

Depends on what you think "local" (your original question) is.


-- 
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com

[Firebird-net-provider] [FB-Tracker] Created: (DNET-536) FbParameter constructor not treating 0 as number, but uses null

[Zvjezdan T. (JIRA) <tr...@fi...>]

FbParameter constructor not treating 0 as number, but uses null
---------------------------------------------------------------

                 Key: DNET-536
                 URL: http://tracker.firebirdsql.org/browse/DNET-536
             Project: .NET Data provider
          Issue Type: Bug
          Components: ADO.NET Provider
    Affects Versions: 4.0.0.0
         Environment: Win7 64
            Reporter: Zvjezdan Tomicevic
            Assignee: Jiri Cincura


Hi.
After using Fbparameeter constructor as
FbParameter pTableId = new FbParameter("@table_id", 0); (string parametername, object value)
pTableId.Value remains null instead of zero.
Fix is:
   pTableId.Value = 0;
But it was hell to debug and locate

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Re: [Firebird-net-provider] Detect current connection is local or network

[Jiri C. <di...@ci...>]

Check the connection string (accessible from FbConnection).


-- 
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com

[Firebird-net-provider] Detect current connection is local or network

[Mr. J. <mr_...@ya...>]

Hi !
I'm using VB.NET 2008 with FB SS 2.5 and FirebirdClient 2.7.7.0

I need to detect if current FB connection is local or over a network.
Thanks.

[Firebird-net-provider] [FB-Tracker] Created: (DNET-535) Exception when using Guid variables

[Andrey (JIRA) <tr...@fi...>]

Exception when using Guid variables
-----------------------------------

                 Key: DNET-535
                 URL: http://tracker.firebirdsql.org/browse/DNET-535
             Project: .NET Data provider
          Issue Type: Bug
          Components: ADO.NET Provider
    Affects Versions: 4.0.0.0
         Environment: net framework 4, firebird 2.5.2, Win XP.
            Reporter: Andrey
            Assignee: Jiri Cincura
            Priority: Critical


Exception when using Guid variables.
When performing the same procedure in four different commands, two of them give out an exception.
            
1)Executing this work fine.
comm.CommandText = String.Format("select * from SCOPEINFO_SP_UPD({0},{1},{2},{3},{4},{5})"
                , "@sync_scope_knowledge"
                , "@sync_scope_id"
                , "@sync_scope_cleanup_knowledge"
                , "@sync_scope_name"
                , "@sync_check_concurrency"
                , "@sync_scope_timestamp");
2)Executing this work fine.
            comm.CommandText = String.Format("execute procedure SCOPEINFO_SP_UPD {0},{1},{2},{3},{4},{5};"
                , "@sync_scope_knowledge"
                , "@sync_scope_id"
                , "@sync_scope_cleanup_knowledge"
                , "@sync_scope_name"
                , "@sync_check_concurrency"
                , "@sync_scope_timestamp");
3)give out an exception
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "SCOPEINFO_SP_UPD";
            comm.Parameters["sync_scope_id"].Value = Guid.Parse("31313131-3131-3131-3131-313131313131");
	    in test class: BugV1_1();
exception:
            Dynamic SQL Error
            SQL error code = -303
            arithmetic exception, numeric overflow, or string truncation 
            numeric value is out of range
4)give out an exception
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "SCOPEINFO_SP_UPD";
            comm.Parameters["sync_scope_id"].Value = Guid.NewGuid();
            in test class: BugV1_2();
exception:
            Dynamic SQL Error
            SQL error code = -303
            conversion error from string "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"


TEST Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using FirebirdSql.Data.FirebirdClient;

namespace GUIDBUG
{
    public class GuidBug
    {
        public GuidBug()
        {
            F_CreateConnectString();
            F_CreateAndInitDB();
        }
        protected string _P_CreateDBString = null;
        protected string _P_ConnectDBString = null;
        
        protected void F_CreateConnectString()
        {
            FbConnectionStringBuilder connectString = new FbConnectionStringBuilder();
            connectString.Database = "GUIDTEST.FDB";
            connectString.Dialect = 3;
            connectString.DataSource = "localhost";
            connectString.UserID = "SYSDBA";
            connectString.Password = "masterkey";
            //connectString.Charset = "WIN1251";
            connectString.ConnectionLifeTime = 100;
            connectString.ServerType = FbServerType.Embedded;
            // connectString.ServerType = FbServerType.Default;
            connectString.Pooling = true;
            connectString.MinPoolSize = 0;
            connectString.MaxPoolSize = 50;
            _P_CreateDBString = connectString.ConnectionString;
            connectString.Charset = "WIN1251";
            _P_CreateDBString = _P_CreateDBString + ";DEFAULT CHARACTER SET = WIN1251;";
            _P_ConnectDBString = connectString.ConnectionString;
            connectString.Enlist = true;

        }
        protected void F_CreateAndInitDB()
        {

            try
            {
                FbConnection.CreateDatabase(_P_CreateDBString, 8192, true, false);
                F_InitDataBase();
            }
            catch { }


        }
        protected void F_InitDataBase()
        {
            using (var conn = new FbConnection(_P_ConnectDBString))
            {
                conn.Open();
                using (var comm = conn.CreateCommand())
                {
                    //-------------------------
                    comm.CommandText = "CREATE GENERATOR SCOPE_INFO_G;";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------
                    comm.CommandText = @"
                   CREATE DOMAIN GUID AS
                   CHAR(16) CHARACTER SET OCTETS
                   COLLATE OCTETS;";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------
                    comm.CommandText = @"
                   CREATE TABLE SCOPE_INFO (
                   SCOPE_LOCAL_ID             INTEGER,
                   SCOPE_ID                   GUID /* GUID = CHAR(16) */,
                   SCOPE_NAME                 VARCHAR(100) NOT NULL,
                   SCOPE_SYNC_KNOWLEDGE       CHAR(8000) CHARACTER SET OCTETS,
                   SCOPE_FORGOTTEN_KNOWLEDGE  CHAR(8000) CHARACTER SET OCTETS,
                   SCOPE_TIMESTAMP            BIGINT,
                   SCOPE_CLEANUP_TIMESTAMP    BIGINT
                   );";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------
                    comm.CommandText = "ALTER TABLE SCOPE_INFO ADD PRIMARY KEY (SCOPE_NAME);";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------
                    comm.CommandText = "CREATE UNIQUE INDEX SCOPE_INFO_IDX1 ON SCOPE_INFO (SCOPE_ID);";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------
                    comm.CommandText = @"
                   CREATE OR ALTER PROCEDURE SCOPEINFO_SP_UPD (
                   sync_scope_knowledge char(8000) character set octets,
                   sync_scope_id guid,
                   sync_scope_cleanup_knowledge char(8000) character set octets,
                   sync_scope_name varchar(100) character set win1251,
                   sync_check_concurrency integer,
                   sync_scope_timestamp bigint)
                   returns (sync_row_count integer)
                   as
                     begin
                        UPDATE  scope_info SET
                                scope_sync_knowledge=:sync_scope_knowledge,
                                scope_id = :sync_scope_id,
                                scope_forgotten_knowledge=:sync_scope_cleanup_knowledge
                                where scope_name=:sync_scope_name and
                                (:sync_check_concurrency=0 or scope_timestamp=:sync_scope_timestamp);
                                sync_row_count=ROW_COUNT;
                        suspend;
                     end";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------
                    comm.CommandText = "insert into scope_info (scope_local_id,scope_id,scope_name)" +
                                       "values(1,'1111111111111111','sync1');";
                    try { comm.ExecuteNonQuery(); }
                    catch { }
                    //-------------------------

                }

            }
        }
        
        protected FbCommand F_GetUpdateScopeInfoCommandV1()
        {
            FbCommand comm = new FbCommand();
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "SCOPEINFO_SP_UPD";

            comm.Parameters.Add("sync_scope_knowledge", FbDbType.Char, 8000);
            comm.Parameters.Add("sync_scope_cleanup_knowledge", FbDbType.Char, 8000);
            //comm.Parameters.Add("sync_scope_knowledge", FbDbType.Binary);
            //comm.Parameters.Add("sync_scope_cleanup_knowledge", FbDbType.Binary);
            comm.Parameters.Add("sync_scope_name", FbDbType.VarChar, 100);
            comm.Parameters.Add("sync_check_concurrency", FbDbType.Integer);
            comm.Parameters.Add("sync_scope_id", FbDbType.Guid);
            comm.Parameters.Add("sync_scope_timestamp", FbDbType.BigInt);
            comm.Parameters.Add("sync_row_count", FbDbType.Integer).Direction = ParameterDirection.Output;

            return comm;
        }
        protected FbCommand F_GetUpdateScopeInfoCommandV2()
        {
            FbCommand comm = new FbCommand();
            comm.CommandText = String.Format("select * from SCOPEINFO_SP_UPD({0},{1},{2},{3},{4},{5})"
                , "@sync_scope_knowledge"
                , "@sync_scope_id"
                , "@sync_scope_cleanup_knowledge"
                , "@sync_scope_name"
                , "@sync_check_concurrency"
                , "@sync_scope_timestamp");


            comm.Parameters.Add("sync_scope_knowledge", FbDbType.Char, 8000);
            comm.Parameters.Add("sync_scope_cleanup_knowledge", FbDbType.Char, 8000);
            //comm.Parameters.Add("sync_scope_knowledge", FbDbType.Binary);
            //comm.Parameters.Add("sync_scope_cleanup_knowledge", FbDbType.Binary);
            comm.Parameters.Add("sync_scope_name", FbDbType.VarChar, 100);
            comm.Parameters.Add("sync_check_concurrency", FbDbType.Integer);
            comm.Parameters.Add("sync_scope_id", FbDbType.Guid);
            comm.Parameters.Add("sync_scope_timestamp", FbDbType.BigInt);

            return comm;
        }
        protected FbCommand F_GetUpdateScopeInfoCommandV3()
        {
            FbCommand comm = new FbCommand();
            comm.CommandText = String.Format("execute procedure SCOPEINFO_SP_UPD {0},{1},{2},{3},{4},{5};"
                , "@sync_scope_knowledge"
                , "@sync_scope_id"
                , "@sync_scope_cleanup_knowledge"
                , "@sync_scope_name"
                , "@sync_check_concurrency"
                , "@sync_scope_timestamp");
           

            comm.Parameters.Add("sync_scope_knowledge", FbDbType.Char, 8000);
            comm.Parameters.Add("sync_scope_cleanup_knowledge", FbDbType.Char, 8000);
            //comm.Parameters.Add("sync_scope_knowledge", FbDbType.Binary);
            //comm.Parameters.Add("sync_scope_cleanup_knowledge", FbDbType.Binary);
            comm.Parameters.Add("sync_scope_name", FbDbType.VarChar, 100);
            comm.Parameters.Add("sync_check_concurrency", FbDbType.Integer);
            comm.Parameters.Add("sync_scope_id", FbDbType.Guid);
            comm.Parameters.Add("sync_scope_timestamp", FbDbType.BigInt);
            return comm;
        }
        public void itWorkV2_1()
        {
            using (var conn = new FbConnection(_P_ConnectDBString))
            {
                conn.Open();
                var comm = F_GetUpdateScopeInfoCommandV2();
                comm.Connection = conn;
                comm.Parameters["sync_scope_name"].Value = "sync1";
                comm.Parameters["sync_scope_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_scope_cleanup_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_check_concurrency"].Value = 0;
                comm.Parameters["sync_scope_id"].Value = Guid.Parse("31313131-3131-3131-3131-313131313131");
                comm.Parameters["sync_scope_timestamp"].Value = 2;
                var sesult=comm.ExecuteScalar();
            }
        }
        public void itWorkV3_2()
        {
            using (var conn = new FbConnection(_P_ConnectDBString))
            {
                conn.Open();
                var comm = F_GetUpdateScopeInfoCommandV3();
                comm.Connection = conn;
                comm.Parameters["sync_scope_name"].Value = "sync1";
                comm.Parameters["sync_scope_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_scope_cleanup_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_check_concurrency"].Value = 0;
                comm.Parameters["sync_scope_id"].Value = Guid.Parse("31313131-3131-3131-3131-313131313131");
                comm.Parameters["sync_scope_timestamp"].Value = 2;
                var sesult = comm.ExecuteScalar();
            }
        }
        public void BugV1_1()
        { 
            using (var conn = new FbConnection(_P_ConnectDBString))
            {
                conn.Open();
                var comm = F_GetUpdateScopeInfoCommandV1();
                comm.Connection = conn;
                comm.Parameters["sync_scope_name"].Value = "sync1";
                comm.Parameters["sync_scope_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_scope_cleanup_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_check_concurrency"].Value = 0;
                comm.Parameters["sync_scope_id"].Value = Guid.Parse("31313131-3131-3131-3131-313131313131");
                comm.Parameters["sync_scope_timestamp"].Value = 2;
                var sesult = comm.ExecuteScalar();
            }
        }
        public void BugV1_2()
        {
            using (var conn = new FbConnection(_P_ConnectDBString))
            {
                conn.Open();
                var comm = F_GetUpdateScopeInfoCommandV1();
                comm.Connection = conn;
                comm.Parameters["sync_scope_name"].Value = "sync1";
                comm.Parameters["sync_scope_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_scope_cleanup_knowledge"].Value = DBNull.Value;
                comm.Parameters["sync_check_concurrency"].Value = 0;
                comm.Parameters["sync_scope_id"].Value = Guid.NewGuid();
                comm.Parameters["sync_scope_timestamp"].Value = 2;
                var sesult = comm.ExecuteScalar();

            }
        }

    }
}


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[Firebird-net-provider] Stored Procedure in Entity Framework

[PRS <ps...@cc...>]

Hi,

I am trying to add a Firebird stored procedure to an entity model in Visual
Studio 2013 (.Net Framework v4.0) When I use the 'Update Model from
Database' I can select the stored procedure and click OK but it doesn't
import it. It definitely isn't in the SSDL of my model. There are no errors
and the warnings have nothing to do with this stored proc.

The output says:

The model was generated with warnings or errors.CCareDomainModel.edmxPlease
see the Error List for more details. These issues must be fixed before
running your application.
Loading metadata from the database took 00:00:00.5623432.
Generating the model took 00:03:51.6605162.

The version of Firebird I am using is: 2.5.1
The version of the Firebird Provider I am using is: 3.2.0.0

I tried it with a very simple selectable stored proc:

create or alter procedure PATIENTS_STARTING_WITH_G
returns (
    SURNAME varchar(255))
as
begin
  for select surname from patient where surname like 'G%' into :surname do
  begin
    suspend;
  end
end

Am I missing something?

Thanks



--
View this message in context: http://firebird.1100200.n4.nabble.com/Stored-Procedure-in-Entity-Framework-tp4634673.html
Sent from the firebird-net-provider mailing list archive at Nabble.com.

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Mark R. <ma...@la...>]

On 27-11-2013 09:28, Kjell Rilbe wrote:
>> The problem is that you did not close the command, which means it is
>> still prepared, which marks the table as used. Now when you close the
>> connection, the command will be closed as well.
>
> I looked for an Unprepare method, and didn't find it. Thought "oh well".
> Didn't think to look for Close. I'll give that a try.

There is no concept of 'unprepare' in Firebird. Either a statement 
handle is prepared with a different statement, or the statement handle 
is closed. Both will effectively unprepare the old statement, but it is 
not entirely the same concept ;)

>> So the solution is to wrap the command (and everything that uses the
>> command) in a using block as well.
>
> Or call cmd.Close explicitly?

Sure, but personally I prefer using, because you don't have to add 
boiler plate to ensure the object gets closed and disposed when 
exceptions occur.

-- 
Mark Rotteveel

[Firebird-net-provider] [FB-Tracker] Created: (DNET-534) EF6 provider incompatible with EntityFrameworkProfiler from hibernatingrhinos.com

[André Z. (J. <tr...@fi...>]

EF6 provider incompatible with EntityFrameworkProfiler from hibernatingrhinos.com
---------------------------------------------------------------------------------

                 Key: DNET-534
                 URL: http://tracker.firebirdsql.org/browse/DNET-534
             Project: .NET Data provider
          Issue Type: Bug
          Components: Entity Framework support
    Affects Versions: 4.0.0.0
         Environment: VS2012 Update 4, Firebird 2.5.2, Windows 8 Pro (x64)
            Reporter: André Ziegler
            Assignee: Jiri Cincura
            Priority: Critical


The EF6 provider is incompatible with the EntityFrameworkProfiler from hibernatingrhinos.com. 

When you add this line to the project

HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

to enable profiling, the function CheckAndCastToFbConnection throws an ArgumentException exception. The Profiler changes the FbConnection to ProfiledConnection and so the check for FbConnection fails:

{HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledConnection`1[[FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c]]}



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

[Firebird-net-provider] [FB-Tracker] Created: (DNET-533) exception at net sync framework 2.1 synchronization

[Andrey (JIRA) <tr...@fi...>]

exception at net sync framework 2.1 synchronization
---------------------------------------------------

                 Key: DNET-533
                 URL: http://tracker.firebirdsql.org/browse/DNET-533
             Project: .NET Data provider
          Issue Type: Bug
          Components: ADO.NET Provider
    Affects Versions: 4.0.0.0
         Environment: win xp, net framework 4.0
            Reporter: Andrey
            Assignee: Jiri Cincura


in attempt of synchronization of net sync framework 2.1
the exception is caused.  The link for SyncTest downloading:
http://uafile.com/file/34487/SyncTest-zip.html

System.IndexOutOfRangeException
 Message=Could not find specified column in results.
  Source=FirebirdSql.Data.FirebirdClient
  StackTrace:
       в FirebirdSql.Data.FirebirdClient.FbDataReader.GetColumnIndex(String name) в c:\Users\Jiri\Documents\devel\NETProvider\working\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbDataReader.cs:строка 747
       в FirebirdSql.Data.FirebirdClient.FbDataReader.GetOrdinal(String name) в c:\Users\Jiri\Documents\devel\NETProvider\working\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbDataReader.cs:строка 377
       в Microsoft.Synchronization.Data.DbSyncScopeHandler.ReadScope(IDbConnection connection, IDbTransaction transaction, ReadKnowledgeType readType, ReadForgottenKnowledgeType readFKtype)
  InnerException:

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

Re: [Firebird-net-provider] DDEX 3.0 upgrade problem

[Fanda <de...@si...>]

Markus Ostenried <magnoz@...> writes:

> 
> On Tue, Mar 19, 2013 at 9:45 PM, Brian Liedtke <liedtke@...> wrote:
> > The download side is confusing, it looks like the msi package
> > is for all the listed versions of .NET. not just 4.5.
> 
> Indeed, I got confused there, too.
> Great work with the provider though -- I'm happy I can continue using
Firebird.
> 
> Thanks a lot!
> 
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> 


I was confused too on download page. I will try your suggestions. It would
be nice to have installer for .NET 4.0 assemblies too.
My DDEX instalation doesn't work too. The key in the registry was added to
11.0_Config section, but I am using VS2010 on Windows 7 64bit OS.

Thank you anyway, it is good job.
Fanda

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Kjell R. <kje...@da...>]

Den 2013-11-26 20:45 skrev Mark Rotteveel såhär:
> On 26-11-2013 14:58, Kjell Rilbe wrote:
>> Please consider this code, which essentially creates a table, commits,
>> fills data and does a select with joins, closes the query, commits, then
>> drops the table in a new transaction.
>>
>> The problem is that without the conn.Close + conn.Open before starting
>> the drop transaction, the drop fails saying the object is in use.
>>
>> Why is the Close/Open required?
>>
> ...
>> FbCommand cmd = conn.CreateCommand();
> ...
>> cmd.CommandText = "select /*...*/ from \"Tmp\" inner join /*...*/ where
>> /*...*/;";
>> cmd.Parameters.Clear();
>> using (FbDataReader rd =
>> cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
>> {
>>        rd.Read();
>>        for (int colnum = 0; colnum < rd.FieldCount; colnum++)
>>        {
>>            string colname = rd.GetName(colnum);
>>            int count = rd.GetInt32(colnum);
>>            if (count > 0)
>>                result.Counts[colname] = count;
>>        }
>>        rd.Close();
>> }
> An rd.Close() is unnecessary when you use using. It will dispose the
> object when it exits the block (which closed the reader).

I added it just to see if it would help. :-)

> The problem is that you did not close the command, which means it is
> still prepared, which marks the table as used. Now when you close the
> connection, the command will be closed as well.

I looked for an Unprepare method, and didn't find it. Thought "oh well". 
Didn't think to look for Close. I'll give that a try.

> So the solution is to wrap the command (and everything that uses the
> command) in a using block as well.

Or call cmd.Close explicitly?

>> trans.Commit();
>> conn.Close();
>> conn.Open();
>> trans = conn.BeginTransaction(new FbTransactionOptions() {
>> TransactionBehavior = FbTransactionBehavior.ReadCommitted |
>> FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
>> FbCommand cmd = conn.CreateCommand();
>> cmd.CommandType = System.Data.CommandType.Text;
>> cmd.Transaction = trans;
>> cmd.CommandText = "drop table \"Tmp\";";
>> cmd.ExecuteNonQuery();
>> trans.Commit();
> BTW: You really should use using for your connections and for
> transactions as well. This will rollback the transaction if no commit
> was called when it exits the block (either because no commit() was in
> the code or an exception occurred before the end of the block).
>
> Basic rule: if something is IDisposable, then use using.

I do have try/finally/catch for conn.Close and trans.Rollback, but 
omitted it in the post for brevity and clarity re. the normal flow of 
operations. My point was to understand why the table is in use in the 
normal case. The exception/error case was not the problem.

I wasn't aware that using {...} will do a rollback on trans. Might use 
that instead of try/catch then - looks a little slimmer in the code, 
albeit possibly slightly less clear to read since it assumes that the 
person studying the code knows that the end of the using block will 
rollback the trans if still open.

Thanks,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@da...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Kjell R. <kje...@da...>]

Den 2013-11-26 17:25 skrev Danny Gorton såhär:
> DbCommand objects are disposable and therefore this pattern can most
> easily be addressed with the 'using' pattern.  I see you are using it
> for your reader, you can also use it for your command objects.  Or you
> can write out your own try...finally blocks if you want (what 'using'
> actually emits).  For that matter, the code you posted could also use
> some protective logic to clean up the connection in case of catastrophy.

Thanks. I'll give it a try, and then also create a separate FbCommand 
for each operation - I currently reuse the same one.

I do have try/finally/catch to protect connection and transactions, but 
I omitted it in the post for brevity and clarity re. the normal flow of 
operations.

Regards,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@da...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Jiri C. <di...@ci...>]

On Wed, Nov 27, 2013 at 9:09 AM, Kjell Rilbe <kje...@da...> wrote:
> 2. For testing, how would I disable pooling?

Pooling=False in connection string.


-- 
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Kjell R. <kje...@da...>]

Den 2013-11-26 19:28 skrev Jiri Cincura såhär:
> On Tue, Nov 26, 2013 at 5:00 PM, Kjell Rilbe <kje...@da...> wrote:
>> Server=localhost;Database=My_DB_Alias;Charset=UTF8;User=My_User;Password=My_Password
>>
>> Anything missing there?
> You're using connection pooling. That might be the reason. The
> connection is kept open on your behalf.
>
Umm...

1. Doing a conn.Close + conn.Open does solve the problem, which seems to 
indicate that the connection is actually closed, not kept open.

2. For testing, how would I disable pooling?

Thanks,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@da...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Re: [Firebird-net-provider] Why is table in use after close & commit?

[Mark R. <ma...@la...>]

On 26-11-2013 14:58, Kjell Rilbe wrote:
> Please consider this code, which essentially creates a table, commits,
> fills data and does a select with joins, closes the query, commits, then
> drops the table in a new transaction.
>
> The problem is that without the conn.Close + conn.Open before starting
> the drop transaction, the drop fails saying the object is in use.
>
> Why is the Close/Open required?
>
...
> FbCommand cmd = conn.CreateCommand();
...
> cmd.CommandText = "select /*...*/ from \"Tmp\" inner join /*...*/ where
> /*...*/;";
> cmd.Parameters.Clear();
> using (FbDataReader rd =
> cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
> {
>       rd.Read();
>       for (int colnum = 0; colnum < rd.FieldCount; colnum++)
>       {
>           string colname = rd.GetName(colnum);
>           int count = rd.GetInt32(colnum);
>           if (count > 0)
>               result.Counts[colname] = count;
>       }
>       rd.Close();
> }

An rd.Close() is unnecessary when you use using. It will dispose the 
object when it exits the block (which closed the reader).

The problem is that you did not close the command, which means it is 
still prepared, which marks the table as used. Now when you close the 
connection, the command will be closed as well.

So the solution is to wrap the command (and everything that uses the 
command) in a using block as well.

> trans.Commit();
> conn.Close();
> conn.Open();
> trans = conn.BeginTransaction(new FbTransactionOptions() {
> TransactionBehavior = FbTransactionBehavior.ReadCommitted |
> FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
> FbCommand cmd = conn.CreateCommand();
> cmd.CommandType = System.Data.CommandType.Text;
> cmd.Transaction = trans;
> cmd.CommandText = "drop table \"Tmp\";";
> cmd.ExecuteNonQuery();
> trans.Commit();

BTW: You really should use using for your connections and for 
transactions as well. This will rollback the transaction if no commit 
was called when it exits the block (either because no commit() was in 
the code or an exception occurred before the end of the block).

Basic rule: if something is IDisposable, then use using.

Mark
-- 
Mark Rotteveel

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Mark R. <ma...@la...>]

On 26-11-2013 19:28, Mark Rotteveel wrote:
> The problem seems to be with connection pooling not closing the command
> when the logical connection is closed (and the physical connection
> returned to the pool). I'd argue that when connection pooling is used, a
> close of the logical connection should close any open dependent objects
> and only leave the connection itself open.

Never mind my comment re connection pooling, I missed that Kjell asked 
why an explicit close and open of the connection fixed the issue.

Mark
-- 
Mark Rotteveel

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Jiri C. <di...@ci...>]

On Tue, Nov 26, 2013 at 5:00 PM, Kjell Rilbe <kje...@da...> wrote:
> Server=localhost;Database=My_DB_Alias;Charset=UTF8;User=My_User;Password=My_Password
>
> Anything missing there?

You're using connection pooling. That might be the reason. The
connection is kept open on your behalf.

-- 
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Mark R. <ma...@la...>]

On 26-11-2013 17:04, Kjell Rilbe wrote:
>
> Den 2013-11-26 15:46 skrev Jiri Cincura såhär:
>> How the connection string looks like?
>
> Please refer to the thread in firebird-support about this issue: "Object
> in use... wtf???"
>
> I got a couple of replies there, from Ann Harrison among others. I
> probably need to make sure somehow that the FbCommand is disposed so
> that it releases/closes the statement in FB.
>
> How...?

using(var command = conn.CreateCommand()) {
    ...
}

The problem seems to be with connection pooling not closing the command 
when the logical connection is closed (and the physical connection 
returned to the pool). I'd argue that when connection pooling is used, a 
close of the logical connection should close any open dependent objects 
and only leave the connection itself open.

Mark
-- 
Mark Rotteveel

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Danny G. <da...@ab...>]

DbCommand objects are disposable and therefore this pattern can most 
easily be addressed with the 'using' pattern.  I see you are using it 
for your reader, you can also use it for your command objects.  Or you 
can write out your own try...finally blocks if you want (what 'using' 
actually emits).  For that matter, the code you posted could also use 
some protective logic to clean up the connection in case of catastrophy.


Danny Gorton II
Co-founder
Absolute Power and Control, LLC
www.absolutepowerandcontrol.com

On 11/26/2013 11:04 AM, Kjell Rilbe wrote:
>
> Den 2013-11-26 15:46 skrev Jiri Cincura såhär:
>> How the connection string looks like?
>
> Please refer to the thread in firebird-support about this issue: "Object
> in use... wtf???"
>
> I got a couple of replies there, from Ann Harrison among others. I
> probably need to make sure somehow that the FbCommand is disposed so
> that it releases/closes the statement in FB.
>
> How...?
>
> Kjell
>

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Kjell R. <kje...@da...>]

Den 2013-11-26 15:46 skrev Jiri Cincura såhär:
> How the connection string looks like?

Please refer to the thread in firebird-support about this issue: "Object 
in use... wtf???"

I got a couple of replies there, from Ann Harrison among others. I 
probably need to make sure somehow that the FbCommand is disposed so 
that it releases/closes the statement in FB.

How...?

Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@da...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Re: [Firebird-net-provider] Why is table in use after close &commit?

[Kjell R. <kje...@da...>]

Den 2013-11-26 15:46 skrev Jiri Cincura såhär:
> How the connection string looks like?

Server=localhost;Database=My_DB_Alias;Charset=UTF8;User=My_User;Password=My_Password

Anything missing there?

Regards,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@da...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Re: [Firebird-net-provider] Why is table in use after close & commit?

[Jiri C. <di...@ci...>]

How the connection string looks like?


-- 
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com

[Firebird-net-provider] Why is table in use after close & commit?

[Kjell R. <kje...@da...>]

Please consider this code, which essentially creates a table, commits, 
fills data and does a select with joins, closes the query, commits, then 
drops the table in a new transaction.

The problem is that without the conn.Close + conn.Open before starting 
the drop transaction, the drop fails saying the object is in use.

Why is the Close/Open required?

FbConnection conn = new FbConnection(connstr);
FbTransaction trans = conn.BeginTransaction(new FbTransactionOptions() { 
TransactionBehavior = FbTransactionBehavior.ReadCommitted | 
FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
FbCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Transaction = trans;
cmd.CommandText = "create table \"Tmp\" (/*...*/));";
cmd.ExecuteNonQuery();
trans.Commit();
trans = conn.BeginTransaction(new FbTransactionOptions() { 
TransactionBehavior = FbTransactionBehavior.ReadCommitted | 
FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
cmd.Transaction = trans;
cmd.CommandText = "insert into \"Tmp\" (/*...*/) values (/*...*/);";
cmd.Parameters.Add(new FbParameter("...", FbDbType.BigInt) { Direction = 
System.Data.ParameterDirection.Input, IsNullable = false });
cmd.Parameters.Add(new FbParameter("...", FbDbType.VarChar, 101) { 
Direction = System.Data.ParameterDirection.Input, IsNullable = false, 
Charset = FbCharset.Utf8 });
cmd.Prepare();
foreach (/*...*/)
{
     cmd.Parameters["..."].Value = /*...*/;
     /*...*/
     cmd.ExecuteNonQuery();
}
cmd.CommandText = "select /*...*/ from \"Tmp\" inner join /*...*/ where 
/*...*/;";
cmd.Parameters.Clear();
using (FbDataReader rd = 
cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
{
     rd.Read();
     for (int colnum = 0; colnum < rd.FieldCount; colnum++)
     {
         string colname = rd.GetName(colnum);
         int count = rd.GetInt32(colnum);
         if (count > 0)
             result.Counts[colname] = count;
     }
     rd.Close();
}
trans.Commit();
conn.Close();
conn.Open();
trans = conn.BeginTransaction(new FbTransactionOptions() { 
TransactionBehavior = FbTransactionBehavior.ReadCommitted | 
FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
FbCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Transaction = trans;
cmd.CommandText = "drop table \"Tmp\";";
cmd.ExecuteNonQuery();
trans.Commit();


Regards,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@da...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

[Firebird-net-provider] [FB-Tracker] Created: (DNET-532) The performance when doing parametrized builk inserts is poor

[Toni M. (JIRA) <tr...@fi...>]

The performance when doing parametrized builk inserts is poor
-------------------------------------------------------------

                 Key: DNET-532
                 URL: http://tracker.firebirdsql.org/browse/DNET-532
             Project: .NET Data provider
          Issue Type: Improvement
          Components: ADO.NET Provider
    Affects Versions: 4.0.0.0
         Environment: Any
            Reporter: Toni Martir
            Assignee: Jiri Cincura


The performance when executing a query with named parameters (50 or more) is very poor.
Example:
 With current code it  takes about 40 seconds to insert 20.000 records.
 With small optimizations it take about 5 seconds.

The problem is in FbParameterCollection.IndexOf

I obtained the much better results modifying FbParameterCollection and FbParameter,
but may be this is not the correct way...

With the proposed optimization only FbParameterCollection must be modified and small modification to FbParameter.

      SortedList<string, int> sortedparams;
      SortedList<string, int> sortedparamsint;

Constructor
         this.sortedparams = new SortedList<string, int>();
         this.sortedparamsint = new SortedList<string, int>();

Add
            this.parameters.Add(value);
            sortedparams.Add(value.ParameterName,this.parameters.Count-1);
            sortedparamsint.Add(value.InternalParameterName, this.parameters.Count - 1);
IndexOf
         int idx = sortedparamsint.IndexOfKey(parameterName);
         if (idx == -1)
            idx = sortedparams.IndexOfKey(parameterName);
         return idx;

To detect parameter changes, in FbParameter:
		public override string ParameterName
		{
			get { return this.parameterName; }
			set { 
             this.parameterName = value;
             if (this.parent != null)
                this.Parent.RebuildSorted();
         }
		}

And define RebuildSorted in the FbParameterCollection
      internal void RebuildSorted()
      {
         sortedparams.Clear();
         sortedparamsint.Clear();
         for (int idx = 0;idx <parameters.Count;idx++)
         {
            sortedparams.Add(parameters[idx].ParameterName,idx);
            sortedparamsint.Add(parameters[idx].InternalParameterName,idx);
         }
      }



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira