Saturday, September 29, 2007

VBScript to read MYSQL database

' Script start

Const adClipString = 2 ' 00000002
Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sFSpec : sFSpec = ".\imexportmysql.txt"
sFSpec = oFS.GetAbsolutePathName( sFSpec )
sFSpec = Replace( sFSpec, "\", "/" )
Dim oCNCT : Set oCNCT = CreateObject( "ADODB.Connection" )
Dim sCS : sCS = "DRIVER={MySQL ODBC 3.51 Driver};" _
+ "Server=localhost;" _
+ "Port=3306;" _
+ "Option=16384;" _
+ "Stmt=;" _
+ "Database=test;" _
+ "Uid=;" _
+ "Pwd=;"
Dim sSQL
oCNCT.Open sCS
If oFS.FileExists( sFSpec ) Then oFS.DeleteFile sFSpec
sSQL = "SELECT * FROM tblPerson INTO OUTFILE '" + sFSpec + "'"
oCNCT.Execute sSQL
WScript.Echo sSQL
sSQL = "DELETE FROM tblPerson"
oCNCT.Execute sSQL
WScript.Echo sSQL
sSQL = "SELECT COUNT(iId) FROM tblPerson"
WScript.Echo sSQL, "=>", oCNCT.Execute( sSQL ).Fields( 0 ).Value
sSQL = "LOAD DATA LOCAL INFILE '" + sFSpec + "' INTO TABLE tblPerson"
oCNCT.Execute sSQL
WScript.Echo sSQL
sSQL = "SELECT * FROM tblPerson"
WScript.Echo sSQL
WScript.Echo oCNCT.Execute( sSQL ).GetString( adClipString, , vbTab, vbCrLf, "NULL" )
End if
oCNCT.Close

output

dbcnt::doImExportMySQL()
SELECT * FROM tblPerson INTO OUTFILE 'M:/trials/02dbcnct/imexportmysql.txt'
DELETE FROM tblPerson
SELECT COUNT(iId) FROM tblPerson => 0
LOAD DATA LOCAL INFILE 'M:/trials/02dbcnct/imexportmysql.txt' INTO TABLE tblPerson
SELECT * FROM tblPerson
1 A
2 B
3 m
4 e
5 K

Script from : http://www.visualbasicscript.com/fb.aspx?m=50814

Queries on customization. Open http://Orangescripts.blogspot.com and post it

No comments: