• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Operand type clash: bigint is incompatible with datetime2 from PowerBuilder.

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

Resolved Operand type clash: bigint is incompatible with datetime2 from PowerBuilder.

  1. How-to
0
Votes
Undo
  1. Sathishkumar S
    Sathishkumar S
  2. PowerBuilder
  3. Monday, 20 June 2022 12:23 PM UTC

Hi Appeon,

I am currently getting an database error from PowerBuilder 2019 R3 as below, (Operand type clash: bigint is incompatible with datetime2) when doing insert or update from the store procedure in which datetime2 data type has been used for the result set table.

 

but I am not getting any error while executing the SQL Statement in SSMS

declare @p2 int
set @p2=NULL
exec dbo.usp_iu_activity N'INSERT',@p2 output,101,5,126,3,NULL,NULL,NULL,12345,12345,NULL,'2022-06-20 01:46:09.2940000'
select @p2

I would like to get your advice why I am getting this error from PB but not from SSMS.

Is it because PB 2019 R2 is not compatible with Datetime2? or is there any other reasons? or Do I have to do any setting for OLE DB profile string? 

Thanks,

Sathishkumar. 

Application crashes with Runtime 21.0.0.1509
R6025 pure virtual function call
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 20 June 2022 15:56 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Sathishkumar ;

   PB does not currently support the DT2 data type - only DateTime. The same for DT's that include a TimeZone (like in JSON).

The workaround would be to either change your SS Data type to DT or CAST the DT2 at run time into a DT data type.

FYI: https://community.appeon.com/index.php/qna/q-a/how-do-people-work-around-datetime2-data-type-in-mssql

HTH

Regards ... Chris

Comment
Load more comments
Andreas Mykonios
  1. Andreas Mykonios
  2. Tuesday, 21 June 2022 06:11 AM UTC
Hi Chris. If datetime2 isnt supported, then why it's mentioned in help https://docs.appeon.com/pb2019r2/connecting_to_your_database/ch10s07.html#XREF_42939_Support_for_new in "Support for new datatypes in SQL Server 2008" topic?

Andreas.
  1. Helpful
Sathishkumar S
  1. Sathishkumar S
  2. Tuesday, 21 June 2022 08:23 AM UTC
Hi Andreas,



I have gone through the help you have mentioned, I also got confused that's why I wrote to Appeon.



Regards,

Sathishkumar.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 21 June 2022 14:47 PM UTC
Hi Andreas,

It's clearly stated in the help .. "PB Supports only up to 6 fractional seconds precision although SQL Server datatype supports up to 7 fractional seconds of precision".

Thus the "not fully supported" remark that I mentioned earlier. You have to either accept the loss of significant digits or depending on your DML actions, CAST the DT2 appropriately. A PIA I must admit.

PS: The same with TimeZone based DT.

Regards .. Chris







Regards,
  1. Helpful
There are no comments made yet.
Responses (4)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Monday, 20 June 2022 14:16 PM UTC
  2. PowerBuilder
  3. # 1

I don't know if this is relevant. In the following link, Database parameters and supported database interfaces - - Connection Reference (appeon.com) I do read:

DateTimeFormat

Description

When you update data in the DataWindow painter, PowerBuilder builds a SQL UPDATE statement in the background. The DateTimeFormat parameter determines how PowerBuilder specifies a DateTime datatype when it builds the SQL UPDATE statement. (A DateTime datatype contains both a date value and a time value.)

Applies to

ADO.NET

OLE DB

SNC SQL Native Client for Microsoft SQL Server

Syntax

DateTimeFormat='datetime_format'

 

Parameter

Description

datetime_format

The datetime format you want PowerBuilder to use when it builds a SQL UPDATE statement to update a data source in the DataWindow painter.

For more on display formats, see the the section called “Defining display formats” in Users Guide.

 

Default value

If no value is specified for the DateTimeFormat parameter, PowerBuilder does not use a datetime datatype.

Usage

When you call stored procedures, the database server might not accept the DateTime format built by PowerBuilder. If this occurs, you can try to use another format. For example, for Microsoft SQL Server, try this format:

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

PowerBuilder parses the backslash followed by two single quotes (\'') as a single quote.

Comment
There are no comments made yet.
Markus Eckert
Markus Eckert Accepted Answer Pending Moderation
  1. Monday, 20 June 2022 12:56 PM UTC
  2. PowerBuilder
  3. # 2

The SQL error message is most likely caused by one of two things within your stored procedure:

  • a BIGINT value was assigned to a DATETIME2 variable/column
  • a BIGINT value was added to a DATETIME2 variable/column

Both of these things were valid with variables/columns of type DATETIME/SMALLDATETIME, but are no longer valid with the newer types like DATE or DATETIME2.

i.e. those three statements are only valid SQL if @value is of type DATETIME/SMALLDATETIME, not if it's of type DATE/DATETIME2

SET @value = 15 
SET @value = @value + 7 
SET @value = @value + @value

 

As for why it doesn't occur within SSMS, that's hard to speculate on without knowing the procedure or at least its parameters.

Comment
Load more comments
Roland Smith
  1. Roland Smith
  2. Monday, 20 June 2022 14:19 PM UTC
DateTime2 has 7 digits sub second whereas the PowerBuilder & SQL Server DateTime types have only 3. It seems to me that you are getting a data overflow.

In the procedure try declaring the local variables with (3) instead of (7).
  1. Helpful
Mark Goldsmith
  1. Mark Goldsmith
  2. Monday, 20 June 2022 14:40 PM UTC
Actually I believe the PB DateTime and Time data types support up to 6 sub-second digits and so maybe try declaring the stored procedure datetime variables with 6 instead of 7.
  1. Helpful
John Fauss
  1. John Fauss
  2. Monday, 20 June 2022 15:12 PM UTC
Sathishkumar - My experience using SQL Server for the last 12 years has shown me that SMSS may automatically perform "behind-the-scenes" datatype conversions. You should not assume that SQL executed from PB will do so cleanly just because the same statement(s) execute cleanly in SMSS. FWIW, we completely avoid using the SQL Server datetime2 datatype, just because of the potential for issues such as what you are experiencing.
  1. Helpful
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Monday, 20 June 2022 12:45 PM UTC
  2. PowerBuilder
  3. # 3

Hi.

Can you check the value of the last param powerbuilder sends to the procedure (the datetime...)?

Andreas.

Comment
Load more comments
Andreas Mykonios
  1. Andreas Mykonios
  2. Monday, 20 June 2022 13:53 PM UTC
Datetime2 is supported from PB 2019 R2 (https://docs.appeon.com/pb2019r2/connecting_to_your_database/ch10s07.html#XREF_42939_Support_for_new). What interface are you using to connect to SQL Server?

Andreas.
  1. Helpful
Sathishkumar S
  1. Sathishkumar S
  2. Monday, 20 June 2022 13:59 PM UTC
I am using MSOLEDBSQL SQL SERVER version 18.3



DBParm= "Database='DB',DelimitIdentifier=1,DelimitIdentifierToDB='Yes',RecheckRows=1"



Thank you,

Sathishkumar.
  1. Helpful
Andreas Mykonios
  1. Andreas Mykonios
  2. Monday, 20 June 2022 14:07 PM UTC
Can you test it with native client? I do that was the drive you should use in 2019 R2. But I'm not 100% sure.

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