Running User Stored Procedure (SQL server) from Calc code

[db_user]

I think I have reached a wall. Is it possible to run a sql procedure located on sql server from within calc using code? I need to transfer data from calc sheet to sql server, bulk insert or row by row, and then run the SQL Server procedure (User Stored Procedure) from openoffice calc code (after push button).

I know how to do it with e.g. C# and if I remember correctly you can also do it with VBA or VBS, but how to do it with calc?

The other way is the same, i.e. I need to put the result of the t-sql procedure into the calc sheet. The event triggering should be done from openoffice calc.

Any suggestions on how best to do this?

[Mountaineer]

Like VBA you can also use BASIC or python macros to send sql to a connected database.

For example: https://forum.openoffice.org/en/forum/v ... estatement

One point to notice: You have to prepare a Statement, not a query. Do you know the handbook for base. It contains several examples (but actually I only read the german version).

For the other way round you can maybe create a query and drag the result to Calc. This could be refreshed later.

J.

[Villeroy]

Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet

[db_user]

Thank you for your answers, will be useful to move the data to one table. However, the main problem remains how to call the stored procedure on the server side (call from calc code).

As I connected to the database (file --> new --> data base --> connect to existing database --> .... --> ODBC), I was prompted to save the new file and the table structure that exists on sqlserver appeared. It appeared to be correct. However, I couldn't access any of the stored procedures (I have full access to the database), and I need to call one of them.

Following the link provided by @Mountaineer I came across the topic https://webcache.googleusercontent.com/ ... clnk&gl=pl, which at first glance looks promising. I'll check it out and let you know. Perhaps the fact that I can't see the procedures through the ODBC connection won't be the problem here.

[db_user]

After testing several possibilities, I was able to find a solution that does not require an additional "base" file, and the connection to the SQL Server database is made directly through the ODBC driver.

Code: Select all

Sub ODBC_TEST_v4

	'Connect directly to SQL Server without base file (.ods file)
	sURL = "sdbc:odbc:R_ODBC"	'R_ODBC --> your name of configured ODBC Data Sources (win10 --> control panel --> system and security --> administrative tools --> ODBC Data Sources)
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	oCon = oManager.getConnection(sURL)
	stmt = oCon.CreateStatement()

	oPrepStmt =  oCon.prepareStatement("EXEC ( ? )") REM: DIRTY SOLUTION but probably better than {? = CALL procname (?,?)} when you can accindentally change position of Stored Procedure variables. Potencial security problem.
	oPrepStmt.setString(1, "[tmp].[usp_OpenOffice_TEST_v2] 		@val_1 = N'll12',		@val_2 = N'22'")	'[tmp].[usp_OpenOffice_TEST_v2] --> name of User Stored Procedure with two parameters @val_1 and @val_2
	oPrepStmt.execute()
	oPrepStmt.clearParameters()


	query = "SELECT * FROM tmp.OpenOfficeTest"	'--> SELECT to get your data and check procedure succes or get data from database to opencalc
	result = stmt.executeQuery(query)
	
	If Not isNull (result) Then
		row = 0
		ctr = ThisCOmponent.CurrentController
		sheet = ctr.getActiveSheet()
		
		While result.next
			CellA = sheet.getCellByPosition(0,row)
			CellB = sheet.getCellByPosition(1,row)
			
			CellA.String = result.getString(1)
			CellB.String = result.getString(2)
			
			row = row + 1
	
	
		Wend
	End if

	oPrepStmt.close
	oPrepStmt.dispose()

	oCon.close()
	oCOn.Dispose

End sub

It seems that the solution is not ideal due to several issues:
1. the oCon.prepareStatement("EXEC ( ? )") line allows you to call any SQL entered as parameter 1. I am not familiar enough with OpenOffice to say whether this is a security risk to the sql server database. If the database and opencalc are on the same computer, operated by a single person, then there is unlikely to be a problem. Alternatively, you would need to limit the privileges of the technical user through whom the connection to the database is made (note: in the proposed solution, the user connects through windows authorization) so that by modifying the code in openoffice slightly, someone does not clear/delete the database tables.

2 I failed to return a set of records after calling a stored procedure located on the sql server side. As a result, I don't know if the called procedure executed correctly and I have to check this for now with an additional SQL query. The ODBC driver documentation (https://learn.microsoft.com/en-us/sql/r ... rver-ver16) says this is possible as does the PrepareStatement documentation (https://www.openoffice.org/api/docs/com ... ement.html). However, I have not been able to find the correct syntax.

3. During testing by calling the command

Code: Select all

sSQL = "tmp.usp_OpenOffice_TEST_v2"
oResult = oStatement.execute( sSQL )

where usp_OpenOffice_TEST_v2 takes two parameters, from within the OpenOffice code, I received a message that a paremeter with a specific name was missing. Thus, it seems that through the ODBC driver, a specific parameter of a stored procedure can be referenced, and it is not necessary to use the position of this parameter for this purpose as in the case of PrepareStatement.

If anyone has managed to solve the problem with returning a set of records after calling a stored procedure (which includes SELECT in addition to other SQL queries), or if anyone knows how to refer to a SQL procedure parameter by its name, please write. Maybe together we can solve it. The same applies to the results returned by the SQL query (SELECT). All solutions I have come across refer to the returned columns by their position. If you modify the SQL query (e.g., adding a column in the middle or deleting it), you may end up with a mess. This problem doesn't exist when you refer to the data by column name as in, say, C# or VBA.

[ms777]

... not sure if this is of any help, because it is on MariaDB and uses the sdbc:mysql:jdbc driver

In MariaDB I create a stored procedure by

Code: Select all

delimiter ;;
create or replace procedure foo(param1 int UNSIGNED, param2 CHAR(10)) contains sql begin select param1, param2; end;;
delimiter ;

This is the code to execute the function and display the returned table

Cheers ms777

Code: Select all

Sub Main
	oDriverManager = CreateUnoService("com.sun.star.sdbc.DriverManager") 

	Dim info(2) as new com.sun.star.beans.PropertyValue
	info(0).Name = "User"
	info(0).Value = ""
	info(1).Name = "Password"
	info(1).Value = ""
	info(2).Name = "JavaDriverClass"
	info(2).Value = "com.mysql.jdbc.Driver"

	sUrl = "sdbc:mysql:jdbc:192.168.123.123:1234/test" 
	oDriver = oDriverManager.getDriverByUrl(sUrl)

	oConnection = oDriver.connect(sUrl, info)
	
	oStatement = oConnection.createStatement()
	oStatement.setPropertyValue("ResultSetType", com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE)
	oStatement.setPropertyValue("ResultSetConcurrency", com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE)
	
	sSQL = "CALL foo(7, 'fff')"
	print sSQL
	
	oResultSet = oStatement.executeQuery(sSQL)
'	xray oResultSet

	lUBound = oResultSet.MetaData.ColumnCount-1
	Dim asColumnName(lUBound) as String
	Dim asColumnTypeName(lUBound) as String
	for kCol=0 to lUBound
		asColumnName(kCol) = oResultSet.MetaData.getColumnName(kCol+1)
		asColumnTypeName(kCol) = oResultSet.MetaData.getColumnTypeName(kCol+1)
	next kCol


	do while not oResultSet.isLast() 
		if not oResultSet.next() then exit do
		for kCol=0 to lUBound
			print "col: " & asColumnName(kCol) & ", type: " & asColumnTypeName(kCol) & ", val: " & oResultSet.getString(kCol+1)
		next kCol
	loop

  	oStatement.close()
  	oConnection.close()

End Sub