Re: [h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Andreas Reichel]

Greetings.

regarding the Migration to MS SQL Server/T-SQL, what has worked for me
in the past was:

1) Either export to SQL Script and then apply a couple of
Regex/Beanshell Replacements using JEdit:

Simple Replace:

NUMBER(   DECIMAL(
TIMESTAMP DATETIME2
SYSDATE   current_timestamp
CLOB  VARCHAR(max)
BLOB  VARBINARY(max)

Jedit Regex + BeanShell:

TABLE (\w*)\.\"?(\w*)\"?   "TABLE [" + _1.toLowerCase() + "].[" + 
_2.toLowerCase() + "]"
index (\w*)\.(\w*) "INDEX " + _2.toLowerCase()
ON (\w*)\.\"?(\w*)\"?  "ON [" + _1.toLowerCase() + "].[" + _2.toLowerCase() 
+ "]"
(\w*)\.(\w*).NEXTVAL   "NEXT VALUE FOR " + _1.toLowerCase() + "." + 
_2.toLowerCase()
/\*TRANSACT: ([\w\s]*)\*/  _1.toUpperCase()

2) Alternatively, parse your exported SQL with JSQLParser and the use a
customized De-Parser to rewrite it T-SQL compliant.
Illustration is here:
https://www.manticore-projects.com/JSQLParser/usage.html#parse-a-sql-statements

Good luck and cheers
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/f32c5a72d4738c11ead6505a2d89f9ac972549be.camel%40manticore-projects.com.

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Evgenij Ryazanov]

Scripts generated by this tool aren't really suitable for data migration.

It will be better to use the SCRIPT 
 command or the Script 
tool to produce an SQL without all this messy temporary tables and edit 
their resulting script as necessary. They also can be used to export 
different tables separately.

Unfortunately, different database systems aren't really compatible with 
each other even in their basic functionality, so usually you cannot execute 
an SQL dump from one database system in another database system without 
some modifications, such as replacements of data types and other.

There are some third-party tools for such migrations, but usually they 
support only few database systems and I don't know whether some of them 
supports migrations from H2 to MS SQL or not.

-- 
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/2ec41a0a-b297-4d62-becd-2b4cac94cc38n%40googlegroups.com.

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Ram K]

thank you for the reply, I have gone through the generated script, some how 
the RunScript did not fail after creating the tamporary (O_N) tables.

I manually copied the script and ran in it in the console worked. 

I am trying to migrate to sql server,  could you please give me some tips 
to migrate to sql server (mssql).

Thank you,
Ram

On Wednesday, January 4, 2023 at 12:50:36 AM UTC-8 Evgenij Ryazanov wrote:

> Hi!
>
> Table definition and table data are stored separately and they are 
> recovered separately too.
>
> The recovery tool works in the following way (I skipped unrelated steps 
> for simplicity):
> 1. It constructs a temporary table with LOB chunks from all tables first.
> 2. Then, it recovers data from tables, it creates a new temporary table 
> for each found table for that purpose. LOB values for them are constructed 
> from chunks from (1). Actual table names, column names, data types and 
> other attributes aren't yet known at that moment.
> 3. Next, it tries to recover metadata of database, including definitions 
> of actual tables.
> 4. After that, If metadata of some table was recovered on step (3) and 
> data for that table was recovered on step (2) data is copied from the 
> temporary table into actual one.
> 5. Finally, all temporary tables are dropped.
>
> This strategy isn't perfect and can potentially be improved, at least for 
> some cases when both data and metadata are recoverable.
>

-- 
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/8205720d-46a2-4b0d-a590-bfab00c900bcn%40googlegroups.com.

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

[Evgenij Ryazanov]

Hi!

Table definition and table data are stored separately and they are 
recovered separately too.

The recovery tool works in the following way (I skipped unrelated steps for 
simplicity):
1. It constructs a temporary table with LOB chunks from all tables first.
2. Then, it recovers data from tables, it creates a new temporary table for 
each found table for that purpose. LOB values for them are constructed from 
chunks from (1). Actual table names, column names, data types and other 
attributes aren't yet known at that moment.
3. Next, it tries to recover metadata of database, including definitions of 
actual tables.
4. After that, If metadata of some table was recovered on step (3) and data 
for that table was recovered on step (2) data is copied from the temporary 
table into actual one.
5. Finally, all temporary tables are dropped.

This strategy isn't perfect and can potentially be improved, at least for 
some cases when both data and metadata are recoverable.

-- 
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/a60484f7-f4c2-456a-8967-d4644854dd48n%40googlegroups.com.