下载myodbc后安装 示例程序 Option ExplicitPublic Function openMYSqlDB(c As Connection, ByVal userName As String, Optional password As String) As Boolean On Error GoTo errorHandler Dim p As String c.Open "DRIVER={MyODBC Driver};SERVER=localhost;PORT=;DATABASE=test;USER=" & userName & ";PASSWORD=" & password
openMYSqlDB = True Exit Function errorHandler: MsgBox Err.Description, vbCritical, "打开数据库错误" End End FunctionPrivate Sub Command1_Click() Dim Conn As New ADODB.Connection Dim Rs As Recordset Dim Sql As String Dim Rc As String If openMYSqlDB(Conn, "root") = True Then Sql = "select length(name) as l,name,address from mytable where name='" & "张三'" Set Rs = Conn.Execute(Sql) Do While Not Rs.EOF And Not Rs.BOF Rc = Rc & "l=" & Rs.Fields("l").Value & "______" Rc = Rc & "Name=" & Rs.Fields("name").Value & "______" Rc = Rc & " Address=" & Rs.Fields("address").Value & vbCrLf Rs.MoveNext Loop Text1.Text = Rc End If Rs.Close Set Rs = Nothing Conn.Close Set Conn = Nothing End SubPrivate Sub Command2_Click() Dim Conn As New ADODB.Connection 'Dim Rs As Recordset Dim Sql As String Dim Rc As String If openMYSqlDB(Conn, "root") = True Then Sql = "insert into mytable(name,address) values('张三','address here')" Conn.Execute Sql MsgBox "ok" End If 'Rs.Close 'Set Rs = Nothing Conn.Close Set Conn = Nothing End SubPrivate Sub Command3_Click() Dim Conn As New ADODB.Connection 'Dim Rs As Recordset Dim Sql As String Dim Rc As String If openMYSqlDB(Conn, "root") = True Then Sql = "create table MyTable(id int(3) auto_increment not null primary key," Sql = Sql & "name varchar(50)," Sql = Sql & "address varchar(100)" Sql = Sql & ")" Conn.Execute Sql MsgBox "Create Done" End If 'Rs.Close 'Set Rs = Nothing Conn.Close Set Conn = Nothing End SubPrivate Sub Command4_Click() Dim Conn As New ADODB.Connection 'Dim Rs As Recordset Dim Sql As String Dim Rc As String If openMYSqlDB(Conn, "root") = True Then Sql = "drop table mytable" Conn.Execute Sql MsgBox "Delete done" End If 'Rs.Close 'Set Rs = Nothing Conn.Close Set Conn = Nothing End Sub
TO:VBToy(无证编程) 谢谢你的回答,不过不太切题呀,我问的是如何导入,导出, Do While Not Rs.EOF And Not Rs.BOF Rc = Rc & "l=" & Rs.Fields("l").Value & "______" Rc = Rc & "Name=" & Rs.Fields("name").Value & "______" Rc = Rc & " Address=" & Rs.Fields("address").Value & vbCrLf Rs.MoveNext Loop Text1.Text = Rc 这个虽然也可以认为是导出,不过数据多的时候,效率会不会很低呀,
倒入导出和普通的SQL执行有区别吗?
没有区别吗? 用select * from table_name into outfile file_name.txt所得到的是乱码,而且导入的时候,还有点问题, 还有能不能在存储过程中用mysqldump ?
MYSQLDUMP是可执行程序。 不可以放到存储过程里的。
TO yueliangdao0608((深圳PHPER,MSN:[email protected])) 能给个方法吗,我这方面的没做过,谢谢!
你能把你的代码贴出来,大家讨论一下
示例程序
Option ExplicitPublic Function openMYSqlDB(c As Connection, ByVal userName As String, Optional password As String) As Boolean
On Error GoTo errorHandler
Dim p As String
c.Open "DRIVER={MyODBC Driver};SERVER=localhost;PORT=;DATABASE=test;USER=" & userName & ";PASSWORD=" & password
openMYSqlDB = True
Exit Function
errorHandler:
MsgBox Err.Description, vbCritical, "打开数据库错误"
End
End FunctionPrivate Sub Command1_Click()
Dim Conn As New ADODB.Connection
Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root") = True Then
Sql = "select length(name) as l,name,address from mytable where name='" & "张三'"
Set Rs = Conn.Execute(Sql)
Do While Not Rs.EOF And Not Rs.BOF
Rc = Rc & "l=" & Rs.Fields("l").Value & "______"
Rc = Rc & "Name=" & Rs.Fields("name").Value & "______"
Rc = Rc & " Address=" & Rs.Fields("address").Value & vbCrLf
Rs.MoveNext
Loop
Text1.Text = Rc
End If
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End SubPrivate Sub Command2_Click()
Dim Conn As New ADODB.Connection
'Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root") = True Then
Sql = "insert into mytable(name,address) values('张三','address here')"
Conn.Execute Sql
MsgBox "ok"
End If
'Rs.Close
'Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End SubPrivate Sub Command3_Click()
Dim Conn As New ADODB.Connection
'Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root") = True Then
Sql = "create table MyTable(id int(3) auto_increment not null primary key,"
Sql = Sql & "name varchar(50),"
Sql = Sql & "address varchar(100)"
Sql = Sql & ")"
Conn.Execute Sql
MsgBox "Create Done"
End If
'Rs.Close
'Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End SubPrivate Sub Command4_Click()
Dim Conn As New ADODB.Connection
'Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root") = True Then
Sql = "drop table mytable"
Conn.Execute Sql
MsgBox "Delete done"
End If
'Rs.Close
'Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End Sub
谢谢你的回答,不过不太切题呀,我问的是如何导入,导出,
Do While Not Rs.EOF And Not Rs.BOF
Rc = Rc & "l=" & Rs.Fields("l").Value & "______"
Rc = Rc & "Name=" & Rs.Fields("name").Value & "______"
Rc = Rc & " Address=" & Rs.Fields("address").Value & vbCrLf
Rs.MoveNext
Loop
Text1.Text = Rc
这个虽然也可以认为是导出,不过数据多的时候,效率会不会很低呀,
用select * from table_name into outfile file_name.txt所得到的是乱码,而且导入的时候,还有点问题,
还有能不能在存储过程中用mysqldump ?
不可以放到存储过程里的。
MYSQLDUMP参考我博客:
http://blog.chinaunix.net/u/29134/showart_287899.html
谢谢你,mysqldump现在已经会用了,能不能再帮一个忙,我想要数据的备份放在一个.bat文件中执行.
应该怎样写?这是这样写的
D:\Program Files\MySQL\MySQL Server 5.0\bin\
mysqldump -hlocalhost -uroot -p67832357 -P 3306 -B data > c:\oo.sql在CMD中,用
cd D:\Program Files\MySQL\MySQL Server 5.0\bin\
mysqldump -hlocalhost -uroot -p67832357 -P 3306 -B data > c:\oo.sql
这是可以正常备份的,
可是在执行.bat时,得到一个空的文件,请问怎样才对?谢谢!
写道环境变量PATH里去呢?
我的BAT文件 :csdn.bat
@echo off
mysqldump -uroot -p123456 --opt -B test2 > c:\\oo.sql
@echo on
oo.sql
的一部分 :
LOCK TABLES `users_groups` WRITE;
/*!40000 ALTER TABLE `users_groups` DISABLE KEYS */;
INSERT INTO `users_groups` VALUES (11,502),(107,502),(100,503),(110,501),(11
1),(100,501),(102,501),(104,502),(100,502);
/*!40000 ALTER TABLE `users_groups` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2007-09-06 8:14:30