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

Resolved MSOLEDBSQL in PB 2019R3

  1. Issue
0
Votes
Undo
  1. Feng Eric
    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

 

  1. MSOLEDBSQL
Why do I keep getting a DB error on an Insert stat...
Datawindow Field Font Size depending on the number...
Responses (6)
Chris Pollach @Appeon
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
Feng Eric
  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
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
Load more comments
Armeen Mazda @Appeon
  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
John Fauss
  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
Armeen Mazda @Appeon
  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
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
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

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 8 February 2021 21:43 PM UTC
Hi Eric;

Yes, I see your ticket #6036 and the technical group there also recommended trying the MARS setting ...

SQLCA.DBParm = "Database='master',ProviderString='MARS Connection=False' "

It's a tricky setting because you need to get it in the long DBParm string with all the correct commas and single quotes in *exactly* the correct place.

Regards ... Chris
  1. Helpful 1
Brett Weaver
  1. Brett Weaver
  2. Sunday, 1 October 2023 22:02 PM UTC
Chris - This was so much help! Thanks!
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 1 October 2023 22:23 PM UTC
My pleasure Brett!
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Monday, 22 February 2021 08:35 AM UTC
  2. PowerBuilder
  3. # 5

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.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 2 October 2023 21:41 PM UTC
  2. PowerBuilder
  3. # 6

Maybe not very helpful, but I'm starting to believe there's some kind of a bug when disablebind <> 1. (and I'm using Oracle).

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.