Creating reports from OCS-NG Inventory using OpenOffice Base.
Because of poor functionality of OOOs Base source editor (it haven't even search facility!), I prefer to develop scripts in MySQL Workbench (both in Windows and in Linux). De-bugged script to be copied into OOO.
Until today all worked fine, including stored procedures and functions. Today I wrote another stored function utilizing data base cursor. On Workbench the script works just perfectly, producing usable results.
I copied script to Base and it failed with error 2013 "lost connection...". In WB the script execution takes about 3-4 seconds to return 348 rows on 4Mbit internet connection and even less on Gbit LAN., so timeouts aren't in charge.
Commenting out parts of stored function, I found that the failure occurs on open cursor_name statement. Well... I can copy-paste WB's script output into spreadsheet, but using all-in-one office package is more convenient.
Let's remind: the same script, calling the same function, does work in WB.
So, if anyone using OOO for accessing MySQL databases, may be you have solution? I asked Q's on OOO forums and asked for TS, but got no usable reply.
edit: Beginning of function
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `get_monitors`(gmhwid int(11)) RETURNS text CHARSET utf8
BEGIN
declare MOV tinytext;
declare RetVal text;
declare NumOfMons, RowNum int;
# CURSOR-related variables
DECLARE done INT DEFAULT FALSE;
declare gmM, gmC, gmD, gmS tinytext charset utf8;
declare MonCursor cursor for select
MANUFACTURER,CAPTION,DESCRIPTION,SERIAL
from ocsweb.monitors
where ocsweb.monitors.hardware_id=gmhwid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
# If there''s an overrider, use it and return
set MOV=(select Realmonitor from accinfo where hardware_id=gmhwid);
if ((MOV<>'') and (not isnull(MOV))) then
return mov;
end if;
# how many monitors for this computer
set NumOfMons=(select count(*) from monitors where hardware_id=gmhwid);
set RowNum=0;
set RetVal='';
#set @sel:=(select MANUFACTURER,CAPTION,DESCRIPTION,SERIAL from monitors where monitors.hardware_id=gmhwid);
return '-';
open MonCursor;
Note two last lines. Fake return with a dash is kinda breakpoint. With it the function works in OOO, but produces no usabel result, of course. Then the main loop started:
lup: loop
set MOV='';
fetch MonCursor into gmM, gmC, gmD, gmS;
But execution will never reach the lup: label, if executing in OOO.
