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

Resolved Time validation errors with DateTime column types

  1. 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
Can I open two windows side by side?
App launcher power client
Accepted Answer
Shenn Sellers
Shenn Sellers Accepted Answer Pending Moderation
  1. Wednesday, 6 November 2024 19:22 PM UTC
  2. PowerBuilder
  3. # Permalink

I figured out the issue, it was a pb.ini setting.  I had copied the pb.ini file from the STF FC by Chris Pollach figuring he would know the best settings to use.  This line was causing the issue.

[Edit Mask Behaviors]
AutocompleteDates=no

Once I took this out it worked fine.  Why would you not want the dates to auto complete?

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 6 November 2024 19:42 PM UTC
Thanks for sharing the solution Shenn!
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 6 November 2024 19:53 PM UTC
Hi Shenn;

Note that the PB.ini in the STD FC & its Demo Apps is only an *example* (with comments) on other "possible" settings that you "might" want to consider to use and *not* necessarily what you should be using. That PB.ini should always come from the PB IDE installation ( basically a "vanilla" settings copy) then amend as required. HTH ;-)

Regards .. Chris

  1. Helpful
There are no comments made yet.
Responses (3)
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
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 1 November 2024 15:07 PM UTC
Hi Shenn;

I just created a simple test case in my Oracle test DBMS this AM with tables that have the Date data type. Then created a few test DWO's around those tables. The DWO's initially worked 100%. Then I modified the Date columns (shown as DateTime data type in the DWOs) to add an Edit Mask to each as you did. Again, no updating issues when using PB 2022 R3 build 3391 test DWO's.

So I am not sure what your specific issue within your environment is. Hopefully, an Oracle Guru (which I am not) can help you further with this weird update error situation.

Regards .. Chris
  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.