B4J Question Convert SQLite table to MySQL

[aaronk]

Hi,

I made a mistake when setting up and developing my B4J app, and I created a big SQLite database with a heap of columns like:

Column 1 | Column 2 | ...... | Column 300

(yes, has a lot of columns in the one table, this is the mistake I made.)

The column names are all different.

I now want to convert it to a MySQL database.

I want to change it to make it so its like the following, which I think it a better way in doing it:

Column 1 | Column 2 | Column 3

This way I can use Column 1 as the Account value, Column 2 will be the old column name, and the Column 3 will be the value from the old database.

Not sure if the following is the best way in doing it, or if there is a better way in doing it..

This will create the new MySQL table

    ' Create new table in MySQL database
    Dim SQLString As String
    SQLString = "CREATE TABLE table1" & "(Account TEXT, Location TEXT, Value TEXT)"
 
    Dim sql As SQL = pool.GetConnection
    Try
        'work with sql
        sql.ExecNonQuery(SQLString)
    Catch
        'handle failure
        Log(LastException.Message)
    End Try
    sql.Close

Then I used the following to read the old database, and then move it to the MySQL table:

Dim sql As SQL = pool.GetConnection
 
    'RS holds old SQLite database
    Dim RS As ResultSet = db_old.ExecQuery("SELECT * FROM `old_table`")
 
    Do While RS.NextRow
        Try
        'work with sql
        For i = 0 To RS.ColumnCount - 1
            If RS.GetColumnName(i) = "Account" Then
            Else
                sql.ExecNonQuery2("INSERT INTO table1 VALUES(?,?,?)", Array As Object(RS.GetString("Account"), RS.GetColumnName(i), RS.GetString(RS.GetColumnName(i))))
            End If
 
        Next

        Catch
        'handle failure
        Log(LastException.Message)
        End Try

    Loop
     
    sql.Close
    RS.Close
     
    Log("Done converting!")

I have over 350 rows in the the SQLite database, which has 300 columns.

Based on my calculations moving it over to the MySQL database, will take approx 6 hours. (approx 1 minute per row)

Is the above the correct way in doing this, or is there a better easier way in converting it ?

 

[Erel]

SQLString = "CREATE TABLE table1" & "(Account TEXT, Location TEXT, Value TEXT)"

Why is this strange syntax needed?

Is the above the correct way in doing this

No. You should create a single transaction for all the inserts. It will be 1000 times faster.
Or even better use batch insert: https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

 

[aaronk]

Why is this strange syntax needed?

It was a mistake. I didn't realise I had it like that, I will fix it up in my B4J app.

No. You should create a single transaction for all the inserts. It will be 1000 times faster.
Or even better use batch insert: https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

I changed it to:

Log("start converting..")

    Dim sql As SQL = pool.GetConnection
  
    'RS holds old SQLite database
    Dim RS As ResultSet = db_old.ExecQuery("SELECT * FROM `old_table`")
    Dim numofitems As Int = 0
    Do While RS.NextRow
        Try
        'work with sql
        For i = 0 To RS.ColumnCount - 1
            If RS.GetColumnName(i) = "Account" Then
            Else
                sql.AddNonQueryToBatch("INSERT INTO table1 VALUES(?,?,?)", Array As Object(RS.GetString("Account"), RS.GetColumnName(i), RS.GetString(RS.GetColumnName(i))))
               numofitems = numofitems + 1
            End If
        Next

        Catch
        'handle failure
        Log(LastException.Message)
        End Try
    Loop
  
    RS.Close

    Log("numofitems = " & numofitems) ' this logs 458430
    Log("converting Done.")
    Log("inserting values..")

    Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
      
    sql.Close
  
    Log("Done!")

Is this correct?

It takes approx 2 minutes to log 'converting Done.' Then it sits on 'inserting values..'
I am guessing at this stage it's running the 'insert into' commands into the MySQL database.
I assume this will take sometime to complete? If so, how long do you think it will take?

There is 458430 'insert into' commands.

 

[Erel]

Looks correct. You need to try it. Impossible to guess.

 

[Jorge M A]

I now want to convert it to a MySQL database.

If it's just a one-time conversion, maybe it can help you:

DBeaver Community | Free Universal Database Tool

dbeaver.io

(Edit Typo)

 

[aaronk]

If it's just a one-time conversion, maybe it can help you:

Looks interesting. I will have a look.