Date issue

[stecosta66]

Hi All

using X# 2.21 and XS2ADO 5.0.6 to connect to a Postgres 17 Database

I've a tight loop used to populate a bArrayServer from an AdoRecordset and I'm facing a little issue with date fields.

When a date is retrieved, FieldType is 133 which is an AdoDataTypeEnum, as per xs2ado documentation, is "adDBDate 133 - A date value (yyyymmdd) (DBTYPE_DBDATE)."

Instead I get a date from System.DateTime that has a format of "dd/mm/yyyy 00:00:00" so it cannot be stored in the field of bArrayServer defined as type "D" (I get a blank field).

AdoSetTimeAsDate is set to TRUE.



the code looks like this:

Code: Select all

				LOCAL odbInvoicePayments AS AdoRecordset
				
				[...]
				
				SELF:oasInvoicePayments:Append()
				FOR y := 1 UPTO dwFields
					cFieldName := Lower( odbInvoicePayments:Fields:Item[ y ]:Name )	// for debugging purpose
					uFieldType := odbInvoicePayments:Fields:Item[ y ]:Type 		// for debugging purpose
					uValue := odbInvoicePayments:FIELDGET( y ) 
					SELF:oasInvoicePayments:FIELDPUT( y, uValue )
				NEXT
				SELF:oasInvoicePayments:Commit()
				
				[...]

How workaround this?

Thanks
Stefano

[stecosta66]

HI All,

following my own reply I've done some little adjustment to my code.

Added a LOCAL dDateValue AS DATE so conversion from System.DateTime is automatic

Code: Select all

	SELF:oasInvoicePayments:Append()
	FOR y := 1 UPTO dwFields
		cFName := Lower( odbInvoicePayments:Fields:Item[ y ]:Name )
		uFieldType := odbInvoicePayments:Fields:Item[ y ]:Type 
                    
		IF uFieldType == adDBDate
			dDateValue := odbInvoicePayments:FIELDGET( y ) 
			SELF:oasInvoicePayments:FIELDPUT( y, dDateValue )
                        
		ELSE
			uValue := odbInvoicePayments:FIELDGET( y ) 
			SELF:oasInvoicePayments:FIELDPUT( y, uValue )

		ENDIF
	NEXT

Issue persist instead on an AdoServer directly attached to a bBrowser that display date column as "dd/mm/yyyy 00:00:00".
In this case NO conversion is made on date field. There is any advice to avoid this?

Thanks
Stefano

[ArneOrtlinghaus]

We use Oracle which should be very similar to PostGres and we make some initialization settings when connecting to the database to be independent of user settings. Below I have put all commands although only two should be important for you.
alter session set NLS_TERRITORY = GERMANY
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SSXFF'
alter session set nls_language = english
ALTER SESSION set NLS_NUMERIC_CHARACTERS = ".,"
alter session set nls_sort = BINARY
ALTER SESSION SET recyclebin = OFF
alter session set ddl_lock_timeout = 600
alter session set session_cached_cursors=1000

Arne

[robert]

Stefano,
If you add a call to AdoDateTimeAsDate(TRUE) then DateTime values should be returned as Date.

Robert

[stecosta66]

Hi Robert,

I have AdoDateTimeAsDate(TRUE) at the start of my app. This works fine in VO + bBrowser + Vo2Ado but not in X#.
As described above when I attacch an AdoServer (Xs2Ado) directly to a bBrowser (X# edition) the date column show 'dd/mm/yyyy 00:00:00'.

VO + Vo2Ado + bBrowser


X# + Xs2Ado + bBrowser (X# edition)


same settings, same code

using X# 2.21 + Xs2Ado 5.0.6 + bBrowser X# edition 4.4.34

in both versions I'm connecting against a Postgres 17 database

Thanks
Stefano