1. Krishnaprasad Sadasivan
  2. PowerServer 2020 or older (Obsolete)
  3. Wednesday, 4 November 2020 20:00 PM UTC

Hello All.,

I have an issue in PB17 Application in Web deployed version.

In our application, We have a requirement to handle Transaction object in Array to connect different databases at the same time and hold the transaction. So while establishing the connection using the array instance of transaction object, My deployed application throws the below error when it execute the statement 'Connect using transactionobj[index];'

Below is the sample code that is used to create transaction object which is throwing the above error in web deployed application.

Long ll_tranrow
Transaction dynamic_conn[]
ll_tranrow = upperbound(dynamic_conn)+1
//
dynamic_conn[ll_tranrow] = Create transaction
//
dynamic_conn[ll_tranrow].Database = <sample>
dynamic_conn[ll_tranrow].LogId = <sampleid>
dynamic_conn[ll_tranrow].LogPass = <sampleLog>
dynamic_conn[ll_tranrow].DBMS = <sampleDBMS>
dynamic_conn[ll_tranrow].ServerName= <sampleserver>
dynamic_conn[ll_tranrow].DbParm = <ls_DbParm >
dynamic_conn[ll_tranrow].DBMS = 'MSS'
dynamic_conn[ll_tranrow].DBParm =ls_DbParm+" CacheName='"+ls_ccn+"'"

dynamic_conn[ll_tranrow].lock = 'RC'
dynamic_conn[ll_tranrow].AutoCommit = False
//
CONNECT using dynamic_conn[ll_tranrow];
//

Note : I am facing this error only in Web deployed application version and it works perfectly fine in Normal EXE.

Please find the PB version details.

I really appreciate if someone can help me to resolve this issue.

 

Thanks,

Krishna

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 4 November 2020 20:19 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 1

Hi Krishna;

   If you want to "hold" the transaction(s) open - then you cannot use a "local" transaction object reference as local variables are destroyed at the end of your script. The TO should be declared as either an Instance of something or another Global variable.

   For PowerServer Apps though, the "server" itself controls the connection an not your Web / Mobile App. Thus, you need to use a different approach called "Dynamic Transaction Object". This means that PowerServer contains the list of DB connections (aka DataSources). Then all your App needs to do is specify (redirect) PS as to which one to use based on the customer.

For example (with SQL Server): 

  • SQLCA.DBMS = "MSS"
  • SQLCA.DBParm="CacheName='CacheName" + <No> + "'"

Note: No Transaction object arrays or other TO variables are required ... Only SQLCA should be used!

HTH

Regards ... Chris

Comment
There are no comments made yet.
Krishnaprasad Sadasivan Accepted Answer Pending Moderation
  1. Wednesday, 4 November 2020 20:43 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 2

Hi Mr.Chris,

Thanks for your quick response.

I have already connected the sqlca with my main database using the connection cache concept in webserver. In our case while saving the transaction in main database using sqlca, I need to connect to different dependent databases (Eg: db1, db2 ... dbn) at the same time and once all the insert/update to these databases are successful then only I need to commit/rollback all the transaction objects including the SQLCA. For achieving this purpose I am using transaction object as array to handle all the dependent databases.

 

Thanks,

Krishnaprasad

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 4 November 2020 21:15 PM UTC
That should work as well .. just make sure that the TO's that your App creates are *not* local variables as in your example code. Also, you'll need to map your TO's in the PS Toolkit App Profile - so you will need named TO objects vs one TO as an array. So if the App has a maximum of 4 logical DB's that is accesses, then create 4 TO's. Each one of those 4 TO's though, can be redirected to a specific DB instance. HTH
  1. Helpful
  1. Krishnaprasad Sadasivan
  2. Tuesday, 10 November 2020 08:43 AM UTC
Hi Mr.Chris.,



Thanks For your suggestion and my apology for the delay in response.



We are using the same cache concept to connect the transaction object in deployed APP. In my sample I just shown as local transaction object but in actual code we are creating the transaction object as instance only.

The difference of my connection is, I am using array concept of transaction object like below example



(Eg: Transacton Tranobj[] // This Transaction object created as Instance variable

Long ll_row

Boolean lb_sucess = true

For ll_cnt = 1 To dw_dblist.rowcount() //Datawindow with total number of databases need to connect

ll_row = upperbound(Tranobj)

Tranobj[ll_row] = create transaction

Tranobj[ll_row].Database = <sample>

Tranobj[ll_row].LogId = <sampleid>

Tranobj[ll_row].LogPass = <sampleLog>

Tranobj[ll_row].DBMS = <sampleDBMS>

Tranobj[ll_row].ServerName= <sampleserver>

Tranobj[ll_row].DbParm = <ls_DbParm >

Tranobj[ll_row].DBMS = 'MSS'

Tranobj[ll_row].DBParm =ls_DbParm+" CacheName='"+<cache_name>+"'" //



Connect using Tranobj[ll_row] // In deployed APP, error is throwing here. Its working fine in EXE



Next



//Once connection is successful then update the data to all the databases using the transaction array object



For ll_cnt = 1 To upperbound(Tranobj)

update <table_name>

set <col_name> = :value

where <condition>

using Tranobj[ll_cnt];



// If any error while updating in any database then need to rollback all the connected transaction objects

If Tranobj[ll_cnt].sqlcode <> 0 Then

For ll_row = 1 To upperbound(Tranobj)

Rollback Using Tranobj[ll_row];

Disconnect using Tranobj[ll_row];

Destroy Tranobj[ll_row]

Next

lb_sucess = False

Exit

End If

Next



/If No Error in above update statement then commit the transactions

If lb_sucess Then

For ll_row = 1 To upperbound(Tranobj)

commit Using Tranobj[ll_row];

Disconnect using Tranobj[ll_row];

Destroy Tranobj[ll_row]

Next

End If



Please advise if Transaction object in array concept is not supported in PB APP deployed version and is there any Workaround for this.



Thanks in Advance..

Krishna
  1. Helpful
  1. Antony Xavier
  2. Monday, 1 February 2021 14:05 PM UTC
Hi Krishna ,



It is a bug in Powerserver, tried with PB2017R3 deployed in PowerServer 2017 and also tried with PB2019R2 deployed in PowerServer 2019, getting the same, error code "Error 10999" with error "Object reference not set to an instance of a object", but it is working in powerbuilder.



Regards

Antony



  1. Helpful
There are no comments made yet.
Krishnaprasad Sadasivan Accepted Answer Pending Moderation
  1. Wednesday, 11 November 2020 19:55 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 3

Hi Chris,

Thanks For your suggestion and my apology for the delay in response.

We are using the same cache concept to connect the transaction object in deployed APP. In my sample I just shown as local transaction object but in actual code we are creating the transaction object as instance only.

The difference of my connection is, I am using array concept of transaction object like below example

(Eg: Transacton Tranobj[] // This Transaction object created as Instance variable

Long ll_row

Boolean lb_sucess = true

For ll_cnt = 1 To dw_dblist.rowcount() //Datawindow with total number of databases need to connect

 ll_row = upperbound(Tranobj)

 Tranobj[ll_row] = create transaction

 Tranobj[ll_row].Database = <sample>

 Tranobj[ll_row].LogId = <sampleid>

 Tranobj[ll_row].LogPass = <sampleLog>

 Tranobj[ll_row].DBMS = <sampleDBMS>

 Tranobj[ll_row].ServerName= <sampleserver>

 Tranobj[ll_row].DbParm = <ls_DbParm >

 Tranobj[ll_row].DBMS = 'MSS'

 Tranobj[ll_row].DBParm =ls_DbParm+" CacheName='"+<cache_name>+"'" 

  Connect using Tranobj[ll_row] // In deployed APP, error is throwing here. Its working fine in EXE

Next

//Once connection is successful then update the data to all the databases using the transaction array object

For ll_cnt = 1 To upperbound(Tranobj)
  update <table_name>
  set <col_name> = :value
  where <condition>
  using Tranobj[ll_cnt];

 // If any error while updating in any database then need to rollback all the connected transaction objects

 If Tranobj[ll_cnt].sqlcode <> 0 Then
   For ll_row = 1 To upperbound(Tranobj)
    Rollback Using Tranobj[ll_row];
    Disconnect using Tranobj[ll_row];
    Destroy Tranobj[ll_row]
   Next
   lb_sucess = False
   Exit
 End If
Next

//If No Error in above update statement then commit the transactions

If lb_sucess Then
  For ll_row = 1 To upperbound(Tranobj)
     commit Using Tranobj[ll_row];
     Disconnect using Tranobj[ll_row];
     Destroy Tranobj[ll_row]
   Next
End If

Please advise if Transaction object in array concept is not supported in PB APP deployed version and is there any Workaround for this.



Thanks in Advance..

Comment
There are no comments made yet.
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 2 February 2021 06:00 AM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 4

0

Hi Krishna,

Thanks for reporting this problem! We reproduced it on our end and will do further research to figure it out. We will keep you posted on the progress.

Is it possible to work around the issue by using transaction object instead of transaction array, you can try to execute all operations in one for loop. For example:

Long ll_row

Boolean lb_sucess = true

For ll_cnt = 1 To dw_dblist.rowcount() //Datawindow with total number of databases need to connect

Tranobj = create transaction

Tranobj.DBMS = 'MSS'

Tranobj.DBParm =ls_DbParm+" CacheName='"+<cache_name>+"'"

Connect using Tranobj;

update <table_name>
set <col_name> = :value
where <condition>
using Tranobj;


If Tranobj.sqlcode <> 0 Then

Rollback Using Tranobj;
Disconnect using Tranobj;
Destroy Tranobj;

lb_sucess = False
Exit
End If

commit Using Tranobj;
Disconnect using Tranobj;

Next

Destroy Tranobj;


Regards,
ZhaoKai

 

Comment
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.