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

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.

Vipin Dwivedi Accepted Answer Pending Moderation

Tuesday, 2 March 2021 06:21 AM UTC
PowerBuilder
# 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
Tuesday, 2 March 2021 15:09 PM UTC

Thanks for sharing the solution!

Helpful 0

There are no comments made yet.

Miguel Leeuwe Accepted Answer Pending Moderation

Wednesday, 24 February 2021 23:16 PM UTC
PowerBuilder
# 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
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

Helpful 0

Miguel Leeuwe
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

Helpful 0

There are no comments made yet.

Chris Pollach @Appeon Accepted Answer Pending Moderation

Wednesday, 24 February 2021 18:57 PM UTC
PowerBuilder
# 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
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

Helpful 0

Chris Pollach @Appeon
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

Helpful 0

Vipin Dwivedi
Thursday, 25 February 2021 13:45 PM UTC

No, we are adding SQL_DRIVER_NOPROMPT in DBparm.

Helpful 0

There are no comments made yet.

Armeen Mazda @Appeon Accepted Answer Pending Moderation

Wednesday, 24 February 2021 17:46 PM UTC
PowerBuilder
# 4

Hi Vipin,

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

Thanks,
Armeen

Comment

Vipin Dwivedi
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.

Helpful 0

There are no comments made yet.

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