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

Resolved PB 2022 R3 vs SQL Server 2022 - Datetimes issue

  1. Issue
0
Votes
Undo
  1. Regis Schwartz
    Regis Schwartz
  2. PowerBuilder
  3. Wednesday, 13 March 2024 13:55 PM UTC

Hello,

I've just starting to test SQL server 2022 with PowerBuilder 2022 R3.

I'm facing a problem with the statments sent by PB to the DB when a retrieval argument is a datetime.

With the old version of SQL server (2000) this was the syntax :

SELECT c.identifiant_commande FROM commande c WHERE c.date_creation >= '2024-01-01 00:00:00.000'

With the new version, quotes are missing :

SELECT c.identifiant_commande FROM commande c WHERE c.date_creation >= 2024-01-01 00:00:00.000

Select Error: SQLSTATE = 42000
Microsoft SQL Server Native Client 11.0
Syntaxe incorrecte vers '00'.


Same thing with an update :

UPDATE utilisateur SET date_modif = 13/03/2024 14:11:12.033 WHERE identifiant_utilisateur = 5367

SQLSTATE = 42000
Microsoft SQL Server Native Client 11.0
Syntaxe incorrecte vers '14'.

No changes made to database.



I've tried with both SNC (11) and MSO (19.3) drivers.

DBParm=Provider='SQLNCLI11',DateTimeFormat='dd/mm/yyyy hh:mm:ss.fff'

or
DBParm=Provider='MSOLEDBSQL19',DateTimeFormat='dd/mm/yyyy hh:mm:ss.fff'

Using ChatGPT to generate PB Code
Can’t send email from application
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 19:00 PM UTC
  2. PowerBuilder
  3. # 1

Hi Regis;

  FYI: the DisableBind=0 setting is the default and that forces PB DB drivers to adhere to the ISO ANSI DML standard that most DBMS expect. The DisableBind=1 setting was introduced by PowerSoft in order to enhance your PB App's SQL Tracing so that you could see the actual data values. This was only expected to be a feature used by the PB developer for Unit Testing / debugging purposes only and then it was expected that this setting would be removed once the DML issue(s) were ratified & before the PB App headed to production.

 Sybase however started to recommend the use of the DisableBind=1 setting to get around PB feature support issues like for example supporting the BIT data type in ASE / SS /  SA/ etc DBMS.. Then then continued to suggested using the DisableBind=1 setting again for other DBMS quirks. This can create a  problem though in that this can dramatically change the way other DML / PBSelect DBMS statements are generated and handled. So while it might fix one DML specific thing, it can damage other existing DML statement execution in your PB App.

  So the key takeaway here is (IMHO) do not use the "DisableBind=1" setting except for debugging. Just my $0.02.  HTH

Regards .. Chris

Comment
There are no comments made yet.
Regis Schwartz
Regis Schwartz Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 10:41 AM UTC
  2. PowerBuilder
  3. # 2

I almost forgot to post my solution : DateTimeFormat='yyyy/mm/dd hh:mm:ss.fff',DisableBind=0

In a french speaking environment Dateformat is mandatory to avoid a conversion error.

Without DisableBind=0 : PowerBuilder does not enclose datetimes in quotes.

 

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 21 March 2024 10:59 AM UTC
I don't know exactly in which version the problems with Disablebind started. It used to only do what it's supposed to do. We're running on Oracle and getting random errors depending on the Disablebind setting. Well done Appeon.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 13 March 2024 16:47 PM UTC
  2. PowerBuilder
  3. # 3

Hi Regis;

  FYI: I just did a test with PB 2022 R3 & SS 2018 on my W10 / W11 test machines. The DT column updates work 100%.

Note1: I do not use the DateTimeFormat='xxxxxxxxxxxxxx'  in my DBParm field.

Note2: If I add the DateTimeFormat='xxxxxxxxxxxxxx'  in my DBParm field - the updates fail (SS DB error).

HTH

Regards ... Chris

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.