List:MySQL on Win32« Previous MessageNext Message »
From:Luc Van der Veken Date:January 9 2001 8:56am
Subject:RE: SQL...
View as plain text  
> 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


Thread
Danger! Will Robinson - NewbieSteve Mills7 Jan
  • Re: Danger! Will Robinson - NewbieJohn Dean7 Jan
    • SQL...jed7 Jan
RE: SQL...Rasmus Nyström7 Jan
  • Re: SQL...Jr. Frank M. Betz8 Jan
    • Re: SQL...Paul DuBois8 Jan
      • Re: SQL...Nick Tentomas8 Jan
        • Re: SQL...Paul DuBois8 Jan
    • RE: SQL...Luc Van der Veken9 Jan
      • Re: SQL...jed9 Jan
        • Re: SQL...Paul DuBois9 Jan
      • Re: SQL...Stephen Woodbridge9 Jan
        • RE: SQL...Luc Van der Veken10 Jan
          • Re: SQL...jed10 Jan
      • Re: SQL...jed9 Jan
      • RE: SQL...Paul DuBois9 Jan
      • Re: SQL...jed9 Jan
  • Re: SQL...jed8 Jan
  • Re: SQL...jed8 Jan
    • Re: SQL...Tim Endres8 Jan
      • Re: SQL...Paul DuBois9 Jan
    • Re: SQL...Paul DuBois9 Jan