Re: esql/c Batch Updates

On Oct 17, 5:32 pm, "Obnoxio The Clown" <obno...@xxxxxxxxxxxxxxx>
wrote:
Art Kagel said:

Don't thank the Clown yet, that's not right.  You can't open a cursor
against an UPDATE statement like that because it is not iterative.  If it
were allowed and worked the statement below would update every row of the
table where t2 = :t2 to the single value of the variable f2 at the time
the
cursor is opened.

Gumby got it right!  You:

He did? Reading is essential. :op

The original problem was that he has a text file full of updates he wants
to apply. You can prepare an update statement, and I think that this is
what he wants to achieve.



EXEC SQL DECLARE updater CURSOR FOR
    SELECT stock_no, manu_code
    FROM stock_values
    WHERE ....
    FOR UPDATE OF price, description;

EXEC SQL BEGIN WORK;
EXEC SQL OPEN updater USING ....;

while (sqlca.sqlcode == 0) {
     EXEC SQL FETCH updater INTO :stock_no, :manu_code;
     if (sqlca.sqlcode != 0) break;

     <Get update values from file>

     EXEC SQL UPDATE stock_values
         SET price = :new_price, description = :new_desc
         WHERE CURRENT OF updater;
}
if (sqlca.sqlcode < 0) {
    .....<Error handling>
     EXEC SQL ROLLBACK WORK;
} e;se
     EXEC SQL COMMIT WORK;
...

The UPDATE ... WHERE CURRENT OF <cursorname> (see TFM) updates the current
cursor row directly without having to search for it again.  This is the
fastest way to update many rows with individualized values.

If you are updating a large number of rows look up CURSOR .... WITH HOLD
and
commit every N updates.

Art

On Fri, Oct 17, 2008 at 10:02 AM, angusm <all4mil...@xxxxxxxxx> wrote:

On Oct 17, 3:59 pm, "Obnoxio The Clown" <obno...@xxxxxxxxxxxxxxx>
wrote:
angusm said:

If I read and understood the docs correctly you declare an update
cursor as a "declare ... select .... where ... for update". So
basically you are fetching all the records that match your select
statement and then you can scroll through them and update/delete etc
right....?  How do I retro fit that to generate updates from a flat
file as every row has different values?

What Im trying to find out is if its possible to us the construct
below but for updates?

EXEC SQL declare ins_cur cursor for
   insert into stock values
   (:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc);

EXEC SQL declare upd_cursor for
update stock_values SET f1 = :f1 where f2 = :f2;

??

--
Bye now,
Obnoxio

http://obotheclown.blogspot.com/

Thanks! been googling for a while now but never came across that sorry
to waste your time ;)
_______________________________________________
Informix-list mailing list
Informix-l...@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@xxxxxxxx)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly.  Neither do
those opinions reflect those of other individuals affiliated with any
entity
with which I am affiliated nor those of the entities themselves.

_______________________________________________
Informix-list mailing list
Informix-l...@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list

--
Bye now,
Obnoxio

http://obotheclown.blogspot.com/

Yip that was the intent of my question. I am trying to improve the
loads times of some 4gl apps that use an "insert -> fails -> update"
logic. I have been playing around with the load2 library (loadinc
tool) from the iiug software repository with some good results. During
my testing I noticed the library also has a load tool which has a
cursor switch which makes a big difference in load times. I was
wondering if I could do the same for updates too hence my post.

On that note is feasible/recommend to create a load app that has this
logic: bulk insert (or update depending on stats) via cursor -> fails -
processing failed batch without cursor? Our environment is such that
we have base source data arriving for the same table (different
columns) in overlapping time slots so there is no way that when your
load starts you can determine update/insert buckets as during the load
process another load may have started. The obvious solution would be
to stage the data but that's out of my control, I'm purely trying to
optimize load times within the current paradigm.

Cheers guys and thank for our input
Angus


.

Relevant Pages

  • Re: [RFC PATCH 2/2] dm: only initialize full request_queue for request-based device
    ...   - a table and its attributes are bound to the block device on resume ... Your patch makes that loading table affects the block device, ... Table load sequence is: ... Blocking allocation inside exclusive _hash_lock can cause deadlock; ...
    (Linux-Kernel)
  • Re: Controlled switching of FETs
    ...     driver ... Where PWR_GND is a long wire to the system power supply, and "load" is ... Twisting PWR_GND and the +24v supply line helped reduce inductance a ...
    (sci.electronics.design)
  • Re: read matrix file
    ... I am thinking a way to load file, containing matrix data, by Fortrancode. ... As you might know that a simple command load() in MatLab can do ...     interface load ...
    (comp.lang.fortran)
  • Re: read matrix file
    ... I am thinking a way to load file, containing matrix data, by Fortrancode. ... As you might know that a simple command load() in MatLab can do ... trying to write a subroutine by Fortran Code. ...     interface load ...
    (comp.lang.fortran)
  • Re: PDB Path set
    ... Still did not load it. ... // SymSetOptions() ... PDB, even though the PDB is in the same directory as the exe. ...    Go back to my first posting earlier today, ...
    (microsoft.public.vc.debugger)