1. Feng Eric
  2. PowerBuilder
  3. Wednesday, 3 February 2021 09:47 AM UTC

Hello Appeon team,

 

I am evaluating PB 2019R3, since PB 2017R3 is not compatible with the new driver MSOLEDBSQL.

Even if we add “DataTypeCompatibility = 80”, the values of the columns of datetime will be truncated (for example, "2021-02-02 14:50:00.000"), the seconds are not saved for datawindows, which is a big problem.

We have to change the related datawindows to set the attribute of disablebind to ensure that the datetime type column can be updated correctly.

For PB 2019R3, we still face the same problem. Do you have any suggestions on how to avoid this problem?

 

my application using the following connection string:

DBMS="OLE DB"

DBParm = "PROVIDER ='MSOLEDBSQL', DATASOURCE ='ERIC-PC', PROVIDERSTRING ='DATABASE = Testing_Dev_9_0; DataTypeCompatibility = 80', PBTrimCharColumns ='Yes', IntegratedSecurity ='SSPI'"

 

If remove the property “DataTypeCompatibility = 80”, just simple modify the column [note] in the relevant DW, it will pop-up an error “DB connection lost! Bnnnn...” and then the application will crash. (see the screenshot below)

Use the SQL Server profiler, it shows the undeclared parameters before the Rollback.  

exec [sys].sp_describe_undeclared_parameters N'UPDATE store SET note1 = @P1, programname = @P2, username = @P3, stationid = @P4, ts_id = @P5 WHERE store_code_id = @P6 '

 

Personally: using MSOLEDBSQL in PB 2019R3,

1. Keep the attribute datatypecompatiblity = 80:

       1.1 Set disablebind for DW (yes), DW can be updated normally

       1.2 Set disablebind for DW (No), DW can be updated, but seconds are not saved in the datetime column.

2 Remove the attribute datatypecompatiblity = 80:

       2.1 Set disablebind for DW (yes), DW can be updated normally

       2.2 Set disablebind for DW (No), DW cannot execute update correctly, it will pop-up an error ”Database connection loss, Bnnn..”, then application crash

 

My project contains too many DWs , 1 need to check it one by one, which is unreasonable.

So, I would like to ask you that if the PB 2019R3 is fully compatible with MSSQLOLEDB?

Do I still need to keep the property “datatypecompatiblity = 80” in PB 2019R3?

Do I need to add some properties (special setup) to DBParm to make it work (just like the old drive "SQLOLEDB",  we don’t need to care about the “disablebind”)

I am looking forward to your reply!

 

Regards,

Eric

 

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 3 February 2021 17:26 PM UTC
  2. PowerBuilder
  3. # 1

Hi Eric;

   What version & build of SQL Server are you using?

Regards ... Chris

Comment
  1. Feng Eric
  2. Thursday, 4 February 2021 02:16 AM UTC
Hi Chris,



My SQL Server version as below:

----------------------------------------------------

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

Apr 29 2016 23:23:58

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows 10 Education 6.3 <X64> (Build 19042: )
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 3 February 2021 20:31 PM UTC
  2. PowerBuilder
  3. # 2

My understanding is that Microsoft created the MSOLEDBSQL provider as a replacement for and a path forward from the functionally obsolete SQL Server Native Client provider. The new MSOLEDBSQL driver in PB 2019 R3 interfaces with this new provider.

I note in your post that you stated:

my application using the following connection string:

DBMS="OLE DB"

and I believe the correct DBMS value for the new driver is SQLCA.DBMS = "MSOLEDBSQL", so this contributes to my confusion. It is not clear to me from your post what PB driver (SQLCA.DBMS = ???) and DBParm options you are currently using with PB 2017. Would you please provide us with this information?

Regards, John

[Update]

I've successfully connected to a SQL Server Express 2019 server using the MSOLEDBSQL PB Driver and MSOLEDBSQL Provider in PB 2019 R3, retrieved information from a table containing columns of datatype datetime in the PB Database Painter using a SELECT statement in the ISQL Session pane, Manually edited and updated/saved datetime values that specifies non-zero seconds in the time portion, and re-retrieved the same rows and modified datetime values retain the times I've specified, including the seconds. This works regardless of whether DisableBind=0 or DisableBind=1 in DBParm.

Here are transaction property values I used in the Database Profile:

SQLCA.DBMS = "MSOLEDBSQL SQL Server"
SQLCA.ServerName = "xxxxxxx"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='xxxxxx',TrustedConnection=1,TrimSpaces=1,DisableBind=0"  // or DisableBind=1

Granted, I'm not using the same property values and DBParm options you are. But, I don't have your environment. Still, it works.

I suggest you experiment with transaction object values, including DBParm options in a PB Database Profile and test with the Database Painter in a similar manner. Start out with as few options as possible or as simple as possible, then change one option at a time or add a single option and re-test, all outside of your application and solely within the Database Painter. Once you have a profile working your scenarios to your satisfaction, then try manipulating data through one or more of your application's DataWindow objects via the DataWindow Painter and verify that works. Once that is working, use the working connection values in your application.

We want to help, but unless you can provide us with more information about the conditions/settings your app is using in PB 12.5 and what you're attempting to use in PB 2019 R3 (which I believe you've sort of done, except for the confusion about the DBMS property I described above), we have insufficient information to assist. This is why Chris has asked for more details on the SQL Server side and I'm asking for more details on the transaction object side.

John

Comment
  1. Armeen Mazda @Appeon
  2. Tuesday, 13 July 2021 00:46 AM UTC
There is no problem but the features are capped at what was available in SQL Server 2012 because that is last Microsoft enhanced this client with new features. Take a look at this short presentation: https://youtu.be/tSaUqXHQ85Y?t=215
  1. Helpful
  1. John Fauss
  2. Tuesday, 13 July 2021 04:56 AM UTC
Thank you for responding, Armeen... I had not yet seen Raymond's follow-up and, as always, you are spot on!
  1. Helpful
  1. Armeen Mazda @Appeon
  2. Tuesday, 13 July 2021 06:33 AM UTC
You're very welcome!
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Thursday, 4 February 2021 09:31 AM UTC
  2. PowerBuilder
  3. # 3

Hi Feng,

 

Firstly, in PB IDE, use MSOLEDBSQL SQL Server to connect to your database as shown below.

Secondly, modify the code in your program to make sure it can connect to the database using this drive.

Finally, run your application and see if it is normal.

 

If you encounter a datastore error/crash during retrieve, please go to PB IDE, use MSOLEDBSQL SQL Server to connect to the database, use the same SQL to recreate a DataWindow to see if this new DataWindow has the issue.

 

If the issue always exists, I suggest you submit a ticket in https://www.appeon.com/standardsupport/newbug along with a reproducible case (including PBL, Tables Syntax, etc.) for us. Thanks in advance.

 

 

Regards,

Ken

 

Comment
There are no comments made yet.
Feng Eric Accepted Answer Pending Moderation
  1. Friday, 5 February 2021 05:25 AM UTC
  2. PowerBuilder
  3. # 4

Hi Ken,

Thanks for your reminder, I use the same SQL to recreate the DataWindow in PB 2019R3, it works now!


But I encountered another issue, we need to click the "SAVE" button twice to save a session, I traced the code and found the relevant transaction cannot begin after clicked the "SAVE" button for the first time.  could you please give me some suggestion for this issue?

SQLErrText as following: 

SQLSTATE = 42000
Microsoft OLE DB Driver for SQL Server
A transaction started in a MARS batch is still active at the end of the batch. The transaction will be rolled back.

 

connection string  is used as following:

DBMS="MSOLEDBSQL SQL Server"
DBParm = "PROVIDER='MSOLEDBSQL',DATASOURCE='ERIC-PC',PROVIDERSTRING='DATABASE=Testing_Dev_9_0',PBTrimCharColumns='Yes',IntegratedSecurity='SSPI'"

 

Thanks a lot,
Eric