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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider