Re: Pro *C/C++ SavePoint problem ?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 Apr 2000 00:58:17 GMT
Message-ID: <8ce32v$9fs$1@nnrp1.deja.com>

In article <8cdoo6$u21$1_at_nnrp1.deja.com>, Eric Chow <eric138_at_yahoo.com> wrote:
> Hi,
>
> If I want to use SAVEPOINT in Pro *C/C++, how to do that ?
>
> I know that it si easy to use : EXEC SQL SAVEPOINT a_save_point_name;
>
> Right ?
>
> But, if I want to use a function setSavePoint(char *name);
>
> It seems that Pro *C/C++ can't allow to implement like that.
>
> Is there another method to implement that method setSavePoint(char
*) ?
>
> Best regards,
> Eric
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

tiny bit of dynamic sql will fix that right up:

void * setSavePoint( char * name )
{
char sqlstmt[255];

sprintf( sqlstmt, "savepoint %s", name ); exec sql execute immediate :sqlstmt; printf( "%s\n", sqlstmt );
}

void * rollbackSavePoint( char * name ) {
char sqlstmt[255];

sprintf( sqlstmt, "rollback to %s", name ); exec sql execute immediate :sqlstmt; printf( "%s\n", sqlstmt );
}

void * dumpT(void)
{
int x;

exec sql declare c cursor for select x from t;

    printf( "------------------\n" );

    exec sql open c;
    for(;;)
    {

        exec sql whenever not found do break;
        exec sql fetch c into :x;
        printf( "%d\n", x );

}
exec sql whenever not found continue; exec sql close c;

    printf( "------------------\n" );

}

main( int argc, char * argv[] )
{
char result[255];

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard(); EXEC SQL CONNECT :USERID;
printf("\nConnected to ORACLE as user: %s\n\n", USERID);

setSavePoint( "s1" );
exec sql insert into t values ( 1 );

setSavePoint( "s2" );
exec sql insert into t values ( 2 );

setSavePoint( "s3" );
exec sql insert into t values ( 3 );

    dumpT();
    rollbackSavePoint( "s3" );
    dumpT();
    rollbackSavePoint( "s2" );
    dumpT();
    rollbackSavePoint( "s1" );
    dumpT();
}

and when I run that against a table T ( x int ) (starts out empty) I get:

Connected to ORACLE as user: /

savepoint s1
savepoint s2
savepoint s3

1
2
3

rollback to s3

1
2

rollback to s2

rollback to s1

which is exactly what you expect...

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 04 2000 - 19:58:17 CDT