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

Resolved SQLOLEDB to MSOLEDBSQL datetime parameter issue

  1. Issue
0
Votes
Undo
  1. Darren Longenecker
    Darren Longenecker
  2. PowerBuilder
  3. Monday, 13 April 2020 14:28 PM UTC

PB 19 Build 2082

SQL SERVER 2017

 

Upgrading our driver from SQLOLEDB to MSOLEDBSQL and now get issues w/ SQL's DBPARAMBINDINFO.

 

 

PROBLEM:

MSOLEDBSQL conflicts w/ the Now() function in that the driver only wants 3 milliseconds instead of Now's 6 milliseconds.

 

AFFECTS:

Anywhere that Now() is used to set a datetime variable that is then used to UPDATE or is sent to a stored procedure.

 

example: 

ldt_date = DateTime(Today(),Now())

--then passing ldt_date to a stored proc expecting a datetime value.

 

Causes 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.

 

 

Tried using multiple combinations to the connectionstring including:

'SQLNCLI11'

DisableBind=1

and multiple versions of:

DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\

 

 

Anyone have this problem? What am I missing?  

Thx, D

 

  1. Database Driver
  2. PowerBuilder (Appeon)
  3. SQL Server
Problem to free memory in Multithread
FileWrite 2019 R2
Responses (4)
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 13 April 2020 18:47 PM UTC
  2. PowerBuilder
  3. # 1

Maybe this will help:
https://answers.sap.com/questions/11578691/increase-bscale-in-dbparambindinfo-error-in-pb-121.html

or as a work-aropnd, this:
http://answers.flyppdevportal.com/MVC/Post/Thread/1241147c-42a1-40a5-b4c6-374374e462cd?category=transactsql

 

Good Luck,

Olan

Comment
There are no comments made yet.
Darren Longenecker
Darren Longenecker Accepted Answer Pending Moderation
  1. Monday, 13 April 2020 21:29 PM UTC
  2. PowerBuilder
  3. # 2

Thx for reply.

The first link didn't help.

The 2nd link, the workaround, was a similar example to mine.

I am using calls from a trans object: SQLCA.p_ProcedureName(parm1, parm2...)

So, I decided to do as the workaround and declare/exec the procedure instead. I then got an error from SQL saying it couldn't convert a nvarchar value to a datetime... But I'm passing in a pb datetime value, so idk.

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

This connectionstring works against SQL Server 2017 w/o tls1.2 enabled and w/o using port numbers:

DBTextLimit='64000',AppName='MyApplication,Host='MyPC',PROVIDER='SQLOLEDB',DATASOURCE='MyServer',ProviderString='database=MyDatabase'

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

We are having to support tls1.2 now and also port numbers. These changes necessitate switching to MSOLEDBSQL. This connectionstring works fine w/ tls1.2 and other db transactions EXCEPT when passing a PB datetime to a stored proc:

DBTextLimit='64000',AppName='MyApplication',Host='MyPC',PROVIDER='MSOLEDBSQL',DATASOURCE='MyServer', DisableBind=1,ProviderString='database=MyDatabase',DateTypeCompatibility=80,DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\'''

 

 

Comment
Darren Longenecker
  1. Darren Longenecker
  2. Monday, 13 April 2020 21:59 PM UTC
I would also like to add this:

I can get MSOLEDBSQL to play well w/ PB datetime but it involves stripping off the milliseconds.



As previously mentioned:

ldt_date = DateTime(Today(),Now())

This generates the DBPARAMBINDINFO error when passing ldt_date to a stored proc expecting datetime.



BUT if I strip off the milliseconds like this:

ldt_date = DateTime(Today(),Time(String(Now())))

Then ldt_date goes into the proc fine.



Problem is..

1. I pass a ton of datetime values in my code. Many places I'd have to change.

(Okay, so my workaround was to make a function to do this for me and replace Now() with f_Now_() throughout my code.)



f_Now_() simply being:

return time(string(now()))



but





2. I've happened to stumble on a few places where I get the same error where Now() is not involved.



Effectively, using f_Now_() gets rid of a ton of errors but is not ironclad. I'd prefer an ironclad solution which brings me back to fixing my connectionstring.

  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 14 April 2020 04:32 AM UTC
  2. PowerBuilder
  3. # 3

Hi Darren,

I've tried to see if I could reproduce the problem:

// Profile msoledb-test
SQLCA.DBMS = "OLE DB"
SQLCA.LogPass = "somepassword"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False
// SQLCA.DBParm = "PROVIDER='MSOLEDBSQL',DATASOURCE='DELL-XPS8300\sqlexpress',DateFormat=''\''dd-mm-yyyy\'''',DateTimeFormat=''\''dd-mm-yyyy hh:mm:ss.fff\'''',TimeFormat=''\'hh:mm:ss.fff\'''',PBCatalogOwner='dbo',DelimitIdentifier='Yes',PROVIDERSTRING='database=test-miguel'"
SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='DELL-XPS8300\sqlexpress',DateFormat=''\''dd-mm-yyyy\'''',DateTimeFormat=''\''dd-mm-yyyy hh:mm:ss.fff\'''',TimeFormat=''\'hh:mm:ss.fff\'''',PBCatalogOwner='dbo',DelimitIdentifier='Yes',PROVIDERSTRING='database=test-miguel'"

The version pasted above works fine, but ... when using MSOLEDBSQL (the commented line), I get exactly the same problem as you have.

Could this not simply be a powerbuilder or microsoft bug? Armeen Mazda also says in a recent post that "We are working on a "new" MSOLEDBSQL driver interface for SQL Server as migration path for existing users of the native client, but it won't be available until the next version.  So given the timing of your project, this may not be an option. " https://community.appeon.com/index.php/qna/q-a/ms-sql-server-2017-use-ado-net-odbc-or-jdbc

I've seen the error reported for C# and there the solution is to use some "OleDBDatetime" instead of Datetime (or something similar, can't remember right now). Of course, in Powerbuilder we only have "DateTime".

Sorry I can't help you out any other way.

regards

Comment
Load more comments
Darren Longenecker
  1. Darren Longenecker
  2. Tuesday, 14 April 2020 15:09 PM UTC
Do I take this to standard technical support?
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 14 April 2020 15:35 PM UTC
yes that's a good idea, let's see what they say. For now we only know that Armeen has said that they are working on a new ms driver but that it won't be out until the next release. Maybe they can tell us if it's any use to keep trying or not.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 14 April 2020 15:36 PM UTC
Maybe there's other drivers you could use which support tls 1.2?
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 April 2020 09:14 AM UTC
  2. PowerBuilder
  3. # 4

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

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 15 April 2020 14:44 PM UTC
Please open support tickets for issues you find with MSOLEDBSQL so we can verify is addressed when this feature is really supported.
  1. Helpful
Ken Guo @Appeon
  1. Ken Guo @Appeon
  2. Thursday, 16 April 2020 08:16 AM UTC
Yes, in order to better support MSOLESQLDB, we will collect all issues related to MSOLESQLDB, including those from the support ticket system, emails, Community, etc. and submit them for the development team for analysis.
  1. Helpful
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.