Ask Your Question
0

Calc: call a stored procedure

asked 2018-04-06 09:22:46 +0200

MarcoMod gravatar image

hi all,

in excel: I have a connection to an MSSQL database, 3 worksheets, on each one, right click on a cell, table-> edit query and I have a sql command that calls a stored procedure

in calc: created a connection to the database via base, registered, opened excel file (also saved in ods format) but, where is the option to insert that sql command? plus, I can not execute "data -> update area" despite the fact that the areas have been correctly "imported" and defined from the original excel file (maybe because the query is missing?)

thank you all for your help

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-04-06 22:52:22 +0200

Ratslinger gravatar image

updated 2018-04-10 00:21:10 +0200

Hello,

In Calc you obtain registered Base Table or Query data via data sources. You can access these data sources with Ctrl+Shift+F4 or from menu item View->Data Sources. When you locate the source wanted you can drag the record or entire table/query to the cell where wanted. This then creates a data range which can be seen in menu Data->Define Range....

When needed you can select a defined range and then refresh range to get latest data. Both of these items are on the menu item Data selection.

You can find more info in the Calc documents, Chapter 10, found here -> Documentation/Publications

Edit 2018-04-09:

@MarcoMod Sorry, but most questioners here mention stored procedure when they mean query.

With LO, the only way to access stored procedures is through a macro. Just finished testing using MySQL. It took a while since I had to change connectors (JDBC finally worked). The test was based upon this post -> Database procedures and callable Statements

To test I used the following code:

Sub CallProcedure
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    rem URL of database (it is also possible to use a registered DB name)
'    dbNAME = "file:///home/YOUR_DIRECTORY/YOUR.odb"  '
    dbNAME = "REGISTERED NAME"
    Db = Context.getByName(dbNAME)
    rem connect to the database
'Allows for prompting of user & password'
    oHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Conn = Db.ConnectWithCompletion(oHandler)
'Below here is from linked post'
sFormat = "minor"
oStmt = Conn.prepareCall("CALL test_proc(?,?)")
' Input parameter binding is exactly the same as for prepared statements'
oStmt.setString(1, sFormat)
'Parameter 2, because it is the position of the question mark that is relevant.'
' For counting of the position all parameters (in, out) are equal'
oStmt.registerOutParameter(2, com.sun.star.sdbc.DataType.VARCHAR, "ignored")
oStmt.execute()
' NOTE: The .getXXX() methods are methods of the statement object and not of'
'       the result set!'
MsgBox "Mode: " & sFormat & " -- Version: " & oStmt.getString(2)
oStmt.close()
'Rest of sub here - move retrieved data to cell(s)'
End Sub

Procedure used:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`( IN p_format VARCHAR(8), OUT ver_param VARCHAR(50))
BEGIN
  DECLARE v_version VARCHAR(50);
  DECLARE v_pos INTEGER;

  SELECT VERSION() INTO v_version;
  IF p_format = 'major' THEN
    SET ver_param = substr(v_version,1,1);
  ELSEIF p_format = 'minor' THEN
    SET v_pos = locate('.',substr(v_version,3,99)) + 2;
    SET ver_param = substr(v_version,1,v_pos-1);
  ELSE
    SET ver_param = v_version;
  END IF;
END$$
DELIMITER ;

Once you've gotten the result, you need more code to move the data into wanted cells.

edit flag offensive delete link more

Comments

"...but i don't have a query to drag." Since you have a Base .odb created and it is registered, the query is created in Base. When you access data sources in Calc, all Table data & Query results from the registered DB are available.

If you want the queries in Calc itself, you can only do this using macros. This is a much more tedious process.

Ratslinger gravatar imageRatslinger ( 2018-04-09 14:02:38 +0200 )edit

i don't have a query i have a procedure with a query inside it, that's the problem, the query list of my database it's empty.

MarcoMod gravatar imageMarcoMod ( 2018-04-09 17:21:11 +0200 )edit

Please see edited answer.

Ratslinger gravatar imageRatslinger ( 2018-04-09 20:54:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-04-06 09:22:46 +0200

Seen: 346 times

Last updated: Apr 10 '18