public void MultipleResultSets(TestCaseResult result)
        {
            DropProcedure();
            ExecuteNonQuery(
                "create procedure bar ()\n" +
                "{\n" +
                "  declare i int;\n" +
                "  declare c char;\n" +
                "  result_names (i);\n" +
                "  result (1);\n" +
                "  result (2);\n" +
                "  end_result ();\n" +
                "  result_names (c);\n" +
                "  result ('a');\n" +
                "  result ('b');\n" +
                "  return 0;\n" +
                "}\n"
                );

            VirtuosoCommand command = connection.CreateCommand();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "bar";

            VirtuosoDataReader reader = null;

            try
            {
                reader = command.ExecuteReader();
                result.FailIfNotEqual(1, reader.FieldCount);
                result.FailIfNotEqual("i", reader.GetName(0).ToLower());
                result.FailIfNotEqual(typeof(int), reader.GetFieldType(0));
                result.FailIfNotEqual(true, reader.Read());
                result.FailIfNotEqual(1, reader["i"]);
                result.FailIfNotEqual(true, reader.Read());
                result.FailIfNotEqual(2, reader["i"]);
                result.FailIfNotEqual(false, reader.Read());
                result.FailIfNotEqual(true, reader.NextResult());
                result.FailIfNotEqual(1, reader.FieldCount);
                result.FailIfNotEqual("c", reader.GetName(0).ToLower());
                result.FailIfNotEqual(typeof(string), reader.GetFieldType(0));
                result.FailIfNotEqual(true, reader.Read());
                result.FailIfNotEqual("a", reader["c"]);
                result.FailIfNotEqual(true, reader.Read());
                result.FailIfNotEqual("b", reader["c"]);
                result.FailIfNotEqual(false, reader.NextResult());
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                command.Dispose();
            }
        }
Beispiel #2
0
        private VirtuosoCommand CollectInfo()
        {
            if (adapter == null)
            {
                throw new InvalidOperationException("The DataAdapter property is not set.");
            }

            VirtuosoCommand selectCommand = (VirtuosoCommand)adapter.SelectCommand;

            if (selectCommand == null)
            {
                throw new InvalidOperationException("The SelectCommand property is not set.");
            }

            VirtuosoConnection connection = (VirtuosoConnection)selectCommand.Connection;

            if (connection == null)
            {
                throw new InvalidOperationException("The Connection property is not set.");
            }

            if (columns != null)
            {
                return(selectCommand);
            }

            bool close = false;

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                close = true;
            }

            VirtuosoDataReader reader = null;

            try
            {
                reader    = (VirtuosoDataReader)selectCommand.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
                tableName = GetTableName(reader.Columns);
                columns   = reader.Columns;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (close)
                {
                    connection.Close();
                }
            }

            return(selectCommand);
        }
        private void DoGetDataTest(TestCaseResult result, string text, int column,
                                   CommandBehavior behavior, Selector selector, Sequence sequence)
        {
            VirtuosoCommand    cmd = null;
            VirtuosoDataReader dr  = null;

            try
            {
                cmd = new VirtuosoCommand(text, connection);
                dr  = cmd.ExecuteReader(behavior);
                CheckGetData(result, dr, column, selector, sequence);
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
        }
        public void ResultSetAndOutputParameters(TestCaseResult result)
        {
            DropProcedure();
            DropProcedure();
            ExecuteNonQuery(
                "create procedure bar (out x integer)\n" +
                "{\n" +
                "  declare i int;\n" +
                "  result_names (i);\n" +
                "  result (1);\n" +
                "  result (2);\n" +
                "  x := 3;\n" +
                "  return 4;\n" +
                "}\n"
                );

            VirtuosoCommand command = connection.CreateCommand();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "bar";

            VirtuosoParameter returnValue = command.CreateParameter();

            returnValue.ParameterName = "ReturnValue";
            returnValue.Direction     = ParameterDirection.ReturnValue;
            returnValue.VirtDbType    = VirtDbType.Integer;
            command.Parameters.Add(returnValue);

            VirtuosoParameter x = command.CreateParameter();

            x.ParameterName = "x";
            x.Direction     = ParameterDirection.Output;
            x.VirtDbType    = VirtDbType.Integer;
            command.Parameters.Add(x);

            VirtuosoDataReader reader = null;
            bool closed = false;

            try
            {
                reader = command.ExecuteReader();
                result.FailIfNotEqual(1, reader.FieldCount);
                result.FailIfNotEqual("i", reader.GetName(0).ToLower());
                result.FailIfNotEqual(typeof(int), reader.GetFieldType(0));
                result.FailIfNotEqual(true, reader.Read());
                result.FailIfNotEqual(1, reader["i"]);
                result.FailIfNotEqual(true, reader.Read());
                result.FailIfNotEqual(2, reader["i"]);
                result.FailIfNotEqual(false, reader.Read());

                reader.Close();
                closed = true;

                result.FailIfNotEqual("Out Parameter", 3, x.Value);
                result.FailIfNotEqual("Return Value", 4, returnValue.Value);
            }
            finally
            {
                if (reader != null && !closed)
                {
                    reader.Close();
                }
                command.Dispose();
            }
        }
Beispiel #5
0
        public static void DeriveParameters(VirtuosoCommand command)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (command.CommandType != CommandType.StoredProcedure)
            {
                throw new InvalidOperationException("DeriveParameters supports only stored procedures.");
            }

            VirtuosoConnection connection = (VirtuosoConnection)command.Connection;

            if (connection == null)
            {
                throw new InvalidOperationException("The Connection property is not set.");
            }
            if (connection.State == ConnectionState.Closed)
            {
                throw new InvalidOperationException("The connection is closed.");
            }

            IInnerCommand      innerCommand = null;
            VirtuosoDataReader reader       = null;

            try
            {
                innerCommand = connection.innerConnection.CreateInnerCommand(null);
                innerCommand.GetProcedureColumns(command.CommandText);
                reader = new VirtuosoDataReader(connection, innerCommand, null, CommandBehavior.Default, false);

                command.Parameters.Clear();
                while (reader.Read())
                {
                    CLI.InOutType iotype = (CLI.InOutType)reader.GetInt16(reader.GetOrdinal("COLUMN_TYPE"));

                    ParameterDirection direction;
                    switch (iotype)
                    {
                    case CLI.InOutType.SQL_PARAM_INPUT:
                        direction = ParameterDirection.Input;
                        break;

                    case CLI.InOutType.SQL_PARAM_OUTPUT:
                        direction = ParameterDirection.Output;
                        break;

                    case CLI.InOutType.SQL_PARAM_INPUT_OUTPUT:
                        direction = ParameterDirection.InputOutput;
                        break;

                    case CLI.InOutType.SQL_PARAM_RETURN_VALUE:
                        direction = ParameterDirection.ReturnValue;
                        break;

                    default:
#if MONO
                        direction = ParameterDirection.Input;
#endif
                        continue;
                    }

                    string name = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
                    if (name == "" && iotype == CLI.InOutType.SQL_PARAM_RETURN_VALUE)
                    {
                        name = "ReturnValue";
                    }

                    CLI.SqlType sqlType = (CLI.SqlType)reader.GetInt16(reader.GetOrdinal("DATA_TYPE"));
                    DataType    type    = DataTypeInfo.MapSqlType(sqlType);
                    if (type == null)
                    {
                        throw new SystemException("Unknown data type");
                    }

                    int sizeOrdinal = reader.GetOrdinal("COLUMN_SIZE");
                    if (sizeOrdinal < 0)
                    {
                        sizeOrdinal = reader.GetOrdinal("PRECISION");
                    }
                    int size = reader.IsDBNull(sizeOrdinal) ? 0 : reader.GetInt32(sizeOrdinal);

                    int scaleOrdinal = reader.GetOrdinal("DECIMAL_DIGITS");
                    if (scaleOrdinal < 0)
                    {
                        scaleOrdinal = reader.GetOrdinal("SCALE");
                    }
                    short scale = reader.IsDBNull(scaleOrdinal) ? (short)0 : reader.GetInt16(scaleOrdinal);

                    int          nullableOrdinal = reader.GetOrdinal("NULLABLE");
                    CLI.Nullable nullable        = (CLI.Nullable)reader.GetInt16(nullableOrdinal);
                    bool         isNullable      = (nullable != CLI.Nullable.SQL_NO_NULLS);

                    VirtuosoParameter parameter = (VirtuosoParameter)command.CreateParameter();
                    parameter.ParameterName = name;
                    parameter.Direction     = direction;
                    parameter.VirtDbType    = type.vdbType;
                    parameter.Size          = type.GetFieldSize(size);
                    parameter.Precision     = type.GetPrecision(size);
                    parameter.Scale         = (byte)scale;
                    parameter.IsNullable    = isNullable;
                    command.Parameters.Add(parameter);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (innerCommand != null)
                {
                    innerCommand.Dispose();
                }
            }
        }