Rhino ETL - BranchingOperation, Oracle and Sequences

[Alvaro Alberto Silva Lopes Filho]

Hi, this is my situation.

            Register(new MergeSourceDestination()
                         .Left(new GetSourceRows())
                         .Right(new GetDestinationRows())
                );

            Register(new BranchingOperation()
                         .Add(new SaveAtDestinationDataBase())
                         .Add(new SaveAtFromToTableInDestinationDataBase()));

The Source came from another oracle instance.
and i need to save new rows in the Destination DB in two diferents tables, one is the destination table and another that is my From/To table that will tell me the equivalent ID of a row in the source table.

But I have a Sequence that give me the ID that i have to persist in destination table. so I need to use that value in this two inserts.

Does anyone knows a manner to retrieve a sequence value and use it in this two operations ?

[Simone Busoli]

Hi Alvaro,

the scenario is not completely clear. You mean that in SaveAtFromToTableInDestinationDataBase you need data that is generated in  SaveAtDestinationDataBase? Can you clarify?

Simone

--
You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.
To view this discussion on the web visit https://groups.google.com/d/msg/rhino-tools-dev/-/zhrZEq3YH1UJ.
To post to this group, send email to rhino-t...@googlegroups.com.
To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.

[Alvaro Alberto Silva Lopes Filho]

Hi, Simome. Thank's for reply.

"the scenario is not completely clear. You mean that in SaveAtFromToTableInDestinationDataBase you need data that is generated in  SaveAtDestinationDataBase? Can you clarify?"

Not Exactly, i just need that each row have a column like row["ID"] that is a value of my sequence, so i can use this ID in this two operations.

SaveAtDestinationDataBase and SaveAtFromToTableInDestinationDataBase

I just cant find a way to retrieve sequence.nextval for each row before call the BranchingOperation()

[Simone Busoli]

What about this?

            Register(new MergeSourceDestination()
                         .Left(new GetSourceRows())
                         .Right(new GetDestinationRows())
                );

          -->> Register(new AddSequenceToEachRow()); <<--

            Register(new BranchingOperation()
                         .Add(new SaveAtDestinationDataBase())
                         .Add(new SaveAtFromToTableInDestinationDataBase())); 

--
You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.

To view this discussion on the web visit https://groups.google.com/d/msg/rhino-tools-dev/-/3-gPpNkh9zoJ.

[Alvaro Alberto Silva Lopes Filho]

Yes, but what kind of operation I can use to proceed an input query from data base and manage Rows to add new column row["ID"] recieving a value from query ?

like this
cmd = "Select MySequence.NEXTVAL from DUAL"

execute this and set

row["ID"] = reader["ID"];

I try to use AbstractCommandOperation. but i had problems with connections closed.

this is how a tried to do...

    public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
        {
            using (Connection)
            {
                using (Command)
                {
                    foreach (var row in rows)
                    {
                        Command.CommandText = string.Format("SELECT Sequence.NEXTVAL as ID FROM DUAL");

                        using (Reader)
                        {
                            while (Reader.Read())
                            {
                                row["ID"] = reader["ID"];
                                yield return row;
                            }
                        }
                    }
                }
            }
        }

Thank's again

[Simone Busoli]

This doesn't have much to do with the project actually, at a quick look the code looks like you're not instantiating as many commands and readers as you would need, but you may want to get a batch of ids from the DB perhaps rather than one at a time.

--
You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.

To view this discussion on the web visit https://groups.google.com/d/msg/rhino-tools-dev/-/8xyl6E6yZ3AJ.

[Alvaro Alberto Silva Lopes Filho]

Removing the "using" from the code make it works.

        public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
        {

            using (IDbConnection connection = Use.Connection(ConnectionStringSettings))
            using (IDbTransaction transaction = BeginTransaction(connection))
            {
                using (currentCommand = connection.CreateCommand())

                {
                    foreach (var row in rows)
                    {

                        currentCommand.CommandText = "SELECT Sequence.NEXTVAL as CODIGO FROM DUAL";
                        using (IDataReader reader = currentCommand.ExecuteReader())
                        {
                            if (reader.Read())

                            {
                                row["ID"] = reader["ID"];
                                yield return row;
                            }
                        }
                    }
                }

                if (transaction != null) transaction.Commit();
            }
        }

[Simone Busoli]

Yes, knowing the language usually helps :)