Hello,
I have noticed that my ESQLC connection to an Ingres database works
asynchronously. Is this the default behavior?

When issuing dozens of insert-statements then my ESQLC calls return really
fast while the Ingres database apparently still works on the inserts and
finishes them seconds after my calls.

This is rather bad behavior when I'm relying on finished sql statements.
I've found that one can configure OpenAPI to work synchronously, but how to
do this with ESQLC ? Is there any default dbevent that is triggered when a
insert/delete/update/... statement finishes?

Thanks for any hints,
Walter

"Walter Quirtmair" <walter.quirtmair (AT) dynamic-design (DOT) com> wrote

I am afraid you have to be mistaken.

What makes you think that? What makes you think Ingres is still working on
the inserts? Ingres certainly doesn't take multiple seconds to execute mere
"dozens" of inserts on any hardware you can buy today (assuming no
slow-running rule-invoked procedures)--you can do hundreds per second even
on low-end hardware.

Absolutely, which is why it doesn't work that way.

Nope, but you could set one up using a rule and a procedure.

Roy

At 10:49 AM +0200 9/2/05, Walter Quirtmair wrote:
What makes you think that? Embedded SQL is synchronous. The IIsyncup()
calls you see in the preprocessed code flush the query to the DBMS and
wait for a response. I can't say I've ever seen it act as you describe.

Karl

Walter,

You probably have fast-commit, write behind configured for your ingres
server.

This means database transactions are forced to the transaction log file
(and work as fast as the disk will let them) but the table changes are
stored in memory until such time as ingres gets round to putting them
onto the disk (the effect you are seeing long after your last commit).
You can reconfigure this behaviour (via cbf) and switch off this
behaviour but ask yourself why you'd want things to run slower !!

Caveat... Microsoft... beware of disk caching enabled on hard drives...
unless you know how to overcome power cuts (or have set the extra
ingres logical)...

Cheers

Gary

<ghingres (AT) yahoo (DOT) co.uk> wrote

One would certainly hope so! Surely that is not what he means though?!

Which is:

ingsetenv II_WRITE_THROUGH_CACHE TRUE

Roy

Thanks for your answer.

Actually I have a strange behavior which can only be explained when thinking
that ESQLC works asynchronously:

I have a window in my app that display simple data which comes from a table
in an Ingres DB. When clicking on "Save" I'm deleting all content of that
table and recreate it completely with inserts inside a loop.

Now our customers reported that they always get a duplication of the rows
with each click on Save. Finally we could reproduce the problem only when
clicking Save more than once *really fast*. Then not all but many of the
rows suddenly are inserted twice, in turn results in many duplicate rows.

The code behind that action is very basic and simple. We finally came to the
conclusion that ESQLC must return from the insert-statements almost
immediately while the database is still working on that data. In the
meantime the second click sends the same data to the DB which then will
produce the duplicated rows. Strange enough the second delete statement must
be executed before the inserts, which explains why not all rows are
duplicated.

This sounds strange and it is - when working in a "normal" pace this never
happens. Only when clicking Save and then OK in the window (which performs a
Save as well) fast, this will lead to the problem. And that's exactly how
our customer works...

To solve the problem I have rewritten the code and now I'm doing a bulk copy
from a file. The problem never could be reproduced since then. Still I
wonder what's the reason for the reported problem. I have no clue...

Besides: it's OpenIngres 2.0 from 1999. I know this is a old version, but
there's no chance to upgrade that for various reasons. And Ingres is not
that fast, really. We work over a LAN and that alone means slower actions by
a factor of approx. 10! CA won't give us any support with that old Ingres,
you know...

Anyway, thanks for your insight.

Walter





"Betty & Karl Schendel" <schendel (AT) kbcomputer (DOT) com> schrieb im Newsbeitrag
news:mailman.1125652201.20154.info-ingres (AT) cariboulake (DOT) com...

"Sylvia+Walter Quirtmair" <sw.quirtmair (AT) liwest (DOT) at> wrote

Is this definitely a single-threaded application with a single database
session?

Does this program of yours run with autocommit turned on (or commit each
statement explicitly)? How much error checking does it do? Does it do
particular checks for deadlocks? What does it do when a deadlock occurs?
Are there reports of deadlocks in errlog.log involving this table at the
time of interest? Is the lockwait timeout set, and what is it set to? What
does the application do if a lockwait timeout occurs?

No, that just isn't the way Ingres works. Even if you use the API to make
asynchronous calls, it is asynchronous only on the client side. The server
still executes the SQL strictly synchronously within each session. But
that's neither here nor there because I don't think you are using the API
anyway.

Although SQL permits duplicate rows by default, a wise database designer
disallows them when creating the table. (I realize that would just change
the way the problem manifests itself, but at least the database wouldn't
have to have duplicates removed as well--never an easy task.)

A more credible explanation is that the delete sometimes fails and the error
isn't detected or isn't handled properly.

Network I/O is slow? Well yes, of course it is. It is by FAR slowest thing
you can do, even if it's a LAN. But what does that have to do with Ingres?
That's like complaining the service in a restaurant is slow because it takes
a long time to drive there.

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"