<Urgent>How to run a sql script with duplicate insert queries on h2db

[Balamurali Krishna Ippili]

Hi Team,

We have a requirement where we are executing an sql script on h2 db and there is a primary key constraint on the table and if there is any single failure in the script is stop executing the rest of the script. How can we allow the java program to ignore the failed query and continue with rest of the script execution?

Thanks,

Balamurali

[Andreas Reichel]

On Wed, 2022-05-25 at 22:25 -0700, 'Balamurali Krishna Ippili' via H2 Database wrote:

Hi Team,

We have a requirement where we are executing an sql script on h2 db and there is a primary key constraint on the table and if there is any single failure in the script is stop executing the rest of the script. How can we allow the java program to ignore the failed query and continue with rest of the script execution?

Greetings,

while I am not a H2 developer, but just a user I would like to advise the following approach:

1) Do not rely on ignoring duplicates, but filter for distinct by using GROUP BY and Min(_rowid_) or Max(_rowid)

2) Example:

DELETE FROM cfe.instrument_attribute
WHERE ( id_instrument, id_attribute, _rowid_ ) IN ( SELECT  id_instrument
                                                            , id_attribute
                                                            , Min( _rowid_ )
                                                    FROM cfe.instrument_attribute
                                                    GROUP BY    id_instrument
                                                                , id_attribute
                                                    HAVING Count( * ) > 1 )
;

The example above would ensure a UNBIQUE KEY (id_instrument, id_attribute) in table cfe.instrument_attribute (although you would need to repeat that delete until no row is returned).

Good luck

Andreas

[Balamurali Krishna Ippili]

Hi Andreas,

Thanks for your quick response. Your suggestion is awesome. Really appreciate it.

However I have just found a specific method and param provided in H2DB to resolve my issue. There is a RunScript.execute method provided with an option to continue execution even if there is an error. The syntax is like below:

execute(java.lang.String url, java.lang.String user, java.lang.String password, java.lang.String fileName, java.nio.charset.Charset charset, boolean continueOnError)

The last boolean parameter for continueOnError set to true is allowing me to run the rest of the queries in the script.

Thanks,

Balamurali

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/23f46631af09f2415f879d6aab19b5a639107ef7.camel%40manticore-projects.com.

This electronic communication and the information and any files transmitted with it, or attached to it, are confidential and are intended solely for the use of the individual or entity to whom it is addressed and may contain information that is confidential, legally privileged, protected by privacy laws, or otherwise restricted from disclosure to anyone else. If you are not the intended recipient or the person responsible for delivering the e-mail to the intended recipient, you are hereby notified that any use, copying, distributing, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you received this e-mail in error, please return the e-mail to the sender, delete it from your computer, and destroy any printed copy of it.