How to use a "SELECT TOP 1..." T-SQL query in a calc macro with MSSQL Server

QueryParamStuckMan · December 1, 2021, 5:15pm

Hi, I have a "SELECT TOP 1 * FROM … " query I would like to execute in a macro:

sub DoStuff()	
	oDBRange = thisComponent.DataBaseRanges.getByName("something")
	oDesc() = oDBRange.getImportDescriptor()
	
	For i = 0 to ubound(oDesc())
		oPrp = oDesc(i)
		If oPrp.Name = "DatabaseName" then
			oPrp.Value = "my_db"
		elseIf oPrp.Name = "SourceType" then
			oPrp.Value = com.sun.star.sheet.DataImportMode.SQL
		elseIf oPrp.Name = "SourceObject" then
			oPrp.Value = "SELECT TOP 1 * FROM myTable ORDER BY val DESC"
		elseif oPrp.Name = "IsNative" then
			oPrp.Value = True
		Endif
		oDesc(i) = oPrp
	Next
	oDBRange.getReferredCells.doImport(oDesc())
end sub

Calc returns invalid SQL error for this. It is ok with the same statement withtout the "TOP 1 ".

I looked at this answer this seems to be specific to data using the UI view with forms.

Is there some way to bypass this syntax check and pass it to MSSQL Server anyway?
Or some alternate syntax?
I could add a RowNumber to the table and add a “WHERE Row = 1” to the query but I’d prefer not to touch the DB schema.

Thanks

Ratslinger · December 1, 2021, 5:38pm

Hello,

Use Limit x

oPrp.Value = "SELECT * FROM myTable ORDER BY val DESC Limit 1"

Reference → MySQL LIMIT

.
An error is an error. Bypass is already stated with:

oPrp.Name = "IsNative" then
			oPrp.Value = True

QueryParamStuckMan · December 1, 2021, 5:39pm

This doesn’t work with Microsoft sql server equivalent. Is there an equivalent for T-SQL?

Ratslinger · December 1, 2021, 5:45pm

Sorry, did not look closely at The DB - Thought it was MySQL.

Don’t you have T-SQL documentation? Should be there.

How about this post → T-SQL SELECT TOP 1

QueryParamStuckMan · December 1, 2021, 6:03pm

It doesn’t look to be a SQL syntax error. I can run the same query with/without the TOP directly in SQL Server and it’s fine. But from the macro it’s failing…

I ran a profiler to see what is being sent to SQL Server too.
Without the top - it gets there with some modification added:

SELECT * FROM "odbc_source_namename"."dbo"."myTable"

With the top - it’s not reaching sql server at all. The Invalid Sql Exception happens somewhere before.

QueryParamStuckMan · December 1, 2021, 6:11pm

A workaround is to use the syntax for paging

.... ORDER BY [column1] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

But I’m still not sure why it’s failiing…

Ratslinger · December 1, 2021, 6:13pm

Appears you need to surround names (table/field) with quotes. Maybe:

"SELECT TOP 1 * FROM ""myTable"" ORDER BY ""val"" DESC"

Do not have MSSQL so cannot help any further.

QueryParamStuckMan · December 1, 2021, 6:24pm

No worries, I should be good with the paging workaround. Thanks for you help

RobertG · December 1, 2021, 7:39pm

Could be it has something to do with EscapeProcessing. In “normal” connections through macros you could set

oSQL_Statement = oConnection.createStatement()
oSQL_Statement.EscapeProcessing = False

Villeroy · December 1, 2021, 8:14pm

You can easily import record sets into Calc without writing any macros.
Anyhow:

Blockquote

elseif oPrp.Name = "IsNative" then
			oPrp.Value = True

IsNative is the opposite of direct SQL mode. IsNative means that Base parses the query before passing it to the driver. You want False for “IsNative”.