1. LOIC DEFIEF
  2. PowerBuilder
  3. Friday, 3 July 2020 12:29 PM UTC

PB 2019 R2 Build 2279

We have a problem using application roles with ODBC driver for SQL Server and the SELECTBLOB function:

First we connect to the sql database using "ODBC Driver 17 for SQL Server" (or 13), then we activate application role by executing the sp_setapprole procedure for the transaction. After that, all the select are working fine and use the permissions granted in the application role, except when we call a SELECTBLOB. We got a message :

SQLSTATE = 42000

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The SELECT permission was denied on the object ...

We have read on some forums the existence of a DbParm OptSelectBlob, which could solve the problem because apparently selectblob would use its "own" transaction and so doesn't use the application roles, but that doesn't seem to work with ODBC.

Do you have any idea to solve our problem?

Thanks

PS: It works fine with SQL native driver (SQLNCLI), but we need to use the ODBC sql driver...

 

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 7 July 2020 13:19 PM UTC
  2. PowerBuilder
  3. # 1

Hi Loic;

  When processing Blob's in either an ASE or SQL Server database, you need to make sure that AutoCommit property of the Transaction object is set to ON.

  • SQLCA.AutoCommit = True

HTH

Regards ... Chris

Comment
  1. LOIC DEFIEF
  2. Wednesday, 15 July 2020 07:39 AM UTC
Thanks for the reply but unfortunately that doesn't solve the problem
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.