Re: [h2] How to run a sql script with duplicate insert queries on h2db

['Balamurali Krishna Ippili' via H2 Database]

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


On Thu, May 26, 2022 at 12:26 PM Andreas Reichel <
andr...@manticore-projects.com> wrote:

> 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 BYid_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
>
> --
> 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+unsubscr...@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.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAG6_yCDSQ_WV6rvA_jSLY_e5_VJ1A%3DK%2B5LU%3Ds29qCtT2R6SyBg%40mail.gmail.com.


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [h2] How to run a sql script with duplicate insert queries on h2db

[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 BYid_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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/23f46631af09f2415f879d6aab19b5a639107ef7.camel%40manticore-projects.com.

[h2] How to run a sql script with duplicate insert queries on h2db

['Balamurali Krishna Ippili' via H2 Database]

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

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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/35ced793-47fa-437c-af4a-36cd3b7e3141n%40googlegroups.com.