• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How do people work around datetime2 data type in MSSQL?

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

Resolved How do people work around datetime2 data type in MSSQL?

  1. How-to
0
Votes
Undo
  1. James Hsu
    James Hsu
  2. PowerBuilder
  3. Wednesday, 4 November 2020 00:06 AM UTC

We are currently working on migrating an application developed in PB 11.5 to PB 2019 R2.  For about 80% of the functionalities the migration went fine, but for the 20% where we use the datawindow to update the database gives us the following error when inserting into a datetime2 column:

SQLSTATE: 22008 [Microsoft][ODBC Driver 11 for SQL Server] Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

I know that PB is not compatible with datetime2 data type in MSSQL, but we do not want to change all of our datetime2 columns to datetime.  I saw a suggestion for casting datetime2 into a string for PB app to use and pass it into a stored procedure where it would handle the passed in string and insert into datetime2 column natively.  However, this insert is a part of a transaction involving updates to multiple tables and with this method rollback from PB would not work.  What do people typically do to get around this limitation?

Sincerely,

James

  1. Migration
  2. PowerBuilder (Appeon)
Chart Pro from Ultimate Suite
Powertools framework from a company called Powerce...
Who is viewing this page
Aliaksei Hlinski
Responses (3)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 18 November 2020 19:28 PM UTC
  2. PowerBuilder
  3. # 1

A documented limitation of PB's support for the Datetime2 datatype in SQL Server is that precision of fractional seconds is limited to six digits:

https://docs.appeon.com/pb2019r2/connecting_to_your_database/ch10s07.html

 

Comment
There are no comments made yet.
James Hsu
James Hsu Accepted Answer Pending Moderation
  1. Wednesday, 18 November 2020 18:15 PM UTC
  2. PowerBuilder
  3. # 2

Hi, it appears Execute immediate is not possible with a prepared statement, giving me a syntax error.

For example:

PREPARE SQLSA FROM "INSERT INTO TABLE_A (COL_A, COL_B, COL_C, COL_D) VALUES (?,?,?,?)";

EXECUTE SQLSA USING :ls_id, :ls_term, :lr_amt, :ldt_date;

gives no errors when pre-compiling, but:

PREPARE SQLSA FROM "INSERT INTO TABLE_A (COL_A, COL_B, COL_C, COL_D) VALUES (?,?,?,?)";

EXECUTE IMMEDIATE SQLSA USING :ls_id, :ls_term, :lr_amt, :ldt_date;

gives a syntax error.

I've looked at PowerScript reference guide for SQL statements, but it doesn't seem straight forward for using execute immediate for insert statement.

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 18 November 2020 19:36 PM UTC
Hi James;

You would need to keep the DT2 data in String format within your PB App. Then on an Update or Insert DML command , for example (hard coded) ...

=> CAST (''2020-05-25 019:39:38.007' AS datetime2) // OR

=> CONVERT(datetime2, '19:39:38.007' , 103)

So you could use either SS function and then pass in the data from a PB String variable for the Cast() or Convert() method

HTH

Regards ... Chris

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 4 November 2020 00:16 AM UTC
  2. PowerBuilder
  3. # 3

Hi James;

  You should be able to do an update to a DT2 column on the PB App side via an in-line "Execute Immediate" SQL command.

Regards ... Chris

Comment
James Hsu
  1. James Hsu
  2. Tuesday, 15 December 2020 19:15 PM UTC
Thanks for your comment, I did some trial and error, and although your suggestion worked, I ended up altering the table columns to datetime, because using update() was essentially without re-writing a big chunk of code.
  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.