MySQL DATE_ADD not recognized in datagrid

[IvanBaldo]

Hello.

I have a MySQL view ‘Months’ with a column ‘Month’ with values ‘01’, ‘02’, etc. all as strings.

This is the smallest example I came up with showing the problem:

SELECT Month, DATE_ADD(CONCAT('2019-', Month, '-01'), INTERVAL 1 MONTH) FROM Months;

In the SQL designer I can run the query if I select the icon to run it natively, but I need to use it with Calc in a datagrid.

So the BASIC code I use is this:

oDBRange = thisComponent.DataBaseRanges.getByName("DataArea")
oDesc() = oDBRange.getImportDescriptor()
For i = 0 to ubound(oDesc())
	oPrp = oDesc(i)
	If oPrp.Name = "DatabaseName" then
		oPrp.Value = "MyDatabase"
	elseIf oPrp.Name = "SourceType" then
		oPrp.Value = com.sun.star.sheet.DataImportMode.SQL
	elseIf oPrp.Name = "SourceObject" then
		oPrp.Value = "SELECT Month, DATE_ADD(CONCAT('2019-', Month, '-01'), INTERVAL 1 MONTH) FROM Months;"
	elseif oPrp.Name = "IsNative" then
		oPrp.Value = True
	Endif
	oDesc(i) = oPrp
Next
oDBRange.getReferredCells.doImport(oDesc())

But it seems that it is trying to parse the query and not understanding it.

The error returned is not useful, just syntax error.

Any ideas?

Thanks a lot!!!

[Ratslinger]

Hello,

Might need to surround Month (2x’s in SQL) with back tick `

[EasyTrieve]

Generally, when something isn’t working in my basic code, I try to create a simpler test case to try and divide and conquer. In this case it might be just the one line, and then just the CONCAT.. expression alone.