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.
