Subject Re: [IB-Architect] SQL Scripts
Author Jason Wharton
>> Interest construct. Better than a series of individual "insert"
>> statements by a long shot. But a better programmatic solution
>> is to prepare one "insert" and execute it many times.

> Not really. There is no batching of inserts into network packets.
> Each record gets its own network packet. This is chatty use of the wire.

Perhaps I misunderstood your post. I realize by doing a re-read that you
aren't necessarily saying where the data is coming from. What I say only
applies if each record comes individually from the client.

The "wound" of situations I have confronted is poor use of network during a
remote batch load process. The bottom line I would like to see solved is a
way to initiate a "batch load" from the client to the server and use the
network efficiently.

"Speaking in the reverse lingo" This *could* be implemented by opening a
batch input cursor and pushing inserts to the server using it as a vehicle.
Internally what would be happening is the client would be placing multiple
insert records in a network packet and then sending them over the wire when
they filled up (with whitespace trimmed prior to putting them in the

To use the existing API mechanisms, the introduction of opening a cursor
could be allowed for statements which have input parameters only. Then, once
"opened" the client could begin a series of execute calls which, due to the
cursor in place, would cache up, and then the errcode would return 0 for
each cached item. When the batch actually went out the errcode would return
the result of whatever happened in the process of doing the whole load. When
complete the cursor would be closed and the batch mode would be removed.

The tricky part most likely would be treating multiple conversations with
the client as a single unit of "unwindable" work to be cancelled in case of
a failure, much like multi-record updates that fail part way through. You
would have to do that or somehow distinguish a way of what was accepted and
what failed on the batch level. This will be the hang-up to overcome. I
prefer the way which treats the entire input cursor batches from open to
closing as a single cancelable piece of work. I think it would be the
cleanest to work with from the client-side.

I would also want this so that you could use a stored procedure as well as
an insert statement. This way, you can use exception handling in a manner
that wouldn't force the "unwind" of the whole batch. You could catch an
exception and handle it in the proc and keep moving on.

Transactions would still be handled the same as they are now.

PS. I've though of a hole or two in my thoughts, but I got to hit the road.
This should be enough to stimulate some first round comments.

PSS. Perhaps this needs a thread of its own.

Jason Wharton
CPS - Mesa AZ