Open cursors with autocommit

Ann Miranda (amiranda@deveel.aero.org) wrote:

: I am running Ingres 6.4/01 on a Sun (SunOs 4.1.1).

: I have an embedded SQL/C routine that does 2 select queries using
: cursors. Both cursors are open at the same time.

: The problem is that the routine works only for the DBA. If I open only one 
: cursor at a time, the routine works for other users.

: The user who tested it has all permissions to the table in question, and they
: are a "superuser" in Ingres. 

Just a (silly) thought:  Do the environments of the DBA and the other users
differ?  Perhaps, normal users have something like ING_SET="set autocommit on".
I've always troubles with cursors and autocommit:  The first statement that
is not related to a cursor (to the first cursor?) generates a commit, which
subsequently closes all open cursors.

If I didn't hit the point, please gimme some more information:
Where do the users differ?  How does your C-code look like?

	Christopher
-- 
____________________________________________________________________________
Christopher Etz            Kopernikusstr. 28          D-65929 Frankfurt/Main
cetz@cetz.rhein-main.de    Tel.: +49 69 318091        Telefax: +49 69 318091



Thanks for the suggestions on the multiple cursor problem.

It turns out, that the problem was that autocommit was set on with the ING_SET
environment variable. But, it was set on for both the users, AND the dba (me). 
So, I'm confused as to why 2 cursors could be opened for the dba, but not for 
another user.

	Ann Miranda




Trevor East  writes:

>
> I have just transferred some esql (C) code from a SCO Box running INGRES
> 6.4/04 onto a HP 817 (9000 OS) running OpenIngres 1.2.  The esql code
> compiles on both machines.  The executable produced is then called from a
> script and works without error on the SCO box but fails with the
> following message on the HP
>
> E_US08B4 No MST is currently in progress, cannot declare another cursor.
>
> This error is trapped and when the code tries to close the cursor and
> terminate the process the message
>
> E_LQ0058 Cursor 'trev' not open for 'close command appears.
>
> The piece of code which is failing is an INSERT statement which resides
> in its own function.  It has been declared without a function prototype
> (would not compile with) and inserts the passed arguments into the table.
>
> I cannot understand why it is failing.  If you have any ideas could you
> please let me know
>
>
> Thanks in aniticpation
>
> Trev

Sounds like your two installations do not have identical "startup"
files and that the OpenIngres 1.2 box is running in "autocommit on"
mode by default.

Ingres does not allow multiple open cursors when running in
autocommit ON mode.

Check your INGSET and related variables.  Following my signature
is some dialog from an SQL session that you can help diagnose whether
something is turning autocommit on or off by default when you
connect to Ingres.

|---------------------------------------------------|
| Michael Leo                                       |
| Caribou Lake Software  http://www.cariboulake.com |
| Java/Ingres Solutions Provider                    |
| mleo@cariboulake.com                              |
|---------------------------------------------------|

mermie% sql iidbdb | cat
INGRES TERMINAL MONITOR Copyright (c) 1981, 1991 Computer Associates Intl, Inc.
OpenIngres SPARC SOLARIS Version OI 1.2/01 (su4.us5/00) login
Fri Feb 28 21:00:52 1997

continue
* commit \g
Executing . . .

continue
* set autocommit on \g
Executing . . .

continue
* select dbmsinfo('autocommit_state') \g
Executing . . .


+--------------------------------+
|col1                            |
+--------------------------------+
|1                               |
+--------------------------------+
(1 row)
continue
* set autocommit off \g
Executing . . .

continue
* select dbmsinfo('autocommit_state') \g
Executing . . .


+--------------------------------+
|col1                            |
+--------------------------------+
|0                               |
+--------------------------------+
(1 row)
continue
*
Ingres Q & A
To William's Home Page
Email William