public void TestInsertArrayColumnParam() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("INSERT INTO array_test (date,x, arr)values ('2017-01-01',1,@p)"); cmd.AddParameter("p", new[] { "*****@*****.**", "awdasdas" }); cmd.ExecuteNonQuery(); } }
public void SelectDecimal() { using (var cnn = ConnectionHandler.GetConnection()) using (var cmd = cnn.CreateCommand("SELECT date,dec1,dec2,dec3 FROM decimal_test")) { using (var reader = cmd.ExecuteReader()) { PrintData(reader); } } }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_99_neg_num").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_99_neg_num (FixedDate Date, Amount1 Decimal(16,4), Amount2 Decimal(20,8), Amount3 Decimal(22,10) ) ENGINE = MergeTree PARTITION BY toYYYYMM(FixedDate) ORDER BY FixedDate SETTINGS index_granularity = 8192").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void TestBadNullableType() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("select 1,1,toNullable(max(datetime)), dumpColumnStructure(toNullable(max(datetime))) from default.nullable_date_time"); using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { Assert.True(r.IsDBNull(2)); }); } } }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_87_lowcardinality").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_87_lowcardinality (a LowCardinality(String)) ENGINE = Memory").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_100_arr_first").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_100_arr_first (d Date, a Array(String) ) ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d SETTINGS index_granularity = 8192").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_119_stringescaping").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_119_stringescaping (a String) ENGINE = Memory").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_decimal (k Date, d Decimal(20,4)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_dt64").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_dt64 (k Date, dt64 DateTime64(5), dt64tz DateTime64(5,'Europe/Moscow')) ENGINE = MergeTree(k, (dt64,dt64tz), 8192)").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void TestConnectionAutoClose() { using (var cnn = ConnectionHandler.GetConnection()) { using (var cmd = cnn.CreateCommand("SELECT * FROM system.databases")) using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { reader.ReadAll(r => { }); } } }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_nfs").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_nfs (k Date, a Int32, nfs Nullable(FixedString(2))) ENGINE = MergeTree(k, (a), 8192)").ExecuteNonQuery(); } Thread.Sleep(1000); }
public void TestInsertBulk() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_nfs (k, nfs) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] { new object[] { DateTime.Now, "aa" }, new object[] { DateTime.Now, null } }) .ExecuteNonQuery(); } }
public void SelectIn() { using (var cnn = ConnectionHandler.GetConnection()) using (var cmd = cnn.CreateCommand("SELECT * FROM `test_data` WHERE user_id IN (@values)")) { cmd.Parameters.Add("values", DbType.UInt64, new[] { 1L, 2L, 3L }); using (var reader = cmd.ExecuteReader()) { PrintData(reader); } } }
public void DecimalParam() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)"); cmd.AddParameter("d", DbType.Decimal, 666m); cmd.ExecuteNonQuery(); cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)"); cmd.AddParameter("d", DbType.Decimal, -666m); cmd.ExecuteNonQuery(); } }
public void TestSsl() { using (var cnn = ConnectionHandler.GetConnection("Compress=False;BufferSize=32768;SocketTimeout=10000;CheckCompressedHash=False;Encrypt=True;Compressor=lz4;Host=ch-test.flippingbook.com;Port=9440;Database=default;User=andreya;Password=123")) { var cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)"); cmd.AddParameter("d", DbType.Decimal, 666m); cmd.ExecuteNonQuery(); cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)"); cmd.AddParameter("d", DbType.Decimal, -666m); cmd.ExecuteNonQuery(); } }
public void TestSelect() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_dt64 (k, dt64, dt64tz) VALUES (@k,@p,@p)").AddParameter("k", DbType.Date, new DateTime(2000, 01, 02)) .AddParameter("p", DbType.DateTime, new DateTime(2000, 01, 02, 01, 01, 01, 10)).ExecuteNonQuery(); var values = new List <Tuple <DateTime, DateTime, DateTime> >(); using (var cmd = cnn.CreateCommand("SELECT k, dt64, dt64tz FROM test_dt64 WHERE k=@k").AddParameter("k", DbType.Date, new DateTime(2000, 01, 02))) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDateTime(1), r.GetDateTime(2))); }); } } }
public void TestRoundtripLiteral() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_98_dt64 (k, dt64, dt64tz) VALUES ('2020-01-02','2020-01-01 00:00:00','2020-01-01 00:00:00')").ExecuteNonQuery(); DateTime noOffset = DateTime.Now, offset = DateTime.Now; cnn.CreateCommand("SELECT dt64, dt64tz FROM test_98_dt64 WHERE k='2020-01-02'").ExecuteReader().ReadAll(r => { noOffset = r.GetDateTime(0); offset = r.GetDateTime(1); }); Assert.AreEqual(noOffset.ToString("O"), "2020-01-01T00:00:00.0000000Z"); Assert.AreEqual(offset.ToString("O"), "2020-01-01T00:00:00.0000000Z"); } }
public void TestInsertArrayColumnBulk() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("INSERT INTO default.`super+` (date,email)values @bulk;"); cmd.Parameters.Add( new ClickHouseParameter { DbType = DbType.Object, ParameterName = "bulk", Value = new[] { new object[] { DateTime.Now, "*****@*****.**" }, new object[] { DateTime.Now.AddHours(-1), "" } } } ); cmd.ExecuteNonQuery(); } }
public void TestRoudtrip() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_decimal (k, d) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] { new object[] { DateTime.Now, 387m }, new object[] { DateTime.Now, 0m } }) .ExecuteNonQuery(); var values = new List <Tuple <DateTime, decimal> >(); using (var cmd = cnn.CreateCommand("SELECT k, d FROM test_decimal ORDER BY d")) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDecimal(1))); }); } Assert.AreEqual(0, (double)values[0].Item2, .33); Assert.AreEqual(387, (double)values[1].Item2, .33); } }
public void TestDecimal128() { using (var cnn = ConnectionHandler.GetConnection()) { decimal d = 12345678901234567890123456789.1234567890m; var values = new List <decimal>(); using (var cmd = cnn.CreateCommand("select toDecimal128('" + d + "',9) as f, toTypeName(f);")) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(r.GetDecimal(0)); }); } Assert.AreEqual(d, values[0]); } }
public void CreateDatabase() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS guid_test").ExecuteNonQuery(); cnn.CreateCommand( @" CREATE TABLE guid_test ( guid UUID, dt DateTime) ENGINE=MergeTree() PARTITION BY toYYYYMM(dt) ORDER BY (dt, guid)" ).ExecuteNonQuery(); } Thread.Sleep(1000); }
public void TestDateTimeInViewsV20() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("select * from test_dt20_view"); using (var reader = cmd.ExecuteReader()) { reader.ReadAll( r => { var date = r.GetDateTime(0); var dateTime = r.GetDateTime(1); var val = r.GetString(2); Console.WriteLine($"{date} {dateTime} {val}"); } ); } } }
public void TestInsertAfterInsert() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("INSERT INTO test (date,val)values @bulk;"); cmd.Parameters.Add( new ClickHouseParameter { DbType = DbType.Object, ParameterName = "bulk", Value = new[] { new object[] { DateTime.Now, 1 }, new object[] { DateTime.Now.AddHours(-1), 2 } } } ); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = new[] { new object[] { DateTime.Now.AddHours(-2), 3 }, new object[] { DateTime.Now.AddHours(-3), 4 } }; cmd.ExecuteNonQuery(); } }
public void TestInsertNestedColumnBulk() { using (var cnn = ConnectionHandler.GetConnection()) { var cmd = cnn.CreateCommand("INSERT INTO nest_test (date,x, values.name,values.value)values @bulk;"); cmd.Parameters.Add( new ClickHouseParameter { DbType = DbType.Object, ParameterName = "bulk", Value = new[] { new object[] { DateTime.Now, 1, new[] { "*****@*****.**", "awdasdas" }, new[] { "dsdsds", "dsfdsds" } }, new object[] { DateTime.Now.AddHours(-1), 2, new string[0], new string[0] } } } ); cmd.ExecuteNonQuery(); } }
public void TestRoundtripParameter() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_98_dt64 (k, dt64, dt64tz) VALUES ('2020-01-03',@d1,@d2)") .AddParameter("d1", DbType.DateTime, new DateTime(2020, 01, 01, 0, 0, 0, DateTimeKind.Utc)) .AddParameter("d2", DbType.DateTime, new DateTime(2020, 01, 01, 0, 0, 0, DateTimeKind.Utc)) .ExecuteNonQuery(); DateTime noOffset = DateTime.Now, offset = DateTime.Now; cnn.CreateCommand("SELECT dt64, dt64tz FROM test_98_dt64 WHERE k='2020-01-03'").ExecuteReader().ReadAll(r => { noOffset = r.GetDateTime(0); offset = r.GetDateTime(1); }); Assert.AreEqual(noOffset.ToString("O"), "2020-01-01T00:00:00.0000000Z"); Assert.AreEqual(offset.ToString("O"), "2020-01-01T00:00:00.0000000Z"); } }
public void CreateStructures() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_decimal (k Date, d Decimal(20,4)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery(); cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal_big").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_decimal_big (k Date, d Decimal(38,16)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery(); cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal_big_double").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_decimal_big_double (k Date, d Decimal(38,4)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery(); if (cnn.ServerInfo.Build > BuildWithDecimal256Support) { cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal_super_big").ExecuteNonQuery(); cnn.CreateCommand("CREATE TABLE test_decimal_super_big (k Date, d Decimal(70,50)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery(); } } Thread.Sleep(1000); }
public void TestRoundtrip() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_99_neg_num (FixedDate,Amount1,Amount2,Amount3) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] { new object[] { DateTime.Now, -100m, -100m, -100m } }) .ExecuteNonQuery(); var values = new List <Tuple <DateTime, decimal, decimal, decimal> >(); using (var cmd = cnn.CreateCommand("SELECT FixedDate,Amount1,Amount2,Amount3 FROM test_99_neg_num ORDER BY FixedDate")) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDecimal(1), r.GetDecimal(2), r.GetDecimal(3))); }); } Assert.AreEqual(-100, (double)values[0].Item2, .33); Assert.AreEqual(-100, (double)values[0].Item3, .33); Assert.AreEqual(-100, (double)values[0].Item4, .33); } }
public void Test() { using (var cnn = ConnectionHandler.GetConnection()) { var items = new List <string>(); for (var i = 0; i < 1000; i++) { items.Add(((char)('A' + (i % 20))).ToString()); } var result = cnn.CreateCommand("INSERT INTO test_87_lowcardinality (a) VALUES @bulk").AddParameter("bulk", DbType.Object, items.Select(x => (object)new object[] { x }).ToArray()) .ExecuteNonQuery(); var values = new List <string>(); using (var cmd = cnn.CreateCommand("SELECT a FROM test_87_lowcardinality")) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(r.GetString(0)); }); } Assert.IsTrue(items.SequenceEqual(values)); } }
public void TestRoundtrip([Values(-100, -40.96, 40.96, 100, -1234567890.1234, 1234567890.1234)] decimal value) { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_99_neg_num (FixedDate,Amount1,Amount2,Amount3) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] { new object[] { DateTime.Now, value, value, value } }) .ExecuteNonQuery(); var values = new List <Tuple <DateTime, decimal, decimal, decimal> >(); using (var cmd = cnn.CreateCommand("SELECT FixedDate,Amount1,Amount2,Amount3 FROM test_99_neg_num ORDER BY FixedDate")) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDecimal(1), r.GetDecimal(2), r.GetDecimal(3))); }); } var row = values.FirstOrDefault(x => decimal.Equals(x.Item2, value)); Assert.IsNotNull(row); Assert.AreEqual(value, row.Item2); Assert.AreEqual(value, row.Item3); Assert.AreEqual(value, row.Item4); } }
public void TestRoundtrip() { using (var cnn = ConnectionHandler.GetConnection()) { cnn.CreateCommand("INSERT INTO test_100_arr_first (a,d) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] { new object[] { new string[] { "a", "b" }, DateTime.Now } }) .ExecuteNonQuery(); var values = new List <Tuple <DateTime, string[]> >(); using (var cmd = cnn.CreateCommand("SELECT d,a FROM test_100_arr_first ORDER BY d")) using (var reader = cmd.ExecuteReader()) { reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), ((object[])r.GetValue(1)).OfType <string>().ToArray())); }); } Assert.Greater(values.Count, 0); Assert.AreEqual("a", values[0].Item2[0]); Assert.AreEqual("b", values[0].Item2[1]); } }