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

Resolved Postgres and asynchronous retrieve

  1. Issue
0
Votes
Undo
  1. Markus Schröder
    Markus Schröder
  2. PowerBuilder
  3. Tuesday, 10 March 2020 10:46 AM UTC

Hello,

we are migrating a powerbuilder 2019 project from Oracle to Postgres 12.

The async database parameter is set to 1 and the retrieverow event is coded, so i can cancel a retrieve.
When i am connected to Oracle (native driver), everything works fine.

With postgres (psqlODBC driver) i can't cancel the retrieve.
A cancel is only possible when the database returns rows (not before).

Is this a known issue?

  1. PowerBuilder
  2. PostgreSQL
Encoding Problem converting RTE to HTML
Fixed DATA in a DW how to Describe and Modify ?
Responses (3)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 10 March 2020 15:09 PM UTC
  2. PowerBuilder
  3. # 1

Well, that leaves one more thing we could check. This one should support Async:

https://github.com/alaisi/postgres-async-driver

 

See if you can create a second transaction object which connects through JDBC.

Maybe a bit of overkill depending on how important this is for your application.

 

 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 10 March 2020 13:58 PM UTC
  2. PowerBuilder
  3. # 2

You don't need Asynchronous retrieval if it's just to be able to cancel a retrieve.

Using the retrieveRow event (which will make your retrieve slower), you can still cancel:

If you search on the help of retrieveRow event, you'll get this example in pb 12.6:

IF ll_row > 250 THEN
        MessageBox("Retrieval Halted", &
            "You have retrieved 250 rows, the allowed & 
            maximum.")
        RETURN 1
ELSE
        RETURN 0
END IF


Maybe something could be done also using Multithreading for your retrieval? Experts please jump in ... :)
regards

Comment
René Ullrich
  1. René Ullrich
  2. Tuesday, 10 March 2020 14:24 PM UTC
There is a difference: RetrieveRow helps to stop retrieval AFTER first row. Async helps you stop retrieval BEFORE first row was retrieved. Multithreading doesn't change something with stopping retrieval but helps you run it in a background process.
  1. Helpful
Markus Schröder
  1. Markus Schröder
  2. Tuesday, 10 March 2020 14:27 PM UTC
Hi Miguel,



I need to cancel the retrieve before the first row is retrieved.

I use the official Postgres ODBC driver, but it doesn't support this feature.



From the Powerbuilder help:

"While the server is compiling and executing the SQL statement and before PowerBuilder retrieves the first row of data, you must have done both of the following to enable asynchronous operation (allowing you to cancel the current operation before it retrieves the first row of data):

- Coded a RetrieveRow event for the DataWindow object or report (the code can contain only a comment)

- Set the Async parameter to 1"
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 10 March 2020 15:01 PM UTC
Hi Ren'e,

I mentioned multithreading like maybe you could simply "kill the session with an execute immediate" for the DB session in one thread, from the other thread. Not very clean though, but I was thinking of something like that.

regards
  1. Helpful
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 10 March 2020 12:07 PM UTC
  2. PowerBuilder
  3. # 3

Hi Samuel,

ODBC only supports the ASYNC parameter if the odbc driver and the database server supports it.

If the driver supports it you can see in "Driver Information" entry in the database painter for your database profile. Check the SQL_ASYNC_MODE value. The PostgreSQL driver I use returns 0 (= not supported). Other values may be 1 (= supported for connection) or 2 (= supported for statements).

 

HTH,

René

Comment
Markus Schröder
  1. Markus Schröder
  2. Tuesday, 10 March 2020 12:34 PM UTC
Thanks for your quick response.

My ODBC Driver doesn't support asynchronous retrieve.



Greetings,

Samuel

  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.