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

 

Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Monday, 22 February 2021 08:35 AM UTC
  2. PowerBuilder
  3. # 1
0
Votes
Undo

Hi Feng,

I suggest you disable MARS in DBParm and then try again, for example:
SQLCA.DBParm = "Database='master',ProviderString='MARS Connection=False'"


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. # 2
0
Votes
Undo

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