* GNADE insert problem
@ 2003-12-19 16:40 Jano
2003-12-20 1:55 ` Stephen Leake
0 siblings, 1 reply; 10+ messages in thread
From: Jano @ 2003-12-19 16:40 UTC (permalink / raw)
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #1: Type: text/plain, Size: 2721 bytes --]
Hello, I'm using GNADE and esql to insert some records in a database.
Please check the code at the end of this post for exact implementation.
The database is MSAccess accessed using ODBC.
The thing is that the first record is correctly inserted (I can later
see the record), but the second call to insert fails with
Line 79 in package Aenea.Db : *** Exception in query ****
SQL State : 42000 SQL Code :-1
---------------
[17:32:38.42] [E] Database insertion failed: Exception name:
GNU.DB.SQLCLI.INVALID_CURSOR_STATE
Message: [Proc=SQLCloseCursor][Server=hyperion][State=24000][Microsoft]
[Controlador ODBC Microsoft Access]Estado del cursor no v�lido
Call stack traceback locations:
0x438ee3 0x439bf6 0x439ef1 0x43b7cd 0x48c8ef 0x6ba969 0x6d5962
As you can see, it seems as if some cursor were left open and tried to
be reused. I've found just an example of insertion and I can see
anything special there.
I don't know if I'm doing something wrong or it is the fault of MSAccess
or esql. Any hints? This is my first look at GNADE and I'm somewhat
lost.
Thanks,
Alex.
--------8<---------- Code begins here --
-- Init is called once, Insert_row is called periodically.
with Aenea.Net;
with Aenea.Trace;
with Sql_standard; use Sql_standard;
package body Aenea.Db is
-- Database
EXEC SQL DECLARE DB DATABASE;
------------------------------------------------------------------------
-- Init --
------------------------------------------------------------------------
procedure Init is
begin
EXEC SQL WHENEVER SQLERROR RAISE DB_Error;
-- Connect
EXEC SQL
CONNECT BY DB
TO "hyperion";
Trace.Log ("Connection with database successful.",
Trace.Informative);
end Init;
------------------------------------------------------------------------
-- Insert_row --
------------------------------------------------------------------------
procedure Insert_row is
EXEC SQL BEGIN DECLARE SECTION;
Hubs, Leaves : INT;
EXEC SQL END DECLARE SECTION;
begin
Hubs := INT (Net.Counter.Avg_hubs);
Leaves := INT (Net.Counter.Avg_leaves);
EXEC SQL AT DB
insert into g2crawl (hubs, leaves, fecha)
values (:Hubs, :Leaves, Now ());
EXEC SQL AT DB COMMIT;
exception
when E : DB_Error =>
Trace.Log ("Database insertion failed: " & Trace.Report (E),
Trace.Error);
when E : others =>
Trace.Log ("Database insertion failed: " & Trace.Report (E),
Trace.Error);
end Insert_row;
end Aenea.Db;
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-19 16:40 GNADE insert problem Jano
@ 2003-12-20 1:55 ` Stephen Leake
2003-12-20 9:47 ` Jano
0 siblings, 1 reply; 10+ messages in thread
From: Stephen Leake @ 2003-12-20 1:55 UTC (permalink / raw)
To: comp.lang.ada
Jano <nono@celes.unizar.es> writes:
> Hello, I'm using GNADE and esql to insert some records in a database.
You should also try the gnade mailing list
gnade-develop@lists.sourceforge.net.
> Line 79 in package Aenea.Db : *** Exception in query ****
>
> SQL State : 42000 SQL Code :-1
> ---------------
> [17:32:38.42] [E] Database insertion failed: Exception name:
> GNU.DB.SQLCLI.INVALID_CURSOR_STATE
> Message: [Proc=SQLCloseCursor][Server=hyperion][State=24000][Microsoft]
> [Controlador ODBC Microsoft Access]Estado del cursor no válido
> Call stack traceback locations:
> 0x438ee3 0x439bf6 0x439ef1 0x43b7cd 0x48c8ef 0x6ba969 0x6d5962
>
> As you can see, it seems as if some cursor were left open and tried to
> be reused. I've found just an example of insertion and I can see
> anything special there.
>
> I don't know if I'm doing something wrong or it is the fault of MSAccess
> or esql.
This is definitely an esql problem.
I suggest you abandon esql, and use the gnu-db-sqli package directly
instead. It's more robust, and really not that hard.
I've used gnu-db-sql to access MySQL via ODBC quite successfully. The
code generated by esql I've looked at has definite problems.
--
-- Stephe
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-20 1:55 ` Stephen Leake
@ 2003-12-20 9:47 ` Jano
2003-12-20 18:35 ` Robert I. Eachus
0 siblings, 1 reply; 10+ messages in thread
From: Jano @ 2003-12-20 9:47 UTC (permalink / raw)
Stephen Leake dice...
> You should also try the gnade mailing list
> gnade-develop@lists.sourceforge.net.
Thanks for the pointer.
> This is definitely an esql problem.
>
> I suggest you abandon esql, and use the gnu-db-sqli package directly
> instead. It's more robust, and really not that hard.
I've workaround it disconnecting from the DB after each insert. Since I
insert a record per minute and the db is local, it can hold that way
until I get into the package you have suggested.
> I've used gnu-db-sql to access MySQL via ODBC quite successfully. The
> code generated by esql I've looked at has definite problems.
Thanks,
--
-------------------------
Jano
402450.at.cepsz.unizar.es
-------------------------
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-20 9:47 ` Jano
@ 2003-12-20 18:35 ` Robert I. Eachus
2003-12-21 9:41 ` Jano
0 siblings, 1 reply; 10+ messages in thread
From: Robert I. Eachus @ 2003-12-20 18:35 UTC (permalink / raw)
Jano wrote:
> I've workaround it disconnecting from the DB after each insert. Since I
> insert a record per minute and the db is local, it can hold that way
> until I get into the package you have suggested.
As an aside, this problem is why I quickly get headaches mixing Ada and
SQL. SQL has this concept of an implicit cursor, and you have to be
very careful that every Ada thread containing SQL maintains its own
cursor correctly.
How you do that depends on the database, the interface language (i.e.
whether it is compiled or embedded SQL) and what you are doing. But
when you have to think in SQL while working in Ada, it is very painful.
Having an explicit cusor parameter for each SQL call is neither Ada or
pure SQL. But when possible, it eliminates a lot of headaches.
--
Robert I. Eachus
"The war on terror is a different kind of war, waged capture by capture,
cell by cell, and victory by victory. Our security is assured by our
perseverance and by our sure belief in the success of liberty." --
George W. Bush
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-20 18:35 ` Robert I. Eachus
@ 2003-12-21 9:41 ` Jano
2003-12-21 10:37 ` Jano
2003-12-31 12:12 ` Michael Erdmann
0 siblings, 2 replies; 10+ messages in thread
From: Jano @ 2003-12-21 9:41 UTC (permalink / raw)
Robert I. Eachus dice...
> Jano wrote:
>
> > I've workaround it disconnecting from the DB after each insert. Since I
> > insert a record per minute and the db is local, it can hold that way
> > until I get into the package you have suggested.
>
> As an aside, this problem is why I quickly get headaches mixing Ada and
> SQL. SQL has this concept of an implicit cursor, and you have to be
> very careful that every Ada thread containing SQL maintains its own
> cursor correctly.
My program as a few tasks, but only one of them is making SQL calls
(though now I think that maybe the initial connection is done from
another task... room for some experimentation). Good to be warned
though.
Kind regards,
Alex.
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-21 9:41 ` Jano
@ 2003-12-21 10:37 ` Jano
2003-12-21 20:32 ` Robert I. Eachus
2003-12-31 12:12 ` Michael Erdmann
1 sibling, 1 reply; 10+ messages in thread
From: Jano @ 2003-12-21 10:37 UTC (permalink / raw)
Jano dice...
> Robert I. Eachus dice...
> > Jano wrote:
> >
> > > I've workaround it disconnecting from the DB after each insert. Since I
> > > insert a record per minute and the db is local, it can hold that way
> > > until I get into the package you have suggested.
> >
> > As an aside, this problem is why I quickly get headaches mixing Ada and
> > SQL. SQL has this concept of an implicit cursor, and you have to be
> > very careful that every Ada thread containing SQL maintains its own
> > cursor correctly.
>
> My program as a few tasks, but only one of them is making SQL calls
> (though now I think that maybe the initial connection is done from
> another task... room for some experimentation). Good to be warned
> though.
Just for the record, connecting from the same tasks makes no difference.
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-21 10:37 ` Jano
@ 2003-12-21 20:32 ` Robert I. Eachus
0 siblings, 0 replies; 10+ messages in thread
From: Robert I. Eachus @ 2003-12-21 20:32 UTC (permalink / raw)
Jano wrote:
> Just for the record, connecting from the same tasks makes no difference.
I meant it when I said thread I meant execution thread, not thread of
control. Maybe I should have spelled it out. Threads of control are
usually mapped to different tasks in Ada, but there can be execution
threads in a program that are NOT visible as tasks or otherwise.
Typically you will create an execution thread in a database when you log
in, and close it by logging out. But for example when you type SQL in a
terminal session, each query is a separate execution thread.
What makes cursor control so painful is that to find where where a
cursor is created or set you have to look below the user visible Ada and
SQL levels. Once you know which actions create cursors, you then have
to find out where cursors are used and where the cursor that is used is
expected to be found.
The error message you are getting could come either from a thread that
does not create a thread but expects an implicit cursor to be passed to
it, or it could be that another action in that thread is leaving the
implicit cursor locked. Without the software in front of me, I can't
tell you what is happening, just to read the documentation--assuming you
have decent documentation--or the source code and mark all the places
where cursors are created, set, locked, and PASSED to other
programs/subprograms/threads.
When you have an Ada program that can have several active execution
threads--and cursors in the database, you will still often have only one
or two Ada tasks. (I typically have one task interacting with a user at
a keyboard and display, and one task spawning queries, although
sometimes I will have additional tasks for doing formatting of data
returned. But the mapping I use is cusor <---> query, not cusor <--->
Ada task.
--
Robert I. Eachus
"The war on terror is a different kind of war, waged capture by capture,
cell by cell, and victory by victory. Our security is assured by our
perseverance and by our sure belief in the success of liberty." --
George W. Bush
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-21 9:41 ` Jano
2003-12-21 10:37 ` Jano
@ 2003-12-31 12:12 ` Michael Erdmann
2003-12-31 15:42 ` Robert I. Eachus
1 sibling, 1 reply; 10+ messages in thread
From: Michael Erdmann @ 2003-12-31 12:12 UTC (permalink / raw)
On Sun, 21 Dec 2003 10:41:39 +0100, Jano wrote:
>
>> As an aside, this problem is why I quickly get headaches mixing Ada and
>> SQL. SQL has this concept of an implicit cursor, and you have to be
>> very careful that every Ada thread containing SQL maintains its own
>> cursor correctly.
Maybe there is some kind to simplifcation requiered for Ada 95 which
could be provided by GNADE? Anny sutiable interface you want to propose?
>
> My program as a few tasks, but only one of them is making SQL calls
> (though now I think that maybe the initial connection is done from
> another task... room for some experimentation). Good to be warned
> though.
Michael
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-31 12:12 ` Michael Erdmann
@ 2003-12-31 15:42 ` Robert I. Eachus
2004-01-01 9:17 ` Michael Erdmann
0 siblings, 1 reply; 10+ messages in thread
From: Robert I. Eachus @ 2003-12-31 15:42 UTC (permalink / raw)
Michael Erdmann wrote:
> Maybe there is some kind to simplifcation requiered for Ada 95 which
> could be provided by GNADE? Anny sutiable interface you want to propose?
...
>
>>My program as a few tasks, but only one of them is making SQL calls
>>(though now I think that maybe the initial connection is done from
>>another task... room for some experimentation). Good to be warned
>>though.
Maybe the best 'simplification' would be to define the SQL
cursor/environment as a per task attribute.
http://www.adaic.org/standards/95lrm/html/RM-C-7-2.html (Actually a
pointer to the (potentially shared) enviroment would probably be the
best implementation.)
That way a user who needed to care could insure that either different
tasks did have different (SQL) environments or shared a single environment.
I don't see any way to get rid of the (mental) pain of those cases where
you do need multiple active SQL cursors, but this way at least, the
simpler cases can be dealt with. Of course, I would implement things so
that there was a "default" environment that all tasks shared unless you
explicitly changed the SQL environment for a task by opening an SQL
session outside the environment task. (Note that the original poster is
doing that, which puts him at best in a currently undefined situation.)
The package that supports this could probably be made optional to
accomodate any compilers that don't support per task attributes.
--
Robert I. Eachus
"The war on terror is a different kind of war, waged capture by capture,
cell by cell, and victory by victory. Our security is assured by our
perseverance and by our sure belief in the success of liberty." --
George W. Bush
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: GNADE insert problem
2003-12-31 15:42 ` Robert I. Eachus
@ 2004-01-01 9:17 ` Michael Erdmann
0 siblings, 0 replies; 10+ messages in thread
From: Michael Erdmann @ 2004-01-01 9:17 UTC (permalink / raw)
On Wed, 31 Dec 2003 10:42:45 -0500, Robert I. Eachus wrote:
>
> Maybe the best 'simplification' would be to define the SQL
> cursor/environment as a per task attribute.
> http://www.adaic.org/standards/95lrm/html/RM-C-7-2.html (Actually a
> pointer to the (potentially shared) enviroment would probably be the
> best implementation.)
>
At least i can provide this concept by means of embedded SQL. Them
embedded SQL implementation of GNADE does not realy consider any
tasking issues.
> That way a user who needed to care could insure that either different
> tasks did have different (SQL) environments or shared a single environment.
>
> I don't see any way to get rid of the (mental) pain of those cases where
> you do need multiple active SQL cursors, but this way at least, the
> simpler cases can be dealt with.
I agree on this, if you are using ODBC then you need to take care about
cursors, we could provide an intermediate layer which checks, if a cursor
is only used within the same task, where it has been allocated.
Michael
^ permalink raw reply [flat|nested] 10+ messages in thread
end of thread, other threads:[~2004-01-01 9:17 UTC | newest]
Thread overview: 10+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2003-12-19 16:40 GNADE insert problem Jano
2003-12-20 1:55 ` Stephen Leake
2003-12-20 9:47 ` Jano
2003-12-20 18:35 ` Robert I. Eachus
2003-12-21 9:41 ` Jano
2003-12-21 10:37 ` Jano
2003-12-21 20:32 ` Robert I. Eachus
2003-12-31 12:12 ` Michael Erdmann
2003-12-31 15:42 ` Robert I. Eachus
2004-01-01 9:17 ` Michael Erdmann
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox