Bug 4416

MSOLEDBSQL datetime parameter issue 26 September, 2020

Darren Longenecker
14 April, 2020
Product: PowerBuilder Category: Database
Version: 2019 R2 Build:
Classification: Enhancement Publishing: Public
Priority: P3
Status: Closed Reason: FIXED
Ken Guo @Appeon 26 September, 2020
#18
Hi Darren,

OK. We will close this ticket! 
If you have any further question, please open a new ticket.

Regards,
Ken
Darren Longenecker 25 September, 2020
#17
(In reply to Ken Guo @Appeon from comment #16)
Hi Darren,

Glad to hear that. Can we close this ticket?

Regards,
Ken
Yes and thx. Looking forward to R3.
Ken Guo @Appeon 25 September, 2020
#16
Hi Darren,

Glad to hear that. Can we close this ticket?

Regards,
Ken
Darren Longenecker 24 September, 2020
#15
(In reply to Ken Guo @Appeon from comment #14)
msoledbsql dbprofile

Hi Darren,

I suggest you use a clean machine with MS OLE DB Driver and PB 2019 R3 Beta
installed and then verify it using PB 2019 R3 Beta IDE.
See the attachment for details.

Additionally, it is suggested you disable TLS 1.2 at first because if you
enable it, you should do some other configurations. 


Regards,
Ken
Ok, happy to say it works using all of your instructions. (clean pc, etc).
Ken Guo @Appeon 14 September, 2020
#14
msoledbsql dbprofile

Hi Darren,

I suggest you use a clean machine with MS OLE DB Driver and PB 2019 R3 Beta installed and then verify it using PB 2019 R3 Beta IDE.
See the attachment for details.

Additionally, it is suggested you disable TLS 1.2 at first because if you enable it, you should do some other configurations. 


Regards,
Ken
Darren Longenecker 14 September, 2020
#13
(In reply to Ken Guo @Appeon from comment #12)
Notes: Please use SQLCA.DBMS = "MSOLEDBSQL SQL Server" in PB 2019 R3 Beta。
Having an issue setting the DBMS. I get a server authentication error: "Reason: DBMS MSOLEDBSQL SQL Server is not supported in your current installation." I also tried just "MSOLEDBSQL" w/o "SQL Server" but got same error. The original DBMS was set to "OLE DB" and connects ok using MSOLEDBSQL as the Provider. I went ahead and created a new "Database Profile Setup" in PB using MSOLEDBSQL as the Provider to see what PB would put in as the DBMS and in the Preview tab, PB had auto-set the DBMS to "OLE DB". NOTE: Using my original connection of having "OLE DB" as DBMS and Provider=MSOLEDBSQL on this new version of PB19 R3 still produces original error.
Ken Guo @Appeon 14 September, 2020
#12
Notes: Please use SQLCA.DBMS = "MSOLEDBSQL SQL Server" in PB 2019 R3 Beta。
Ken Guo @Appeon 14 September, 2020
#11
Hi Darren,

We are glad to inform you that PowerBuilder 2019 R3 Beta is available for your evaluation. The bug or enhancement you reported in this ticket has been fixed or implemented in this release. Please download and install it to verify it on your side and let us know your feedback soon!

You can find the Beta installer, release notes, and more about this release from the link below. (You need to sign in with your Appeon account)
https://account.appeon.com/download/beta

Warnings: 
1. Do not use a Beta release for production use. 
2. PowerBuilder 2l019 R3 cannot coexist with PowerBuilder 2019 or PowerBuilder 2019 R2. It will upgrade/overwrite these versions. We suggest that you install it on a separate machine or a virtual machine for your test. 

Regards,
Ken
Ken Guo @Appeon 14 September, 2020
#10
Hi Tobias,

We are glad to inform you that PowerBuilder 2019 R3 Beta is available for your evaluation. The bug or enhancement you reported in this ticket has been fixed or implemented in this release. Please download and install it to verify it on your side and let us know your feedback soon!

You can find the Beta installer, release notes, and more about this release from the link below. (You need to sign in with your Appeon account)
https://account.appeon.com/download/beta

Warnings: 
1. Do not use a Beta release for production use. 
2. PowerBuilder 2l019 R3 cannot coexist with PowerBuilder 2019 or PowerBuilder 2019 R2. It will upgrade/overwrite these versions. We suggest that you install it on a separate machine or a virtual machine for your test. 

Regards,
Ken
Ken Guo @Appeon 08 June, 2020
#9
Hi Darren,

PB doesn’t support TLS 1.2 and MSOLEDBSQL for SQL Server at present. These are the new requirements. Currently the development team is researching and analyzing them and trying to implement them in PB 2019 R3. 
I will escalate your issue as a new requirement to the product team for consideration as well.


Regards,
Ken
Darren Longenecker 05 June, 2020
#8
(In reply to Ken Guo @Appeon from comment #7)
Hi Darren,

I can reproduce the issue. In the meantime, I will escalate this issue to
our development team for further analysis.
At present, if you use SQLCA.DBMS = "OLE DB", I suggest you:
1. Set PROVIDER='SQLOLEDB'.
2. Or use SQLCA.DBMS = "SNC SQL Native Client(OLE DB)" directly.


Regards,
Ken
Thank You. We were using OLEDB but our clients are moving to use tls1.2 which won't work w/ OLEDB as it is not supported (handshake error). So, we're forced to use MSOLEDBSQL or SNC, --both of which generates the DBPARAMBINDINFO error whenever PB passes a datetime w/ milliseconds to a proc. Thx -D
Ken Guo @Appeon 05 June, 2020
#7
Hi Darren,

I can reproduce the issue. In the meantime, I will escalate this issue to our development team for further analysis.
At present, if you use SQLCA.DBMS = "OLE DB", I suggest you:
1. Set PROVIDER='SQLOLEDB'.
2. Or use SQLCA.DBMS = "SNC SQL Native Client(OLE DB)" directly.


Regards,
Ken
Darren Longenecker 04 June, 2020
#6
dev1.pbl (280KB)

(In reply to Ken Guo @Appeon from comment #5)
Hi Darren,

Due to that I haven’t been able to reproduce the issue locally using
SQLNCLI11, could you please provide a complete small case including PBL, DB
Table Syntax, etc. for us?


Regards,
Ken
dev1.pbl attached We use a transaction object and declare our procs as local external functions. then use SQLCA.exampleproc(parms) --Create a test table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MyTable]( [MyID] [int] NULL, [MyDateTime] [datetime] NULL ) ON [PRIMARY] GO --Create test proc: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= create PROCEDURE [dbo].[MyProcedure] @MyID int, @MyDateTime datetime AS BEGIN INSERT INTO MyTable SELECT @MyID, @MyDateTime END GO
Ken Guo @Appeon 04 June, 2020
#5
Hi Darren,

Due to that I haven’t been able to reproduce the issue locally using SQLNCLI11, could you please provide a complete small case including PBL, DB Table Syntax, etc. for us?


Regards,
Ken
Darren Longenecker 15 April, 2020
#4
(In reply to Ken Guo @Appeon from comment #3)
Hi Darren,

Thanks for reporting this problem.
PB doesn’t support MSOLEDBSQL at present. Supporting MSOLEDBSQL is a new
requirement for PB 2019 R3 as well as supporting SQL Server TLS 1.2.

In order for PB to better support MSOLEDBSQL, please feel free to let us
know if you encounter any issues when using MSOLEDBSQL. Thanks in advance.


Regards,
Ken
Thx Ken. Could you also confirm the same for SQLNCLI11? Because I have the same issue w/ this as well.
Ken Guo @Appeon 15 April, 2020
#3
Hi Darren,

Thanks for reporting this problem.
PB doesn’t support MSOLEDBSQL at present. Supporting MSOLEDBSQL is a new requirement for PB 2019 R3 as well as supporting SQL Server TLS 1.2.

In order for PB to better support MSOLEDBSQL, please feel free to let us know if you encounter any issues when using MSOLEDBSQL. Thanks in advance.


Regards,
Ken
Darren Longenecker 14 April, 2020
#2
IDK if this helps any, but just noticed that this problem does NOT affect update statements using SQLCA in my code. i.e.:

ls_notes = THIS.Object.Text
ldt_TodayNow = DateTime( Today(), Now() )

UPDATE Instructions 
   SET Data = :ls_notes,
       LastMod = :ldt_TodayNow
WHERE DataInstructions = :id_instructionid;

This appears to have updated both fields fine when run, no errors.
Chris Pollach @Appeon 14 April, 2020
#1
Hi Darren;

  I believe that TLS 1.x support for not only SS, but other DBMS that can utilize this setting is being planned for PB2019 R3. However, I am not 100% sure. So I will defer this question over to the Engineering Team for an official answer.

  The same for the officially supported MSOLEDBSQL driver. I will now transfer this ticket over to the main Support / Engineering teams for their review & feedback on these questions.

Regards ... Chris
Darren Longenecker 14 April, 2020
As referenced here: https://community.appeon.com/index.php/qna/q-a/sqloledb-to-msoledbsql-datetime-parameter-issue

*Phenomenon:
MSOLEDBSQL conflicts w/ the Now() function in that passed datetime variables to stored proc raises the error:
SQLSTATE = 22007
Microsoft OLE DB Driver for SQL Server
The fractional part of the provided time value overflows the scale of the
corresponding SQL Server parameter or column. Increase bScale in
DBPARAMBINDINFO or column scale to correct this error.

*Reproduce Steps:
Using MSOLEDBSQL or SQLNCLI11 in SQLCA object to connect to SQL Server17

Remarks:
I have read MSOLEDBSQL is not supported by PB as of yet. Wondering on an updated timeline? and also any workarounds till then? 
Our clients are upgrading to SQL 17. We currently use SQLOLEDB which works fine except our clients are also implementing tls1.2 w/ port#'s and SQLOLEDB doesn't like like that; returns a handshake error:

oldSQLCA.dbparm = DBTextLimit = '64000', AppName = 'app', Host='mypc', PROVIDER='SQLOLEDB', 
DATASOURCE='myserver,port', ProviderString='database=mydb'

SQLSTATE = 08001
[DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).]SSL
Security error.
OS:
All
Platform:
All
Database Type:
Microsoft SQL Server
Database Version:
SQL Server 17 v13.0.4259.0