• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Semicolon in Select SQL datasource. Is there any Bug in PB 2017R3 MR#1858

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Semicolon in Select SQL datasource. Is there any Bug in PB 2017R3 MR#1858

  1. Issue
0
Votes
Undo
  1. Vipin Dwivedi
    Vipin Dwivedi
  2. PowerBuilder
  3. Wednesday, 24 February 2021 17:24 PM UTC

Hello Appeon Team,

We have PB application running in background as service which process some files. We are facing one issue where we have written one function in NVO object and doing below.

Create local datastore using n_ds

setting dataobject

setting Transobject using SQLCA

Retrieve the local datastore.

 

Now the problem is since this function being called for each line item for the files. We are seeing the service getting stall after processing few lines. we put some log to see where it is getting stall and it is on Retrieve. Once observation we found that the datasource SQL query contains a semicolon (;). I am not sure if semicolon is causing this problem and if it does then it should at first line itself but few lines are being processed successfully. 

FYI  - since this is local datastore, we are not explicitly calling Destroy method to destroy the local object.

Here is the code snippet

 

n_ds lds_options_on_order

lds_options_on_order = Create n_ds
lds_options_on_order.dataobject = 'd_options_on_order'
lds_options_on_order.of_Settransobject( SQLCA)

f_Log(Before call Retrieve)

lds_options_on_order.retrieve(il_Stock_No) // process hangs up on this line.

f_Log(After call Retrieve) // This log appears for few line items....

 

I need help to understand if semicolon in Select Query in datasource is causing this issue as I don't see why it will fail to retrieve in between.

Please help. We have a critical issue.

 

Full build and optimize of each library
Migration of old PB6 code to latest version
Responses (4)
Vipin Dwivedi
Vipin Dwivedi Accepted Answer Pending Moderation
  1. Tuesday, 2 March 2021 06:21 AM UTC
  2. PowerBuilder
  3. # 1

This issue got resolved.... Looks like some table lock was causing this issue. We reverted few changes as of now which resolved the issue.

We observed that when we use LEFT OUTER JOIN with table B but never use B table column in select, it locks the table LIKE

SELECT A. id,A.name,A.age

FROM A LEFT OUTER JOIN B

WHERE A.name = B.name

and A.age=B.age

Our database is Sybase Central 17 (SQL Anywhere 17).

 

Vipin

 

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 2 March 2021 15:09 PM UTC
Thanks for sharing the solution!
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 24 February 2021 23:16 PM UTC
  2. PowerBuilder
  3. # 2

Hi Vipin,

1. You say " Since we are using datastore, we can't use the Sqlpreview event as per my understanding". You CAN, but you would have to use a "standard non visual object, inherited from a datastore". Like in the PFC classes there's n_ds object. Okay ... now that I look at the code you posted, you already ARE using PFC n_ds object.

So that's on object in which you can manipulate whatever you'd like in the sqlpreview.

Also, you say that you don't DESTROY the datastore and that your code runs in a loop.

Make sure that your "

n_ds lds_options_on_order

lds_options_on_order = Create n_ds

"

is OUTSIDE (before) the loop. If not, you DO have to destroy the datastore also.

In my opinion, it's always better to destroy the datastores before exiting the script.

HIH,

Miguel

 

Comment
Vipin Dwivedi
  1. Vipin Dwivedi
  2. Wednesday, 24 February 2021 23:35 PM UTC
Thanks for letting me know through n_ds we can inherit the SqlPreview event and manipulate the SQL for datastore.



No.. I am not creating the datastore in loop. When I said calling in a loop mean we are calling the function for each line read.. from file.... so didn't use the DESTROY... creation of datastore is only once per call and since function is exiting as well so it should destroy itself.



Regards.... Vip



  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 February 2021 23:39 PM UTC
That sounds okay to me then. I do remember there have been some reports of people saying something about a memory leak when doing retrieves within a loop, but those were specific cases.

Never hurts to check how well your memory is behaving while running the retrieves in the loop though.

regards
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 24 February 2021 18:57 PM UTC
  2. PowerBuilder
  3. # 3

Hi Vipin;

  FWIW:  Since PB 1.0, the ";" is not supposed to be in any DWO's SQL source. The ";" is only to be used in in-line SQL within a PowerScript code line. I suspect your issue though is with the current DB Client driver.

  Thought: A workaround here would be to use the SQLPreview Event and then remove the duplicate ";" from the SQL before letting the DWO process the Retrieve(). Food for thought.

Regards ... Chris

 

Comment
Load more comments
Vipin Dwivedi
  1. Vipin Dwivedi
  2. Wednesday, 24 February 2021 22:26 PM UTC
It would be great to have fix to remove ; in long term to avoid manual edit. In our project, we are not using ; except this data source which is having issue I believe.



We are going to deploy the change without ; to the user and see if this goes fine. I have one question here...

Do you think Retrieve() ever can open Retrieval Argument dialog box even if we pass variable into argument list.



I am suspecting if ; does not resolve the issue then there could be a possibility that retrieve() may be opening Retrieval Argument dialog box in background and waiting for user input which is not possible as application is running as service. Just a though getting closure to the root cause.

What's your thought?



Regards.... Vip





  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 24 February 2021 23:34 PM UTC
Hi Vipin;

I don't think that most DB Client software would open a dialogue for this type of error. The only exception might be where your App is using "middleware" like ODBC, and your not setting the "SQL_DRIVER_NOPROMPT" option in the DBParm field of SQLCA.

Regards ... Chris
  1. Helpful
Vipin Dwivedi
  1. Vipin Dwivedi
  2. Thursday, 25 February 2021 13:45 PM UTC
No, we are adding SQL_DRIVER_NOPROMPT in DBparm.
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 24 February 2021 17:46 PM UTC
  2. PowerBuilder
  3. # 4

Hi Vipin,

Please open a support ticket and attach your test case so we can reproduce the issue.

Thanks,
Armeen

Comment
Vipin Dwivedi
  1. Vipin Dwivedi
  2. Wednesday, 24 February 2021 18:52 PM UTC
Here is the SQL code behind the Data source.



SELECT "TableA"."stock_no",

"TableA"."code",

"TableA"."type"

FROM ("TableA" LEFT OUTER JOIN "TableB" ON "TableA"."code" = "TableB"."code" AND "TableA"."type" = "TableB"."type" AND "TableA"."make" = "TableB"."make")

WHERE ("TableA"."stock_no" = :al_no) AND

("TableA"."type" = 'O');



Here :al_No is the retrieval argument



Note - There is a semicolon in the query. We are going to remove the semicolon from the query and going to test if this works.



One thing to remember that since we are running this process in the background, this will not have any user interaction so if by any chance Retrieve() will open Retrieval argument then also process will stall though we are passing the retrieval argument.



There is nothing extra in this code.

  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.