Re: Pro*C -- PL/SQL - Problem

I sent a simplified version of the executed function when daemon received request.
As you can see my function performs at least a ROLLBACK RELEASE... after each error processing

Note: I open#close connection for every request (i don't too much request...)

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE asci_20 IS STRING(20) REFERENCE; asci_20 glh_username;
asci_20 glh_password;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;

long FUNCTION(long a)
{

    strcpy(glh_password, "TOTO");
    strcpy(glh_username, "TATA_at_DECOR1B");

    EXEC SQL WHENEVER SQLERROR DO sql_error();     EXEC SQL CONNECT :glh_username IDENTIFIED BY :glh_password;

   /* On se déconnect si il n'y a pas eu de probleme sql */     if (NO_SQL_ERROR)

       EXEC SQL COMMIT WORK RELEASE;     return(1);
}

int sql_error()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     EXEC SQL ROLLBACK RELEASE;
    return(1);
}

Should the problen is in WHENEVER SQLERROR treatment..

Thx

Kenneth C Stahl wrote in message <376A4772.4797274D_at_lucent.com>...
:Is it performing an EXEC SQL CONNECT each time? If not, then whenever you
get
:the error you should somehow indicate to the program that it needs to log
on
:again before it tries another operation.
:
:What I'd really suggest is that the daemon itself only do minimal setup
work and
:then spawn a child which actually performs the work against the remote
database.
:This child process should perform the EXEC SQL CONNECT and should conclude
with
:something like EXEC SQL COMMIT WORK RELEASE; and the child would have to
ensure
:that if it gets a connection that it also performs the RELEASE.
:
:That way if you get a failure on an operation, the child will report the
error,
:the child will exit and the daemon itself can be preparing other children.
I
:suppose there is a minor problem of race conditions here, but that is just
:something that you would have to solve within the context of your
particular
:operational environment. I once wrote a daemon that could have as many as
15
:active child processes at any moment. I had to do a lot of fancy footwork
with
:SIGCHLD and waitpid() combined with some polling of the active children
(because
:my version of unix does not support signal queueing), but it works quite
well
:and while occasionally errors do occur when the remote database is down,
the
:original request is simply requeued until it is handled successfully.
:
:Vo Thuan wrote:
:
:> Program
:> -----------
:> I created a daemon which retrieve, when requested, data on DB (v7.3.4)
(on
:> another unix server). I used Pro*C(v.2.2.4) and Sql*Net (v.2.3.4) and
PL/SQL
:> (we have some packages on db server). Everything goes well when DB is up
o
:> another server.
:>
:> Problem
:> -----------
:> When db is down...
:> At first requested, from daemon, we got this message...
:>
:> ORA-01034: ORACLE not available
:> ORA-07429: smsgsg: shmget() failed to get segment.
:> Digital Unix Error: 2: No such file or direct
:>
:> FINE!
:>
:> All other subsequent requests gave us this message...
:>
:> ORA-12154: TNS:could not resolve service name
:>
:> Even if DB turn ON, we got the same message... I have to stop (kill) my
:> daemon and restart it to do more request.
:
Received on Fri Jun 18 1999 - 08:59:18 CDT