> From: Frank M. Betz, Jr. [mailto:fbetz@stripped]
> Sent: Monday 08 January 2001 6:11
>
> if that works that's unbelievable. I always thought you
> could only move
> data from table to table from the same database not from two
> different ones.
> How do you establish a connection to two at the same time
> through straight
> SQL?
You have a connection to a server, not to a database, and a server can carry
more than one database (in fact, most of them should: your own database(s)
and its own MySQL database with info about databases, tables and users).
I find it unbelievable that in this whole thread, nobody actually tried it
after that first message ;-)
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
mysql> create table test (txt varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values('Line1'),('Line2'),('Line3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> use test2;
Database changed
mysql> create table tst2 select * from test1.test;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test1.test;
+-------+
| txt |
+-------+
| Line1 |
| Line2 |
| Line3 |
+-------+
3 rows in set (0.00 sec)
mysql> select * from test2.tst2;
+-------+
| txt |
+-------+
| Line1 |
| Line2 |
| Line3 |
+-------+
3 rows in set (0.00 sec)
And it works equally well through ADO/ODBC.
I just put this in an empty form in VB (note that test1 and test2 still
existed from the previous example, so the first thing I do is kill them ;-)
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
cn.Open "Driver=MySQL;Server=<my server>", "root", <my password>
cn.Execute "DROP DATABASE Test1;"
cn.Execute "DROP DATABASE Test2;"
cn.Execute "CREATE DATABASE Test1;"
cn.Execute "CREATE DATABASE Test2;"
cn.Execute "USE Test1;"
cn.Execute "CREATE TABLE Tst1 (Txt varchar(20));"
cn.Execute "INSERT INTO Tst1 VALUES ('Line1'),('Line2');"
cn.Execute "USE Test2;"
cn.Execute "CREATE TABLE Tst2 SELECT * FROM Test1.Tst1;"
Set rs = cn.Execute("SELECT * FROM Tst2;")
Do While Not rs.EOF
Debug.Print rs!Txt
rs.MoveNext
Loop
rs.Close
Set rs = cn.Execute("SELECT * FROM Test1.Tst1;")
Do While Not rs.EOF
Debug.Print rs!Txt
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
