Header & Details

[nicolasfrench]

Hi everybody.

I need help. I got the following tables: Teams, Players

When I create a new team, have to define the players too. How I make the transaction to make sure that the fields just been copied to the DB only if that transaction is complete ?

Sorry about my english.

Nicolas French
Argentina

 

[TomBascom]

This would work:

create team.
assign
  team.teamId = 1
  team.teamName = "Red Sox"
.
create player.
assign
  player.teamId = team.teamId
  player.playerId = 1
  player.playerName = "David Ortiz"
.
create player.
assign
  player.teamId = team.teamId
  player.playerId = 2
  player.playerName = "Manny Ramirez"
.

However it is a "naked" and implicit transaction scoped to whatever block contains it (which might be the implied "procedure" block) and that really isn't a good thing. Plus it hard codes all of the values which probably isn't terribly realistic

Better:

do for team, player transaction:

  create team.
  assign
    team.teamId = 1
    team.teamName = "Red Sox"
  .
  create player.
  assign
    player.teamId = team.teamId
    player.playerId = 1
    player.playerName = "David Ortiz"
  .
  create player.
  assign
    player.teamId = team.teamId
    player.playerId = 2
    player.playerName = "Manny Ramirez"
  .
end.

This explicitly declares a transaction and confines the scope of the team and player records to the DO block. But it still hard codes the values. And the team creation and player creation are in the same transaction. Personally I would prefer to split those apart -- after all a team can be established with no players and players will need to be added without the creation of a team.

Which leads to:

define variable teamNum as integer no-undo.

do for team transaction:

  teamNum = next-value( "teamSeq" ).

  create team.
  assign
    team.teamId = teamNum
    team.teamName = "Red Sox"
  .
end.

do for player transaction:

  create player.
  assign
    player.teamId = teamNum
    player.playerId = 1
    player.playerName = "David Ortiz"
  .
  create player.
  assign
    player.teamId = teamNum
    player.playerId = 2
    player.playerName = "Manny Ramirez"
  .
end.

Notice that you cannot use references to the team record in the second DO block (because it is "strong scoped"). Thus we need the teamNum variable which, in this case, is getting it's value from a db sequence. But that's just one way to do that bit - there are lots of others.

Ultimately you'd probably actually turn both blocks into procedures sort of like this one:

procedure createPlayer do:

  define parameter tNum as integer no-undo.
  define parameter pNum as integer no-undo.
  define parameter pName as character no-undo.

  define buffer player for player.

  do for player transaction:

    create player.
    assign
      player.teamId = tNum
      player.playerId = pNum
      player.playerName = pName
    .

  end.

  return.

end.

The "define buffer player for player." looks odd but it's a very useful trick that prevents the player record's scope from being "borrowed" by the containing procedure block.

This procedure would then perhaps be bundled into a persistent super-procedure or maybe be run on an app-server... :awink:

In reality the code to do this is going to depend greatly on the method that you are using to populate the data fields -- if you're importing from a file some variation on the above is fairly straight-forward. If there is a user and a UI things get more complex; it will be very tempting (and simple) to enter data directly into the db fields with a transaction active. But that would probably be a costly mistake -- the UI layer, the business logic and the persistence layer should all be discrete. But that's a topic for another post

 

[nicolasfrench]

This would work:

create team.
assign
  team.teamId = 1
  team.teamName = "Red Sox"
.
create player.
assign
  player.teamId = team.teamId
  player.playerId = 1
  player.playerName = "David Ortiz"
.
create player.
assign
  player.teamId = team.teamId
  player.playerId = 2
  player.playerName = "Manny Ramirez"
.

However it is a "naked" and implicit transaction scoped to whatever block contains it (which might be the implied "procedure" block) and that really isn't a good thing. Plus it hard codes all of the values which probably isn't terribly realistic

Better:

do for team, player transaction:
 
  create team.
  assign
    team.teamId = 1
    team.teamName = "Red Sox"
  .
  create player.
  assign
    player.teamId = team.teamId
    player.playerId = 1
    player.playerName = "David Ortiz"
  .
  create player.
  assign
    player.teamId = team.teamId
    player.playerId = 2
    player.playerName = "Manny Ramirez"
  .
end.

This explicitly declares a transaction and confines the scope of the team and player records to the DO block. But it still hard codes the values. And the team creation and player creation are in the same transaction. Personally I would prefer to split those apart -- after all a team can be established with no players and players will need to be added without the creation of a team.

Which leads to:

define variable teamNum as integer no-undo.
 
do for team transaction:
 
  teamNum = next-value( "teamSeq" ).
 
  create team.
  assign
    team.teamId = teamNum
    team.teamName = "Red Sox"
  .
end.
 
do for player transaction:
 
  create player.
  assign
    player.teamId = teamNum
    player.playerId = 1
    player.playerName = "David Ortiz"
  .
  create player.
  assign
    player.teamId = teamNum
    player.playerId = 2
    player.playerName = "Manny Ramirez"
  .
end.

Notice that you cannot use references to the team record in the second DO block (because it is "strong scoped"). Thus we need the teamNum variable which, in this case, is getting it's value from a db sequence. But that's just one way to do that bit - there are lots of others.

Ultimately you'd probably actually turn both blocks into procedures sort of like this one:

procedure createPlayer do:
 
  define parameter tNum as integer no-undo.
  define parameter pNum as integer no-undo.
  define parameter pName as character no-undo.
 
  define buffer player for player.
 
  do for player transaction:
 
    create player.
    assign
      player.teamId = tNum
      player.playerId = pNum
      player.playerName = pName
    .
 
  end.
 
  return.
 
end.

The "define buffer player for player." looks odd but it's a very useful trick that prevents the player record's scope from being "borrowed" by the containing procedure block.

This procedure would then perhaps be bundled into a persistent super-procedure or maybe be run on an app-server... :awink:

In reality the code to do this is going to depend greatly on the method that you are using to populate the data fields -- if you're importing from a file some variation on the above is fairly straight-forward. If there is a user and a UI things get more complex; it will be very tempting (and simple) to enter data directly into the db fields with a transaction active. But that would probably be a costly mistake -- the UI layer, the business logic and the persistence layer should all be discrete. But that's a topic for another post

Thanks a lot for answering!

I need to do a transaction like the orders and orderlines. Need to save all or nothing.

the teams & players it was an wrong example of the exercise.

 

[TomBascom]

One of the biggest mistakes that people make is to confuse a business transaction and a database transaction. The business transaction may have an "all or nothing" rule but you should be very careful about how you implement that. It is tempting, but wrong, to use a database transaction to enforce that sort of business rule.

For instance it is not a good idea to start a database transaction when the user comes to the "order" screen. This will lead to serious scalability problems (due to contention for record locks) if your application is successful some day. Instead you might build up the order and order-lines in a pair of temp tables (or a ProDataSet) and when it is time to commit you would copy the temp tables to the corresponding db tables (with appropriate error handling of course).

Another common method is to create the order and the order-lines in the database as they are entered (although still using temp-tables or variables -- just not all in a big batch) and then provide a "reversing transaction" to back out the business transaction if need be. This might be more attractive if, for instance, you need to reserve inventory to fill the order.