[Solved] Updating a Timestamp with Basic

[davewise]

I am trying to use Basic to update a timestamp in a table. I'm first setting a row with some data and then I'm trying to add a timestamp. The timestamp that shows up is not the current data and time. Anybody know what I'm doing wrong?

Code: Select all

With rsCurrentTable
	.DataSourceName=theDataSourceName
	.CommandType=com.sun.star.sdb.CommandType.TABLE
	.Command=theTableName
	.IgnoreResult=True 'NOT INTERESTED IN RESULT
	.execute()
End With

rsCurrentTable.moveToInsertRow()
colIdentifier =rsCurrentTable.getColumns().getByName( theColumnName )
colIdentifier.updateString(dataItem)
		
colIdentifier =rsCurrentTable.getColumns().getByName( "timeStampModified" )
colIdentifier.updateTimestamp(colIdentifier.getTimeStamp)

rsCurrentTable.insertRow()

[Villeroy]

Anybody know what I'm doing wrong?

Probably you do not debug your code and don't use enough symbols to do so.

Code: Select all

colIdentifier.updateTimestamp(colIdentifier.getTimeStamp)

Code: Select all

xVal = colIdentifier.getTimeStamp()
colIdentifier.updateTimestamp(xVal)

Inspecting xVal, you would notice that you try to update the field with what's already in there.

Putting a time stamp into every newly created record is extremely easy. In the main database window call menu:Edit>Database>Advanced Settings... and uncheck option "Form data input checks required fields". Then call menu:Tools>SQL ... and set the default value of the stamp field (the GUI's table design view fails to do so):

Code: Select all

ALTER TABLE <tablename> ALTER COLUMN "timeStampModified" SET DEFAULT CURRENT_TIMESTAMP

Make the respective form control read-only, so the stamp is visible in the form while being untouchable.

[davewise]

Putting a time stamp into every newly created record is extremely easy...

Thanks for the advice on a timestamp in a newly created record. I will also have to do this when I update with Basic code. Any tips on how to do this within the context of my existing code?

[Villeroy]

I will also have to do this when I update with Basic code

Well, this is crucial information missing. Method updateTimestamp takes an UNO DateTime struct. The Basic language has it's own data-type.

Code: Select all

Function getUnoTimeStamp()
Dim oUnoStamp as new com.sun.star.util.DateTime
Dim basicNow as Date
basicNow = Now()
oUnoStamp.Year = Year(basicNow)
oUnoStamp.Month = Month(basicNow)
oUnoStamp.Day = Day(basicNow)
oUnoStamp.Hours = Hour(basicNow)
oUnoStamp.Minutes = Minute(basicNow)
oUnoStamp.Seconds = Second(basicNow)
REM oUnoStamp.HundredthSeconds = Basic does not support fractions of seconds
getUnoTimeStamp = oUnoStamp
End Function

[davewise]

Very cool. Thanks Villeroy. Just out of curiosity is there a way to get the current time within an Uno service?

[Villeroy]

Very cool. Thanks Villeroy. Just out of curiosity is there a way to get the current time within an Uno service?

If I knew I would have used it.

[davewise]

I ended up doing it this way. It seems to work well. This is partly from Drew Jensens post: http://www.oooforum.org/forum/viewtopic ... f8b46cd2cd

Code: Select all

		

With rsCurrentTable
			.DataSourceName=theDataSourceName
			.CommandType=com.sun.star.sdb.CommandType.TABLE
			.Command=theTableName
			'.IgnoreResult=True 'NOT INTERESTED IN RESULT
			.execute()
		End With

		rsCurrentTable.moveToInsertRow()
		colIdentifier =rsCurrentTable.getColumns().getByName( theColumnName )
		colIdentifier.updateString(dataItem)
		
		colIdentifier =rsCurrentTable.getColumns().getByName( "timeStampModified" )
		colIdentifier.updateTimestamp( current_timestamp( rsCurrentTable.ActiveConnection) ) 
		rsCurrentTable.insertRow()

...

function current_timestamp( aConn as variant ) as new "com.sun.star.util.DateTime" 
	dim stmt 
	dim rs 
	stmt = aConn.createStatement 
	stmt.EscapeProcessing = False 
	rs = stmt.executeQuery( " call current_timestamp " ) 
	rs.next 
	current_timestamp = rs.columns(0).getTimestamp 
end function