GNADE insert problem

GNADE insert problem

[Jano]

[-- 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;

Re: GNADE insert problem

[Stephen Leake]

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

Re: GNADE insert problem

[Jano]

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
-------------------------

Re: GNADE insert problem

[Robert I. Eachus]

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

Re: GNADE insert problem

[Jano]

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.

Re: GNADE insert problem

[Jano]

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.

Re: GNADE insert problem

[Robert I. Eachus]

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

Re: GNADE insert problem

[Michael Erdmann]

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

Re: GNADE insert problem

[Robert I. Eachus]

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

Re: GNADE insert problem

[Michael Erdmann]

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