Ask Your Question
0

call a MySQL prodedure

asked 2017-11-01 09:24:55 +0200

xoristzatziki gravatar image

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 ...

edit retag flag offensive close merge delete

Comments

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.

Jim K gravatar imageJim K ( 2017-11-01 16:12:50 +0200 )edit

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.

xoristzatziki gravatar imagexoristzatziki ( 2017-11-02 12:29:15 +0200 )edit

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?

Jim K gravatar imageJim K ( 2017-11-02 17:29:42 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-11-01 16:11:08 +0200

Jim K gravatar image

updated 2017-11-03 16:46:08 +0200

Try this example adapted from https://forum.openoffice.org/en/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.

edit flag offensive delete link more

Comments

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 gravatar imagexoristzatziki ( 2017-11-03 09:06:28 +0200 )edit

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
xoristzatziki gravatar imagexoristzatziki ( 2017-11-03 09:21:35 +0200 )edit

See edited answer.

Jim K gravatar imageJim K ( 2017-11-03 16:37:36 +0200 )edit
  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.
xoristzatziki gravatar imagexoristzatziki ( 2017-11-04 21:06:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-01 09:24:55 +0200

Seen: 77 times

Last updated: Nov 03 '17