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
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
