Jose Montero wrote:
> Paul Lutus wrote:
>> Jose Montero wrote:
>>
>> / ...
>>
>>>> Obviously, once an error condition comes up, trying to commit then is
>>>> too
>>>> late -- the prevailing error condition prevents the commit from being
>>>> carried out.
>>> --------------------------------------------------------------------
>>> Well the thing here is that if i commit each 5 records, and i get an
>>> error loading the 4th record, those 4 records wont commit, wont load. I
>>> think the thing here is how to recover from an error, how to commit the
>>> records that were already loaded to the database.....is it possible?
>> Apparently not. I want to emphasize this is just at first glance, 
>> without
>> running any tests. To me it would seem better to try to figure out why 
>> the
>> error is coming up. You obviously have a consistent though random error
>> that isn't going away, and that is undermining your database processing. 
>> I
>> would want to know why.
>>
>> It looks as though the error is on the database side (not the file 
>> reading
>> side), and since the database is in an error state after the failed 
>> query,
>> it cannot commit either. I think you need to analyze the error itself,
>> rather than try to get past it.
> ------------------------------------------------------------------------
> Well yes, i have detected when an error comes up. Sometimes its cause 
> the line contains malformed UTF-8 caracter, in other cases the length is 
> longer than the size of the field in the database,etc...And im 
> controlling all this cases, but what i want to do is that if appear an 
> unknown error, ok, dont process that line, continue and commit the rest 
> of already loaded lines. And what im trying to figure out is exactly 
> what u say: what is the state error the database is in, after the failed 
> query? How could i know this?
> 
> Thanks
> 
> 
Just to add something perhaps irrelevant.

As far as i take it the idea with transaction and commit is to commit
every time database is in a consistent state. In this case that would be
after every line, correct?

I'd assume the main objection would be performance, but isn't that
another issue entirely? Well, perhaps not. But if you do want to
bulk-load database without the chance of rollback, why not do
bulk-load/bulk insert. Thats the idea of it. If i remember correctly you
can do that (have a weak memory about it since some hack a long time
ago). I haven't figured out what db you are using, but look for it. I
would be to use the tools transaction/commit/rollback the intended way.

Bulk loading inside transaction is really intended to be able to
rollback it ALL if it failed.

Just my 2 cents (and even them a loan).
/Thomas