1. Pierre Rene
  2. PowerBuilder
  3. Wednesday, 17 February 2021 22:20 PM UTC

Hi there,

Powerbuilder 12.5 classic application has no issues creating temporary tables in an Azure MSSQL database (account dbo user) but for some unknown reason... a deployed web application from PB 12.5 using the same connection parameters (user name/password) suddenly does not have 'CREATE' permissions.

Tried it with an ADO.NET and SNC.... no joy ;(

Any suggestions ?

Thanks in advance

Pierre Rene Accepted Answer Pending Moderation
  1. Wednesday, 17 February 2021 22:43 PM UTC
  2. PowerBuilder
  3. # 1
0
Votes
Undo

Hi Chris,

Yes, Autocommit is set to TRUE in the transaction object. (set via same INI file web/non-web)

It's an 'embedded' CREATE command (powerscript) 

// create temporary table
ls_temp = "CREATE TABLE #ipas_pension_summarization " + &
"(calendar_year decimal(4,0) NOT NULL, " + &
"contribution_rate decimal(10,4) NOT NULL, " + &
"effective_months decimal(10,4) NOT NULL, " + &
"months_to_distribute decimal(4,0) NOT NULL, " + &
"employer_code varchar(5) NOT NULL, " + &
"schedule_id decimal(10,0) NULL)"

EXECUTE IMMEDIATE :ls_temp;

Works fine from a local compiled app... but not when deployed as a web app. Does APPEON play with the autocommit at connection time ?

Can you elaborate on the #TempTable route ?

Thanks in advance,

Pierre

Comment
Web app? So you are using PowerServer?
  1. Armeen Mazda @Appeon
  2. Wednesday, 17 February 2021 22:51 PM UTC
The MSSQL database is an Azure resource and I've deployed the 'web' application on a virtual machine (Azure) also.

I'm testing the access from outside the Azure 'environment' ... everything works except for these pesky 'temptables'
  1. Pierre Rene
  2. Wednesday, 17 February 2021 23:00 PM UTC
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 17 February 2021 22:28 PM UTC
  2. PowerBuilder
  3. # 2
0
Votes
Undo

Hi Pierre;

   Sounds like you are using the CREATE DML option vs the #TempTable route.

   For the Create DML ... did you set "AutoCommint = TRUE" in your App's Transaction Object?

Regards ... Chris

Comment
Hi Chris,



Yes, Autocommit is set to TRUE in the transaction object. (set via same INI file web/non-web)



It's an 'embedded' CREATE command (powerscript)



// create temporary table

ls_temp = "CREATE TABLE #ipas_pension_summarization " + &

"(calendar_year decimal(4,0) NOT NULL, " + &

"contribution_rate decimal(10,4) NOT NULL, " + &

"effective_months decimal(10,4) NOT NULL, " + &

"months_to_distribute decimal(4,0) NOT NULL, " + &

"employer_code varchar(5) NOT NULL, " + &

"schedule_id decimal(10,0) NULL)"



EXECUTE IMMEDIATE :ls_temp;



Works fine from a local compiled app... but not when deployed as a web app. Does APPEON play with the autocommit at connection time ?



Can you elaborate on the #TempTable route ?



Thanks in advance,



Pierre
  1. Pierre Rene
  2. Wednesday, 17 February 2021 23:06 PM UTC
Hi Pierre;

I just realized that your were talking about a PowerServer WEB based application. PS does support the AutoCommit setting. So I am not sure why Azure's SQL Server would not comply. Could be a "Borg" issue! ;-)

I would suggest opening a Support Ticket for this issue. As a workaround though ... what about using a Stored Procedure instead to create the TempTable?

Regards ... Chris
  1. Chris Pollach @Appeon
  2. Thursday, 18 February 2021 15:54 PM UTC
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.