Bug 83737

Cannot save as with new macro basic text beyond End Sub in Main Macro Module 1.

@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

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.

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

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)

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

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

Also:

 INSERT INTO "Table1" VALUES (DEFAULT);

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