WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
ADO DAO
plus
ADSI LDAP CDO
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

ADO with mySQL

Since mySQL is GNU-ware and the download and setup not that difficult, I adpated a quick WB test script to illustrate using ADO with mySQL. The script goes the extra inch by persisting a mySQL Recordset to XML, and associated scripts (not included here) deal with 'distributed mySQL Recordsets' and linking tables to MS Access. Please see the assumptions in the script header. [written in 2004D]

Stan 

; Winbatch mySQL Connection
; Assumes mySQL Server is Installed and Running and
; that the MySQL ODBC 3.51 Driver has been installed.
;
; The script sets up a DSN-less Connection to the 'test' database
; using root with no password (default setup, obviously not good for production)
; substitute your own UID, PWD if you desire
;
; Then a sample table is created, rows inserted and the table closed.
; Table is then re-opened as a Recordset and persisted to XML
; so you can view the data types in ADO
;
; Stan Littlefield June 26, 2004 [please do not remove this header]
;////////////////////////////////////////////////////////////////////////////////////////

#DefineSubRoutine ErrorProcessing(deleteIni,showerr,logfile)
WbError = LastError()
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   ; 1668 ; "Minor user-defined error"
   ; 2669 ; "Moderate user-defined error"
   ; 3670 ; "Severe user-defined error"
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
If deleteIni
   FileDelete(WbErrorFile)
   IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
   IniWritePvt("","","",WbErrorFile)
EndIf
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
   cSep = StrCat(StrFill("=",50),@CRLF)
   cLogFile = StrCat(DirScript(),"log.err")
   If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
   FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
EndIf
If showerr Then Message("wbErrorHandler",WbErrorMsgText)
Return(1)
#EndSubRoutine

IntControl(73,1,0,0,0)
path = DirScript()
cXML = StrCat(path,"mySQL.xml")
If FileExist(cXML) Then FileDelete(cXML)
DB = 0
DB = CreateObject("ADODB.Connection")

; in Production you want code like
;AddExtender("wwodb34i.dll")
;list=StrUpper( qDriverList() )
;If ! StrIndex(list,"MYSQL ODBC 3.51 DRIVER",0,@FWDSCAN) Then Exit
;just to check that the driver is installed

cConn = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=stan;PWD=;OPTION=3'
DB.Open(cConn)
cSQL = "DROP TABLE IF EXISTS WB_Test"
Display(2,"MySQL Connection Opened Executing...",cSQL)
DB.Execute(cSQL)
cSQL = "CREATE TABLE WB_Test(id int not null primary key, name varchar(20),txt text, entered timestamp)"
Display(2,"Building Table...",cSQL)
DB.Execute(cSQL)
;Note: The TimeStamp Column 'entered' is auto-filled
cSQL = "INSERT INTO WB_Test(id,name,txt) values(1,'First Entry','Winbatch Rocks!')"
Display(2,"Creating Row...",cSQL)
DB.Execute(cSQL)
;let's get a little tricky with our quotes within quotes
cSQL = `INSERT INTO WB_Test(id,name,txt) values(2,2004,"Varchars as numbers don't need quotes")`
Display(2,"Creating Row...",cSQL)
DB.Execute(cSQL)
DB.Close()

;let's see what we did
Display(2,"Opening as Recordset...","Persisting To %cXML%")
RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = 3   ; adUseClient
RS.Open("WB_Test",cConn,1,4)
RS.Save(cXML,1)
RS.Close()


:End
RS = 0
DB = 0
Exit

:WBERRORHANDLER
IntControl(73,1,0,0,0)
ErrorProcessing(1,1,1)
Goto End
;/////////////////////////////////////////////////////////////////////

Article ID:   W16566

File Created: 2014:07:25:08:22:52
Last Updated: 2014:07:25:08:22:52