Database procedures and callable Statements

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Database procedures and callable Statements

Post by rudolfo »

Because I haven't found any example code on how to use stored procedures from within OOo Base I will describe here what works for me with MySQL as a database backend. The way how to do this should be pretty much the same for other database engines that support stored procedures. The JDBC driver of Oracle supports CallableStatements so it should work withing OpenOffice as well, though I have tested this only with a Java/JDBC application and not with OpenOffice.

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
So one of the major reasons to use stored procedures "they are the only way to retrieve more than one output variable from your function/procedure blackbox" isn't true for PostgreSQL. You can even use the above Select from a function statement in Query of the Base application -- if you activate "Direct SQL" mode.
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)
The second category behaves a bit like the above mentioned record returning functions of PostgreSQL. But with a crucial difference: As with all procedures in MySQL you need the CALL syntax to execute them. This makes it impossible to use it in a Query of Base. It won't work ... not even if you turn "direct SQL" mode on. Well, with "direct SQL" it will not throw an error, but it won't give you any results, either.

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 ;
Run the above SQL statements in the mysql command line tool or in your favourite MySQL administration tool. The SQL command window won't help you here as it doesn't understand the DELIMITER syntax. If you have nothing but the Tools -> SQL window of Base to interact with the database I rather say you'd better stay away from stored procedures anyway.

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
Once you have the connection you create a statement handle with prepareCall and bind the input parameters in the same way as you would do for a prepared statement. If you use a procedure that falls in the first category with out parameters you have to additionally tell the database driver about the output parameters: this is done with the XOutParameters Interface. And then you send the statement to the database engine with the execute() method. Don't use exuecteQuery() because you don't have a result set.

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()
I used .getString() for the last output parameter because this respects the 2 decimal digits when converting to a string. oStmt.getDouble(3) would have been more appropriate if you want to do anything with the value apart from displaying it in a message box. But in this example with the version .getDouble would output just 5 without any decimal part for Mysql 5.0.x and 5.1 or 5,1 for version 5.1.x.

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()
Tested with Sun OpenOffice 3.1 and LibreOffice 3.3.2 with a JDBC connection to the Mysql database.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
aay
Posts: 4
Joined: Thu Aug 20, 2009 3:29 pm

Re: Database procedures and callable Statements

Post by aay »

This is good for procedures. But what about stored functions in MySQL? I was trying to run them with PreparedStatement, but the results I get are always wrong numbers. Do I have to run prepareCall for stored functions?
OOo 3.0 Linux Ubuntu
Post Reply