• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • 6-digit milliseconds on insert: quick fix on the server side?

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

Resolved 6-digit milliseconds on insert: quick fix on the server side?

  1. How-to
0
Votes
Undo
  1. Scott Gorke
    Scott Gorke
  2. PowerBuilder
  3. Thursday, 23 June 2022 15:40 PM UTC

Hi,

 

Powerbuilder 2019 R3

MS SQL Server

INI file excerpt:

[SQLServer]
LogID=<SQLuser>
Logpass=<SQLpassword>

[Database]
DBMS=SNC SQL Native Client(OLE DB)
Database=<DBname>
ServerName=<SQL server instance>
DBParm=Provider='SQLNCLI10',Database='<DBname>',DBTextLimit='32766', ProviderString='OLE DB Services=-4; MARS Connection=False', Identity='SCOPE_IDENTITY()', TRIMSPACES=1, RecheckRows=1

 

We have run into instances in which PB and/or SQL Native Client tries to insert a row into a table - the record includes a datetime column, that for some reason sets the datetime with a 6-digit millisecond. (EX: insert into mytable (col1, col2) values (<ID value>, '2022-06-23 12:34:56:357777').

 

If the datetime only has 3 digit milliseconds, no problem.

 

We could write a PB fix to truncate the milliseconds down to 3 digits, but that would require compiling the app, testing, delivering, etc. which would take weeks. To save time, is there a setting within MS SQL Server to automatically truncate the milliseconds as we wish?

 

Thanks in advance,
Scott

Does any online formatter works for PowerScript?
RTE right margin setting doesn't save
Accepted Answer
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 23 June 2022 18:31 PM UTC
  2. PowerBuilder
  3. # Permalink

Maybe you can set the datetimeformat in the SQLCA dbparm to be limited to fff (3 miliseconds)?

regards.

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 24 June 2022 04:51 AM UTC
In Oracle you can do an ALTER SESSION to set the timstamp and date(time) precision for milliseconds. I don't know if there's something similar for sql server.
  1. Helpful
Scott Gorke
  1. Scott Gorke
  2. Wednesday, 17 August 2022 14:15 PM UTC
Thanks Miguel. The SQLCA DBParm solution was considered, but because a client was complaining about the issue, we wrote a fix in the Powerbuilder code to truncate the milliseconds to three digits. For the unhappy client, this was the fastest fix (our app already trims to 3-digit milliseconds in several other areas, and our client found 2 other areas that we missed.).
  1. Helpful
There are no comments made yet.
Responses (3)
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 24 June 2022 17:25 PM UTC
  2. PowerBuilder
  3. # 1

In SQL Server DATETIME is 3 digits while DATETIME2 is 7 digits.

The PowerScript data types Time & DateTime has 6 digits. I'm guessing the DataWindow columns are the same.

The PowerScript Now() function only returns 3 digits.

The quick fix would be to change DATETIME2 columns to DATETIME2(3).

 

Comment
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 24 June 2022 17:35 PM UTC
  2. PowerBuilder
  3. # 2

I submitted a bug:

https://www.appeon.com/standardsupport/track/view?id=8603

 

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.