1. Hristo Antov
  2. PowerBuilder
  3. Monday, 22 July 2024 10:39 AM UTC

Hi All,

I hope you're well. First, I'd like to mention that I'm not a PowerBuilder developer, but I've been tasked with migrating an old application from SQLAnywhere to PostgreSQL. Without much guidance from the client, I've encountered several concerns and questions about the embedded SQL queries in the application. I would greatly appreciate any insights you can provide.

Here are a few specific queries I'm grappling with:

  • SELECT ISNULL(some_id, 0) INTO :my_id from db.table - Since ISNULL is not supported in PostgreSQL, should this be changed to COALESCE? Also, does specifying the schema with .db translate correctly?
  • SELECT column_one, column_two INTO :column_one, :column_two FROM db.table - Is multiple data binding supported and will it be transformed correctly in PostgreSQL?
  • SELECT IF total IS NULL THEN 1 ELSE 0 ENDIF INTO :total FROM db.table - Do I need to change it to a valid PostgreSQL syntax like SELECT CASE WHEN total IS NULL then 1 ELSE 0 END INTO :total FROM db.table ?
  • SELECT String(DateFormat(some_date,'mm/dd/yy')INTO :some_date FROM db.table - Do I need to change it to valid PostgreSQL syntax like SELECT TO_CHAR(some_date,'mm/dd/yy')  INTO :some_date FROM db.table ? 
  • ROLLBACK using global.SQLOBJ; - Will this translate to a valid PostgreSQL ROLLBACK query, or do I need to modify it for PostgreSQL compatibility? If modifications are necessary, what changes should I make?

Any guidance on these queries would be highly appreciated. Additionally, if there are other considerations or common pitfalls I should be aware of during this migration, your advice would be invaluable.

Thank you in advance for your help!

Best Regards,

Hristo

 

 

Andreas Mykonios Accepted Answer Pending Moderation
  1. Monday, 22 July 2024 11:25 AM UTC
  2. PowerBuilder
  3. # 1

Hi.

I'm not experienced in Postgresql, but we have:

  1. I would use coalelse as you mentioned. We do that in oracle.
  2. I guess this should work as it is. We such statements in oracle.
  3. You are right, you have to use case... We do that in oracle.
  4. Yes, using to_char should be ok... We do that in oracle.
  5. ROLLBACK using global.SQLOBJ: in powerbuilder rollback using is followed from powerbuilder's transaction object you need to issue rollback. This is the same for commit statements. If you omit the using part (example: commit; or rollback;) it's executed in sqlca which is the "global" transaction object in powerbuilder.

One thing you may have in mind, is that sql anywhere, as most T-SQL databases, has support for nested transactions. In PL-SQL you do have savepoints instead. So, if in the original code, you have nested transactions, those should be transformed to savepoints. This, in my opinion, is the most dangerous problem you may face.

Andreas.

Comment
  1. Hristo Antov
  2. Tuesday, 23 July 2024 08:58 AM UTC
Hi Andreas,



Thank you very much for your answers and assistance. Your help is greatly appreciated!



Kind Regards,

Hristo
  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.