About SQL (the languate)

Cheat Sheet

SHOW DATABASES; # show databases
USE dbname; # change database
SHOW TABLES; # show all tables
DESCRIBE tbl1; # show table fields and their properties
SHOW CREATE TABLE tbl1\G # show the command to create table tbl1
SELCT * FROM tbl1 WHERE a>1 LIMIT 10 OFFSET 20;
SELECT * FROM tbl1  WHERE id=1000\G # <---\G means display row verticaly
INSERT INTO table2 SELECT * FROM table1; # copy data from table to table
INSERT INTO table2 (a,b,c,d) SELECT a,b,c,'test' FROM table1 LIMIT 100,10; # copy 10 rows begging from #100 $FILENAME # guess what --no-data does

Enable detailed log in mysql

SHOW VARIABLES LIKE "general_log%";
SET GLOBAL general_log = 'ON';
SHOW VARIABLES LIKE "general_log%";

Enabling MySQL Transactions

Multiple Statements are supported since 3.5.2. MySQL ODBC driver.
You must create a DSN with 'Multiple Statements' True,
Then you can create a new pass-through query with the commands (see example)

Example of MySQL transaction

RunMySQL_Passthrough("START TRANSACTION;UPDATE list SET A=5 WHERE brandid=1; UPDATE list SET A=6 WHERE brandid=2;COMMIT;")

Public Sub RunMySQL_Passthrough(sSQL As String)
Dim cmd                       As ADODB.Command
Dim ConnectStrg               As String

ConnectStrg = "Driver={MySQL ODBC 3.52 Driver};" & _
              "Server=" & SVR & ";" & _
              "Port=3306;" & _
              "Option=131072;" & _
              "Stmt=;" & _
              "Database=dbname;" & _
              "Uid=" & USER_MYSQL_DB & ";" & _
              "Pwd=" & PASS_MYSQL_DB & ""

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = ConnectStrg
  .CommandText = sSQL
  .CommandType = adCmdText                                   
  .Execute
End With
Set cmd = Nothing

End Sub

MS Access, how to create a Pass Through Query

1. Click Create - Query Design.
2. Don't select any tables on the "Show Tables" dialog. Instead, click Close
3. Click "Pass-Through" in the Design ribbon
4. In the Property Sheet, set the "ODBC Connect Str" to a valid Connection String
5. Save that query.