To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=55949
Issue #:|55949
Summary:|TimeStamp fields are not consistent in fractional
|seconds.
Component:|Database access
Version:|OOo 2.0
Platform:|All
URL:|
OS/Version:|All
Status:|NEW
Status whiteboard:|
Keywords:|
Resolution:|
Issue type:|DEFECT
Priority:|P3
Subcomponent:|none
Assigned to:|dbaneedsconfirm
Reported by:|pitonyak
------- Additional comments from [EMAIL PROTECTED] Thu Oct 13 13:31:43 -0700
2005 -------
Note: Some of these issues may have been fixed with the for Issue 45302, but
this is just a guess.
Bug: getTimeStamp() never returns fractional seconds.
Bug: getString() returns fractional seconds for a ResultSet but not for a
RowSet.
Bug: I can not use updateTimeStamp() if the time stamp contains fractional
seconds.
Download the attached file.
Open the contained form.
run The top level macro in the main form.
To demonstrate my concerns, I created a Base document. The database contains a
Time and a TimeStamp field. The contained form contains a macro that demonstrate
the problems. This is the output from the macro:
*************************************
This demonstrates that a ResultSet does not return fractional seconds for a Time
field.
Time1 15:50:51.0
Time2 15:50:51
This demonstrates that a ResultSet returns fractional seconds from getString(1),
but not from getTimeStamp(1) (BUG).
TS1 = 1965-3-13 13:14:15.0
TS2 = 1965-03-13 13:14:15.123456789
Now, we see that a RowSet also does not return fractional seconds for time
Time1 15:50:51.0
Time2 15:50:51
This is interesting. From a RowSet, getTimeStamp() does not return fractional
seconds, even from getString()
TS1 = 1965-3-13 13:14:15.0
TS2 = 1965-03-13 13:14:15
Use updateTimeStamp() for the RowSet WITH fractional seconds and the value is
erased:
TS2 =
Use updateTimeStamp() for the RowSet WITHOUT fractional seconds and the
timestamp is updated:
TS2 = 1965-03-13 12:30:17.000000000
*************************************
If you want to see the Macro before you run it, here it is...
Option Explicit
Sub ProblemsWithTimeStamps
Dim sDBName As String
sDBName = ThisComponent.getParent().getURL()
UseResultSet(sDBName)
End Sub
Sub UseResultSet(dbFileName As String)
Dim oStatement
Dim oResultSet
Dim oBaseContext
Dim oDataBase
Dim oTables
Dim oCon
Dim s$
Dim oTS
Dim oTime
'**** Set up things here ****
Const dbUser as String = ""
Const dbPass as String = ""
oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDataBase = oBaseContext.getByName(dbFileName)
oCon = oDataBase.getConnection(dbUser, dbPass)
'some error handling
On Error Goto ErrorHandler
oStatement = oCon.createStatement()
REM Set the timestamp to include
oResultSet = oStatement.executeQuery("update NUM Set TS='1965-03-13
13:14:15.123456789' Where ID=0")
'oResultSet = oStatement.executeQuery("update NUM Set TS=""CURRENT_TIMESTAMP""
Where ID=0")
oResultSet = oStatement.executeQuery("update NUM Set T=""CURRENT_TIME"" Where
ID=0")
oResultSet = oStatement.executeQuery("select TS, T from NUM Where ID=0")
If Not IsNull(oResultSet) Then
oResultSet.next
oTS = oResultSet.getTimestamp(1)
oTime = oResultSet.getTime(2)
s = "This demonstrates that a ResultSet does not return " & _
"fractional seconds for a Time field." & CHR$(10) & CHR$(10)
s = s & "Time1 " & oTime.Hours & ":" & oTime.Minutes & ":" & _
oTime.Seconds & "." & oTime.HundredthSeconds & CHR$(10) & _
"Time2 " & oResultSet.getString(2) & chr$(10) & chr$(10)
s = s & "This demonstrates that a ResultSet returns " & _
"fractional seconds from getString(1), but not from getTimeStamp(1)
(BUG)." & CHR$(10) & CHR$(10)
s = s & "TS1 = " & oTS.Year & "-" & oTS.Month & "-" & oTS.Day & " " & _
oTS.Hours & ":" & oTS.Minutes & ":" & _
oTS.Seconds & "." & oTS.HundredthSeconds & CHR$(10) & _
"TS2 = " & oResultSet.getString(1) & CHR$(10)
End If
Dim oDoc
Dim oCurs
Dim oText
Dim oRowSet As Variant
Dim sSql$
Dim n%
REM Now, lets try a RowSet
oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.setPropertyValue("DataSourceName", dbFileName)
oRowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
oRowSet.ResultSetConcurrency =
com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE
sSQL = "SELECT * from `NUM` Where ID=0"
oRowSet.setPropertyValue("Command", sSQL)
oRowSet.execute()
n = oRowSet.getPropertyValue("RowCount")
If n > 0 Then
oRowSet.next()
Dim l1 As Long
Dim l2 As Long
l1 = oRowSet.findColumn("TS")
l2 = oRowSet.findColumn("T")
oTS = oRowSet.getTimestamp(l1)
oTime = oRowSet.getTime(l2)
s = s & CHR$(10) & CHR$(10) & "Now, we see that a RowSet also does not
return fractional seconds for time" & CHR$(10) & CHR$(10)
s = s & "Time1 " & oTime.Hours & ":" & oTime.Minutes & ":" & _
oTime.Seconds & "." & oTime.HundredthSeconds & CHR$(10) & _
"Time2 " & oRowSet.getString(l2) & chr$(10) & chr$(10)
s = s & "This is interesting. From a RowSet, getTimeStamp() does not return
fractional seconds, even from getString()" & CHR$(10) & CHR$(10)
s = s & "TS1 = " & oTS.Year & "-" & oTS.Month & "-" & oTS.Day & " " & _
oTS.Hours & ":" & oTS.Minutes & ":" & _
oTS.Seconds & "." & oTS.HundredthSeconds & CHR$(10) & _
"TS2 = " & oRowSet.getString(l1) & CHR$(10) & chr$(10)
'oTime.Hours = 12
'oTime.Minutes = 30
'oTime.Seconds = 17
'oTime.HundredthSeconds = 987
'oRowSet.updateTime(l2, oTime)
s = s & "Use updateTimeStamp() for the RowSet WITH fractional seconds and
the value is erased:" & CHR$(10) & CHR$(10)
oTS.Hours = 12
oTS.Minutes = 30
oTS.Seconds = 17
oTS.HundredthSeconds = 12
oRowSet.updateTimeStamp(l1, oTS)
oRowSet.updateRow()
oResultSet = oStatement.executeQuery("select TS, T from NUM Where ID=0")
oResultSet.next
s = s & "TS2 = " & oResultSet.getString(1) & CHR$(10) & CHR$(10)
s = s & "Use updateTimeStamp() for the RowSet WITHOUT fractional seconds and
the timestamp is updated:" & CHR$(10) & CHR$(10)
oTS.Hours = 12
oTS.Minutes = 30
oTS.Seconds = 17
oTS.HundredthSeconds = 0
oRowSet.updateTimeStamp(l1, oTS)
oRowSet.updateRow()
oResultSet = oStatement.executeQuery("select TS, T from NUM Where ID=0")
oResultSet.next
s = s & "TS2 = " & oResultSet.getString(1) & CHR$(10) & CHR$(10)
Else
MsgBox "without data"
Endif
oRowSet.dispose()
oCon.close()
oDoc =
StarDesktop.loadComponentFromURL("private:factory/swriter","_blank",0,Array())
oText = oDoc.getText()
oCurs = oText.createTextCursor()
oText.insertString(oCurs, s, False)
Exit Sub
ErrorHandler:
MsgBox "Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13)
+ Now , 16 ,"An Error Occurred"
oCon.close()
End Sub
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
