call a MySQL prodedure

[xoristzatziki]

I can’t get oConnection = ThisDatabaseDocument.CurrentController.ActiveConnection + oStmt = oConnection.prepareCall("CALL NEWDA(?,?,?,?,?,?,?)") to work, throws an exception err()=1

I even tried oConnection.prepareCall("NEWDA(?,?,?,?,?,?,?)"), oConnection.prepareCall("CALL ""NEWDA""(?,?,?,?,?,?,?)"), oConnection.prepareCall("NEWDA(?,?,?,?,?,?,?)"). createCall (mentioned somewhere) throws the (logical) 423 (method does not exist)

I even granted user FULL PRIVILEGES, and changed PROCEDURE TO match credentials
the Workbench shows:

CREATE DEFINER=myspecialusername@% PROCEDURE NEWDA(IN afm char(9),
IN toplace integer,
IN carnum varchar(15),
IN descr text,
IN quantity integer,
IN skopos text,
OUT newnum integer)
BEGIN …

[jimk]

The err()=1 message is puzzling. Is that the full message? If so, are you sure it occurs during prepareCall? Please provide a simplified, reproducible example that demonstrates the problem, including all necessary code.

[xoristzatziki]

err()=1 is from the error handling (ON ERROR GOTO …). erl() from the same error handling shows the line with oConnection.prepareCall as line causing the error. Calling the procedure from php works fine.

[jimk]

Well, that explains why the message was displayed that way. Maybe you should remove the error handling. Anyway, I cannot help further without a complete example. Did you try my answer?

[jimk]

Try this example adapted from Database procedures and callable Statements (View topic) • Apache OpenOffice Community Forum. Go to Tools → SQL and paste the following.

CREATE PROCEDURE NEWDA( 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

The following code worked for me.

Sub RunStoredProc
    oConnection = ThisComponent.CurrentController.ActiveConnection
    sFormat = "minor"
    oStmt = oConnection.prepareCall("CALL NEWDA(?,?,?)")
    oStmt.setString(1, sFormat)
    oStmt.registerOutParameter(2, com.sun.star.sdbc.DataType.VARCHAR, "ignored")
    oStmt.registerNumericOutParameter(3, com.sun.star.sdbc.DataType.DECIMAL, 2)
    oStmt.execute()
    MsgBox "Mode: " & sFormat & " -- Version: " & oStmt.getString(2) _
          & chr(10) & "Numerical: " & oStmt.getString(3)
   oStmt.close()
End Sub

To run it, go to Tools → Macros → Run Macro.

EDIT:

OConnection::prepareCall: feature not implemented.

We’ve finally found the actual error message! From a web search, it looks like that error is from the MySQL Connector. Similar messages are listed here.

Perhaps the driver needs to be updated. The file on my Windows system is mysql-connector-java-5.1.40-bin.jar, for MySQL server version 5.7.17 and LO 5.4.0.3.

[xoristzatziki]

running macro from Tools → Macros → Run Macro has different evaluation from running macro from within a document using events or buttons. Anyway, I will try the example as soon as I get a new schema )probably today) for tests because I do not want to mesh my project.

[xoristzatziki]

OConnection::prepareCall: feature not implemented.

LO 1:5.1.6~rc2-0ubuntu1~xenial2

UBUNTU

lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.3 LTS
Release:	16.04
Codename:	xenial

[jimk]

See edited answer.

[xoristzatziki]

1. These are openoffice’s problems (which means that may not be related). The only related problem is a getProcedureColumns is a stub but it is from 2009 and the “report” has been removed from pastebin. I also have latest updates on my system.