Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Problems writing date and time values in TPS files
837 views
Skip to first unread message
jaccode...@gmail.com
Mar 27, 2013, 4:09:13 AM3/27/13
to
Hi there,
I'm using BusinessObjects Dataservices to import bulk data from an oracle database into tps files.
I access the tps files with the topspeed ODBC driver. Reading the files is no problem, but writing is a problem especially date and time fields.
This is the sql statement that Business Object dataservices generates :
INSERT INTO Docent (DOCENTID, CODEFLD, UURTARIEF, ACHTERNAAM, VOORLETTERS, TUSSENVOEGSEL, STAMNUMMER, GESLACHT, GEBOORTEDATUM) VALUES (26073, 'SMIDGR', 33.27, 'Smid', 'G', NULL, 26073, 'Vrouw', '1967-05-25')
and this is the error message I get back :
[SoftVelocity Inc.][TopSpeed ODBC Driver]Invalid operand for operator: <assignment> (10024)
When I exclude the date field (geboortedatum) there is no problem. I think it has some thing to do with formatting of the date values, but I have no clue what the format needs to be. Also changing the field type is not an option, the application will not work then and I don't have access to the Clarion development environment.
I also tried this :
http://www.44342.com/clarion-f374-t4826-p1.htm
(mainly summing up the date fields) but that didn't work either.
Any help is appreciated.
Thanks in advance.
Jacco
I'm using BusinessObjects Dataservices to import bulk data from an oracle database into tps files.
I access the tps files with the topspeed ODBC driver. Reading the files is no problem, but writing is a problem especially date and time fields.
This is the sql statement that Business Object dataservices generates :
INSERT INTO Docent (DOCENTID, CODEFLD, UURTARIEF, ACHTERNAAM, VOORLETTERS, TUSSENVOEGSEL, STAMNUMMER, GESLACHT, GEBOORTEDATUM) VALUES (26073, 'SMIDGR', 33.27, 'Smid', 'G', NULL, 26073, 'Vrouw', '1967-05-25')
and this is the error message I get back :
[SoftVelocity Inc.][TopSpeed ODBC Driver]Invalid operand for operator: <assignment> (10024)
When I exclude the date field (geboortedatum) there is no problem. I think it has some thing to do with formatting of the date values, but I have no clue what the format needs to be. Also changing the field type is not an option, the application will not work then and I don't have access to the Clarion development environment.
I also tried this :
http://www.44342.com/clarion-f374-t4826-p1.htm
(mainly summing up the date fields) but that didn't work either.
Any help is appreciated.
Thanks in advance.
Jacco
Hans Kjaergaard
Mar 27, 2013, 5:45:51 AM3/27/13
to
On Wed, 27 Mar 2013 01:09:13 -0700 (PDT), jaccode...@gmail.com
wrote:
>Any help is appreciated.
What format is your time and date in?
**********Clarion time format is:
A Clarion standard time is the number of hundredths of a second that
have elapsed since midnight, plus one (1). The valid range is from 1
(defined as midnight) to 8,640,000 (defined as 11:59:59.99 PM). A
standard time of one is exactly equal to midnight to allow a zero
value to be used to detect no time entered. Although time is expressed
to the nearest hundredth of a second, the system clock is only updated
18.2 times a second (approximately every 5.5 hundredths of a second).
The LONG data type with a time format (@T) display picture is normally
used for a standard time. The TIME data type is a data format used in
the Btrieve Record Manager. A TIME field is internally converted to
LONG containing the Clarion standard time before any mathematical or
time procedure operation is performed. Therefore, TIME should be used
for external Btrieve file compatibility, and LONG should normally be
used for other times.
**********Clarion date format is:
A Clarion standard date is the number of days that have elapsed since
December 28, 1800. The range of accessible dates is from January 1,
1801 (standard date 4) to December 31, 9999 (standard date 2,994,626).
Date procedures will not return correct values outside the limits of
this range. The standard date calendar also adjusts for each leap year
within the range of accessible dates. Dividing a standard date by
modulo 7 gives you the day of the week: zero = Sunday, one = Monday,
etc.
The LONG data type with a date format (@D) display picture is normally
used for a standard date. Data entry into any date format picture with
a two-digit year defaults to the century of next 20 or previous 80
years. For example, entering 01/01/01 results in 01/01/2001 if the
current year (per the system clock) is greater than 1980, and
01/01/1901 if the current year is 1980 or earlier.
The DATE data type is a data format used in the Btrieve Record Manager
and some other file systems. A DATE field is internally converted to
LONG containing the Clarion standard date before any mathematical or
date procedure operation is performed. Therefore, DATE should be used
for external file compatibility, and LONG is normally used for other
dates.
/Hans
wrote:
>Any help is appreciated.
What format is your time and date in?
**********Clarion time format is:
A Clarion standard time is the number of hundredths of a second that
have elapsed since midnight, plus one (1). The valid range is from 1
(defined as midnight) to 8,640,000 (defined as 11:59:59.99 PM). A
standard time of one is exactly equal to midnight to allow a zero
value to be used to detect no time entered. Although time is expressed
to the nearest hundredth of a second, the system clock is only updated
18.2 times a second (approximately every 5.5 hundredths of a second).
The LONG data type with a time format (@T) display picture is normally
used for a standard time. The TIME data type is a data format used in
the Btrieve Record Manager. A TIME field is internally converted to
LONG containing the Clarion standard time before any mathematical or
time procedure operation is performed. Therefore, TIME should be used
for external Btrieve file compatibility, and LONG should normally be
used for other times.
**********Clarion date format is:
A Clarion standard date is the number of days that have elapsed since
December 28, 1800. The range of accessible dates is from January 1,
1801 (standard date 4) to December 31, 9999 (standard date 2,994,626).
Date procedures will not return correct values outside the limits of
this range. The standard date calendar also adjusts for each leap year
within the range of accessible dates. Dividing a standard date by
modulo 7 gives you the day of the week: zero = Sunday, one = Monday,
etc.
The LONG data type with a date format (@D) display picture is normally
used for a standard date. Data entry into any date format picture with
a two-digit year defaults to the century of next 20 or previous 80
years. For example, entering 01/01/01 results in 01/01/2001 if the
current year (per the system clock) is greater than 1980, and
01/01/1901 if the current year is 1980 or earlier.
The DATE data type is a data format used in the Btrieve Record Manager
and some other file systems. A DATE field is internally converted to
LONG containing the Clarion standard date before any mathematical or
date procedure operation is performed. Therefore, DATE should be used
for external file compatibility, and LONG is normally used for other
dates.
/Hans
Shandin
Apr 1, 2013, 2:51:03 AM4/1/13
to
I use PostgreSQL, but maybe my experience will help You.
About drivers:
In Clarion dictionary for Clarion TPS tabele I use TPS database
driver.
For PostgreSQL I've installed PostgreSQL's ODBC driver (!) (which is
not Clarion's ODBC driver)
and in Clarion dictionary, for SQL table, I use ODBC database driver.
About date and time value:
in SQL table in Clarion DCT, MyDateAndTime field (for example) is
defined as String(8).
Same field on SQL server side is defined as "timestamp".
In addition for SQL table in Clarion dictionary, a Group type field is
defined MyDateAndTimeGroup (for example)
which consists from two fields:
MyDate (Date)
MyTime (Time)
MyDateAndTimeGroup is placed over MyDateAndTime and is not defined on
SQL server side. It just share the same memory space.
For accessing date and time I use MyDate and MyTime in Clarion code.
Hope it helps.
Zoran.
0 new messages