Macro to insert row TIMESTAMP

English
,
#1

I have been building a database to aid my home brewing efforts and have hit a wall trying to get a Timestamp value into a table using a macro. This is using a split HSQLDB database v2.3.2. I have a variable as: Dim dGravitydateUNO As New com.sun.star.util.DateTime, and can see that it is populated but running the macro results in ‘BASIC runtime error. Incorrect property value’. I have done a bit of reading around this but have so far drawn a blank. Any help offered would be welcome.

This is the macro with the issue arising within the final SQL string:

Sub FermTrack(oEvent As Object)
	Dim oForm As Object
	Dim oStatement As Object
	Dim stSQL As String
	Dim oResult As Object
	Dim dFermdate As New com.sun.star.util.Date
	Dim doFermepoch As Double
	Dim dCaskdate As New com.sun.star.util.Date
	Dim doCaskepoch As Double
	Dim oDatabaseContext As Object
	Dim oDatasource As Object
	Dim oConnection As Object
	Dim doFGActual As Double
	Dim stTopic As String
	Dim iFermID As Integer
	Dim doGravity As Double
	Dim doOGActual As Double
	Dim dGravitydate As Date
	Dim dGravitydateUNO As New com.sun.star.util.DateTime
	Dim doGravityepoch As Double
	Dim doGravitydate As Double
	
	

	oForm = oEvent.Source.Model.Parent

	iBrewID = oForm.getLong(1)
	dFermdate = oForm.getDate(3)
	dCaskdate = oForm.getDate(18)
	doOGActual = oForm.getDouble(17)
	
    MsgBox "Fermentation date = " & dFermdate.day & dFermdate.month & dFermdate.year
    
    doFermepoch = CDateFromUnoDate(dFermdate)
    doFermepoch = (doFermepoch -25569) * 86400
    
    doCaskepoch = CDateFromUnoDate(dCaskdate)
    doCaskepoch = (doCaskepoch -25569) * 86400
    
    MsgBox "Fermentation date = " & doFermepoch

	stTopic = "ispindelsuninn/I1_gravity"
	

	oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
	oDatasource = oDatabaseContext.getByName("Gravity")
	oConnection = oDatasource.GetConnection("","")

	oStatement = oConnection.createStatement()
	
	stSQL =  "SELECT ""id"", ""payload"", ""created_at"" FROM ""sensors_data"" WHERE ""created_at"" > "& doFermepoch &" AND created_at < "& doCaskepoch &""
	oResult = oStatement.executeQuery(stSQL)

	While oResult.Next
		iFermID = oResult.getLong(1)
		doGravity = oResult.getDouble(2)
		doGravityepoch = oResult.getDouble(3)

		
		If doGravity = doOGActual And oResult.row =1 Then
		Elseif doGravity < doOGActual Then
		
			doGravitydate = (doGravityepoch / 86400) + 25569
		
			MsgBox "Gravity epoch = " & doGravityepoch	& " Gravity date = " & doGravitydate
	   		dGravitydate = CDate(doGravitydate)
			MsgBox "CDate = " & dGravitydate    
			dGravitydateUNO = CDatetoUNODateTime(doGravitydate)
			MsgBox  " UNO = " & dGravitydateUNO.day & "/" & dGravitydateUNO.month  & "/" & dGravitydateUNO.year  & "  " & dGravitydateUNO.hours & ":" & dGravitydateUNO.minutes & ":" & dGravitydateUNO.seconds


			MsgBox "Brew ID= "& iBrewID &" FermID - "& iFermID &" Date= "& dGravitydate &" Gravity= "& doGravity
		
			oStatement = oForm.ActiveConnection.createStatement()
			stSQL = "INSERT INTO ""tblFermentation"" (""BrewID"", ""FermID"", ""Date"", ""Gravity"") VALUES ('"+ iBrewID +"','"+ iFermID +"', '"+ dGravitydateUNO +"', '"+ doGravity +"')"
			oStatement.executeUpdate(stSQL)
			
			doOGActual = doGravity
			
		Endif



	Wend
	
End Sub

Version: 25.8.0.4 (X86_64)
Build ID: 48f00303701489684e67c38c28aff00cd5929e67
CPU threads: 2; OS: Windows 10 X86_64 (build 19045); UI render: Skia/Raster; VCL: win
Locale: cy-GB (en_GB); UI: en-GB
Calc: threaded

#2

Do you get the right date in a message box?
At which point the error will appear?
You try to insert into “Date”. The value, which is needed, should be formatted as ‘YYYY-MM-DD’. Is
dGravitydateUNO formatted like this?
The UNO-Construction will be needed to write a value to the form, but won’t be needed to write a value through SQL to the database.

1 Like
#3

I do get the right date in the message box, the error occurs against the SQL string stSQL.

stSQL = "INSERT INTO ""tblFermentation"" (""BrewID"", ""FermID"", ""Date"", ""Gravity"") VALUES ('"+ iBrewID +"','"+ iFermID +"', '"+ dGravitydateUNO +"', '"+ doGravity +"')"

I think from what you are saying I should use the Date variable dGravitydate but format it as YYYY-MM-DD rather than the UNOStruct?

#4

Dim dGravitydateUNO As New com.sun.star.util.DateTime
 

dGravitydate (Date) or dGravitydate (DateTime)
(?)

#5

So I had previously tried to use the dGravitydate variable Dim dGravitydate As Date in the SQL string as '"+ dGravitydate +"' but got the error

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: data exception: invalid datetime format

What I think is needed is to format the dGravitydate as YYYY MM DD hh mm ss and revert back to '"+ dGravitydate +"' in the SQL string?

#6

Thanks for all your help.
I replaced the Date variable with string formatted YYYY-MM-DD hh:mm:ss and it worked :grinning:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.