Windows脚本编程核心技术精解Chapter23.pdf

《Windows脚本编程核心技术精解Chapter23.pdf》由会员分享，可在线阅读，更多相关《Windows脚本编程核心技术精解Chapter23.pdf（24页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Chapter 23Using DatabasesIn This Chapter?Learn about ADO and MDAC,the standard interfaces to any database?Check your ADO and MDAC versions,and update your system with the helpof free downloads?Access the sample database using a DSN-free connection?Read and write to any database using scripts and SQL

2、?Deal with recordsets,count records,delete records,and retrieve informationabout table fields and variable types?Eliminate the“single quote”problem and work around reserved SQLcharacters?Create completely new database tables and delete old ones?Learn all about the Index Server,and use Boolean operat

3、ors to quickly findthe documents you are looking for?Do full text searches and queries in natural language?Limit search scope to specific folders using the hidden ixsso.UtilobjectMicrosoft has made database access easy.By using MDAC(MicrosoftDatabase Access Components),your scripts can connect thems

4、elves to any kind of database and read information using SQL statements.Yourscripts can even store information and change database structures.In thischapter,you learn how to add powerful database capabilities to your scripts.Accessing DatabasesImagine what you could do if your scripts were able to“t

5、alk”to databases.You could create your own little address book,retrieve invoice information,or store user names.Fortunately,Microsoft has created a standard interface to access any kind ofdatabase,including simple text databases as well as full-scale SQL servers orOracle database servers.4684-8 ch23

6、.f.qc 3/3/00 9:45 AM Page 645Checking ADO versionThis interface is called ActiveX Data Objects(ADO).Many products silentlyinstall ADO on your system,so lets check whether ADO is working on yoursystem and whether its up-to-date(see Figure 23-1).23-1.VBSset fs=CreateObject(“Scripting.FileSystemObject”

7、)set wshshell=CreateObject(“WScript.Shell”)msg=“ADO status:”&vbCr&vbCr check whether Connection is accessibleon error resume nextset dummy=CreateObject(“ADODB.Connection”)if not err.number=0 thenMsgBox msg&“ADODB.Connection isnt working!”WScript.Quitelsemsg=msg&“ADODB.Connection is operational.”&vbC

8、rend ifon error goto 0 retrieve fileson error resume nextclsid=wshshell.RegRead(“HKCRADODB.ConnectionCLSID”)exec=wshshell.RegRead(“HKCRCLSID”&clsid&“InProcServer32”)path=Left(exec,InstrRev(exec,“”)-1)path=Left(path,InstrRev(path,“”)-1)&“Ole DB”if not err.number=0 thenMsgBox msg&“Error retrieving fil

9、e version information!”WScript.Quitend ifon error goto 0 check for ADO componentsfilename=“msdadc.dll”if fs.FileExists(path&filename)thenfilever=fs.GetFileVersion(path&filename)msg=msg&filename&“exists:Version“&filever&vbCrelsemsg=msg&filename&“is missing.”&vbCrend iffilename=“oledb32.dll”if fs.File

10、Exists(path&filename)thenfilever=fs.GetFileVersion(path&filename)msg=msg&filename&“exists:Version“&filever&vbCrelsemsg=msg&filename&“is missing.”&vbCr646Part V:Accessing Hidden ComponentsII4684-8 ch23.f.qc 3/3/00 9:45 AM Page 646end ifMsgBox msg,vbInformationThis script makes some assumptions.If you

11、r ADODB.Connection isoperational,but neither file is found,you should search for the files manually.Figure 23-1:Check whether ADO is operational and which MDAC version you have.Use Table 23-1 to calculate the interface version your system currently uses.Table 23-1Information to Determine ADO Version

12、 InstalledADODB.ConnectionMSDADC.DLLOLEDB32.DLLVersionnot operationalmissingmissingno MDAC(MicrosoftData AccessComponents)installedoperational1.50.3506.0missingMDAC 1.5coperational2.0.3002.42.0.1706.0MDAC 2.0operational2.0.3002.232.0.1706.0MDAC 2.0 SP1/SP2operational2.10.3513.02.10.3513.0MDAC 2.1.0.

13、3513.2(SQL)operational2.10.3711.22.10.3711.2MDAC 2.1.1.3711.6(Internet Explorer 5)operational2.10.3711.22.10.3711.9MDAC 2.1.1.3711.11operational2.50.4403.02.50.4403.3MDAC 2.5(Windows2000)Do I need to upgrade or install anything?Heres the good news.If your script reports ADODB.Connectionoperational,y

14、ou are ready for action.Out of curiosity,you could check your MDACversion,but even if its MDAC 1.5,you are fine.Chapter 23:Using Databases647II4684-8 ch23.f.qc 3/3/00 9:45 AM Page 647And heres more good news.If MDAC is missing on your system,you caninstall it for free.Microsoft offers the complete s

15、oftware kit for downloading.But,you guessed it,theres some not-so-good news,too.Download addresseschange frequently,and Microsoft download addresses change even morefrequently.Start your search here: are also available at for MDAC version 2.1 or later.The download is huge,though;more than 6MB are wa

16、iting for you.Never touch a running system.If your system supports ADODB.Connection,then think twice before deciding to upgrade.There are minor inconsistenciesbetween the MDAC versions,and installing a higher version on a system withapplications that use a lower version might very well crash those a

17、pplications.So,heres an easy ruleinstall MDAC only if its not yet installed or if youspecifically need a new feature only available in the update.Remember:Onceyou have upgraded to a higher MDAC version,you cant go back.You canteven uninstall MDAC.Its a one-way street.Do install the MDAC update if an

18、y of the following example scripts raise a“Cant create Object”error.Its a rather common problem:On some systems,MDAC was installed at some time but parts of it became damaged or wereuninstalled.Those systems can access ADODB.Connection,but they fail themoment you try to open a database.Getting Acces

19、s to a DatabaseFirst of all,you need a database to access.ADO is able to store data as plaintext files in case you have no database at hand.On the companion CD,youfind a sample MS Access database.You should use this database for all ofyour experiments if you dont have a database of your own.As it tu

20、rns out,your scripts can change and even add tables to the sample database.Opening and querying a databaseTo use a database,your script needs to open it first.There are many ways to open a database.Most often you will see the DSN approach:Using theControl Panel and its 32-bit ODBC Data Source module

21、,you define a SystemDSN and store all the details about your database in the DSN.Then,yourscripts just refer to the DSN name tag,and ADO reads the connection detailsfrom your DSN definition(see Figure 23-2).However,the DSN approach has some major drawbacks.It requires you to setup the DSN first.You

22、need to go through many dialog boxes.Not so good.Amuch easier way bypasses the DSN.Here,the script provides the connectiondetails manually.Using this approach,you can distribute your database fileand your scripts,and your users can access the data base information rightaway.No further setup is requi

23、red.648Part V:Accessing Hidden ComponentsII4684-8 ch23.f.qc 3/3/00 9:45 AM Page 648Figure 23-2:Dont use fixed DSNs,or you will lose a lot of flexibility.Try it out.Along with the scripts of this chapter,you will find a sampledatabase(sample.mdb).Copy the entire folder onto your hard drive and getrid

24、 of the write-protection attributes.You can then immediately start workingwith this database and even create your very own table design.All files on a CD have a write-protection attribute.If you copy CD-ROM files toyour local hard drive,they keep this attribute,and you cant change the files.Therefor

25、e,whenever you copy data from a CD ROM,you must remove theattribute:Right-click the files and choose Properties.Then,clear the write-protection attribute and click OK.Now,without any further hassle,launch the script 23-2.VBS.It will accessthe database and retrieve the sample information:my name and

26、my e-mailaddress(see Figure 23-3).Figure 23-3:Retrieve database information without even owning a database.Next,launch the script 23-5.VBS.Now you can enter a name and an e-mailaddress and add them to the sample database.To double-check,launch23-2.VBSagain.It worked:The new entry appears(see Figure

27、23-4).You cant add entries if you dont remove the write-protection attribute or ifyou launch the scripts directly from the CD-ROM.Chapter 23:Using Databases649II4684-8 ch23.f.qc 3/3/00 9:45 AM Page 649Figure 23-4:Add new entries to the sample database.Reading database contentsTake a look at how 23-2

28、.VBSretrieves the database information:23-2.VBS determine current foldermyname=WScript.ScriptFullNamemypath=Left(myname,InstrRev(myname,“”)open databaseset db=CreateObject(“ADODB.Connection”)db.Open(“DRIVER=Microsoft Access Driver(*.mdb);DBQ=”_&mypath&“SAMPLE.MDB”)use SQL to select informationsql=“s

29、elect*from info”set rs=db.Execute(sql)read resulting record setdo until rs.EOFlist=list&rs(“id”)&vbTab&rs(“name”)_&“Email to:“&rs(“email”)&vbCrrs.MoveNextloop output informationMsgBox listFirst,the script determines the current folder.Because you are not going touse a static DSN,your script must tel

30、l ADO the location of your database.Inthis case,the database is stored in the same folder with the script.Therefore,the script needs to find out the folder name it is stored in.Next,it opens the database.It first gets a vanilla instance of the ADODB.Connectionobject and calls its open method to open

31、 the real database.Instead of a DSN,it provides the connection details directly,and DRIVER=specifies the database driver.DBQ=specifies the location of the database file.650Part V:Accessing Hidden ComponentsII4684-8 ch23.f.qc 3/3/00 9:45 AM Page 650Figure 23-5:Your scripts can actually read and write

32、 the database.How do you get information from your database?You ask for it.Databasesuse the SQL database query language.To select all data in a specific table,use SELECT*FROM tablename.Because theres only one table stored in thesample database and this table is named info,you use SELECT*FROM INFO.Ex

33、ecuteexecutes your SQL statement and returns the results as recordset.The script now loops through the recordsetuntil the end is reached and EOFis true.It can read the contents of each recordset.RS(“name”)retrieves theinformation of the“name”field of the current record.Alternatively,you canspecify t

34、he field index.RS(1)would also retrieve the name field value becausethe name field happens to be the field number 1(field count starts at 0,andfield 0 in the sample table is called“id”).Always make sure your loop uses MoveNextto move on to the next tablerecord.If you dont,your loop will constantly r

35、ead the same record set,andyoull never reach the end of the table.Finding out design informationSo far,your script has had a great advantage in that it knows the names of thetable fields and can access them directly.What if you dont know how manyfields a table contains or what the table fields are c

36、alled?Find out(see Figure 23-6).Heres how:23-3.VBS determine current foldermyname=WScript.ScriptFullNamemypath=Left(myname,InstrRev(myname,“”)open databaseset db=CreateObject(“ADODB.Connection”)db.Open(“DRIVER=Microsoft Access Driver(*.mdb);DBQ=”_&mypath&“SAMPLE.MDB”)use SQL to select informationsql

37、=“select*from info”set rs=db.Execute(sql)find out number of fieldsfields=rs.fields.countChapter 23:Using Databases651II4684-8 ch23.f.qc 3/3/00 9:45 AM Page 651 find out field namesredim fieldnames(fields-1)for x=0 to fields-1fieldnames(x)=rs.fields(x).namenext output informationmsg=“Table contains“&

38、fields&“fields:”&vbCrfor x=0 to fields-1msg=msg&fieldnames(x)&vbCrnext output informationMsgBox msgFigure 23-6:Read the names of all the fields available in the sample table.Now you can create a script that reads information from any kind of table:23-4.VBS determine current foldermyname=WScript.Scri

39、ptFullNamemypath=Left(myname,InstrRev(myname,“”)open databaseset db=CreateObject(“ADODB.Connection”)db.Open(“DRIVER=Microsoft Access Driver(*.mdb);DBQ=”_&mypath&“SAMPLE.MDB”)use SQL to select informationsql=“select*from info”set rs=db.Execute(sql)do until rs.EOFfor x=0 to rs.fields.count-1list=list&

40、rs(x)&vbTabnextlist=list&vbCrrs.MoveNextloop output informationMsgBox list652Part V:Accessing Hidden ComponentsII4684-8 ch23.f.qc 3/3/00 9:45 AM Page 652Adding new data to your database tableNext,lets examine how write.vbsmanages to insert new data sets intoyour table:23-5.VBS find out current folde

41、rmyname=WScript.ScriptFullNamemypath=Left(myname,InstrRev(myname,“”)ask for new dataname=InputBox(“Enter name to add!”)email=InputBox(“Enter email address to add!”)access databaseset db=CreateObject(“ADODB.Connection”)db.Open(“DRIVER=Microsoft Access Driver(*.mdb);DBQ=”_&mypath&“SAMPLE.MDB”)use SQL

42、to insert new datasql=“insert into info(name,email)values(“_&name&“,“&email&“)”uncomment next line to see SQL statement!MsgBox sql execute sql statementset rs=db.Execute(sql)MsgBox“Added entry!”Its fairly easy:The script uses the same basic architecture.Instead ofSELECT,it uses the INSERT INTOstatem

43、ent.SQL uses single quotes to delimit text information.If you enter single quotesin your data text,they interfere with the single quotes the SQL statementuses.Try it out.Launch write.vbsand include single quotes.Youll get anODBC error.Solving the single quote problemObviously,you cant expect your us

44、ers to avoid single quotes.You dont needto,either.Just convert single quotes to two single quotes.Two single quotesare recognized as data single quotes just as Visual Basic recognizes twoquotes as a single quote inside of a text string.So,after you ask for the information to save in your database,us

45、e acommand like this:datatext=Replace(datatext,“,“”)To pinpoint the issue:Replace a single quote with two single quotes.Chapter 23:Using Databases653II4684-8 ch23.f.qc 3/3/00 9:45 AM Page 653Autonumber fieldsWhy didnt the write.vbsscript add a number for the id column eventhough the new data set sti

46、ll received an id number?When I designed the info table,I assigned the info field a variable type of“AutoNumber.”This value is an automatic counter.It will issue a uniquenumber to each new dataset.Its a good idea to use AutoNumberfieldsbecause theyre your only way to uniquely identify data sets.Retr

47、ieving variable fieldsYou can retrieve the variable types of your table fields,too(see Figure 23-7):23-6.VBS determine current foldermyname=WScript.ScriptFullNamemypath=Left(myname,InstrRev(myname,“”)open databaseset db=CreateObject(“ADODB.Connection”)db.Open(“DRIVER=Microsoft Access Driver(*.mdb);D

48、BQ=”_&mypath&“SAMPLE.MDB”)use SQL to select informationsql=“select*from info”set rs=db.Execute(sql)find out number of fieldsfields=rs.fields.count find out field namesredim fieldnames(fields-1)for x=0 to fields-1fieldnames(x)=rs.fields(x).name&“Type:“&rs.fields(x).typenext output informationmsg=“Tab

49、le contains“&fields&“fields:”&vbCrfor x=0 to fields-1msg=msg&fieldnames(x)&vbCrnext output informationMsgBox msgFigure 23-7:Determine the variable type of database fields.654Part V:Accessing Hidden ComponentsII4684-8 ch23.f.qc 3/3/00 9:45 AM Page 654Table 23-2 shows which numeric values correspond t

50、o the variable type thatcan be stored in the field.Table 23-2Database Variable TypesVariable TypeDescription8,12,129,130,200,201,202,203Text strings7,133,134,135Date11Boolean9,10,13,128,132,204,205SpecialAll elseNumericYou need the variable type when trying to insert new data via INSERT INTO:String