Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

ADO Ms Access date\time

208 views
Skip to first unread message

Rick Lipkin

unread,
Feb 7, 2012, 6:00:42 PM2/7/12
to
To All

Just upgraded to xHarbour 1.21 rev 9421 and when I assign a variable
to a Ms Access Date\Time field the ValType returns (T) ime ..

Here is the error log :

Application
===========
Path and name: C:\Fox\PMOSQL-N\pmow32.Exe (32 bits)
Size: 3,553,792 bytes
Compiler version: xHarbour build 1.2.1 Intl. (SimpLex) (Rev. 9421)
FiveWin Version: FWHX 11.12
Windows version: 5.1, Build 2600 Service Pack 3

Time from start: 0 hours 0 mins 30 secs
Error occurred at: 02/07/2012, 15:51:34
Error description: Error BASE/1075 Argument error: >
Args:
[ 1] = T 12/30/1899 00:00:00.000
[ 2] = D 01/20/2023

The first value is oRs:Field("Start_date"):Value ( 2/7/2012 in the
Access table ) and returns the above [ 1] = T 12/30/1899
00:00:00.000.

The second value was a variable assigned to Date() .. As you can see
when I compare the values a run-time error occurs.

This is a program that has been working fine for a long time ..
nothing has changed but xHarbour.

Any advice would be appreciated.

Rick Lipkin

Andi Jahja

unread,
Feb 8, 2012, 12:20:42 AM2/8/12
to
Hi Rick:

It's a known problem since "T" was introduced to xHarbour. Following
is my notes to developer list some time ago:

-- quote --
Date: Tue, 15 Nov 2011 08:17:42 +0700
From: Andi Jahja

> Please let us know which code if any will require addtional work.
> Walter, Andi, ..., any input?

IMHO, the following areas need urgent reviews:

1. RDD
2. RTL (incl. CT, which is an integrel part in xHarbour)

..........
-- unquote--

I believe the error you highlighted relates to RDD. Woud you please
post a self-contained program to demonstrate the error? We might as
well make a corresponding correction.

Andi

Rick Lipkin

unread,
Feb 8, 2012, 10:07:49 AM2/8/12
to
Andi

Compile this program with the current xHarbour and you will see the
error on the date comparisons ..

Rick Lipkin

// AccessDB.prg
// creating an access database from code
// showing how date time field translates incorrectly


STATIC lOK

//-------------

Local catNewDB,xProvider,xConnect,cFile,aDir,dExe,cDefa,mStart
Local oCn,cSql,oErr,oRsUser,cLOGIN,Saying
Local cTitle,oWnd1,oBrw,oCol,nYear,dCreatedate

setmode( 25, 80 )

lOK := .F.

//-- get timestamp on .exe //

cFILE := HB_ARGV(0)
aDIR := DIRECTORY( cFILE )

// where .exe started from is default directory //

mSTART := RAT( "\", cFILE )
cDEFA := SUBSTR(cFILE,1,mSTART-1)

aDIR := NIL
SET DEFA to ( cDEFA )

SET DELETED on
SET CENTURY on


nYEAR := ( year( DATE() )-30 )
SET EPOCH to ( nYEAR )

Ferase( cDefa+"\Rick.mdb" )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Rick.mdb"
xPASSWORD := "dec2011"

// create the adox object
Try
catNewDB := CreateObject("ADOX.Catalog")
Catch
? "Could not Create ADOX object"
wait
Return(.f.)
End try

// create the table Rick.mdb
Try
catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet
OLEDB:Engine Type=5;Jet OLEDB:Database Password='+xPASSWORD )
Catch
? "Could not create the table "+xSource
wait
Return(.f.)
End Try


// global connection string
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet
OLEDB:Database Password='+xPASSWORD

Try
oCn := CREATEOBJECT( "ADODB.Connection" )
Catch
? "Could not create the ADO object for connection"
wait
End Try

TRY
oCn:Open( xCONNECT )
CATCH oErr
? "Could not open a Connection to Database "+xSource
wait
RETURN(.F.)
END TRY


cSQL := "CREATE TABLE USERINFO"
cSQL += "( "
cSQL += "[USEREID] char(18) NOT NULL, "
cSQL += "[USERID] char(8) NULL, "
cSQL += "[READONLY] char(1) NULL, "
cSQL += "[WRITEONLY] char(1) NULL, "
cSQL += "[SUPER] char(1) NULL, "
cSQL += "[LASTLOG] datetime NULL, "
cSQL += "[CREATEDATE] datetime NULL, "
cSQL += "[PASSWORD] char(10) NULL, "
cSQL += "CONSTRAINT PK_USERINFO PRIMARY KEY ( USEREID )"
cSQL += " )"

// create the table Userinfo
// with primary key

Try
oCn:Execute( cSQL )
Catch
? "Table USERINFO Failed"
wait
Return(.f.)
End try

oCn:Close()
oCn := nil

cLOGIN := upper(NetName(.T.))+space(8)
cLOGIN := SUBSTR(cLOGIN,1,8)

// open the Userinfo table record set
// append the first record
// could have also used a connection and the INSERT command

oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType := 1 // opendkeyset
oRsUser:CursorLocation := 3 // local cache
oRsUser:LockType := 3 // lockoportunistic

// check for very first user

cSQL := "SELECT * FROM USERINFO"
TRY
oRsUser:Open( cSQL, xCONNECT )
CATCH oErr
? "Error in Opening USERINFO table here"
wait
RETURN(.F.)
END TRY

If oRsUser:eof
oRsUser:AddNew()

oRsUser:Fields("UserEid"):Value := "011111111111111111"
oRsUser:Fields("UserId"):Value := cLOGIN
oRsUser:Fields("ReadOnly"):Value := "Y"
oRsUser:Fields("WriteOnly"):Value := "Y"
oRsUser:Fields("Super"):Value := "Y"
oRsUser:Fields("CreateDate"):Value := dtoc(DATE())+" "+time()
oRsUser:Fields("LastLog"):Value := dtoc(DATE())+" "+time()
oRsUser:Fields("PassWord"):Value := "ADMIN"

oRsUser:Update()
Endif

dCreateDate := oRsUser:Fields("CreateDate"):Value
oRsUser:Close()

? dCreateDate
? ValType( dCreateDate )
wait

? (date()+3) - dCreateDate // errors here with current xHarbour
wait

Return(nil)

Andi Jahja

unread,
Feb 8, 2012, 4:58:11 PM2/8/12
to
Hi Rick:

I've compiled the program but: "cannot create rick.mdb" error.
FYI, I have nothing installed in my machine. Do I have to install
something to be able to create rick.mdb? Sorry, frankly I never play
with this stuff before.

Looks like this is an OLE problem (not RDD as I said before). I think
I almost there.

Please advise me what to install to run your program (somesort of
server?)

Thanks
Andi

Andi Jahja

unread,
Feb 8, 2012, 5:33:05 PM2/8/12
to
Forget all my previous message below. It is not relevant :(

The self-contained program is as follows:

// begin
proc main()
? DATE() - DATETIME( 2012, 02, 01, 18, 42, 16 )
// end

There is something to correct in the VM module.
I am working on it now.

Andi

On Wed, 08 Feb 2012 21:58:11 GMT, andi...@internet.com (Andi Jahja)

Ron Pinkas

unread,
Feb 8, 2012, 5:39:06 PM2/8/12
to
Andi,

The error appears not related to RDD, it appears to suggest that we simply
are missing proper DATETIME support in hb_vmGreater() (and maybe other
similar operators).

Ron

Rick Lipkin

unread,
Feb 8, 2012, 6:14:58 PM2/8/12
to
On Feb 8, 5:39 pm, "Ron Pinkas" <Ron.Pinkas_remove_th...@xHarbour.com>
wrote:
Ron, Andi

I appreciate your support .. Andi .. just curious what breakpoint in
my program was your error caught .. To my knowledge the Microsoft Jet
oledb provider is part of all Windows OS's .. should not matter if you
are running 32 or 64 bit ??

Thanks
Rick Lipkin

Andi Jahja

unread,
Feb 8, 2012, 7:20:28 PM2/8/12
to
Hi Rick:

Would you please try after my latest commit:

2012-02-09 07:05 UTC+0700 Andi Jahja <xharbour/AT/telkom/net/id>

Andi

Andi Jahja

unread,
Feb 8, 2012, 7:21:44 PM2/8/12
to
Ron,

Yes, you're right.
I have just commited changes related to VM routines.

Andi

Rick Lipkin

unread,
Feb 9, 2012, 11:15:25 AM2/9/12
to
Andi

I was just now able to compile your commit with version 9430 .. The
math part works fine now .. however the comparison fails ..

I have modified your example and you will see the last expression
errors when comparing the two values ..

Rick Lipkin

// begin

*Proc Main()
local dDATE

dDate := DATETIME( 2012, 02, 01, 18, 42, 16 )

? "Valtype dDate"
? ValType( dDate )
Wait

? "Value of dDate"
? dDate
Wait

? DATE() - dDate
Wait

If dDate < date()
? "dDate < date()"
Wait
Endif

// end

----------------------------- xHarbour Error Log
------------------------------

Date...............: 02/09/12
Time...............: 11:08:27

Application name...: C:\Fox\Access2003\Test.Exe
Workstation name...: DEMO-PC
Available memory...: 1440288
Current disk.......: C
Current directory..: Fox\Access2003
Free disk space....: 109635043328

Operating system...: Windows XP Home Edition 5.01.2600 Service Pack 3
xHarbour version...: xHarbour build 1.2.1 Intl. (SimpLex) (Rev. 9430)
xHarbour built on..: Feb 9 2012 13:57:05
C/C++ compiler.....: Borland C++ 5.5.1 (32 bit)
Multi Threading....: NO
VM Optimization....: 2

Current Area ......:1

-------------------- Internal Error Handling Information
---------------------

Subsystem Call ....: BASE
System Code .......: 1073
Default Status ....: .F.
Description .......: Argument error
Operation .........: <
Arguments .........: [ 1] = Type: T Val: 20120201184216.000 [ 2] =
Type: D Val: 20120209
Involved File .....:
Dos Error Code ....: 0

Trace Through:
----------------
TEST : 19 in Module: Test.prg


############################## Video Screen Dump
##############################

+--------------------------------------------------------------------------------
+
|
|
|Valtype
dDate
|
|
T
|
|Press any key to
continue... |
|Value of
dDate
|
|02/01/12
18:42:16.000
|
|Press any key to
continue... |
|
7.220648
|
|Press any key to
continue... |
|Error BASE/1073 Argument error: < Arguments: ( [ 1] = Type: T Val:
201202011842|
|16.000 [ 2] = Type: D Val:
20120209) |
|
|
|Error at ...: TEST(19) in Module: Test.prg



Andi Jahja

unread,
Feb 9, 2012, 1:22:13 PM2/9/12
to
Hi Rick,

I am working on it. All relational comparison logic for datetime
values are to be modified ( >, >=, <=, !=, etc). I'll come back to the
group once finished.

Andi

Rick Lipkin

unread,
Feb 9, 2012, 2:17:20 PM2/9/12
to
Andi

Thank you for your help and diligence !

Rick

Enrico Maria Giordano

unread,
Feb 9, 2012, 2:27:38 PM2/9/12
to
> I am working on it. All relational comparison logic for datetime
> values are to be modified ( >, >=, <=, !=, etc). I'll come back to the
> group once finished.

Thank you!

EMG

--
EMAG Software Homepage: http://www.emagsoftware.it
The EMG's ZX-Spectrum Page: http://www.emagsoftware.it/spectrum
The Best of Spectrum Games: http://www.emagsoftware.it/tbosg
The EMG Music page: http://www.emagsoftware.it/emgmusic


Andi Jahja

unread,
Feb 9, 2012, 2:27:36 PM2/9/12
to
Hi Rick,

It should be OK now after:


2012-02-10 02:25 UTC+0700 Andi Jahja <xharbour/AT/telkom/net/id>
! source/vm/hvm.c
! fixed the comparison logic related to DATETIME values
( <, >, <=, >=, =, ==, != )


Andi

Rick Lipkin

unread,
Feb 9, 2012, 3:20:05 PM2/9/12
to
On Feb 9, 2:27 pm, andija...@internet.com (Andi Jahja) wrote:
> Hi Rick,
>
Andi

I will test your commit as quick as I can ..Dumb question .. is there
a PICTURE clause that will take a datetime string and convert it to
view just a date like 02/09/2012 ?

Thank You!
Rick Lipkin

Rick Lipkin

unread,
Feb 9, 2012, 5:19:09 PM2/9/12
to
Andi

Your fix WORKED .. however when my DateTime value writes back to the
Access table it stores 12:00 am instead of the correct Date :(

I hope this is not turning out to be too much of a burden .. to
maintain legacy compatibility I hope you can make the table write back
and save correctly with the date ..

See one of the links below for the table view ..

Thanks
Rick

http://www.freeimagehosting.net/n5lrc

[url=http://www.freeimagehosting.net/n5lrc][img]http://
www.freeimagehosting.net/t/n5lrc.jpg[/img][/url]

Andi Jahja

unread,
Feb 9, 2012, 6:05:03 PM2/9/12
to
On Thu, 9 Feb 2012 14:19:09 -0800 (PST), Rick Lipkin
<r1....@live.com> wrote:


>Your fix WORKED .. however when my DateTime value writes back to the
>Access table it stores 12:00 am instead of the correct Date :(
>
>I hope this is not turning out to be too much of a burden .. to
>maintain legacy compatibility I hope you can make the table write back
>and save correctly with the date ..
>
>See one of the links below for the table view ..
>
>Thanks
>Rick
>
>http://www.freeimagehosting.net/n5lrc
>
>[url=http://www.freeimagehosting.net/n5lrc][img]http://
>www.freeimagehosting.net/t/n5lrc.jpg[/img][/url]


Hi Rick,

I've seen the pic, but could you please how to reproduced the error
please?

Andi

Rick Lipkin

unread,
Feb 10, 2012, 1:02:41 PM2/10/12
to
Andi

Here is the same code as before only difference is it writes to the
Access Database. I do not know why the Access database would not be
created on your machine .. I have had universal good luck with this
code on just about every machine I have tried.

You will need Ms Access to open Rick.mdb to view the UserInfo Table.
You can download the free trial Office 2010 Professional trial from
MS :

http://office.microsoft.com/en-us/try/

If you prefer I can e-mail you the Access table .. but you will still
need to Install MS Access to view the table.

Let me know if there is anything I can do ..

Thanks
Rick


// AccessDBx.prg
// creating an access database from code
// showing how date time field translates incorrectly


STATIC lOK

//-------------
*Func Main()

Local catNewDB,xProvider,xConnect,cFile,aDir,dExe,cDefa,mStart
Local oCn,cSql,oErr,oRsUser,cLOGIN,Saying
Local cTitle,oWnd1,oBrw,oCol,nYear,dCreatedate

setmode( 25, 80 )

lOK := .F.

//-- get timestamp on .exe //

cFILE := HB_ARGV(0)
aDIR := DIRECTORY( cFILE )

// where .exe started from is default directory //

mSTART := RAT( "\", cFILE )
cDEFA := SUBSTR(cFILE,1,mSTART-1)

aDIR := NIL
SET DEFA to ( cDEFA )

SET DELETED on
SET CENTURY on


nYEAR := ( year( DATE() )-30 )
SET EPOCH to ( nYEAR )

Ferase( cDefa+"\Rick.mdb" )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Rick.mdb"

// create the adox object
Try
catNewDB := CreateObject("ADOX.Catalog")
Catch
? "Could not Create ADOX object"
wait
Return(.f.)
End try

// create the table Rick.mdb
Try
catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet
OLEDB:Engine Type=5' )
Catch
? "Could not create the table "+xSource
wait
Return(.f.)
End Try


// global connection string
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet
OLEDB:Database'
? "Value of dCreateDate"
? dCreateDate
wait

? " "
? "ValType of dCreateDate"
? ValType( dCreateDate )
wait

? " "
? "Date()+3) - dCreateDate"
? (date()+3) - dCreateDate // errors here with current xHarbour
wait

? " "
? "dCreateDate > Date()+4000"
? dCreateDate > DATE()+4000
Wait

oRsUser:Fields("CreateDate"):Value := dCreateDate
oRsUser:Update()
oRsUser:Close()

? " "
? "Open Rick.Mdb with Ms Access and Table UserInfo to view CreateDate"
Wait


Return(nil)

Rick Lipkin

unread,
Feb 10, 2012, 1:37:57 PM2/10/12
to
Andy

You can try it like this .. without needing to download MS Access ..
still need to figure out why this code will not create the .mdb .. It
should work on any system.

Rick

// AccessDBx.prg
// creating an access database from code
// showing how date time field translates incorrectly


STATIC lOK

//-------------
*Func Main()

Local catNewDB,xProvider,xConnect,cFile,aDir,dExe,cDefa,mStart
Local oCn,cSql,oErr,oRsUser,cLOGIN,Saying
Local cTitle,oWnd1,oBrw,oCol,nYear,dCreatedate,xCreateDate

setmode( 25, 80 )

lOK := .F.

//-- get timestamp on .exe //

cFILE := HB_ARGV(0)
aDIR := DIRECTORY( cFILE )

// where .exe started from is default directory //

mSTART := RAT( "\", cFILE )
cDEFA := SUBSTR(cFILE,1,mSTART-1)

aDIR := NIL
SET DEFA to ( cDEFA )

SET DELETED on
SET CENTURY on


nYEAR := ( year( DATE() )-30 )
SET EPOCH to ( nYEAR )

Ferase( cDefa+"\Rick.mdb" )

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Rick.mdb"
xPASSWORD := "dec2011"

// create the adox object
Try
catNewDB := CreateObject("ADOX.Catalog")
Catch
? "Could not Create ADOX object"
wait
Return(.f.)
End try

// create the table Rick.mdb
Try
catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet
OLEDB:Engine Type=5' ) //;Jet OLEDB:Database Password='+xPASSWORD )
Catch
? "Could not create the table "+xSource
wait
Return(.f.)
End Try


// global connection string
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet
OLEDB:Database' // Password='+xPASSWORD
xCreateDate := oRsUser:Fields("CreateDate"):Value
oRsUser:Close()

? " "
? "Create Date in Table"
? xCreateDate
Wait

Return(nil)

Andi Jahja

unread,
Feb 10, 2012, 3:15:23 PM2/10/12
to
Rick,

Many thanks for your help tracing the bug.
I was able to create and run the program, thus I see what you meant.

I am trying to fix it now.

Andi

Andi Jahja

unread,
Feb 10, 2012, 3:27:26 PM2/10/12
to
Rick,

Would you mind try again after my latest commit please:

2012-02-11 03:25 UTC+0700 Andi Jahja <xharbour/AT/telkom/net/id>
! source/rtl/hbwinole.c
! fixed handling DATETIME values (bug report by Rick Lipkin)

Andi
Message has been deleted

Rick Lipkin

unread,
Feb 11, 2012, 9:28:07 AM2/11/12
to
Andi

You are REAL CLOSE .. the only problem is when you extract the
DateTime Value from the table in a Get and save it back to the
Table .. the field in the table that should have been changed has not
been updated or changed.

I have modified my example to demonstrate .. change the Datetime value
in the Get and you will notice it is not saved back to the Table.
? Version()

dCreateDate := oRsUser:Fields("CreateDate"):Value
? " "
? "Value of dCreateDate"
? dCreateDate
wait

? " "
? "ValType of dCreateDate"
? ValType( dCreateDate )
wait

? " "
? "Date()+3) - dCreateDate"
? (date()+3) - dCreateDate // errors here with current xHarbour
wait

? " "
? "dCreateDate > Date()+4000"
? dCreateDate > DATE()+4000
Wait

oRsUser:Fields("CreateDate"):Value := dCreateDate
oRsUser:Update()

xCreateDate := oRsUser:Fields("CreateDate"):Value

? " "
? "Create Date in Table"
? xCreateDate
Wait

@ 23,0 Say "Change the date "get xCreateDate
Read

oRsUser:Fields("CreateDate"):Value := xCreateDate
oRsUser:Update()

xCreateDate := oRsUser:Fields("CreateDate"):Value

? " "
? "xCreatedate from table after Get"
? xCreateDate
Wait

Return(nil)

Andi Jahja

unread,
Feb 12, 2012, 3:14:28 AM2/12/12
to
Rick,

OK, I see problems on source/rtl/tget.prg and source/rtl/transfrm.c.
I'll try my best to solve it next week. Meanwhile, if there is any
volunteer, by all means, please ....

Andi

Rick Lipkin

unread,
Feb 12, 2012, 12:08:57 PM2/12/12
to
On Feb 12, 3:14 am, andija...@internet.com (Andi Jahja) wrote:
> Rick,
>
> OK, I see problems on source/rtl/tget.prg and source/rtl/transfrm.c.
> I'll try my best to solve it next week. Meanwhile, if there is any
> volunteer, by all means, please ....
>
> Andi
>

Andi

Thank you again for your Help and diligence!!

Rick

Rick Lipkin

unread,
Feb 21, 2012, 4:52:53 PM2/21/12
to
Just a quick follow up to any Developer .. Andi has done a wonderful
job with only this one last modification to complete..

Anyone ??

Thanks
Rick Lipkin

Rick Lipkin

unread,
Mar 15, 2012, 8:21:05 AM3/15/12
to
Just keeping this thread open to any developer who would like to finish what Andi has started ??

Many Thanks
Rick Lipkin

Andi Jahja

unread,
Mar 17, 2012, 9:47:15 AM3/17/12
to
Hi Rick,

I am very sorry for very late follow-up on this issue.
BTW, I have just committed a quick fix for the subject matter.

Please test it and report any problem.

Thanks.

Andi

Rick Lipkin

unread,
Mar 19, 2012, 9:38:36 AM3/19/12
to
Andi

YES .. In build 9444 it appears that all your changes work GREAT .. Math between DateTime vadiables work as well as writing the value of DateTime back to SQL tables work as well.

Many Thanks
Rick Lipkin
Andi

YES .. All your modifications now work in build 9444.. math between DateTime variables and writing the DateTime back to the SQL table is working Perfect!

Many Thanks
Rick Lipkin
0 new messages