ADO
When you are coding with the ADO API and MyODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code here:
Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Longmyconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCountmyrs.Close
myconn.Close
.................................................ODBC Driver for MySQL (via MyODBC)
To connect to a local databaseoConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"
To connect to a remote databaseoConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
.................................................OLE DB Provider for mySQL
oConn.Open "Provider=MySQLProv;" & _
"Data Source=mySQLDB;" & _
"User Id=myUsername;" & _
"Password=myPassword" .........................................................MySQL .NET Native Provider
The MySQL .NET Native Provider is an add-on component to the .NET Framework that allows you to access the MySQL database through the native protocol, without going through OLE DB.Using C#using EID.MySqlClient;
MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "Data Source=localhost;" +
"Database=mySQLDatabase;" +
"User ID=myUsername;" +
"Password=myPassword;" +
"Command Logging=false";
oMySqlConn.Open();
Using VB.NETImports EID.MySqlClient
Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "Data Source=localhost;" & _
"Database=mySQLDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword;" & _
"Command Logging=false"
oMySqlConn.Open()
When you are coding with the ADO API and MyODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code here:
Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Longmyconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCountmyrs.Close
myconn.Close
.................................................ODBC Driver for MySQL (via MyODBC)
To connect to a local databaseoConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"
To connect to a remote databaseoConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
.................................................OLE DB Provider for mySQL
oConn.Open "Provider=MySQLProv;" & _
"Data Source=mySQLDB;" & _
"User Id=myUsername;" & _
"Password=myPassword" .........................................................MySQL .NET Native Provider
The MySQL .NET Native Provider is an add-on component to the .NET Framework that allows you to access the MySQL database through the native protocol, without going through OLE DB.Using C#using EID.MySqlClient;
MySqlConnection oMySqlConn = new MySqlConnection();
oMySqlConn.ConnectionString = "Data Source=localhost;" +
"Database=mySQLDatabase;" +
"User ID=myUsername;" +
"Password=myPassword;" +
"Command Logging=false";
oMySqlConn.Open();
Using VB.NETImports EID.MySqlClient
Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "Data Source=localhost;" & _
"Database=mySQLDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword;" & _
"Command Logging=false"
oMySqlConn.Open()
C.1 ADO - rs.addNew, rs.delete and rs.update
The following ADO(ActiveX Data Objects) sample creates a table my_ado and demonstrates the usage of rs.addNew, rs.delete and rs.update. Private Sub myodbc_ado_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String 'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=35" conn.Open 'create table
conn.Execute "DROP TABLE IF EXISTS my_ado"
conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _
& "txt text, dt date, tm time, ts timestamp)" 'direct insert
conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')" Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer 'fetch the initial table ..
rs.Open "SELECT * FROM my_ado", conn
Debug.Print rs.RecordCount
rs.MoveFirst
Debug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close 'rs insert
rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Name = "Monty"
rs!txt = "Insert row"
rs.Update
rs.Close 'rs update
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-row"
rs.Update
rs.Close 'rs update second time..
rs.Open "SELECT * FROM my_ado"
rs!Name = "update"
rs!txt = "updated-second-time"
rs.Update
rs.Close 'rs delete
rs.Open "SELECT * FROM my_ado"
rs.MoveNext
rs.MoveNext
rs.Delete
rs.Close 'fetch the updated table ..
rs.Open "SELECT * FROM my_ado", conn
Debug.Print rs.RecordCount
rs.MoveFirst
Debug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
conn.Close
End SubC.2 DAO - rs.addNew, rs.update and scrolling
The following DAO (Data Access Objects) sample creates a table my_dao and demonstrates the usage of rs.addNew, rs.update, and result set scrolling. Private Sub myodbc_dao_Click() Dim ws As Workspace
Dim conn As Connection
Dim queryDef As queryDef
Dim str As String 'connect to MySQL using MySQL ODBC 3.51 Driver
Set ws = DBEngine.CreateWorkspace("", "venu", "venu", dbUseODBC)
str = "odbc;DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=35"
Set conn = ws.OpenConnection("test", dbDriverNoPrompt, False, str) 'Create table my_dao
Set queryDef = conn.CreateQueryDef("", "drop table if exists my_dao")
queryDef.Execute Set queryDef = conn.CreateQueryDef("", "create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, " _
& "Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT)")
queryDef.Execute 'Insert new records using rs.addNew
Set rs = conn.OpenRecordset("my_dao")
Dim i As Integer For i = 10 To 15
rs.AddNew
rs!Name = "insert record" & i
rs!Id2 = i
rs.Update
Next i
rs.Close 'rs update..
Set rs = conn.OpenRecordset("my_dao")
rs.Edit
rs!Name = "updated-string"
rs.Update
rs.Close 'fetch the table back...
Set rs = conn.OpenRecordset("my_dao", dbOpenDynamic)
str = "Results:"
rs.MoveFirst
While Not rs.EOF
str = " " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print "DATA:" & str
rs.MoveNext
Wend 'rs Scrolling
rs.MoveFirst
str = " FIRST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str rs.MoveLast
str = " LAST ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str rs.MovePrevious
str = " LAST-1 ROW: " & rs!Id & " , " & rs!Name & ", " & rs!Ts & ", " & rs!Id2
Debug.Print str 'free all resources
rs.Close
queryDef.Close
conn.Close
ws.Close
End SubC.3 RDO - rs.addNew and rs.update
The following RDO (Remote Data Objects) sample creates a table my_rdo and demonstrates the usage of rs.addNew and rs.update. Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim cl As rdoColumn
Dim SQL As String 'cn.Connect = "DSN=test;"
cn.Connect = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=35" cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverPrompt 'drop table my_rdo
SQL = "drop table if exists my_rdo"
cn.Execute SQL, rdExecDirect 'create table my_rdo
SQL = "create table my_rdo(id int, name varchar(20))"
cn.Execute SQL, rdExecDirect 'insert - direct
SQL = "insert into my_rdo values (100,'venu')"
cn.Execute SQL, rdExecDirect SQL = "insert into my_rdo values (200,'MySQL')"
cn.Execute SQL, rdExecDirect 'rs insert
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.AddNew
rs!id = 300
rs!Name = "Insert1"
rs.Update
rs.Close 'rs insert
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.AddNew
rs!id = 400
rs!Name = "Insert 2"
rs.Update
rs.Close 'rs update
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
rs.Edit
rs!id = 999
rs!Name = "updated"
rs.Update
rs.Close 'fetch back...
SQL = "select * from my_rdo"
Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
Do Until rs.EOF
For Each cl In rs.rdoColumns
Debug.Print cl.Value,
Next
rs.MoveNext
Debug.Print
Loop
Debug.Print "Row count="; rs.RowCount 'close
rs.Close
cn.Close
End Sub