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

Resolved Time validation errors with DateTime column types

  1. New
  2. Issue
0
Votes
Undo
  1. Shenn Sellers
    Shenn Sellers
  2. PowerBuilder
  3. Thursday, 31 October 2024 17:12 PM UTC

I'm using Windows 11 and PB 2022 R3 B3391.  Database back end is Oracle 19.

I'm pretty sure this has something to do with the new compiler being used in the latest version of PB.

This always worked before.  I have a datetime column named "op_time".

In the DW painter I make the column an EditMask with "hh:mm:ss" as the mask.  Format is listed as [time].

Before you could enter the time value fine as "09:37:00" and it would automatically convert it to a datetime like "1/1/1900 09:37:00" to save in the database.  Now I get the validition error I attached.

Any ideas?  Thanks.

 

 

 

Attachments (1)
error.jpg error.jpg
ImportDatawindow using PDFDocument is returning -1...
Datawindow Image background turning to Fuchsia bac...
Responses (2)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 31 October 2024 17:27 PM UTC
  2. PowerBuilder
  3. # 1

Hi Shenn;

  Can you tell us ...

  • Are you connection to O19 using the native DB client drivers (ie: SQLCA.DBMS = "ORA"?
  • What is the exact data type of the "op_time" column in your DB schema?
  • What data type does your DWO think that the "op_time" column is?
  • What are your Date / Time "Regional Settings" set to?
  • All ANSI standard DBMS expect the DT  to be sent as CCYY-MM-DD HH:MM:SS. Is there something in your DBParm settings that is overriding PB from sending this format to O19? (Remember, the Edit Mask only handles the App users perception of DT & validates it that way externally).

Regards ... Chris 

Comment
Shenn Sellers
  1. Shenn Sellers
  2. Thursday, 31 October 2024 17:44 PM UTC
Are you connection to O19 using the native DB client drivers (ie: SQLCA.DBMS = "ORA"?

// Profile Waste Production

SQLCA.DBMS = "ORA Oracle"

SQLCA.LogPass = "*********"

SQLCA.ServerName = "******************"

SQLCA.LogId = "********"

SQLCA.AutoCommit = False

SQLCA.DBParm = "ThreadSafe='Yes',Timestamp=0,PBCatalogOwner='WASTE'"



What is the exact data type of the "op_time" column in your DB schema?

Exact datatype is "datetime".



What data type does your DWO think that the "op_time" column is?

datetime



What are your Date / Time "Regional Settings" set to?

Regional format: English (United States)

Calendar: Gregorian Calendar

First day of week: Sunday

Short date: 10/31/2024

Long date: Thursday, October 31, 2024

Short time: 10:42 AM

Long time: 10:42:07 AM

Standard digits: 0123456789



All ANSI standard DBMS expect the DT to be sent as CCYY-MM-DD HH:MM:SS. Is there something in your DBParm settings that is overriding PB from sending this format to O19? (Remember, the Edit Mask only handles the App users perception of DT & validates it that way externally).

DBParm settings: SQLCA.DBParm = "ThreadSafe='Yes',Timestamp=0,PBCatalogOwner='WASTE'"









  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 31 October 2024 18:21 PM UTC
  2. PowerBuilder
  3. # 2

Hi Shenn;

  Thanks for conforming those settings! Since you are set to US English then the DT data handling should be good from the MS-Windows / PB perspective.

I wonder if the issue might be on the O19 side.You might want to check with your DBA team to check that aspect.

  • Can you use the DB Painter's SQL Pane to test a similar SQL Update and see if it works OK there?
  • Does the DWO DT update work OK when run in the DW Painter?
  • Have you tried running an SQL trace to see what it happening DB Client handshaking wise?
  • If this is a App EXE issue, is there any SQLPreview Event code playing with the SQL leaving the App?

Regards ... Chris 

Comment
Shenn Sellers
  1. Shenn Sellers
  2. Thursday, 31 October 2024 18:55 PM UTC
Can you use the DB Painter's SQL Pane to test a similar SQL Update and see if it works OK there?

Works if I add an empty date i.e. 1/1/1900 12:32:22. Doesn't work if I just add a time i.e. 12:32:22.



Does the DWO DT update work OK when run in the DW Painter?

No, same error occurs.



Have you tried running an SQL trace to see what it happening DB Client handshaking wise?

Ran a trace and the file is full of a bunch of nulls and/or chinese looking characters.



If this is a App EXE issue, is there any SQLPreview Event code playing with the SQL leaving the App?

Nothing is in the SQLPreview Event.
  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.