PostgreSQL is a bit different, because strictly speaking there are no procedures in Postgresql, only functions. And another important difference to Mysql and Oracle is that functions in Postgresql can return record types which can be used to wrap multiple return values into a single one. And even better Postgresql treats these record return values as one line tables and allows you to use them in SQL Query syntax wherever you use tables.
Code: Select all
SELECT * FROM my_func_returning_a_record(14,0)
| rec_field1 | rec_field2 | rec_field3
|------------+------------+-----------
| 0 | 1 | text-data
And there are nearly no restrictions for such functions in Postgresql. In MySQL they can contain UPDATE, DELETE or INSERT commands in Oracle they are not allowed to do any changes in the database tables.
In short you don't need CallableStatements if you work with a Postgresql backend.
MySQL knows two categories of procedures
- Procedures with input and output parameters (see test_proc in the following code block)
- Procedures that create a result set (see proc_with_resultset)
Code: Select all
DELIMITER //
CREATE PROCEDURE test_proc( IN p_format VARCHAR(8), OUT ver_param VARCHAR(25), OUT p_numeric DECIMAL(4,2))
BEGIN
DECLARE v_version VARCHAR(25);
DECLARE v_pos INTEGER;
SELECT VERSION() INTO v_version;
IF p_format = 'major' THEN
SET ver_param = substr(v_version,1,1);
SET p_numeric = cast(ver_param AS DECIMAL(2));
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 p_numeric = cast(ver_param AS DECIMAL(4,2));
ELSE
SET ver_param = v_version;
SET p_numeric = cast(v_version AS DECIMAL(4,3));
END IF;
END//
-- This procedure doesn't make much sense in a real world application, because
-- it hasn't got any procedural content. The complete procedure body consists of
-- only a single select statement. Instead of calling the procedure you would
-- directly execute the SELECT statement (with the same result but less
-- complexity and overhead).
CREATE PROCEDURE proc_with_resultset( IN p_mode INTEGER)
BEGIN
-- no INTO ... --> MySQL has to create a result set in order to keep the
-- selected fields "somewhere"
SELECT p_mode AS mode, unix_timestamp(), 'some text data' AS third;
END//
DELIMITER ;
CALL needs to know how the parameter list for the procedure that is used/called looks like. To accomplish this it accesses the metadata of the database engine. This means that the user that executes the CALL statement needs at least the privilege to read the mysql.proc table: GRANT select ON mysql.proc to the_user.
The only way to retrieve something from the stored procedures is through the scripting languages of OpenOffice. For simplicity I use OOo Basic. You need to get hold of a connection handle first. I skipped this part because you can find several examples for that elsewhere, (so very short):
- Click of a push button: oConnection = oEvent.Source.Model.getParent().ActiveConnection
- In the main base window: oConnection = ThisComponent.DataSource.getConnection("", "")
- Without an active connection: Use the .getConnectionWithInfo() method of the "com.sun.star.sdbc.DriverManager" instance
Code: Select all
sFormat = "minor"
oStmt = oConnection.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")
' The final scale parameter says that we expect 2 digits after the decimal separator
oStmt.registerNumericOutParameter(3, com.sun.star.sdbc.DataType.DECIMAL, 2)
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) _
& chr(10) & "Numerical: " & oStmt.getString(3)
oStmt.close()
If you have a procedure that creates an internal result set the first part of the code is the same as with the other category of procedures. But because there are no output parameters there is no need to use registerOutParameter. And executeQuery() is used to send the prepared statement to the database engine, because this is the one of the 3 execute... methods that returns a result set.
Code: Select all
nMode = 8
oStmt = oConnection.prepareCall("CALL proc_with_resultset(?)")
oStmt.setInt(1, nMode)
oResult = oStmt.executeQuery()
oResult.next() ' We expect only one row here, so no while loop needed
MsgBox "Mode: " & oResult.getInt(1) & " -- Timestamp: " & oResult.getLong(2) _
& chr(10) & "A string: " & oStmt.getString(3)
oResult.close()
oStmt.close()