Menu
▾
▴
firebird-net-provider
|
From: Kjell R. <kje...@da...> - 2013-11-26 13:58:49
|
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
|
|
From: Jiri C. <di...@ci...> - 2013-11-26 14:46:19
|
How the connection string looks like?
--
Jiri {x2} Cincura (x2develop.com founder)
http://blog.cincura.net/ | http://www.ID3renamer.com
|
|
From: Kjell R. <kje...@da...> - 2013-11-26 16:00:51
|
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 |
|
From: Jiri C. <di...@ci...> - 2013-11-26 18:28:52
|
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
|
|
From: Kjell R. <kje...@da...> - 2013-11-26 16:04:54
|
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 |
|
From: Danny G. <da...@ab...> - 2013-11-26 16:38:37
|
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 > |
|
From: Kjell R. <kje...@da...> - 2013-11-27 08:12:14
|
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 |
|
From: Mark R. <ma...@la...> - 2013-11-26 18:28:02
|
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
|
|
From: Mark R. <ma...@la...> - 2013-11-26 19:40:17
|
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 |
|
From: Mark R. <ma...@la...> - 2013-11-26 19:45:20
|
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
|
|
From: Kjell R. <kje...@da...> - 2013-11-27 08:28:26
|
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
|
|
From: Mark R. <ma...@la...> - 2013-12-06 19:38:56
|
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 |
|
From: Kjell R. <kje...@da...> - 2013-11-27 08:09:54
|
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 |
|
From: Jiri C. <di...@ci...> - 2013-11-27 08:11:28
|
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
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X