外文翻译-数据库的工作

0英文原文WorkingwithDatabasesThischapterdescribeshowtouseSQLstatementsinembeddedapplicationstocontroldatabases.Therearethreedatabasestatementsthatsetupandopendatabasesforaccess:SETDATABASEdeclaresadatabasehandle,associatesthehandlewithanactualdatabasefile,andoptionallyassignsoperationalparametersforthedatabase.SETNAMESoptionallyspecifiesthecharactersetaclientapplicationusesforCHAR,VARCHAR,andtextBlobdata.TheserverusesthisinformationtotransliteratefromadatabasesdefaultcharactersettotheclientscharactersetonSELECToperations,andtotransliteratefromaclientapplicationscharactersettothedatabasecharactersetonINSERTandUPDATEoperations.gCONNECTopensadatabase,allocatessystemresourcesforit,andoptionallyassignsoperationalparametersforthedatabase.Alldatabasesmustbeclosedbeforeaprogramends.AdatabasecanbeclosedbyusingDISCONNECT,orbyappendingtheRELEASEoptiontothefinalCOMMITorROLLBACKinaprogram.DeclaringadatabaseBeforeadatabasecanbeopenedandusedinaprogram,itmustfirstbedeclaredwithSETDATABASEto:CHAPTER3WORKINGWITHDATABASES.Establishadatabasehandle.Associatethedatabasehandlewithadatabasefilestoredonalocalorremotenode.Adatabasehandleisaunique,abbreviatedaliasforanactualdatabasename.DatabasehandlesareusedinsubsequentCONNECT,COMMITRELEASE,andROLLBACKRELEASEstatementstospecifywhichdatabasestheyshouldaffect.ExceptindynamicSQL(DSQL)applications,databasehandlescanalsobeusedinsidetransactionblockstoqualify,ordifferentiate,tablenameswhentwoormoreopendatabasescontainidenticallynamedtables.Eachdatabasehandlemustbeuniqueamongallvariablesusedinaprogram.Databasehandlescannotduplicatehost-languagereservedwords,andcannotbe1InterBasereservedwords.Thefollowingstatementillustratesasimpledatabasedeclaration:EXECSQLSETDATABASEDB1=employee.gdb;Thisdatabasedeclarationidentifiesthedatabasefile,employee.gdb,asadatabasetheprogramuses,andassignsthedatabaseahandle,oralias,DB1.Ifaprogramrunsinadirectorydifferentfromthedirectorythatcontainsthedatabasefile,thenthefilenamespecificationinSETDATABASEmustincludeafullpathname,too.Forexample,thefollowingSETDATABASEdeclarationspecifiesthefullpathtoemployee.gdb:EXECSQLSETDATABASEDB1=/interbase/examples/employee.gdb;Ifaprogramandadatabasefileitusesresideondifferenthosts,thenthefilenamespecificationmustalsoincludeahostname.ThefollowingdeclarationillustrateshowaUnixhostnameisincludedaspartofthedatabasefilespecificationonaTCP/IPnetwork:EXECSQLSETDATABASEDB1=jupiter:/usr/interbase/examples/employee.gdb;OnaWindowsnetworkthatusestheNetbeuiprotocol,specifythepathasfollows:EXECSQLSETDATABASEDB1=/venus/C:/Interbase/examples/employee.gdb;DECLARINGADATABASEEMBEDDEDSQLGUIDE37DeclaringmultipledatabasesAnSQLprogram,butnotaDSQLprogram,canaccessmultipledatabasesatthesametime.Inmulti-databaseprograms,databasehandlesarerequired.Ahandleisusedto:1.Referenceindividualdatabasesinamulti-databasetransaction.2.Qualifytablenames.3.SpecifydatabasestoopeninCONNECTstatements.2IndicatedatabasestoclosewithDISCONNECT,COMMITRELEASE,andROLLBACKRELEASE.DSQLprogramscanaccessonlyasingledatabaseatatime,sodatabasehandleuseisrestrictedtoconnectingtoanddisconnectingfromadatabase.Inmulti-databaseprograms,eachdatabasemustbedeclaredinaseparateSETDATABASEstatement.Forexample,thefollowingcodecontainstwoSETDATABASEstatements:.EXECSQLSETDATABASEDB2=employee2.gdb;EXECSQLSETDATABASEDB1=employee.gdb;.4UsinghandlesfortablenamesWhenthesametablenameoccursinmorethanonesimultaneouslyaccesseddatabase,adatabasehandlemustbeusedtodifferentiateonetablenamefromanother.Thedatabasehandleisusedasaprefixtotablenames,andtakestheformhandle.table.Forexample,inthefollowingcode,thedatabasehandles,TESTandEMP,areusedtodistinguishbetweentwotables,eachnamedEMPLOYEE:.EXECSQLDECLAREIDMATCHCURSORFORSELECTTESTNOINTO:matchidFROMTEST.EMPLOYEEWHERETESTNO100;EXECSQLDECLAREEIDMATCHCURSORFORSELECTEMPNOINTO:empidFROMEMP.EMPLOYEEWHEREEMPNO=:matchid;3.CHAPTER3WORKINGWITHDATABASES38INTERBASE6IMPORTANTThisuseofdatabasehandlesappliesonlytoembeddedSQLapplications.DSQLapplicationscannotaccessmultipledatabasessimultaneously.4UsinghandleswithoperationsInmulti-databaseprograms,databasehandlesmustbespecifiedinCONNECTstatementstoidentifywhichdatabasesamongseveraltoopenandprepareforuseinsubsequenttransactions.DatabasehandlescanalsobeusedwithDISCONNECT,COMMITRELEASE,andROLLBACKRELEASEtospecifyasubsetofopendatabasestoclose.ToopenandprepareadatabasewithCONNECT,see“Openingadatabase”onpage41.TocloseadatabasewithDISCONNECT,COMMITRELEASE,orROLLBACKRELEASE,see“Closingadatabase”onpage49.Tolearnmoreaboutusingdatabasehandlesintransactions,see“Accessinganopendatabase”onpage48.PreprocessingandruntimedatabasesNormally,eachSETDATABASEstatementspecifiesasingledatabasefiletoassociatewithahandle.Whenaprogramispreprocessed,gpreusesthespecifiedfiletovalidatetheprogramstableandcolumnreferences.Later,whenauserrunstheprogram,thesamedatabasefileisaccessed.Differentdatabasescanbespecifiedforpreprocessingandruntimewhennecessary.4UsingtheCOMPILETIMEclauseAprogramcanbedesignedtorunagainstanyoneofseveralidenticallystructureddatabases.Inothercases,theactualdatabasethataprogramwilluseatruntimeisnotavailablewhenaprogramispreprocessedandcompiled.Insuchcases,SETDATABASEcanincludeaCOMPILETIMEclausetospecifyadatabaseforgpretotestagainstduringpreprocessing.Forexample,thefollowingSETDATABASEstatementdeclaresthatemployee.gdbistobeusedbygpreduringpreprocessing:4EXECSQLSETDATABASEEMP=COMPILETIMEemployee.gdb;IMPORTANTThefilespecificationthatfollowstheCOMPILETIMEkeywordmustalwaysbeahard-coded,quotedstring.DECLARINGADATABASEEMBEDDEDSQLGUIDE39WhenSETDATABASEusestheCOMPILETIMEclause,butnoRUNTIMEclause,anddoesnotspecifyadifferentdatabasefilespecificationinasubsequentCONNECTstatement,thesamedatabasefileisusedbothforpreprocessingandruntime.TospecifydifferentpreprocessingandruntimedatabaseswithSETDATABASE,useboththeCOMPILETIMEandRUNTIMEclauses.4UsingtheRUNTIMEclauseWhenadatabasefileisspecifiedforuseduringpreprocessing,SETDATABASEcanspecifyadifferentdatabasetouseatruntimebyincludingtheRUNTIMEkeywordandaruntimefilespecification:EXECSQLSETDATABASEEMP=COMPILETIMEemployee.gdbRUNTIMEemployee2.gdb;ThefilespecificationthatfollowstheRUNTIMEkeywordcanbeeitherahard-coded,quotedstring,orahost-languagevariable.Forexample,thefollowingCcodefragmentpromptstheuserforadatabasename,andstoresthenameinavariablethatisusedlaterinSETDATABASE:.chardb_name125;.printf(Enterthedesireddatabasename,includingnodeandpath):n);gets(db_name);EXECSQL5SETDATABASEEMP=COMPILETIMEemployee.gdbRUNTIME:db_name;.Notehost-languagevariablesinSETDATABASEmustbepreceded,asalways,byacolon.ControllingSETDATABASEscopeBydefault,SETDATABASEcreatesahandlethatisglobaltoallmodulesinanapplication.Aglobalhandleisonethatmaybereferencedinallhost-languagemodulescomprisingtheprogram.SETDATABASEprovidestwooptionalkeywordstochangethescopeofadeclaration:gSTATIClimitsdeclarationscopetothemodulecontainingtheSETDATABASEstatement.NootherprogrammodulescanseeoruseadatabasehandledeclaredSTATIC.CHAPTER3WORKINGWITHDATABASES40INTERBASE6EXTERNnotifiesgprethataSETDATABASEstatementinamoduleduplicatesaglobally-declareddatabaseinanothermodule.IftheEXTERNkeywordisused,thenanothermodulemustcontaintheactualSETDATABASEstatement,oranerroroccursduringcompilation.TheSTATICkeywordisusedinamulti-moduleprogramtorestrictdatabasehandleaccesstothesinglemodulewhereitisdeclared.ThefollowingexampleillustratestheuseoftheSTATICkeyword:EXECSQLSETDATABASEEMP=STATICemployee.gdb;TheEXTERNkeywordisusedinamulti-moduleprogramtosignalthatSETDATABASEinonemoduleisnotanactualdeclaration,butreferstoadeclarationmadeinadifferentmodule.Gpreusesthisinformationduringpreprocessing.ThefollowingexampleillustratestheuseoftheEXTERNkeyword:6EXECSQLSETDATABASEEMP=EXTERNemployee.gdb;IfanapplicationcontainsanEXTERNreference,thenwhenitisusedatruntime,theactualSETDATABASEdeclarationmustbeprocessedfirst,andthedatabaseconnectedbeforeothermodulescanaccessit.AsingleSETDATABASEstatementcancontaineithertheSTATICorEXTERNkeyword,butnotboth.AscopedeclarationinSETDATABASEappliestobothCOMPILETIMEandRUNTIMEdatabases.SpecifyingaconnectioncharactersetWhenaclientapplicationconnectstoadatabase,itmayhaveitsowncharactersetrequirements.Theserverprovidingdatabaseaccesstotheclientdoesnotknowabouttheserequirementsunlesstheclientspecifiesthem.TheclientapplicationspecifiesitscharactersetrequirementusingtheSETNAMESstatementbeforeitconnectstothedatabase.SETNAMESspecifiesthecharactersettheservershouldusewhentranslatingdatafromthedatabasetotheclientapplication.Similarly,whentheclientsendsdatatothedatabase,theservertranslatesthedatafromtheclientscharactersettothedatabasesdefaultcharacterset(orthecharactersetforanindividualcolumnifitdiffersfromthedatabasesdefaultcharacterset).Forexample,thefollowingstatementsspecifythattheclientisusingtheDOS437characterset,thenconnecttothedatabase:EXECSQLOPENINGADATABASEEMBEDDEDSQLGUIDE41SETNAMESDOS437;EXECSQLCONNECTeurope.gdbUSERJAMESPASSWORDU4EEAH;Formoreinformationaboutcharactersets,seetheDataDefinitionGuide.ForthecompletesyntaxofSETNAMESandCONNECT,seetheLanguageReference.7OpeningadatabaseAfteradatabaseisdeclared,itmustbeattachedwithaCONNECTstatementbeforeitcanbeused.CONNECT:1.Allocatessystemresourcesforthedatabase.2.Determinesifthedatabasefileislocal,residingonthesamehostwheretheapplicationitselfisrunning,orremote,residingonadifferenthost.3.Opensthedatabaseandexaminesittomakesureitisvalid.InterBaseprovidestransparentaccesstoalldatabases,whetherlocalorremote.Ifthedatabasestructureisinvalid,theon-diskstructure(ODS)numberdoesnotcorrespondtotheonerequiredbyInterBase,orifthedatabaseiscorrupt,InterBasereportsanerror,andpermitsnofurtheraccess.Optionally,CONNECTcanbeusedtospecify:4.Ausernameandpasswordcombinationthatischeckedagainsttheserverssecuritydatabasebeforeallowingtheconnecttosucceed.Usernamescanbeupto31characters.Passwordsarerestrictedto8characters.5.AnSQLrolenamethattheuseradoptsonconnectiontothedatabase,providedthattheuserhaspreviouslybeengrantedmembershipintherole.Regardlessofrolemembershipsgranted,theuserbelongstonoroleunlessspecifiedwiththisROLEclause.Theclientcanspecifyatmostoneroleperconnection,andcannotswitchrolesexceptbyreconnecting.6.Thesizeofthedatabasebuffercachetoallocatetotheapplicationwhenthedefaultcachesizeisinappropriate.UsingsimpleCONNECTstatementsInitssimplestform,CONNECTrequiresoneormoredatabaseparameters,eachspecifyingthenameofadatabasetoopen.Thenameofthedatabasecanbea:DatabasehandledeclaredinapreviousSETDATABASEstatement.CHAPTER3WORKINGWITHDATABASES842INTERBASE61.Host-languagevariable.2.Hard-codedfilename.4UsingadatabasehandleIfaprogramusesSETDATABASEtoprovidedatabasehandles,thosehandlesshouldbeusedinsubsequentCONNECTstatementsinsteadofhard-codednames.Forexample,.EXECSQLSETDATABASEDB1=employee.gdb;EXECSQLSETDATABASEDB2=employee2.gdb;EXECSQLCONNECTDB1;EXECSQLCONNECTDB2;.ThereareseveraladvantagestousingadatabasehandlewithCONNECT:1.Longfilespecificationscanbereplacedbyshorter,mnemonichandles.2.Handlescanbeusedtoqualifytablenamesinmulti-databasetransactions.DSQLapplicationsdonotsupportmulti-databasetransactions.3.Handlescanbereassignedtootherdatabasesasneeded.4.ThenumberofdatabasecachebufferscanbespecifiedasanadditionalCONNECTparameter.Formoreinformationaboutsettingthenumberofdatabasecachebuffers,see“Settingdatabasecachebuffers”onpage47.4Usingstringsorhost-languagevariablesInsteadofusingadatabasehandle,CONNECTcanuseadatabasenamesuppliedatruntime.Thedatabasenamecanbesuppliedaseitherahost-languagevariableorahard-coded,quotedstring.9ThefollowingCcodedemonstrateshowaprogramaccessingonlyasingledatabasemightimplementCONNECTusingafilenamesolicitedfromauseratruntime:.charfname125;.printf(Enterthedesireddatabasename,includingnodeandpath):n);OPENINGADATABASEEMBEDDEDSQLGUIDE43gets(fname);.EXECSQLCONNECT:fname;.TipThistechniqueisespeciallyusefulforprogramsthataredesignedtoworkwithmanyidenticallystructureddatabases,oneatatime,suchasCAD/CAMorarchitecturaldatabases.MULTIPLEDATABASEIMPLEMENTATIONTouseadatabasespecifiedbytheuserasahost-languagevariableinaCONNECTstatementinmulti-databaseprograms,followthesesteps:1.DeclareadatabasehandleusingthefollowingSETDATABASEsyntax:EXECSQLSETDATABASEhandle=COMPILETIMEdbname;Here,handleisahard-codeddatabasehandlesuppliedbytheprogrammer,dbnameisaquoted,hard-codeddatabasenameusedbygpreduringpreprocessing.2.Prompttheuserforadatabasetoopen.3.Storethedatabasenameenteredbytheuserinahost-languagevariable.4.Usethehandletoopenthedatabase,associatingthehost-languagevariablewiththe10handleusingthefollowingCONNECTsyntax:EXECSQLCONNECT:variableAShandle;ThefollowingCcodeillustratesthesesteps:.charfname125;.EXECSQLSETDATABASEDB1=employee.gdb;printf(Enterthedesireddatabasename,includingnodeandpath):n);gets(fname);EXECSQLCONNECT:fnameASDB1;.CHAPTER3WORKINGWITHDATABASES44INTERBASE6Inthisexample,SETDATABASEprovidesahard-codeddatabasefilenameforpreprocessingwithgpre.Whenauserrunstheprogram,thedatabasespecifiedinthevariable,fname,isusedinstead.4Usingahard-codeddatabasenamesINSINGE-DATABASEPROGRAMSInasingle-databaseprogramthatomitsSETDATABASE,CONNECTmustcontainahard-coded,quotedfilenameinthefollowingformat:EXECSQLCONNECThostpathfilename;hostisrequiredonlyifaprogramandadatabasefileitusesresideondifferentnodes.Similarly,pathisrequiredonlyifthedatabasefiledoesnotresideinthecurrentworkingdirectory.Forexample,thefollowingCONNECTstatementcontainsahard-codedfilenamethatincludesbothaUnixhostnameandapathname:11EXECSQLCONNECTvaldez:usr/interbase/examples/employee.gdb;NoteHostsyntaxisspecifictoeachserverplatform.IMPORTANTAprogramthataccessesmultipledatabasescannotusethisformofCONNECT.INMULTI-DATABASEPROGRAMSAprogramthataccessesmultipledatabasesmustdeclarehandlesforeachoftheminseparateSETDATABASEstatements.ThesehandlesmustbeusedinsubsequentCONNECTstatementstoidentifyspecificdatabasestoopen:.EXECSQLSETDATABASEDB1=employee.gdb;EXECSQLSETDATABASEDB2=employee2.gdb;EXECSQLCONNECTDB1;EXECSQLCONNECTDB2;.Later,whentheprogramclosesthesedatabases,thedatabasehandlesarenolongerinuse.Thesehandlescanbereassignedtootherdatabasesbyhard-codingafilenameinasubsequentCONNECTstatement.Forexample,OPENINGADATABASEEMBEDDEDSQLGUIDE45.EXECSQLDISCONNECTDB1,DB2;EXECSQLCONNECTproject.gdbASDB1;12.AdditionalCONNECTsyntaxCONNECTsupportsseveralformatsforopeningdatabasestoprovideprogrammingflexibility.Thefollowingtableoutlineseachpossiblesyntax,providesdescriptionsandexamples,andindicateswhetherCONNECTcanbeusedinprogramsthataccesssingleormultipledatabases:ForacompletediscussionofCONNECTsyntaxanditsuses,seetheLanguageReference.SyntaxDescriptionExampleSingleaccessMultipleaccessCONNECTdbfile;Opensasingle,hard-codeddatabasefile,dbfile.EXECSQLCONNECTemployee.gdb;YesNoCONNECThandle;Opensthedatabasefileassociatedwithapreviouslydeclareddatabasehandle.ThisisthepreferredCONNECTsyntax.EXECSQLCONNECTEMP;YesYesCONNECTdbfileAShandle;Opensahard-codeddatabasefile,dbfile,andassignsapreviouslydeclareddatabasehandletoit.EXECSQLCONNECTemployee.gdbASEMP;YesYesCONNECT:varnameAShandle;Opensthedatabasefilestoredinthehost-languagevariable,varname,andassignsapreviouslydeclareddatabasehandletoit.13EXECSQLCONNECT:fnameASEMP;YesYesTABLE3.1CONNECTsyntaxsummaryCHAPTER3WORKINGWITHDATABASES46INTERBASE6AttachingtomultipledatabasesCONNECTcanattachtomultipledatabases.ToopenalldatabasesspecifiedinpreviousSETDATABASEstatements,useeitherofthefollowingCONNECTsyntaxoptions:EXECSQLCONNECTALL;EXECSQLCONNECTDEFAULT;CONNECTcanalsoattachtoaspecifiedlistofdatabases.Separateeachdatabaserequestfromotherswithcommas.Forexample,thefollowingstatementopenstwodatabasesspecifiedbytheirhandles:EXECSQLCONNECTDB1,DB2;Thenextstatementopenstwohard-codeddatabasefilesandalsoassignsthemtopreviouslydeclaredhandles:EXECSQLCONNECTemployee.gdbASDB1,employee2.gdbASDB2;TipOpeningmultipledatabaseswithasingleCONNECTismosteffectivewhenaprogramsdatabaseaccessissimpleandclear.Incomplexprogramsthatopenandcloseseveraldatabases,thatsubstitutedatabasenameswithhost-languagevariables,orthatassignmultiplehandlestothesamedatabase,useseparateCONNECTstatementstomakeprogramcodeeasiertoread,debug,andmodify.HandlingCONNECTerrors.TheWHENEVERstatementshouldbeusedtotrapandhandleruntimeerrorsthatoccurduringdatabasedeclaration.ThefollowingCcode14fragmentillustratesanerror-handlingroutinethatdisplayserrormessagesandendstheprograminanorderlyfashion:.EXECSQLWHENEVERSQLERRORGOTOerror_exit;.OPENINGADATABASEEMBEDDEDSQLGUIDE47:error_exitisc_print_sqlerr(sqlcode,status_vector);EXECSQLDISCONNECTALL;exit(1);.ForacompletediscussionofSQLerrorhandling,seeChapter12,“ErrorHandlingandRecovery.”15中文翻译数据库的工作这章描述怎样使用在嵌入式应用过程中的SQL语句控制数据库。有3个数据库陈述建立并且打开进入的数据库：确定数据库宣布一数据库经营，把这个柄与一真实数据库文件联系起来，并且选择分配给数据库的操作的参数。确定名字选择指定客户应用为CHAR，VARCHAR和正文一些数据使用的字符集。服务器使用这信息从形成客户性质从而选择经营的一数据库反映性质直译，从应用性质和更新操作的数据库的一客户那里直译。连结打开数据库，分配去它的系统资源，并且选择因那些数据库而分配操作参数。在一个程序结束之前，全部数据库必须被关闭。一数据库可能通过使用不连接或者在附加选择对最后做的释放或者在一个程序内的返回而被关闭。宣布数据库在数据库之前可能被打开并且被在计划内使用，它必须首先确定数据库被宣布：第三章数据库的工作确定数据库操作。联系数据库与文件关于一地方和遥远节点储存的一数据库一起经营。一数据库处理一独特，缩写的别名适合一真实数据库名字。数据库处理被使用在过程中随后连结，做释放，和随后释放陈述指定他们影响哪数据库。除了在动态的SQL(DSQL)应用里，数据库处理也能在相互联系里面使用使有合格的块，或者使有差异，表格是当打开两个数据库或更多包含同等命名的表格什么时候的名字。每个数据库处理一定在一个计划内使用的全部变量中是独特的。数据库经营不能复制主语言保留字，并且不能是InterBase保留字。以下的陈述说明一个简单的数据库宣告：EXECSQLSETDATABASEDB1=employee.gdb;宣告数据库这鉴定那些文件数据库，employee.gdb，作为那些计划使用，并且分配那些数据库一个处理或者别名，DB1的数据库。如果一个程序在一份不同于包含数据库文件的目录里运转，然后那些说明文件名在数据库也必须包括全部路径名。例如，确定宣告指定全部的数据库的那些如下内容通向的路径employee.gdb:EXECSQLSETDATABASEDB1=/interbase/examples/employee.gdb;如果它使用的一个程序和一个数据库文件保存在不同的主人，然后文件名说明也必须包括一个主机名。以下的宣告说明一个Unix主机名怎样被作为关于一个传输控制协议/网际协议网络的数据库文件说明表的部分包括：EXECSQLSETDATABASEDB1=jupiter:/usr/interbase/examples/employee.gdb;在使用Netbeui协议的一个Windows网络上，指定道路如下：EXECSQLSETDATABASEDB1=/venus/C:/Interbase/examples/employee.gdb;宣布数据库嵌入SQL指南16宣布多数据库一个SQL程序，但不是一个DSQL程序，能同时访问多数据库。在多数据库的计划内，数据库处理被要求。习惯于：多数据库交易参考个别数据库。1使表格有合理的名字。2指定数据库为打开并且连结状态。3表明，要接受的数据库没有连接，做释放，并且随后释放。DSQL计划能访问单一数据库只一次，数据库处理使用连接并且从数据库那里拆开限制。在多数据库计划内，不是每数据库都一定被宣布用一单独陈述数据库。例如，以下的代码包含两个固定的数