Copy ROW using Temporary Table MySQL

Databases
1

I’m trying to use a ‘SELECT *’ statement to grab a record from one Host.database.table, and insert it into a DifferentHost.database.table. The following works GREAT (and side-steps issues with auto-generated Pkey-IDs), but only when copying across the same Host:

Var xDB As MySQLCommunityServer = New MySQLCommunityServer

// more setup here (left out for brevity)

xDB.ExecuteSQL("INSERT INTO <dbase_name_here>.tmptable_1 SELECT * FROM <another_database>.<identical_table_design> WHERE id = 123;)
xDB.ExecuteSQL("UPDATE <dbase_name_here>.tmptable_1 SET id = NULL;")
xDB.ExecuteSQL("INSERT INTO <dbase_name_here>..<identical_table_design> SELECT * FROM <dbase_name_here>.tmptable_1;")
xDB.ExecuteSQL("DROP TEMPORARY TABLE IF EXISTS <dbase_name_here>.tmptable_1;")
xDB.CommitTransaction
xDB.Close

The same User and Credentials work for connecting to both Hosts, Databases, and their Tables. Obviously the “xDB” variable is a single connection to a single host, which is where I’m struggling.

I’m really hoping to streamline my code by using a “INSERT INTO” combined with “SELECT * FROM” and not have to hard-code Vars and field names.

Is there a best-practices means of copying a record from one host and inserting it into another? (thanks!)

2

If you want the MySQL sever to connect to a foreign database, the search term you are looking for is FEDERATED TABLE

Federated tables provide a view on data residing in a separate instance of MySQL, whether on the same host or an entirely different host. To connect to a different host the transport needs to be in place to allow it.

Federated tables and streamlined do not fit easily in the same sentence. Unless you have a compelling reason to keep it server side, it may be easier to manage two connections at the client.

3

Hi Matthew, thanks for the feedback!

The databases are located across the shop floor, and the (xojo) Service app has to grab data from these points, and save selected rows back to another centralized database for more fun.

Using the advice to manage two connections I have a follow-on question…

I’m now using the Rowset acquired from the 1st dbase query and then opening a 2nd connection to send a new insert statement to the central dbase. However, I’m not easily finding syntax to allow me to do something like “INSERT INTO mydatabase SELECT * myRowSet”.

I can go thru the pains of building a loooong SQL insert statement, but that’s a lot of hand-coding for each “param_1 = (rows.Column(“PARAM_NAME_XYZ”))”.

Anyone know a easier way to iterate thru a RowSet (single Row) and insert it in-whole into a like-for-like table?

4

Does Xojo’s IdxField fit the bill? Allows a loop to iterate fields by numerical position.

Also, if you need the foreign schema to create a temporary table, there is the MySQL, SHOW CREATE TABLE

5

Unsure, but…

Var xDB As MySQLCommunityServer = New MySQLCommunityServer

xDB is used to read from a database and also to write to the other database ?

6

Hi Emile, I’m using one ‘xDB’ to connect to datasource #1, grabbing a Rowset, closing the connection, opening another ‘xDB’ (MySQLCommunityServer) connection to datasource #2, and wanting to find the simplest way to inject this record into a Table in #2 (that has an identical schema).

As Matthew pointed out there’s ways to iterate thru the Rowset, however that’d mean I’d have to have an (iterable?) means of inserting the data into a ‘INSERT’ statement, which is where I’m stuck now.

7

Maybe this will help get you started?

Var sourceDb As New Database

Var sqlInsert As String, sqlValues As String, sqlValue As String
Var maxIdx As Integer, fieldNames As String
Var doInsert As Boolean, idx As Integer

Var rs As RowSet = sourceDb.SelectSQL("SELECT * FROM myTable;")

If Not (rs Is Nil) Then
  If Not (rs.BeforeFirstRow And rs.AfterLastRow) Then
    doInsert = True
    maxIdx = rs.ColumnCount -1
    
    //assemble field names
    For idx = 0 To maxIdx
      fieldNames = fieldNames + "'" + rs.ColumnAt(idx).Name + "',"
    Next
    //trim the last comma
    
    //assemble values
    For idx = 0 To maxIdx
      sqlValue = rs.ColumnAt(idx)
      
      If Not (IsNumeric(sqlValue)) Then
        sqlValues = sqlValues + "'" + sqlValue + "'"
      Else
        sqlValues = sqlValues + sqlValue
      End If
      sqlValues = sqlValues + ","
    Next
    
    //trim the last comma
    fieldNames = fieldNames.Left(fieldNames.Length -1)
    sqlValues = sqlValues.Left(fieldNames.Length -1)
    
    //assemble insert statement 
    sqlInsert = "INSERT INTO myTable(" + fieldNames + ") VALUES(" + sqlValues + ");" 
  End If
End If

//insert into target
Try
  If doInsert Then
    Var targetDb As New Database
    targetDb.ExecuteSQL sqlInsert
  End If
  
Catch DatabaseException
  
End Try
8

I love this strategy - it’s totally reusable as well. I’ll give it a go!