Re: Transactions and SavePoints

Hi...
Marko, debtor for the aid.
Not wanting to ask for very, but it would like to lher to ask for of
the same skill..... I already I had given one looked at in the sources
of driver and more or less had identified the problem, ok, but it
happens that I I do not have much knowledge in C and I do not obtain
to compile driver, will be that you it could not make this for me?

In any obliged way

Cleber

2005/9/1, Marko Ristola <Marko(dot)Ristola(at)kolumbus(dot)fi>:
>
>
> Unfortunately SAVEPOINT support has not been implemented
> into psqlodbc driver yet.
>
> So it doesn't work unfortunately.
>
> However, if you have a possibility use the native PostgreSQL
> libpq driver (not an ODBC driver) instead,
> you can use fully featured savepoints.
>
> Depending on your needs, you can even pay
> somebody to implement the feature into psqlodbc. A good enough
> implementation could be included into the driver.
>
> I have a small part of an implementation, but that's not enough.
>
> You described your problem: the ODBC driver does the rollback
> for you, even though you don't want it.
>
> The problem is just the ice berg of the lack of the
> SAVEPOINT feature in the psqlodbc driver.
>
>
> However, I know one easy solution into the problem, you found:
>
> Get source code of the psqlodbc ODBC driver.
>
> On file connection.c, C code function CC_send_query():
>
> else if (strnicmp(cmdbuffer, "ROLLBACK", 8) == 0)
> CC_on_abort(self, NO_TRANS);
>
> The above must be changed not to call CC_on_abort(self,NO_TRANS), if the
> case
> is "ROLLBACK TO SAVEPOINT name";
> CC_on_abort(self,NO_TRANS); must be called on "ROLLBACK WORK"
>
> NO_TRANS means, that the ODBC thinks, that there is no transaction in the
> PostgreSQL server.
>
> So you can get your problem away with:
>
> else if (strnicmp(cmdbuffer, "ROLLBACK", 8) == 0 &&
> (strnicmp(query,"ROLLBACK TO",11) != 0)
> CC_on_abort(self, NO_TRANS);
>
> I used "query" argument, because cmdbuffer is "ROLLBACK", but query is
> "ROLLBACK TO SAVEPOINT name".
>
> Then you need to recompile the ODBC driver, and you don't have that
> problem anymore.
> ODBC debugging options help you to track further problems with savepoints.
>
> With this, only a partial SAVEPOINT implementation can be achieved.
> You might get well with SAVEPOINTs, if you don't use CURSORs,
> because with them the problem becomes a lot harder, and the simple
> fix from above won't work anymore.
>
> Yours,
> Marko Ristola
>
> Cleber Nardelli wrote:
>
> >Marko, debtor for the aid that you have given me.
> >
> >My problem in the truth nor is SAVEPOINT and ROLLBACK You the SAVEPOINT.
> >
> >My problem is that when an error is generated during the execution of
> >commands inside of a transaction driver automatically it makes a
> >ROLLBACK of all the executed commands until then and ai I do not
> >obtain to execute the ROLLBACK You the SAVEPOINT therefore the block
> >of the transaction already does not exist more.
> >
> >Yours truly Cleber
> >
> >2005/8/31, Marko Ristola <marko(dot)ristola(at)kolumbus(dot)fi>:
> >
> >
> >>Hi
> >>
> >>I tried to write SAVEPOINT SQL support into PsqlODBC in Summer.
> >>
> >>I was able to implement some suport for savepoint creation, savepoint
> >>rollback and savepoint RELEASE.
> >>
> >>I didn't understand, how the query results should behave at savepoint
> >>rollbacks.
> >>
> >>You can find more information about my thoughts about savepoint
> >>implementation on this list ealier this year, or with Google.
> >>
> >>Main design issue was, that SAVEPOINT tracking must be implemented
> >>into PSQLODBC.
> >>
> >>That's what I implemented:
> >>- Linked list of all defined savepoints.
> >>- Maintain the list during SAVEPOINT maintenance functions.
> >>
> >>Savepoint command parsing speed was 600000 commands per second,
> >>that I wrote (UTF-8). With UCS-4 , without a state machine, it was
> >>300000 commands per second. The subsystem's speed was okay.
> >>
> >>I implemented a full PostgreSQL SQL SAVEPOINT support with
> >>the Bison/Flex parser, because it is easy, but that part is
> >>not at all easy to integrate into an existing PsqlODBC driver.
> >>
> >>Are you interested in a patch for the mergeable part of the savepoint
> >>support?
> >>
> >>Regards,
> >>Marko Ristola
> >>
> >>Carlos Henrique Reimer wrote:
> >>
> >>
> >>
> >>>Is there a ODBC flavor than can implement ROLLBACK TO SAVEPOINT?
> >>>
> >>>Marko Ristola wrote:
> >>>
> >>>
> >>>>Unfortunately SAVEPOINTs are not implemented in PsqlODBC yet.
> >>>>
> >>>>
> >>>>ROLLBACK TO SAVEPOINT -case is easy to fix, but the full
> >>>>implementation is hard to make correctly for psqlodbc, at least for me.
> >>>>
> >>>>Marko Ristola
> >>>>
> >>>>
> >>>>
> >>>>>Cleber Nardelli wrote:
> >>>>>
> >>>>>
> >>>>>Hello.
> >>>>>I am using the Driver odbc for access to postgresql. I have a
> >>>>>
> >>>>>
> >>>referring problem to the cancellation of the transaction when
> >>>
> >>>
> >>>>>some error
> >>>>>is found at the moment of the execution of the same one. I try to
> >>>>>
> >>>>>
> >>>execute this sql command:
> >>>
> >>>
> >>>>>BEGIN TRANSACTION;
> >>>>>DELETE FROM TABL1 WHERE ID = 1;
> >>>>>INSERT INTO TABL1 VALUES (1,1);
> >>>>>SAVEPOINT SVPT;
> >>>>>INSERT INTO TABL1 VALUES (1,1);
> >>>>>ROLLBACK TO SAVEPOINT SPVT;
> >>>>>COMMIT;
> >>>>>
> >>>>>When I try to insert as register it accuses to error with key
> >>>>>
> >>>>>
> >>>duplicate. Ok. But I do not want to cancel the transaction I I need to
> >>>only continue
> >>>
> >>>
> >>>>>with the same one skirting this error. For this reason I am using
> >>>>>SavePoints but it generates the error:
> >>>>>"ERROR: ROLLBACK TO SAVEPOINT may only be used in transaction blocks"
> >>>>>
> >>>>>Necessary of the your help. I thank
> >>>>>
> >>>>>Yours truly
> >>>>>
> >>>>>
> >>>>>
> >>>------------------------------------------------------------------------
> >>>Yahoo! Acesso Grátis
> >>><%20http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/>:
> >>>Internet rápida e grátis. Instale o discador agora!
> >>><%20http://us.rd.yahoo.com/mail/br/taglines/*http://br.acesso.yahoo.com/>
> >>>
> >>>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 3: Have you checked our extensive FAQ?
> >>
> >> http://www.postgresql.org/docs/faq
> >>
> >>
> >>
> >
> >
> >
> >
>
>

--
Cleber Nardelli
IPM Automação e Consultoria
9998-8670