MySQL DATE_ADD not recognized in datagrid

asked 2019-07-24 02:04:32 +0200

Ivan Baldo gravatar image

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!!!

edit retag flag offensive close merge delete

Comments

Hello,

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

Ratslinger gravatar imageRatslinger ( 2019-08-15 04:09:05 +0200 )edit

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.

EasyTrieve gravatar imageEasyTrieve ( 2019-08-15 16:18:15 +0200 )edit