Re: Pro*C and Roles

Jim Ward wrote:
>
> Does anyone know if it is possible to set a role using Pro*C?
> The following code below fails...
>
> /*----------------------------------------------------*/
> exec sql begin declare section;
> VARCHAR myrole[20];
> exec sql end declare section;
>
> .. some C code ...
>
> strcpy(myrole.arr, "adpi");
> myrole.len = strlen(myrole.arr);
>
> exec sql set role :myrole;
Try this:

    char setRole[256];
    sprintf(setRole,"SET ROLE %s IDENTIFIED BY %s",role,rolePwd);

     EXEC SQL CONNECT :user IDENTIFIED BY :passwd;
     EXEC SQL EXECUTE IMMEDIATE :setRole;

Where the chars; role and rolePwd are defined programatically. I would prefer to use:

EXEC SQL SET ROLE :role IDENTIFIED by :rolePwd;

but this doesn't work in ProC 2.0.x. I don't know when/if they are ever going to add it. Roles are relatively new, so I hope they are working on it.

-- 
Rob Allan                       |  rob.e.allan_at_hydro.on.ca
Ontario Hydro                   |  Tel. (416) 592 4195
Toronto, Ontario, Canada        |  Fax  (416) 592 4966