4gl insert cursor - know it?
A poster asked how to use an insert cursor in 4GL, having heard it's faster than plain INSERT. Replies gave the recipe: PREPARE/DECLARE a cursor FOR INSERT INTO ... VALUES (?,...), OPEN it, issue PUT ... FROM hostvars inside the loop, then FLUSH (essential, since rows are buffered) and CLOSE/FREE, with BEGIN WORK/COMMIT as needed. Tips: only worthwhile for many rows (50-100+), serial values can't be retrieved, FET_BUF_SIZE can be raised to 32767, and errors only surface on flush, so use sqlca.sqlerrd[2] to find which rows went in and re-insert the rest; dbcopy.ec was cited as an example. A correction noted PUT uses FROM, not USING.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, Does anyone know how to use an insert cursor to insert into a table? I heard it's much faster than using the standard "insert into..." statement. Min
Min Kim wrote in message <374AAE1A.1655A8FC@icpma.com>... >Hello, >Does anyone know how to use an insert cursor to insert into a table? I >heard it's much faster than using the standard "insert into..." >statement. > Yes, it can be, if you have a lot of rows (a large array, for instance) to insert. prepare an insert statement as usual. declare a cursor, just as you would for a prepared select statement begin work as required open the cursor (with no USING clause to fill in the "?" in the prepared statement) during the for loop that is getting the data you want to insert: put cursor-name from .... (here's where you fill in the "?") after the loop: flush cursor-name commit work as required close & free as usual. Make sure you remember to flush; the speed of insert cursors is that they fill up memory buffer before sending rows to the database; if you don't flush your results will be similar to forgetting to call finish report.
declare i_x cursor for insert into x values (f_x.*) open i_x # each time your record (f_x) changes issue a put cursor_name command put i_x # this puts the values into a buffer flush command can be used to force buffer flush Yes it is much faster then an insert , however I have found out the it is only worth while when there a many (50 , 100 +++) records to be inserted .... Another limitation is that you can'nt get the value of the inserted serial field which you can get with an insert . Mariusz Min Kim <mkim@icpma.com> wrote in article <374AAE1A.1655A8FC@icpma.com>... > Hello, > Does anyone know how to use an insert cursor to insert into a table? I > heard it's much faster than using the standard "insert into..." > statement. > > Min >
Min Kim wrote:
>
> Hello,
> Does anyone know how to use an insert cursor to insert into a table? I
> heard it's much faster than using the standard "insert into..."
> statement.
Yes it is. Another thing you can do to speed inserts is to increase
the size of the communications buffer by setting the environment
variable FET_BUF_SIZE to 32767 (default 4096, max 32767). HOWEVER,
you will only be able to detect errors when the comm buffer is flushed
to the server and only those rows inserted successfully before the
first row in error will actually be inserted. You will have to
re-insert the remaining rows in the cursor. Here are the basics, quick
and dirty anyway (there are sample codes in $INFORMIXDIR
subdirectories):
DECLARE i_insert CURSOR FOR
INSERT INTO mytable VALUES (?,?,?,...)OPEN i_insert;
while (<still data>)
PUT i_insert USING hostvar1, hostvar2, hostvar3, ...
if (sqlca.sqlcode < 0) then
<handle errors and reput the missing rows>
end if
end while
FLUSH i_insert
CLOSE i_insert
You can use the sqlca.sqlerrd[2] value to know how many rows since the
last automatic or manual flush were actually inserted. The row after
that last one is the row in error. You can know when the buffer has
flushed either by manually flushing it before the buffer fills
(calculate the number of rows that fit and flush one row sooner) or
because sqlca.sqlerrd[2] is > 0 and contains the number of rows
flushed. If sqlca.sqlerrd[2] > 0 and sqlca.sqlcode = 0 then all rows
in the buffer were successfully flushed. To recover from errors you
will have to buffer rows yourself or keep pointers into the incomming
data so you can backtrack after an error and reinsert.
Check out the code in dbcopy.ec for a complete example. It is ESQL/C
not 4GL so you will have to adjust (like the sqlerrd array is 0 based
in ESQL not 1 based so the indexes are off by one) but you'll get the
idea.
Art S. Kagel
Art S. Kagel wrote:
>
> Min Kim wrote:
> >
> > Hello,
> > Does anyone know how to use an insert cursor to insert into a table? I
> > heard it's much faster than using the standard "insert into..."
> > statement.
>
> Yes it is. Another thing you can do to speed inserts is to increase
> the size of the communications buffer by setting the environment
> variable FET_BUF_SIZE to 32767 (default 4096, max 32767). HOWEVER,
> you will only be able to detect errors when the comm buffer is flushed
> to the server and only those rows inserted successfully before the
> first row in error will actually be inserted. You will have to
> re-insert the remaining rows in the cursor. Here are the basics, quick
> and dirty anyway (there are sample codes in $INFORMIXDIR
> subdirectories):
>
> DECLARE i_insert CURSOR FOR
> INSERT INTO mytable VALUES (?,?,?,...)> OPEN i_insert;
>
> while (<still data>)
> PUT i_insert USING hostvar1, hostvar2, hostvar3, ...
OOPPSS. Dennis is correct the keyword here is FROM not USING which is
from taking input from an SQL DESCRIPTOR area or an sqlda structure.
So the line above should be:
PUT i_insert FROM hostvar1, hostvar2, hostvar3, ...
> if (sqlca.sqlcode < 0) then
> <handle errors and reput the missing rows>
> end if
> end while
>
> FLUSH i_insert
> CLOSE i_insert
BTW while my style is like Dennis's and I like to put the FROMN clause
in the PUT statement rather than using the USING clause in the OPEN
statement the results are the same. I just think this way documents
better what is happening since the FROM clause in the PUT is located
immediately after most of the data manipulation that will set the
values being added while the OPEN may be many lines above or even in a
different module and so the documentation value is lost.
> You can use the sqlca.sqlerrd[2] value to know how many rows since the
> last automatic or manual flush were actually inserted. The row after
> that last one is the row in error. You can know when the buffer has
> flushed either by manually flushing it before the buffer fills
> (calculate the number of rows that fit and flush one row sooner) or
> because sqlca.sqlerrd[2] is > 0 and contains the number of rows
> flushed. If sqlca.sqlerrd[2] > 0 and sqlca.sqlcode = 0 then all rows
> in the buffer were successfully flushed. To recover from errors you
> will have to buffer rows yourself or keep pointers into the incomming
> data so you can backtrack after an error and reinsert.
>
> Check out the code in dbcopy.ec for a complete example. It is ESQL/C
> not 4GL so you will have to adjust (like the sqlerrd array is 0 based
> in ESQL not 1 based so the indexes are off by one) but you'll get the
> idea.
Art S. Kagel