Download it now!
Bug 83737 - BASE MACRO: Cannot use macro to save empty/unmodified new row: insertRow function returns Function Sequence Error
Summary: BASE MACRO: Cannot use macro to save empty/unmodified new row: insertRow fu...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-10 20:47 UTC by Doug
Modified: 2016-06-15 14:36 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
demonstration database: enable macros and press button in Form1, browse to insert row (13.15 KB, application/vnd.sun.xml.base)
2015-07-08 04:19 UTC, Doug
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Doug 2014-09-10 20:47:21 UTC
1.  In Base, open form for editing
2.  In a macro on the form, set oParent as the form and call method oParent.insertRow on a control in the form.
3.  Open form and send cursor to new row.
4.  Activate macro (execute oParent.insertRow)

This should insert the new record into the database, but it does not.  Instead, it returns the following error:

  BASIC runtime error.
  An exception occurred 
  Type: com.sun.star.sdbc.SQLException
  Message: Function sequence error..

The problem was documented in 2010 with OOo on the following message page:

https://forum.openoffice.org/en/forum/viewtopic.php?f=39&t=36903#p169447

Specific implication is that form cannot obtain autogenerated parent key for additional functions, because key is autogenerated by database and record is not saved.
Comment 1 moshe 2014-09-15 05:49:14 UTC
Cannot save as with new macro basic text beyond End Sub in Main Macro Module 1.
Comment 2 Alex Thurgood 2015-01-03 16:23:23 UTC
@Doug : please provide sample odb file containing form and macro code so that we can try to reproduce otherwise we are just guessing at what exactly we are supposed to be doing.

setting to NEEDINFO
Comment 3 Alex Thurgood 2015-01-03 17:41:05 UTC Comment hidden (no-value)
Comment 4 Doug 2015-07-08 04:19:53 UTC
Created attachment 117115 [details]
demonstration database: enable macros and press button in Form1, browse to insert row

The report was commenting on the LO requirement that the parentFrm.IsModified property be True to allow the parentFrm.InsertRow method.  This prevents using the form to insert a blank row, which can be useful for populating subform controls when the user first browses to the insert row, at which time the IsModified property is False.  Seems to me that the better way would be to let PRIMARY key, UNIQUE constraints, FOREIGN keys be the determinants.
Comment 5 Alex Thurgood 2016-06-15 08:36:33 UTC
Tested on

Version: 5.3.0.0.alpha0+
Build ID: 4d49c9601c9b3e26a336e08e057d299895683480
Threads CPU : 2; Version de l'OS :Mac OS X 10.11.5; UI Render : par défaut; 
Locale : fr-FR (fr.UTF-8)

Confirming
Comment 6 Lionel Elie Mamane 2016-06-15 09:23:45 UTC
There is no SQL command that LibreOffice can issue to the database that does what you want in a sane way.

The sane version of what you want is "please insert a row with all values being the default". So we have to give a SQL INSERT command without giving any value. This is not allowed by SQL:

# INSERT INTO "Table1" ( ) VALUES ( );
ERROR:  syntax error at or near ")"
LINE 1: INSERT INTO "Table1" ( ) VALUES ( );
                               ^

1: Unexpected token: ) in statement [INSERT INTO "Table1" ()]


So yes, you need to provide a value, even an explicit NULL or empty string or whatever. Which would translate to:

# INSERT INTO "Table1" ( "text" ) VALUES ( NULL );
INSERT 0 1
2: Command successfully executed.


Now, I admit that just setting an explicit NULL won't work in LibreOffice, you need to set a string and then set NULL again. Yes, this is imperfect.


Why can't we just put an explicit NULL to make the command "pass"? Well, that's because then we override any default value. And we definitely don't want to do that. Theoretically we could set the default value explicitly, but this requires knowing what the default value is, which is very db-specific (need to parse the default value...) and another can of worms.


So, sorry, but add before the insertRow() something like:
    parentFrm.updateString(2, "")
    parentFrm.updateNull(2)
Comment 7 Doug 2016-06-15 14:13:18 UTC
Can I just have a clarification.  The command

  INSERT INTO `table` () VALUES ();

is interpreted by MariaDB 10.1.6, and there is no error.  It performs as desired, using default values for a new row.

Is this a java bug, throwing an error for a legal SQL statement, in which case it really is 'NOTOURBUG'.
Comment 8 Lionel Elie Mamane 2016-06-15 14:33:34 UTC
(In reply to Doug from comment #7)
> Can I just have a clarification.  The command
> 
>   INSERT INTO `table` () VALUES ();
> 
> is interpreted by MariaDB 10.1.6, and there is no error.  It performs as
> desired, using default values for a new row.

Then MariaDB supports that as an extension, that other RDBMS do not support. I tried PostgreSQL, HSQLDB 1.8 and Firebird 2.5.

My guess is that the SQL standard does require support for that. OTOH, I now discover, PostgreSQL and Firebird both support:

INSERT INTO "table" DEFAULT VALUES;

Maybe that's another non-standard extension, but it seems to be more widely supported  :) E.g. also Microsoft SQL Server.

Beyond what SQL supports, would the ODBC / ADO / JDBC drivers also accept such a "insert all defaults" command? I'm not sure either way.
Comment 9 Lionel Elie Mamane 2016-06-15 14:36:33 UTC
Also:

 INSERT INTO "Table1" VALUES (DEFAULT);

Hmm... This one might even be standard. Not supported by HSQLDB 1.8, though.