[Zope-DB] DCOracle2 and multiple SQl statements

Smith, Neil Neil.Smith@npower.com
Wed, 30 Jan 2002 15:49:24 -0000


Matthew T. Kromer  wrote:

>Thats what is supposed to happen, yes.  Unless you call something that 
>explicitly does a commit() there shouldn't be any problem with issuing 
>multiple statements in the same transaction.

I don't do any commits.  Maybe it is the way that things are being called.

I have a dtml-method that displays a form and it calls the sql method like
this:

    <dtml-if expr="submit=='Update'">
        <dtml-try>
            <dtml-call "MasterDetailUpdateMethod(REQUEST)">
            Your data was updated!
            <dtml-var back_button>
        <dtml-except DatabaseError>
            ...error handling for recognised problems
        </dtml-try>
    </dtml-if>

And the SQL method MasterDetailUpdateMethod is like this:

ARGUMENTS:
    MASTER_ID
    MASTER_FIELD_1
    MASTER_FIELD_2
    ...
    DETAIL_FIELD_1:list=""

CODE:

  update MasterTable set 
    master_field_1=<dtml-sqlvar MASTER_FIELD_1 type=string >,
    master_field_2=<dtml-sqlvar MASTER_FIELD_2 type=string >
  where master_id=<dtml-sqlvar MASTER_ID type=int> 

  <dtml-var sql_delimiter>

  delete from DetailTable
  where master_id = <dtml-sqlvar MASTER_ID type=int>

  <dtml-if DETAIL_FIELD_1>
    <dtml-in DETAIL_FIELD_1>
      <dtml-if "_['sequence-item']">

        <dtml-var sql_delimiter>

        insert into DetailTable (
          master_id,
          child_field_1
        ) values (
          <dtml-sqlvar MASTER_ID type=int>,
          <dtml-var "_['sequence-item']">
        )

      </dtml-if>
    </dtml-in>
  </dtml-if>

There is a unique key on the detail table, and if I pass it duplicates then
I end up with the updates to the master table being commited, and the
deletes of the detail, and whatever inserts up to the point of the
duplicate.  It doesn't roll back anything.

I have the Oracle 8i client libraries installed so I can use the DCOracle2
binary release, but the database is 7.3.4.  Would that affect anything?

Neil Smith

**********************************************************************
 
The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify postmaster@npower.com (UK 01384 275454) and delete it immediately from your system.
    
Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.

Npower Limited
Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277

This e-mail may be sent on behalf of a member of the Innogy plc group of companies.

**********************************************************************