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
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
You can find more info in the Calc documents, Chapter 10, found here → Documentation/Publications
@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:
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'
'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")
' NOTE: The .getXXX() methods are methods of the statement object and not of'
' the result set!'
MsgBox "Mode: " & sFormat & " -- Version: " & oStmt.getString(2)
'Rest of sub here - move retrieved data to cell(s)'
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`( IN p_format VARCHAR(8), OUT ver_param VARCHAR(50))
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);
SET ver_param = v_version;
Once you’ve gotten the result, you need more code to move the data into wanted cells.