Exemplo n.º 1
0
 public void PqsqlCommandTest17()
 {
     using (PqsqlCommand cmd =
                new PqsqlCommand("select /*state*/'x' from /* xxx */ pg_stat_activity ", mConnection)
     {
         CommandType = CommandType.Text
     })
     {
         PqsqlDataReader r = cmd.ExecuteReader();
         while (r.Read())
         {
             string s = !r.IsDBNull(0) ? r.GetString(0) : null;
         }
     }
 }
Exemplo n.º 2
0
 public void PqsqlCommandTest19()
 {
     using (PqsqlCommand cmd =
                new PqsqlCommand("select state from pg_stat_activity -- no semicolon", mConnection)
     {
         CommandType = CommandType.Text
     })
     {
         PqsqlDataReader r = cmd.ExecuteReader();
         while (r.Read())
         {
             string s = !r.IsDBNull(0) ? r.GetString(0) : null;
         }
     }
 }
Exemplo n.º 3
0
        public void PqsqlDataReaderTest11()
        {
            mCmd.CommandText = @"select null, ''::varchar, '你', '好'::text, '吗'::char(1), 'x'::""char"" ;";

            using (PqsqlDataReader reader = mCmd.ExecuteReader())
            {
                Assert.IsTrue(reader.HasRows);

                reader.Read();

                char   c1 = reader.GetChar(1);
                string s1 = reader.GetString(1);
                char   c2 = reader.GetChar(2);
                string s2 = reader.GetString(2);
                char   c3 = reader.GetChar(3);
                string s3 = reader.GetString(3);
                char   c4 = reader.GetChar(4);
                string s4 = reader.GetString(4);
                char   c5 = reader.GetChar(5);
                string s5 = reader.GetString(5);

                Assert.IsTrue(reader.IsDBNull(0));
                Assert.AreEqual(default(char), c1);
                Assert.AreEqual(string.Empty, s1);
                Assert.AreEqual('你', c2);
                Assert.AreEqual("你", s2);
                Assert.AreEqual('好', c3);
                Assert.AreEqual("好", s3);
                Assert.AreEqual('吗', c4);
                Assert.AreEqual("吗", s4);
                Assert.AreEqual('x', c5);
                Assert.AreEqual("x", s5);

                reader.Close();
            }
        }
Exemplo n.º 4
0
        public void PqsqlCommandTest11()
        {
            PqsqlCommand cmd = new PqsqlCommand("select :p1;", mConnection);

            cmd.CommandType = CommandType.Text;

            // recursive parameters
            PqsqlParameter p1 = cmd.Parameters.AddWithValue(":p1", ":p1");

            PqsqlDataReader r = cmd.ExecuteReader();

            while (r.Read())
            {
                string s = r.GetString(0);
                Assert.AreEqual(p1.Value, s);
            }
        }
Exemplo n.º 5
0
        public void PqsqlTypeRegistryTest3()
        {
            mCmd.CommandText = "select 'YES'::information_schema.yes_or_no;";
            mCmd.CommandType = CommandType.Text;

            PqsqlDataReader reader = mCmd.ExecuteReader();

            bool read = reader.Read();

            Assert.IsTrue(read);

            string yes = reader.GetString(0);

            Assert.AreEqual("YES", yes);

            read = reader.Read();
            Assert.IsFalse(read);
        }
Exemplo n.º 6
0
        public void PqsqlCopyFromTest2()
        {
            PqsqlTransaction tran = mConnection.BeginTransaction();

            mCmd.Transaction = tran;

            mCmd.CommandText    = "CREATE TEMP TABLE temp (id int4, val int4, txt text)";
            mCmd.CommandTimeout = 200;
            mCmd.CommandType    = CommandType.Text;

            const int    upperbound = 2000000;
            const string text       = "text value with ü and ä ";

            int q = mCmd.ExecuteNonQuery();

            Assert.AreEqual(0, q);

            PqsqlCopyFrom copy = new PqsqlCopyFrom(mConnection)
            {
                Table       = "temp",
                CopyTimeout = 30
            };

            copy.Start();

            for (int i = 0; i < upperbound; i++)
            {
                int j = copy.WriteInt4(i);
                Assert.AreEqual(4, j);

                j = copy.WriteInt4(i);
                Assert.AreEqual(4, j);

                j = copy.WriteText(text + i);
                Assert.AreEqual(Encoding.UTF8.GetByteCount(text + i), j); // length without nul byte
            }
            copy.WriteNull();                                             // id
            copy.WriteNull();                                             // val
            copy.WriteNull();                                             // txt
            copy.End();
            copy.Close();

            mCmd.CommandText    = "select * from temp";
            mCmd.CommandTimeout = 30;
            mCmd.CommandType    = CommandType.Text;

            PqsqlDataReader r = mCmd.ExecuteReader();

            int n = 0;
            int k = 0;

            while (r.Read())
            {
                if (n++ == upperbound)
                {
                    Assert.IsTrue(r.IsDBNull(0));
                    Assert.IsTrue(r.IsDBNull(1));
                    Assert.IsTrue(r.IsDBNull(2));
                }
                else
                {
                    Assert.AreEqual(k, r.GetInt32(0));
                    Assert.AreEqual(k, r.GetInt32(1));
                    Assert.AreEqual(text + k, r.GetString(2));
                    k++;
                }
            }

            Assert.AreEqual(upperbound + 1, n);

            r.Close();

            tran.Rollback();
        }
Exemplo n.º 7
0
        public void PqsqlDataReaderTest6()
        {
            const string qs = @"select ARRAY[0,1,2,3,42,null,4711]::_text, :p7, unnest(:p6), unnest(:p8) ;
								select interval '20 days', interval '123 secs', interval '20 years 10 months', now(), :p4, timestamp 'infinity', timestamp '-infinity', date 'infinity', date '-infinity';
								select 123.456::numeric, :p3::text ;
								select 'NaN'::numeric, '-1234567898765432123456789.87654321'::numeric ;
								select generate_series(1,1000000),generate_series(1,1000000);
								select generate_series(1,1000000),generate_series(1,1000000);
								select :p1,:p2::text ;
								select extract(epoch from date_trunc('day',current_date - :p9 ))::integer "                                ;

            const int    p1_val = -1;
            const int    p2_val = 2;
            const int    p3_val = -3;
            const double p4_val = 3.1415925;

            decimal p5_val = decimal.Parse("123456789,87654321");

            int[] p6_arr = { 1, 42, 4711, 0815 };

            const string p7_0 = "1 string mit ü and ä sowie 0";
            const string p7_1 = "2 string mit ü and ä sowie 🐨";
            const string p7_2 = "42 string mit ü and ä sowie е́";
            const string p7_3 = "0815 string mit ü and ä sowie П";

            decimal[] p8_arr = { decimal.MinValue, 1.23M, 12.345M, -123.4567M, decimal.MaxValue };

            const int p9_val = 47;

            mCmd.CommandText    = qs;
            mCmd.CommandTimeout = 20;

            PqsqlParameter p1 = mCmd.CreateParameter();

            p1.ParameterName = "p1";
            p1.Value         = p1_val;
            p1.DbType        = DbType.Int32;

            PqsqlParameter p2 = mCmd.CreateParameter();

            p2.ParameterName = "p2";
            p2.Value         = p2_val;
            p2.DbType        = DbType.Int32;

            PqsqlParameter p3 = mCmd.CreateParameter();

            p3.ParameterName = "p3";
            p3.Value         = p3_val;
            p3.DbType        = DbType.Int32;

            PqsqlParameter p4 = mCmd.CreateParameter();

            p4.ParameterName = "p4";
            p4.Value         = p4_val;
            p4.DbType        = DbType.Double;

            PqsqlParameter p5 = mCmd.CreateParameter();

            p5.ParameterName = "p5";
            p5.Value         = p5_val;
            p5.DbType        = DbType.Decimal;

            PqsqlParameter p6 = mCmd.CreateParameter();

            p6.ParameterName = "p6";
            p6.Value         = p6_arr;
            p6.PqsqlDbType   = PqsqlDbType.Array | PqsqlDbType.Int4;

            PqsqlParameter p7 = mCmd.CreateParameter();

            p7.ParameterName = "p7";
            Array b = Array.CreateInstance(typeof(string), new int[] { 4 }, new int[] { -1 });

            b.SetValue(p7_0, -1);
            b.SetValue(p7_1, 0);
            b.SetValue(p7_2, 1);
            b.SetValue(p7_3, 2);
            p7.Value       = b;
            p7.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Text;

            PqsqlParameter p8 = mCmd.CreateParameter();

            p8.ParameterName = "p8";
            Array c = Array.CreateInstance(typeof(decimal), new int[] { 5 }, new int[] { -2 });

            c.SetValue(p8_arr[0], -2);
            c.SetValue(p8_arr[1], -1);
            c.SetValue(p8_arr[2], 0);
            c.SetValue(p8_arr[3], 1);
            c.SetValue(p8_arr[4], 2);
            p8.Value       = c;
            p8.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Numeric;

            PqsqlParameter p9 = mCmd.CreateParameter();

            p9.ParameterName = "p9";
            p9.Value         = p9_val;
            // let Pqsql guess the parameter type

            mCmd.Parameters.Add(p1);
            mCmd.Parameters.Add(p2);
            mCmd.Parameters.Add(p3);
            mCmd.Parameters.Add(p4);
            mCmd.Parameters.Add(p5);
            mCmd.Parameters.Add(p6);
            mCmd.Parameters.Add(p7);
            mCmd.Parameters.Add(p8);
            mCmd.Parameters.Add(p9);

            PqsqlTransaction t = mConnection.BeginTransaction();

            mCmd.Transaction = t;

            PqsqlDataReader r = mCmd.ExecuteReader();

            int p6_rt = 0;

            //int p8_rt = 0;

            // select ARRAY[0,1,2,3,42,null,4711]::_text, :p7, unnest(:p6), unnest(:p8)
            while (r.Read())
            {
                object o0 = r.GetValue(0);
                object o1 = r.GetValue(1);
                object o2 = r.GetValue(2);
                object o3 = r.GetValue(3);

                Array arr = (Array)o0;

                Assert.AreEqual("0", arr.GetValue(1));
                Assert.AreEqual("1", arr.GetValue(2));
                Assert.AreEqual("2", arr.GetValue(3));
                Assert.AreEqual("3", arr.GetValue(4));
                Assert.AreEqual("42", arr.GetValue(5));
                Assert.AreEqual(null, arr.GetValue(6));
                Assert.AreEqual("4711", arr.GetValue(7));

                arr = (Array)o1;

                Assert.AreEqual(p7_0, arr.GetValue(1));
                Assert.AreEqual(p7_1, arr.GetValue(2));
                Assert.AreEqual(p7_2, arr.GetValue(3));
                Assert.AreEqual(p7_3, arr.GetValue(4));

                Assert.AreEqual(p6_arr[p6_rt++ % 4], o2);

                // TODO decimal vs. double: decimal.MinValue cast to double does not work here
                //Assert.AreEqual(p8_arr[p8_rt++], o3);
            }

            bool next = r.NextResult();

            Assert.IsTrue(next);

            // select interval '20 days', interval '123 secs', interval '20 years 10 months', now(), :p4, timestamp 'infinity', timestamp '-infinity', date 'infinity', date '-infinity'
            while (r.Read())
            {
                TimeSpan s = r.GetTimeSpan(0);
                Assert.AreEqual(TimeSpan.FromDays(20), s);

                s = r.GetTimeSpan(1);
                Assert.AreEqual(TimeSpan.FromSeconds(123), s);

                s = r.GetTimeSpan(2);
                Assert.AreEqual(TimeSpan.FromDays(7609), s);

                DateTime d    = r.GetDateTime(3);
                TimeSpan diff = DateTime.UtcNow - d;
                Assert.IsTrue(diff < TimeSpan.FromHours(1));
                Assert.IsTrue(-diff < TimeSpan.FromHours(1));

                double db = r.GetDouble(4);
                Assert.AreEqual(p4_val, db);

                d = r.GetDateTime(5);
                Assert.AreEqual(DateTime.MaxValue, d);

                d = r.GetDateTime(6);
                Assert.AreEqual(DateTime.MinValue, d);

                d = r.GetDateTime(7);
                Assert.AreEqual(DateTime.MaxValue, d);

                d = r.GetDateTime(8);
                Assert.AreEqual(DateTime.MinValue, d);
            }

            next = r.NextResult();
            Assert.IsTrue(next);

            // select 123.456::numeric, :p3::text
            while (r.Read())
            {
                decimal dec = r.GetDecimal(0);
                Assert.AreEqual(123.456M, dec);

                string str = r.GetString(1);
                Assert.AreEqual(p3_val.ToString(), str);
            }

            t.Commit();

            next = r.NextResult();
            Assert.IsTrue(next);

            // select 'NaN'::numeric, '-1234567898765432123456789.87654321'::numeric
            while (r.Read())
            {
                double dou = r.GetDouble(0);
                Assert.AreEqual(Double.NaN, dou);

                dou = r.GetDouble(1);
                Assert.AreEqual(decimal.ToDouble(-1234567898765432123456789.87654321M), dou);
            }

            next = r.NextResult();
            Assert.IsTrue(next);

            int comp = 1;

            // select generate_series(1,1000000),generate_series(1,1000000)
            while (r.Read())
            {
                int gen = r.GetInt32(0);
                Assert.AreEqual(comp++, gen);
            }

            next = r.NextResult();
            Assert.IsTrue(next);

            comp = 1;

            // select generate_series(1,1000000),generate_series(1,1000000)
            while (r.Read())
            {
                int gen = r.GetInt32(0);
                Assert.AreEqual(comp++, gen);
            }

            Assert.AreEqual(1000001, comp);

            next = r.NextResult();
            Assert.IsTrue(next);

            comp = 0;

            // select :p1,:p2::text
            while (r.Read())
            {
                int    i  = r.GetInt32(0);
                string st = r.GetString(1);

                Assert.AreEqual(p1_val, i);
                string p2s = Convert.ToString(p2_val);
                Assert.AreEqual(p2s, st);
                comp++;
            }

            Assert.AreEqual(1, comp);

            next = r.NextResult();
            Assert.IsTrue(next);

            comp = 0;

            // select extract(epoch from date_trunc('day',current_date - :p9 ))::integer
            while (r.Read())
            {
                int i = r.GetInt32(0);

                int unix = (int)DateTime.UtcNow.Subtract(new TimeSpan(p9_val, 0, 0, 0)).Date.Subtract(new DateTime(1970, 1, 1)).TotalSeconds;

                Assert.AreEqual(unix, i);
                comp++;
            }

            next = r.NextResult();
            Assert.IsFalse(next);

            r.Close();
            r.Dispose();
        }