What am I doing wrong!

From: "Wm(dot) G(dot) Urquhart" <wgu(at)wurquhart(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: What am I doing wrong!
Date: 2002-05-17 11:54:42
Message-ID: Pine.LNX.4.44.0205171200050.17126-300000@mailer.wurquhart.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

I've wasted a lot of time doing what I thought was a favour for my client.
I.E. Migrating an existing, working SQL Server Application to PostgreSQL,
but now I'm starting to wish I'd never bothered.

Apart from the odd bought of blundering stupidity my problem is this:

My user uses a PropertySheet Wizard with 4 pages to enter the data
required. The data elicited from the user is then entered into 3 tables
(Parent, Progeny and Vaccinations). In the Progeny and Vaccinations tables
the number of rows entered must be 1 or more.

But this is where my problem begins since this requires an atomic tranaction.

Once the wizard is complete I create an instance of my DBDriver class,
that is simply a wrapper on the ODBC! I then start a transaction by
setting the Connection Handle's SQL_ATTR_AUTOCOMMIT to OFF.

The current isolation level is 'serializable' set in the .conf file. I
then call Page One's ApplyData method to update the database and it, in
return, returns the PK ID for the new row.

If this succeeds (it does) I then pass the ID to Page Two's ApplyData
method. Now, this page does an update on the previously inserted row, well
it would if it worked. This method always fails and as a result the
transaction aborts and is rolled back.

Now as I understand it, since the transaction level is serializable then
the new row is visable and should be updateable. But this appears not to
be since it always fails, and the only reason I can come to is simply
because the UPDATE WHERE fails since it can't 'see' the new row.

Now this code worked as is using SQL Server so I've no reason to doubt
that it shouldn't work using PostgreSQL, I just can't see what or where
I'm going wrong.

I really need to get this resolved as soon as possible, it's doing my head
in, but I don't want to give up on it. If anyone can point me in the right
direction I'd appreciate it. I've attached the SQL (from source) for the
first two sql statements. Can you please cast your eyes over them and give
me your feedback. If you need more information then please ask, I'll
gladly provide it.

Thank you.

--
Regards,

Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk

Attachment Content-Type Size
Apply_Partner_Details.sql text/plain 777 bytes
Apply_Parent_Details.sql text/plain 1.7 KB

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Eckermann 2002-05-17 14:04:09 Re: simple yet complex join
Previous Message Niclas Gustafsson 2002-05-17 10:54:00 Indices for both where and order by.