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?
Running User Stored Procedure (SQL server) from Calc code
Running User Stored Procedure (SQL server) from Calc code
OpenOffice 4.1.13 / Win10
-
- Posts: 314
- Joined: Sun Sep 06, 2020 8:27 am
Re: Running the User Stored Procedure (sql server) from calc code.
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.
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.
OpenOffice 3.1 on Windows Vista
Re: Running User Stored Procedure (SQL server) from Calc code
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Running User Stored Procedure (SQL server) from Calc code
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.
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.
OpenOffice 4.1.13 / Win10
Re: Running User Stored Procedure (SQL server) from Calc code
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.
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 commandwhere 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.
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
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 )
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.
OpenOffice 4.1.13 / Win10
Re: Running User Stored Procedure (SQL server) from Calc code
... 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
This is the code to execute the function and display the returned table
Cheers ms777
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 ;
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