SQL外文翻译.docx

SQL外文翻译.docx

《SQL外文翻译.docx》由会员分享，可在线阅读，更多相关《SQL外文翻译.docx（22页珍藏版）》请在冰豆网上搜索。

SQL外文翻译

WorkingwithDatabases

ThischapterdescribeshowtouseSQLstatementsinembeddedapplicationstocontroldatabases.Therearethreedatabasestatementsthatsetupandopendatabasesforaccess:

SETDATABASEdeclaresadatabasehandle,associatesthehandlewithanactualdatabasefile,andoptionallyassignsoperationalparametersforthedatabase.

SETNAMESoptionallyspecifiesthecharactersetaclientapplicationusesforCHAR,VARCHAR,andtextBlobdata.Theserverusesthisinformationtotransliteratefromadatabase’sdefaultcharactersettotheclient’scharactersetonSELECToperations,andtotransliteratefromaclientapplication’scharactersettothedatabasecharactersetonINSERTandUPDATEoperations.

gCONNECTopensadatabase,allocatessystemresourcesforit,andoptionallyassignsoperationalparametersforthedatabase.Alldatabasesmustbeclosedbeforeaprogramends.AdatabasecanbeclosedbyusingDISCONNECT,orbyappendingtheRELEASEoptiontothefinalCOMMITorROLLBACKinaprogram.

Declaringadatabase

Beforeadatabasecanbeopenedandusedinaprogram,itmustfirstbedeclaredwithSETDATABASEto:

CHAPTER3WORKINGWITHDATABASES.Establishadatabasehandle.Associatethedatabasehandlewithadatabasefilestoredonalocalorremotenode.Adatabasehandleisaunique,abbreviatedaliasforanactualdatabasename.DatabasehandlesareusedinsubsequentCONNECT,COMMITRELEASE,andROLLBACKRELEASEstatementstospecifywhichdatabasestheyshouldaffect.ExceptindynamicSQL（DSQL）applications,databasehandlescanalsobeusedinsidetransactionblockstoqualify,ordifferentiate,tablenameswhentwoormoreopendatabasescontainidenticallynamedtables.

Eachdatabasehandlemustbeuniqueamongallvariablesusedinaprogram.Databasehandlescannotduplicatehost-languagereservedwords,andcannotbeInterBasereservedwords.Thefollowingstatementillustratesasimpledatabasedeclaration:

EXECSQL

SETDATABASEDB1=’employee.gdb’;

Thisdatabasedeclarationidentifiesthedatabasefile,employee.gdb,asadatabasetheprogramuses,andassignsthedatabaseahandle,oralias,DB1.

Ifaprogramrunsinadirectorydifferentfromthedirectorythatcontainsthedatabasefile,thenthefilenamespecificationinSETDATABASEmustincludeafullpathname,too.Forexample,thefollowingSETDATABASEdeclarationspecifiesthefullpathtoemployee.gdb:

EXECSQL

SETDATABASEDB1=’/interbase/examples/employee.gdb’;

Ifaprogramandadatabasefileitusesresideondifferenthosts,thenthefilenamespecificationmustalsoincludeahostname.ThefollowingdeclarationillustrateshowaUnixhostnameisincludedaspartofthedatabasefilespecificationonaTCP/IPnetwork:

EXECSQL

SETDATABASEDB1=’jupiter:

/usr/interbase/examples/employee.gdb’;

OnaWindowsnetworkthatusestheNetbeuiprotocol,specifythepathasfollows:

EXECSQL

SETDATABASEDB1=’//venus/C:

/Interbase/examples/employee.gdb’;

DECLARINGADATABASE

EMBEDDEDSQLGUIDE37

Declaringmultipledatabases

AnSQLprogram,butnotaDSQLprogram,canaccessmultipledatabasesatthesametime.Inmulti-databaseprograms,databasehandlesarerequired.Ahandleisusedto:

1.Referenceindividualdatabasesinamulti-databasetransaction.

2.Qualifytablenames.

3.SpecifydatabasestoopeninCONNECTstatements.

IndicatedatabasestoclosewithDISCONNECT,COMMITRELEASE,andROLLBACKRELEASE.

DSQLprogramscanaccessonlyasingledatabaseatatime,sodatabasehandleuseisrestrictedtoconnectingtoanddisconnectingfromadatabase.

Inmulti-databaseprograms,eachdatabasemustbedeclaredinaseparateSETDATABASEstatement.Forexample,thefollowingcodecontainstwoSETDATABASEstatements:

...

EXECSQL

SETDATABASEDB2=’employee2.gdb’;

EXECSQL

SETDATABASEDB1=’employee.gdb’;

...

4Usinghandlesfortablenames

Whenthesametablenameoccursinmorethanonesimultaneouslyaccesseddatabase,adatabasehandlemustbeusedtodifferentiateonetablenamefromanother.Thedatabasehandleisusedasaprefixtotablenames,andtakestheformhandle.table.

Forexample,inthefollowingcode,thedatabasehandles,TESTandEMP,areusedtodistinguishbetweentwotables,eachnamedEMPLOYEE:

...

EXECSQL

DECLAREIDMATCHCURSORFOR

SELECTTESTNOINTO:

matchidFROMTEST.EMPLOYEE

WHERETESTNO>100;

EXECSQL

DECLAREEIDMATCHCURSORFOR

SELECTEMPNOINTO:

empidFROMEMP.EMPLOYEE

WHEREEMPNO=:

matchid;

...

CHAPTER3WORKINGWITHDATABASES

38INTERBASE6

IMPORTANT

ThisuseofdatabasehandlesappliesonlytoembeddedSQLapplications.DSQLapplicationscannotaccessmultipledatabasessimultaneously.

4Usinghandleswithoperations

Inmulti-databaseprograms,databasehandlesmustbespecifiedinCONNECTstatementstoidentifywhichdatabasesamongseveraltoopenandprepareforuseinsubsequenttransactions.

DatabasehandlescanalsobeusedwithDISCONNECT,COMMITRELEASE,andROLLBACK

RELEASEtospecifyasubsetofopendatabasestoclose.ToopenandprepareadatabasewithCONNECT,see“Openingadatabase”onpage41.TocloseadatabasewithDISCONNECT,COMMITRELEASE,orROLLBACKRELEASE,see“Closingadatabase”onpage49.Tolearnmoreaboutusingdatabasehandlesintransactions,see“Accessinganopendatabase”onpage48.

Preprocessingandruntimedatabases

Normally,eachSETDATABASEstatementspecifiesasingledatabasefiletoassociatewithahandle.Whenaprogramispreprocessed,gpreusesthespecifiedfiletovalidatetheprogram’stableandcolumnreferences.Later,whenauserrunstheprogram,thesamedatabasefileisaccessed.Differentdatabasescanbespecifiedforpreprocessingandruntimewhennecessary.4UsingtheCOMPILETIMEclauseAprogramcanbedesignedtorunagainstanyoneofseveralidenticallystructureddatabases.Inothercases,theactualdatabasethataprogramwilluseatruntimeisnotavailablewhenaprogramispreprocessedandcompiled.Insuchcases,SETDATABASEcanincludeaCOMPILETIMEclausetospecifyadatabaseforgpretotestagainstduringpreprocessing.Forexample,thefollowingSETDATABASEstatementdeclaresthatemployee.gdbistobeusedbygpreduringpreprocessing:

EXECSQL

SETDATABASEEMP=COMPILETIME’employee.gdb’;

IMPORTANT

ThefilespecificationthatfollowstheCOMPILETIMEkeywordmustalwaysbeahard-coded,quotedstring.

DECLARINGADATABASE

EMBEDDEDSQLGUIDE39

WhenSETDATABASEusestheCOMPILETIMEclause,butnoRUNTIMEclause,anddoesnotspecifyadifferentdatabasefilespecificationinasubsequentCONNECTstatement,thesamedatabasefileisusedbothforpreprocessingandruntime.TospecifydifferentpreprocessingandruntimedatabaseswithSETDATABASE,useboththeCOMPILETIMEand

RUNTIMEclauses.

4UsingtheRUNTIMEclause

Whenadatabasefileisspecifiedforuseduringpreprocessing,SETDATABASEcanspecifyadifferentdatabasetouseatruntimebyincludingtheRUNTIMEkeywordandaruntimefilespecification:

EXECSQL

SETDATABASEEMP=COMPILETIME’employee.gdb’

RUNTIME’employee2.gdb’;

ThefilespecificationthatfollowstheRUNTIMEkeywordcanbeeitherahard-coded,quotedstring,orahost-languagevariable.Forexample,thefollowingCcodefragmentpromptstheuserforadatabasename,andstoresthenameinavariablethatisusedlaterinSETDATABASE:

...

chardb_name[125];

...

printf（"Enterthedesireddatabasename,includingnodeandpath）:

\n"）;gets（db_name）;

EXECSQL

SETDATABASEEMP=COMPILETIME’employee.gdb’RUNTIME:

db_name;

...

Notehost-languagevariablesinSETDATABASEmustbepreceded,asalways,byacolon.

ControllingSETDATABASEscope

Bydefault,SETDATABASEcreatesahandlethatisglobaltoallmodulesinanapplication.

Aglobalhandleisonethatmaybereferencedinallhost-languagemodulescomprisingtheprogram.SETDATABASEprovidestwooptionalkeywordstochangethescopeofadeclaration:

gSTATIClimitsdeclarationscopetothemodulecontainingtheSETDATABASEstatement.NootherprogrammodulescanseeoruseadatabasehandledeclaredSTATIC.

CHAPTER3WORKINGWITHDATABASES

40INTERBASE6

EXTERNnotifiesgprethataSETDATABASEstatementinamoduleduplicatesaglobally-declareddatabaseinanothermodule.IftheEXTERNkeywordisused,thenanothermodulemustcontaintheactualSETDATABASEstatement,oranerroroccursduringcompilation.

TheSTATICkeywordisusedinamulti-moduleprogramtorestrictdatabasehandleaccesstothesinglemodulewhereitisdeclared.Thefollowingexampleillustratestheuseofthe

STATICkeyword:

EXECSQL

SETDATABASEEMP=STATIC’employee.gdb’;

TheEXTERNkeywordisusedinamulti-moduleprogramtosignalthatSETDATABASEinonemoduleisnotanactualdeclaration,butreferstoadeclarationmadeinadifferentmodule.Gpreusesthisinformationduringpreprocessing.ThefollowingexampleillustratestheuseoftheEXTERNkeyword:

EXECSQL

SETDATABASEEMP=EXTERN’employee.gdb’;

IfanapplicationcontainsanEXTERNreference,thenwhenitisusedatruntime,theactualSETDATABASEdeclarationmustbeprocessedfirst,andthedatabaseconnectedbeforeothermodulescanaccessit.

AsingleSETDATABASEstatementcancontaineithertheSTATICorEXTERNkeyword,butnotboth.AscopedeclarationinSETDATABASEappliestobothCOMPILETIMEandRUNTIMEdatabases.

Specifyingaconnectioncharacterset

Whenaclientapplicationconnectstoadatabase,itmayhaveitsowncharactersetrequirements.Theserverprovidingdatabaseaccesstotheclientdoesnotknowabouttheserequirementsunlesstheclientspecifiesthem.TheclientapplicationspecifiesitscharactersetrequirementusingtheSETNAMESstatementbeforeitconnectstothedatabase.

SETNAMESspec