valuests服务器

TSDataServerTMforMSSQLServerReferenceManualVersion8.
8.
046VreelandDrive,Suite1Skillman,NJ08558-2638Telephone:732-560-1377OutsideNJ800-524-0430Fax:732-560-1594Internetaddress:http://www.
tbred.
comPublishedby:ThoroughbredSoftwareInternational,Inc.
46VreelandDrive,Suite1Skillman,NewJersey08558-2638Copyright2013byThoroughbredSoftwareInternational,Inc.
Allrightsreserved.
Nopartofthecontentsofthisdocumentmaybereproducedortransmittedinanyformorbyanymeanswithoutthewrittenpermissionofthepublisher.
DocumentNumber:TQ8.
8.
0M102TheThoroughbredlogo,Swashlogo,andSolution-IVAccountinglogo,OPENWORKSHOP,THOROUGHBRED,VIPFORDICTIONARY-IV,VIP,VIPImage,DICTIONARY-IV,andSOLUTION-IVareregisteredtrademarksofThoroughbredSoftwareInternational,Inc.
ThoroughbredBasic,TSEnvironment,T-WEB,Script-IV,Report-IV,Query-IV,Source-IV,TSNetworkDataServer,TSODBCDataServer,TSODBCR/WDataServer,TSDataServerforOracle,TSXMLDataServer,GWW,GatewayforWindows,TSChartServer,TSReportServer,TSWebServer,TbredComm,WorkStationManager,Solution-IVReprographics,Solution-IVezRepro,TS/Xpress,andDataSafeGuardaretrademarksofThoroughbredSoftwareInternational,Inc.
Othernames,productsandservicesmentionedarethetrademarksorregisteredtrademarksoftheirrespectivevendorsororganizations.
1Copyright2013ThoroughbredSoftwareInternational,Inc.
INTRODUCTIONTSDataServerforMSSQLServerisanintegralpartoftheThoroughbredSoftwarenetworksolution.
WithTSEnvironment8.
7.
1+,datafromSQLServertablescanbemadeavailableacrossplatformsforuseinDictionary-IV,Report-IV,Query-IV,Script-IV,andThoroughbredBasicapplications.
ClientOperatingSystemSupport:UNIX,Linux,OpenVMS,andWindows.
ServerOperatingSystemSupport:WindowsServersForspecificinformation,pleasecontactyourThoroughbredSalesRepresentative.
ThisguidefirstdescribestheClientsideofTSDataServerforMSSQLServer,whichisprovidedwitheveryTSEnvironmentinstallation.
ItthenprovidesspecificinformationforaccessingSQLservers.
Yourapplicationsmayrequirespecialprivilegestoaccessthedatabase.
Youshouldconsiderthefollowingissues:Settingprivilegestoaccessthedatabase.
Designingormodifyingsiteprocedures.
Settingproceduresforservertransactionprocessing.
RequirementsTorunTSDataServerforMSSQLServeryoursystemmustmeetthefollowingrequirements:NetworkcommunicationsimplementedwithTCP/IPprotocolTSEnvironment8.
7.
1oraboveSQLServer2000(Version8.
0orhigher)2Copyright2013ThoroughbredSoftwareInternational,Inc.
INSTALLATIONAllinstallationsandactivationsmustberunwithAdministratorprivileges.
Oncefullyinstalledandactivatedtheproductscanberunusingstandarduseraccounts.
1.
LoginusinganAdministratoraccount.
2.
Werecommendclosingallunnecessaryapplicationsbeforerunningtheinstallation.
3.
InserttheTSDataServerforMSSQLServerV8.
8.
0CD.
BrowsetheCDandrunsetup.
exelocatedintheTSMSSQLfolder.
Thereisonlyonesetup.
exeforallplatforms.
4.
TheInstallShieldWizardwillstarttheinstallationprocess.
YoucanexitthisinstallationatanytimebyselectingtheCancelbutton.
UserInformationEnteryourName,Companyandserialnumbersuppliedonthedistributionmedia.
ChooseDestinationLocationTypeandenterorbrowseandenterthepathtothelocationfortheinstallation.
ThedefaultdestinationfolderisC:\ProgramFiles\TSSQLSVR.
Westronglyrecommendinstallingintothedefaultfolder.
NOTE:IfyouareinstallingonWindowsVista,WindowsServer2008,Windows7,Windows8orWindowsServer2012youmustinstallintotheProgramFilesfolder.
OncetheWizardcompletes,theTSDataServerforMSSQLServerservicewillneedtobestarted.
IMPORTANT:Theproductmustbeactivatedpriortostartingtheservice.
3Copyright2013ThoroughbredSoftwareInternational,Inc.
ACTIVATIONFromtheStartmenu,selecttheThoroughbredSoftwareprogramgroup.
SelectTSDataServerforMSSQLServerActivation.
Thefollowingscreenwilldisplay:BeforeyoucontactThoroughbredSoftwareInternationalmakesureyouhavethefollowinginformationhandy.
YourDealerCode.
Thesoftwareserialnumber,whichisfoundonthelabelonthedistributionmediaoronthedisplayedActivationscreen.
TheInstallationCode,whichisalsodisplayedontheActivationscreen.
TogetanActivationKeyyoucan:CallThoroughbredSoftwareInternationalat(800)524-0430or(732)560-1377andfollowthepromptstoobtainanActivationKey.
SendafaxtoThoroughbredSoftwareInternationalat(732)560-1594.
Note:PleasemakesurethatyouincludeyourfaxnumberinthemessagesothatThoroughbredSoftwareInternationalcansendtheActivationKeytoyou.
Visitourwebpage,www.
tbred.
com,andselecttheActivationsoption.
AfterThoroughbredSoftwareInternationalprovidestheActivationKey,youmustenterthevalueusingthefollowingprocedure.
EntertheKeyintheEnterActivationKeyfieldandclicktheAuthorizebutton.
4Copyright2013ThoroughbredSoftwareInternational,Inc.
Followingasuccessfulactivation,thesystemrespondswiththisscreen:ClickOK.
Theserverisnowactivated.
IfaninvalidActivationkeyisentered,thefollowingscreenwilldisplay:ClickOK.
ThesystemwillreturntothemainActivationwindow.
Toverifythattheserverisactivated,executetheServerActivationprogram.
Thefollowingscreenwilldisplay:ClickOKtoclosethewindow.
Nowtheservicecanbestarted.
FromtheStartMenu,selectControlPanelAdministrativeToolsServices.
LocatetheTSDataServerforMSSQLServerManagerservice.
ClicktheStartbutton.
AServiceControldialogwilldisplaywhilethesystemstartstheservice.
Oncestarted,thestatusfieldwillbechangedto"Started".
Serveraccessisenabledbytheadditionofanumberofconfigurationitems.
FormoreinformationseeSERVER.
MAPandIPLINPUT.
5Copyright2013ThoroughbredSoftwareInternational,Inc.
PRODUCTDESCRIPTIONTSDataServerforMSSQLServerComponentsTheTSDataServerforMSSQLServerconsistsofthreecomponents:AClientenabledTSEnvironment,AManagerProcessthatrunsasaWindowsService(tsmssqlmgr),andAServerProcess(tsmssql)foreachconnection.
ClientEnabledTSEnvironmentTheclientsideisenabledinthestandardTSEnvironment.
Whenconfiguredfordataserveraccess,itdetectsrequestsfordatathatresideontheserver.
Whenthiseventoccurs,itsendstheI/Orequesttotheserverforprocessing.
Theserverrespondswiththeresultsetandprocessingcontinues.
ManagerProcessTheManagerprogramrunsasaWindowsService.
TheManagerProgram(tsmssqlmgr)createsServerProcessesforclientsasneeded.
WhentheServicedetectsaclientattemptingtoconnect,ittriestocreateaServerProcessfortheclient.
IfaServerProcessiscreatedsuccessfully,theclientandservercanperformclient/servercommunications.
NOTE:TheServicemustbestartedbeforeclientsattempttoconnecttoit.
ServerProcessTheServerProcess(tsmssql)istriggeredbyrequestsfromtheclient.
Oncearequestisreceived,itisprocessedandtheresultsreturnedtotheclient.
SystemArchitectureThefollowingdiagramsoutlinethecoreoptionsforconfiguringthecomponentsoftheThoroughbredclient.
ClientandserverononesystemClientononesystem-serveronanother6Copyright2013ThoroughbredSoftwareInternational,Inc.
ClientandServerCo-existonOneSystemSystemAThoroughbredClientSystemrunningTSEnvironmentforWindowsTCP/IPvialoopbacktsmssqlmgrWindowsServicethatspawnsaprocess(tsmssql)thathandlescommunicationbetweentheClientandServerSQLServerInthelayoutdescribedbytheabovefigure,theinformationissenttotheServerProcessthroughtheloopbackTCP/IPaddress.
ClientonSystemAandServeronSystemBSystemASystemBThoroughbredClientSystemrunningTSEnvironmentforWindows,UNIXorVMSTCP/IPtsmssqlmgrWindowsServicethatspawnsaprocess(tsmssql)thathandlescommunicationbetweentheClientandServerSQLServerIntheabovefiguretheinformationissentoverthenetworktotheserversystem.
OncetherequestsreachtheServerProcessitcreatesSQLstatementsthatareinterpretedbytheserversystem.
TheresultsarethenpassedbacktotheclientthroughtheServerProcess.
7Copyright2013ThoroughbredSoftwareInternational,Inc.
CLIENTANDSERVERENVIRONMENTTosetupTSDataServerforMSSQLServer,thefollowingfilesmustbecreatedormodified:SERVER.
MAPIPLINPUTRegistrySettings(optional)SERVER.
MAPTheSERVER.
MAPtextfileestablishesarelationshipbetweena2-characterserverIDusedbyThoroughbredandtheserversystem.
Thisfileisasimpletextfile.
NOTE:ForUNIXClients,thisfileshouldbeplacedinthe/usr/lib/basicdirectory.
ThoroughbredusestheServerIDtoreferencetheserversystem.
EachlineinthefilerepresentsanentryforoneServer.
Thesyntaxforanentryis:server-id:[host-nameorTCP/IP-address]:TCP/IP-port:DataSourceserver-idisa2-characteralphanumericstringusedwithinBasictoreferenceaparticularserversystem.
host-nameisthehostnamefortheserversystem.
ForinstallationswheretheThoroughbredclientanddatabaseserverenvironmentsresideonthesamephysicalsystem(WindowsNetworkBasic)thisfieldcancontaintheloopbackaddressorhostname.
TCP/IP-addressistheTCP/IPaddressfortheserversystem.
ForinstallationswheretheThoroughbredclientanddatabaseserverenvironmentsresideonthesamephysicalsystem(WindowsNetworkBasic)thisfieldcancontaintheloopbackaddressorhostname.
TCP/IP-portportnumberisanoptionalparameter.
Itisusedtooverridethedefaultportnumber(5674),intheeventofaconflictwithanotherprocessusingTCP/IP.
ThetransportprotocollayeroftheTCP/IPprocessusestheportnumbertodeliverthepacketdatatotheappropriateapplication.
Intheeventofaconflictonaparticularsystem,addtheportnumbertotheSERVER.
MAPfileandexecutetheserverprocessusingtheportnumberasanargument.
FormoreinformationseeServerProcessinthefollowingsection.
DataSourceisanoptionalsetting.
DataSourcesaredefinedusingtheMicrosoftAdministrationTools.
DataSourcesallowmultipleuserstosharethesamesettings(i.
e.
defaultSQLServerdatabase).
Bydefault,theLocalServerDataSourceisused.
SeeSettingUpaDataSourcesectionlaterinthisdocument.
DataSourcenamesintheSERVER.
MAPfilemaycontainupto32alphanumericcharacters(nodashesorunderscores).
8Copyright2013ThoroughbredSoftwareInternational,Inc.
ExampleThefieldsinaSERVER.
MAPentryareseparatedbyacolon(:).
ThefollowingareexamplesofvalidSERVER.
MAPentries:ServerIDorTCP/IPAddressHostNameSQ:server1:5674ServerID(SQ)PortNumber(5674)SQ:198.
189.
167.
20:5674TCP/IPAddress(Colonsareusedasaseparatorcharacter.
)9Copyright2013ThoroughbredSoftwareInternational,Inc.
IPLINPUTOnceyouestablishtheserverIDyoucanmaketherequiredentriesfortheIPLINPUTfile.
DEVEachDEVlinecorrespondstoadirectoryontheServer.
ThesyntaxforaDEVlineis:DEVdevice-name,1,,server-flag,authentication-type,logon-cache,case-sensitivity,server-id:log/pswddevice-nameisdeviceIDusedtoreferencelogicaldiskdirectories.
ValidvaluesrangefromD0throughD9,DAthroughDZ,andDathroughDz.
server-flagindicatesthatthiswillbeaclienttotheconfiguredserversystem.
ThevaluefortheSQLserveris4.
authentication-typeindicateswhetherloginstotheSQLServerdatabasearedonewithWindowsauthenticationorSQLServerauthentication.
ThedefaultisWindows(blankor0).
TouseSQLServerauthentication,setthisflagto1.
logon-cacheconfiguresavalueforthemaximumnumberofactiveloginstotheSQLServerdatabase.
FormoreinformationseeLogonCacheinthePerformanceTuningsection.
case-sensitivityindicateswhetherornottoperformacasesensitivecomparisonofthekeyvaluereturnedfromtheSQLdataserverduringaREAD,KEY=operation.
Thedefaultistoperformacasesensitivecomparison(blankor0).
TohaveBasicperformacaseinsensitivecomparison,setthisflagto1.
server-idisthetwo-characteralphanumericcodeassignedtothisserver.
TheserverIDontheDEVlinemustmatchanentryintheSERVER.
MAPfile.
log/pswdwhenusingSQLServerauthentication,thisistheSQLServerloginandpasswordusedtoaccessthedatabase.
Theidandpasswordareoptional.
Ifomitted,thesystempromptsfortheinformationwhentheclientisexecuted.
Youcannotmixauthenticationmodes.
Iftheauthentication-typeis0,thisfieldmustbeblank.
PRMIfmultipleMSSQLServersareconfiguredforSQLServerauthenticationandthelogin/passwordarethesameforeach,thePRMMSSQL-LOGINmaybeusedasashortcuttodefiningthelogin/passwordoneachDEVline.
PRMMSSQL-LOGIN=login/passwordlogin/passwordistheMSSQLServerloginandpasswordusedtoconnecttothedatabase.
Onlytheloginandslash(/)characterarerequired.
Apromptforthepasswordwillappearwhentheclientconnects.
10Copyright2013ThoroughbredSoftwareInternational,Inc.
PRMCREATWDBATTRThisparameterwillcreatenewtablesusingtheattributesfromthesystemdictionary.
Mandatoryfields(EntryType2and3andthefirstkeyfield)willbecreatedwithaNOTNULLconstraint.
Inadditionanyfixedlengthfields(EntryType1or3)willbecreatedwiththeCHARdatatypesothefieldalwayscontainsdata.
PRMORA_NVLNULLSPRMORA_DONTWRITENULLSTheseparameterscontrolhowthesystemordersrecordswhenthekeyfieldcontainsnulls.
FormoreinformationseeNullProcessing.
PRMSQL_NUMERIC_NULLSThisparametercontrolshownumericnullvaluesarestoredinthedatabase.
WhenthedataforanumericfieldissentoverfromaThoroughbredBasicapplicationasallspaces,ifenabled,thenthisdatawillbewrittentothedatabaseasanullvalue,otherwise,itwillbewrittentothedatabaseasa0value.
PRMUNIQUE-KEYSThisparameterwillforcealltablecreatestohaveuniquesecondarykeys.
ThisisaccomplishedbyaddingthePrimarykeytoeachsortdefinition.
Someaccommodationsaremadewhenthesamedataelementnameisusedmorethanonceinasortdefinition.
ThisisnotsupportedbySQLServer.
11Copyright2013ThoroughbredSoftwareInternational,Inc.
RegistrySettingsThefollowingcontrolparametersforthetsmssqlserverprocessarestoredintheWindowsregistry:HKEY_LOCAL_MACHINE\SOFTWARE\ThoroughbredSoftwareInternational,Inc.
\TSDataServerforMSSQLServerThefirsttimeatsmssqlprocessisexecuted,thedefaultvaluesforthesecontrolparametersarecreatedintheWindowsregistry.
PleasedonotedittheWindowsRegistryunlessyouareconfidentaboutdoingso.
CLIENT-SYSTEM-INFOThisflagwillenableprocessandhostinformationtobelogged.
FormoreinformationseeUsingtheDebuggingFacility.
COMMIT-COUNTThisentryisusedtospecifythecommitcount.
Themaximumcommitcountis65535.
Ifthespecifiedcommitcountisgreaterthanthemaximum,themaximumvaluewillbeused.
Thedefaultcommitcountis1.
ThecommitcountcanbeoverriddenbySETCTCinBasic.
DEBUG-LEVELThisentryisusedtospecifythedebugginglevel.
FormoreinformationseeUsingtheDebuggingFacility.
DEBUG-FLAGSThisentryisusedtoenableordisableadditionalinformationthatcanbewrittenintothedebuglogfile.
TheFlagvaluesare:FlagNumberDescription1DisableloggingoftheprocessIDnumber2Enableloggingofatimestamp4Enableloggingofparametervalues.
8EnableloggingoffetchedvaluesLDA-CACHETheentryisusedtospecifythelogoncachecount.
FormoreinformationseeLogonCache.
NO-OPENLOCKTheflagwilldisabletheuseoftheTS_TABLE_LOCKtable.
READUNCOMMITTED12Copyright2013ThoroughbredSoftwareInternational,Inc.
Theflagisusedtoadd'WITH(NOLOCK)'toSQLstatementswhenREADing.
Thisflagisenabledbydefault.
UNIQUE-SORTSThisflagwillenabletablestobecreatedwithuniquesecondarykeys.
SetupExampleThisenvironmentconsistsoftwoSQLServersontwodifferenthostsystems.
Existingserver-nameTCP/IPaddressinformation:acct:125.
32.
1.
1dev:125.
32.
1.
10TheSERVER.
MAPfilemaybesetupinoneofthefollowingways:UsinghostnamesS1:acctS2:devUsingTCP/IPaddressesS1:125.
32.
1.
1S2:125.
32.
1.
10UsingacombinationofhostnamesandTCP/IPaddressesS1:125.
32.
1.
1S2:devSpecifyingaDataSourcenameotherthanthedefaultLocalServerS1:125.
32.
1.
1::ProductionInthisexamplethelogininformationtotheSQLServerdatabase(usingSQLServerauthentication)isasfollows:acct:loginid=marypassword=mjsdev:loginid=kurtpassword=ktc13Copyright2013ThoroughbredSoftwareInternational,Inc.
TheIPLINPUTshouldreflectthefollowing:DEVD8,1,,4,1,,,S1:mary/mjsDEVD9,1,,4,1,,,S2:kurt/ktcAfterBasicisexecuted,therewillnowbeatsmssqlprocessoneachoftheserversystems(acctanddev).
IftheAuthenticationFlagisnot1,Basicwilldisplayanerrormessage.
StarttheWindowsService(tsmssqlmgr)Thetsmssqlmgrmanagerisasystemservicethatisinstalledontheserversystem.
EachtimeaBasicclientexecutes,atsmssqlprocessisstarted.
Themanagerprocessisinstalledbydefaultwithastartuptypeofautomatic.
Eachtimetheserverrestarts,thisservicewillbeautomaticallystarted.
TheLogOnvalueforthisserviceshouldbeavalidWindowsuser(nottheLocalSystemaccount).
TheservicecanbestartedwiththefollowingStartparameters:-dDebugLogging-pOverridedefaultportThe–doptionisfollowedbyanon-zerovalueindicatingthedebuglevel.
The–poptionisfollowedbyaTCP/IPportnumber.
Bydefault,themanagerwilllistenonport5674.
Ifthiscausesaconflict,usethe–poptiontooverridethedefaultport.
Formoreinformationusingthe–doption,seeUsingtheDebuggingFacility.
SettingUpaDataSourceThetsmssqlmanagerrequiresaSystemdatasource.
TheSystemdatasourcestoresinformationabouthowtoconnecttotheSQLServerdatabase.
ThetsmssqlmanagerwillbydefaultuseaSystemdatasourcecalledLocalServer.
IftheSystemdatasourceisnotnamedLocalServer,thenitmustbespecifiedineitherRegistryortheSERVER.
MAPfile(formoreinformationseetheSERVER.
MAPsectionearlierinthismanual).
SystemdatasourcescanbeaddedusingtheODBCDataSourceAdministratoraccessibleviatheControlPanel.
14Copyright2013ThoroughbredSoftwareInternational,Inc.
DICTIONARY-IVFORMATSANDLINKSYoumusthaveaFormatandLinkdefinedtoaccessanSQLServertable.
FormatDefinitionAlthoughnochangesarerequiredtoexistingformatsinordertousetheclientcapabilitiesoftheenvironment,youshouldbeawareofthefollowinginformation.
ThedataelementnamesmustnotbeSQLServerreservedwords.
FormoreinformationseetheappropriateSQLServerdocumentation.
FileTypeWhenThoroughbredcreatesanSQLServertable,datatypesaretranslatedasfollows:Alphanumeric:VARCHARorCHARTextfield:TEXTNumeric:DECIMALDate:DATETIMETheVARCHARandCHARdatatypesonlysupportcharactersabovehex20tobeincludedinthedata.
Anycharacterbelowhex20isconvertedtoaspace.
TheCHARtypewillbeusedwhentheBasicPRMCREATWDBATTRisconfiguredandthedataelementissetasafixedlengthfieldeithermandatoryoroptional(EntryType1or3).
Whenrecordsarewrittentothistable,anyfieldoftheCHARtypewillbeassignedavalueofatleastonespace.
Thiswillinsurethatthecolumnisnotnull.
FormoreinformationseePRMCREATWDBATTRintheIPLINPUTtopic.
EntryTypeAllmandatoryfields(EntryType2and3)willbesetasNOTNULLfieldsintheSQLServertableswhentheBasicPRMCREATWDBATTRisset.
DataElements–NamingSQLServeronlyallowsalphanumericcharacters,thecurrencysymbol($),anunderscore(_),andapoundsign(#)incolumnnames.
Thefirstcharacterofthecolumnmuststartwithaletter.
Anydeviationfromthisconventionresultsintablecreationfailure.
Thetsmssqlprogramtranslatesthehyphen(-)thatisacceptableinThoroughbredDictionary-IVFormatstoanunderscore(_).
TheuseofSQLServerreservedwordsalsoresultsintablecreationfailure.
FormoreinformationseeyourappropriateSQLServerreferencematerial.
15Copyright2013ThoroughbredSoftwareInternational,Inc.
DataElements–MultipleOccurrencesSQLServerdoesnotsupportmultipleoccurrencesforasingledataelement.
Formoreinformation,seeMultipleOccurrencesintheDictionary-IVDeveloperGuide.
ThesystemcreatesseparatecolumnsintheSQLServertablefromamultipleoccurrencefield.
Thefollowingisthenamingconventionformultipleoccurrences.
data-element-name_seq#seq#istherangefromonetothenumberofdefinedoccurrences.
BinaryNumericFieldsFieldsdefinedasbinaryintheFormatareconvertedtodecimalvaluesandstoredinSQLServerDECIMALfields.
Thetypeofbinaryfielddeterminesthelengthofthefield.
ThefollowingtabledisplaystheformulasforcalculatingthefieldwidthintheSQLServertable.
FormulasforCalculatingFieldWidthsNumericTypeFieldWidthFormulaforSQLServercolumnWidthL=FieldWidth3,51-3(L*2)+1+14-5(L+1)*2=16-8(L+1)*2+1+141-2(L*2)+13-4(L+1)*25-7(L+1)*2+18(L+2)*26All(L*2)+171016OddPrec.
(((L*10)-15)*2)/10EvenPrec>(((L*10)-5)*2)/10–18All89All14AAll(L*2)-1+1BAll(L*2)–1CAll(L*2)DAllL+1E,FAllLInallcases:ifthevaluecontainsadecimal,addonetothefieldwidth.
SQLDatesThesystemautomaticallytranslatesdatesstoredineitheroftheSQLformatsintothestandardSQLServerDATETIMEstorageformatasyouupdaterecords.
16Copyright2013ThoroughbredSoftwareInternational,Inc.
LinkDefinitionTheLinkdefinitioncontainstheconfigurationparametersnecessarytodescribeaservertabletoDictionary-IV.
ToaccesstheSQLServertables,youmustenterthefollowinginformationintheLinkdefinition:FileType:MServerIDTableNameBelowisasampleLinkdefinitionscreen:ServerIDTheserverIDisauniqueidentifierthatreferencesthehostviathedefinitionintheSERVER.
MAPfile.
ItwillbevalidatedagainstthelistofserverdevicesconfiguredintheIPLINPUTfile.
AlthoughnoIDwillbedisallowed,awarningwillbedisplayed.
ServerTableNameTheTableNameisthenameusedtocreateatableontheserver.
TextFileATextFilevalueisrequirediftheFormatcontainsTextfields.
Intheexampleabove,theTextFielddatawillbemaintainedinanSQLServertablenamedCUSTOMER_TXT.
IftheTextFilenamehada.
TXTextension,theSQLServertablenamewouldbeconvertedtouse_TXT.
BoththetableandtextfilenamesmustconformtoSQLServertablenamingconventions.
FormoreinformationseetheappropriateSQLServerdocumenttoreviewtherestrictions.
LinksmustbedefinedasafiletypeM(MSORT).
WhilethephysicalfileisnotstoredasanMSORTfile,itwillbeprocessedasoneinDictionary-IVforsortsandtextfieldstorage.
FormoreinformationseeUsingSortsandTextFieldsforSQLServerData.
17Copyright2013ThoroughbredSoftwareInternational,Inc.
ForDictionary-IVtodetectthataLinkreferencesaserver-basedtable,FileType,ServerID,andTableNamemustallcontainvalidinformation.
Iftheydonotalocalfilewillbeused,referencedbythenameenteredintheDataFilefield.
Thesystemsupportsfilesuffixes.
Thefilesuffixcharacter(@)mustbeplacedintheTableNamefieldforserverfiles,ratherthantheDataFilefield.
Wheretextfieldsareconfiguredandafilesuffixisused,thetextfilenamemustincludethesuffixcharacter.
FormoreinformationrefertotheDictionary-IVUserGuide.
SortdefinitionoccursinthesamewayforserverLinksasforlocalLinksandshouldbesavedandupdatedwithF8processing.
Eventhoughnophysicalrebuildtakesplace,thiswillproperlyupdatetheLinkheader.
FormoreinformationseetheDictionary-IVDeveloperGuide.
IfsortdefinitionsexistintheLinkandthetableisrecreated,theindexeswillbedefinedautomaticallyontheserveraspartofthecreateprocess.
UnderthecurrentDictionary-IVmethodology,firstbuildaLinkheaderandcreatethetable.
ModifytheLinkheadertodefinesorts.
Thenexttimethetableiscreated,theindexesforallsortswillbedefinedontheserver.
OncetheLinkheaderisconfigured,multipleandsinglerecordmaintenance,CONNECTs,reports,queries,andrecompiledscriptscanaccessservertables.
FormoreinformationaboutmodifyingBasicprogramstosupportaccesstoservertablesseeUsingBasic.
WhilethedataandsortfilenamesmaybepopulatedduringtheLinkheadercreation,neitherisreferencedduringserveraccess.
However,thedatafilenameisusedtogeneratethetextfilenamefortextfieldstorage.
Thereforebesuretoeitherenterthedatafilenamefortheautomaticgenerationofatextfilenameormanuallyenterthefield.
18Copyright2013ThoroughbredSoftwareInternational,Inc.
SQLSERVERTABLESThefollowingdescribeshowtoprocessSQLServertablesfromtheTSEnvironment.
AccessingSQLServerTablesThefollowingdescribeshowtocreateSQLServertablesandthenaccessthesetablesfromtheTSEnvironment.
FormoreinformationspecifictoScript-IVandDictionary-IVseeScript-IV/Dictionary-IVTables.
UsingBasicExistingserverfilesareaccessedusingthefollowingsyntax:OPEN(CH,OPT="LINK")"link-name""LINK"specifiesthatthefiletobeopenedonthischannelisfoundinthesystemdictionaryinthelink-namerecord.
"link-name"specifiesavalidlink-namecontainingthenecessaryserverreferences.
NewserverfilesmaybecreatedfromtheBasicenvironmentasfollows:OPEN(CH,OPT="LINK|CREATE")"link-name""CREATE"isaparameterthatcausesBasictogeneratetheservercommandsnecessarytobuildthefile.
TheSQLServertabledefinitionisbasedontheformatlistedintheLinkDefinition.
IfthetablealreadyexistsontheSQLServer,theCREATEisignoredandthetableopened.
AspecialOPENsyntaxisusedforaccesstotextfielddata.
ThissyntaxisusedinternallybytheDictionary-IVsystemforaccessingtextfielddata.
FormoreinformationseetheTextFieldsforSQLServersectionlaterinthismanual.
OPEN(CH,OPT="LINK|TEXT")"link-name""TEXT"specifiesthatthetextfilefromthelinkistobeopened.
IfmultipleSQLserversareconfigured,existingserverfilesondifferentserverscanbeaccessedusingthefollowingsyntax:OPEN(CH,OPT="LINK|SID='server-id'")"link-name""SID="isaparameterthatcausesBasictoaccessthespecifiedserverinsteadoftheserverIDdefinedinthelinkdefinition.
"server-id"specifiestheidofoneoftheconfiguredSQLservers.
19Copyright2013ThoroughbredSoftwareInternational,Inc.
Serverfileswithnamesdifferentfromthenamesdefinedinalinkmaybeaccessedusingthefollowingsyntax:OPEN(CH,OPT="LINK|TABLE-NAME='table-name'|TEXT-FILE='text-table-name'")"link-name""TABLE-NAME="isaparameterthatcausesBasictousethespecifiedtablenameinsteadoftheonedefinedinthelinkdefinition.
"table-name"specifiesthetablename.
"TEXT-FILE="isaparameterthatcausesBasictousethespecifiedtextfieldtablenameinsteadoftheonedefinedinthelinkdefinition.
"text-table-name"specifiesthetextfieldtablename.
ThefollowingcodefragmentsshowmultiplewaysofusingthenewOPENsyntaxtoaccessfiles.
TheexamplesassumeaLinkdefinitionwithinformationintheServerIDandTableNamefieldsandafiletypeofM.
Example1OPEN/READserverfileintoIOLIST(variables)10IOLISTCUST_CODE$,CUST_NAME$,ADDRESS$100CH=UNT;OPEN(CH,OPT="LINK")"UTCUST"READ(CH,KEY="0001")IOL=10ThisexampleassumesaFormatcontainingfieldseparators.
Example2OPEN/READserverfileintoFORMAT10CH=UNT;OPEN(CH,OPT="LINK")"UTCUST";FORMATINCLUDE#UTCUST;READ(CH,KEY="0001")#UTCUSTThisexamplecanbeusedonanyformatwithorwithoutfieldseparators.
20Copyright2013ThoroughbredSoftwareInternational,Inc.
Example3OPEN/READserverfileintorecordvariable10CH=UNT;OPEN(CH,OPT="LINK")"UTCUST";READRECORD(CH,KEY="CUST0001)REC$ThisexampleshowsaccessusingaFormatwithoutfieldseparators.
ThedatainREC$couldbeusedtopopulatetheformatdataarea.
Undercertaincircumstancesanerror167maybereturnedwhenwritingtoaLinkopenedwithOPT="LINK".
WhenemployingaFormatdirectlyoraFormatthatcontainsfieldseparators,thesystemassignsthatdatatothedataelementname.
Ifthisdataisinvalidfortheparticulardataelementname(invalidvalueforYes/Nofields,blankmandatoryfields,orinvaliddatainadatefield)anerror167occurs.
PrintingtheDNEsystemvariableattheinstanceoftheerrorwilldisplaythedataelementnameforwhichtheassignmentfailed.
Example4OPENalink'stableonadifferentserver10CH=UNT;OPEN(CH,OPT="LINK|SID=Q2")"UTCUST"Example5CREATEandOPENalink'stablesusingdifferenttablenames10OV$="|TABLE-NAME=BUCUST|TEXT-FILE=BUCUST_TXT";DCHL=UNT;OPEN(DCHL,OPT="LINK|CREATE"+OV$)"UTCUST";XCHL=UNT;OPEN(DCHL,OPT="LINK|TEXT"+OV$)"UTCUSTERASEServertablesaredeletedfromwithintheBasicenvironmentbyspecifyingtheLinknameandOPT="LINK".
ERASE"UTCUST",OPT="LINK"ThisdeletesthetablefromtheserverreferencedintheLinkUTCUST.
Italsodeletesthetextfieldtableifanytextfieldsweredefined.
INITFILEServertablesmaybeclearedofalldatabyusingtheINITFILEdirective.
Thisdirectiveisusefulforclearingatablewithouttheextrastepofperforminganerase.
INITFILE"UTCUST",OPT="LINK"ThisclearsthetableontheserverreferencedintheLinkUTCUSTbydoingaDROPfollowedbyanADD.
Italsoclearsthetextfieldtableifanyfieldsaredefined.
21Copyright2013ThoroughbredSoftwareInternational,Inc.
RENAMETherenamefunctionallowsatablenametobechangedontheserverfromtheclientprocess.
RENAME"UTCUST","UTTEMP",OPT="LINK"ThisrenamesthetablereferencedinLinkUTCUSTtothetableUTTEMP.
NochangesaremadetotheoriginalLink.
FIDFunctionWhentheFIDisexecutedonachannelwhereaLinkreferencingaservertableisopened,theresultingstringcontains(inadditiontothestandardFIDinformation)thefollowing:Byte1Valueis"S".
Indicatesthatthisisachannelopenedtoalinkreferencingaservertable.
Byte2-3ThetwocharacterServerIDfromtheLink.
ThebytesthatnormallycontainthefilenamenowcontaintheLinkname.
TCBFunctionError150hasbeenaddedtotheBasicerrorlisttoreflecterrorsresultingfromserveroperations.
Toreturnthespecificservererror,anargumenthasbeenaddedtotheTCB()function.
TheTCB(20)nowreturnstheservererrorinthesamewaythattheTCB(3)returnstheoperatingsystemerror.
ThevaluereturnedfortheTCB(20)canbeinterpretedbyreferencingtheMicrosoftknowledge-base.
OftenBasicdirectiveswillgeneratemultipleSQLcommands.
ThereforeitispossibleformultipleSQLerrorstooccur.
SeetheVirtualErrorTableintheErrorMessagesectionofthismanual.
DUMPDirectiveSeveraloftheDUMPoptionshavebeenchangedtobeawareofserveraccess.
DUMPCHANNELSnowdisplaythelinknamewhenalinkhasbeenOPENedwithOPT="LINK".
DUMPIPLDEVSwillspecifythatdevicesconfiguredasaserverwillreturn"Accessusingserverxx.
"22Copyright2013ThoroughbredSoftwareInternational,Inc.
XFDFunctionThefollowingdescribesthecurrentvaluesreturnedforservertables:Option=0BytesDescription1-2Unused3-10N/A11-35Unused36-38N/A38-41N/A42-47Unused48"N"49-53Unused54-59N/A60-65N/A66-71N/A72-85Unused86+N/AOption=1AsdocumentedintheThoroughbredBasicReferenceManual.
Option=3BytesDescription1$05$2$83$3Numberofsorts(binary)4Currentsortnumber(binary)5-12"NNY"(bytes8-12Unused)13-14N/A15-16COBOLonly17-42Unused45+Sortdefinitionparameters(FormoreinformationseetheThoroughbredBasicReferenceManual.
)Option=4AsdocumentedintheThoroughbredBasicReferenceManual.
Option=6BytesDescription1$06$2$00$3-6$00000000$7-8Bytesperrecord(binary)9+LinknameOption=10Returns-123Copyright2013ThoroughbredSoftwareInternational,Inc.
FSTFunctionThisfunctionisnotsupportedforservertables.
INDFunctionSQLServerdoesnotsupportanINDfunction.
INDforachannelopenedtoanSQLServertablewillalwaysreturn1.
ErrorswillbereportedwhentryingtoaccessSQLServertablesusingIND.
NOTE:ThestringfollowingOPT=canbeavariableinanyofthecommandswhereitisspecified.
Thevaluedoesnotneedtobehard-codedintotheprogram.
UsingSortsSorts,alsoknownasindexes,arehandledinsubstantiallythesamemannerforservertablesastheyareforlocalfiles.
CreatedintheLinkdefinition,theycanbeusedforaccessinanyoperationthatcurrentlysupportssorts.
AswithMSORT,iftheLinkreferencesanexistingfile,thelinkdefinitioncanbeupdatedtoincludesortsdefinedinthefilewithoutrebuildingthefile.
Thedifferencesoccurinthewaythatsortingisappliedtotheservertable.
IndexesdonotneedtophysicallyexistintheserverenvironmenttouseLinksortsanywhereinDictionary-IV.
AslongasthesortdefinitionisestablishedintheLinkdefinition,Dictionary-IVcanuseittoaccesstheSQLtables.
BasicwillexpandthesortstatementdefinedintheLinkintotheappropriatefieldnames,ratherthanpassingonlythesortnumber.
SQLsortstatementswillrefertothesefield(column)names,notindexnames,whendefininganORDERBYstatement.
Soiftheindexdoesnotexist,SQLperformsthesortingoperationin-line.
However,ifanSQLindexexistsforthecolumnsintheORDERBYstatement,theindexisusedtoaccessthedatafile.
However,theIndexeswillbeusedonlyifoneofthecolumnshasbeendefinedwithaNOTNULLconstraint.
FormoreinformationseethePerformanceTuningsectionlaterinthismanual.
IfsortdefinitionsexistintheLinkandthetableisrecreated,theindexeswillbedefinedautomaticallyontheserveraspartofthecreateprocess.
UnderthecurrentDictionary-IVmethodologyfirstbuildaLinkheaderandcreatethetable.
ModifytheLinkheadertodefinethesorts.
Eraseandrecreatethetable.
Theindexesforallsortsarenowdefinedontheserver.
SQLServerdoesnotsupportsortsonsubstringsoffields.
Thesorts(indexes)arecreatedusingthefollowingnamingconvention:"I_"+table-nameDictionary-IVsortnumberExample:Forsortnumber2onaLinkwithatable-namedefinedasTSISERVER,theindexwouldbecreatedas"I_TSISERVER_2".
AddinganddeletingsortsisnotsupportedfromwithintheBasicclient(ADDSORT/REMSORT).
Thesefunctionsarereservedforthedatabaseadministrator.
24Copyright2013ThoroughbredSoftwareInternational,Inc.
LockingRecordsThelockingfacilitiesavailablewithSQLServerprovidethedeveloperwithresourcestoinsuredataconsistency.
Manyofthefeaturesdesignedforthesegoalsoccurautomatically.
FormoreinformationrefertotheappropriateMicrosoftSQLServerdocumentation.
ThissectionexplainshowtheselockingmechanismscorrelatetoThoroughbreddirectivesandhowtorelatelockingthatoccursintheThoroughbredapplicationtothat,whichoccursinanSQLapplication.
ThefollowingtablesummarizestherelationshipofThoroughbreddirectivestoSQLstatementsandtheleveloflockingassociatedwitheach.
ThoroughbredDirectives/SQLStatementsThoroughbredDirectiveSQLStatementTypeofLockLockModesPermitted(seelegendbelowchartfordescription)RSRXSSRXXREAD,FIND,PREAD,andallkeyfunctionsSELECT.
.
.
FROMtable.
.
.
noneYYYYYWRITE,ifnewINSERTINTOtable.
.
.
RXYYNNNWRITE,ifexistUPDATEtable.
.
.
RXY*Y*NNNERASE,INITFILEDELETEFROMtable.
.
.
RXY*Y*NNNEXTRACT,WRITESELECT.
.
.
FROMtable.
.
.
FORUPDATEOF.
.
.
RSY*Y*Y*Y*NOPEN,UNLOCKSELECT…FROMtable…WITH(HOLDLOCK)WHERE1=2RSYYYYNLOCKSELECT…FROMtable…WITH(HOLDLOCK,TABLOCKX)WHERE1=2XNNNNNRSRowShareRXRowExclusiveSShareSRXShareRowExclusiveXExclusive*Validaslongasnoconflictingrecordsareinvolved.
FormoreinformationabouttheWRITEstatementseetheGeneratedSQLCodesectionlaterinthismanual.
TheLockModesPermittedcolumnindicateswhattypeoflockmaybeplacedonthefile(table)orrecord(row)whiletheparticulardirectiveisineffect.
Forexample,ifarecordisEXTRACTedinThoroughbred,exclusive(X)locksbyanotherclientoranSQLtaskarenotallowedandproduceanerror.
Allotherlocktypesarevalidaslongasadifferentrowislocked.
25Copyright2013ThoroughbredSoftwareInternational,Inc.
Sincethequery-typefunctions(READ,FIND,etc.
)donotgeneratealock,itwillnotfailunderanylockingmodethetableorrecordmaycurrentlyhave.
ThismeansthatREADinglockedrecordswillnotrequirePRMREADONLYtoaccesstablesorrowslockedbyeitheranSQLtaskoranotherThoroughbredclientprocess.
NullProcessingBydefault,nullkeyvalueswillbereturnedtotheThoroughbredBasicapplicationfollowingallotherrecordsforascendingsortsandbeforeallrecordsfordescendingsorts.
ThisisanMSSQLServerconventionandcannotbechanged.
However,twooptionsexisttoalterthisbehavior.
Bothmethodsinvolvethemodificationofthenulldatasothatthesortingmechanismtreatsthenullkeyvalueasnon-nulldata.
ThisismoreconsistentwithaThoroughbredBasicapplicationwherenullkeysappearatthetopofanascendingsortandatthebottomofadescendingsort.
Inthefirstmethod,thenulldataislogicallychanged.
ThisisaccomplishedbyusingtheMSSQLServerISNULLfunctionintheORDERBYclauseoftheSELECTstatement.
EachcolumnlistedintheORDERBYclauseispassedthroughthisfunctionwhichwillmapnullvaluestothevaluesuppliedtothefunction.
Inthiscase,thevalueisasinglespace.
Thisvaluewillthenbepassedtothesortingprocesssothattheserecordswillsortattheappropriateendoftheresultset.
Whilethismethodmakesnomodificationtothephysicaldata,itmaycausesortstoperformpoorlyforcompositeindexes.
Toactivatethismethod,specifythePRMORA_NVLNULLSintheIPLINPUTfile.
Forsinglecolumnindexesthatallownullvalues,performancemaynotbeanissuesincethisindexwouldnotbeutilized.
Inthesecondmethod,thedataischangedphysically.
WheneveranullcharacterfieldiswrittenorupdatedtoanMSSQLServertable,itwillbechangedtoasingleblank.
Allsubsequentaccesseswithindexeswillreturnthedatawiththeblankfieldssortedappropriately.
Thismethodwillallowtheindextobeusedforqueriessothattheresultsetisreturnedefficiently,butthedataismodified.
Itwillbeimportantforotherapplicationstomaintainthespaceinthedata.
Toactivatethismethod,specifythePRMORA_DONTWRITENULLSintheIPLINPUTfile.
Asanalternativetotheabovemethods,thetablecouldbecreatedsothatcolumnsusedasindexescannevercontainnullvalues.
ThiswillhappenifthefieldisdefinedinDictionary-IVasafixedlengthtype,eitheroptionalormandatory(EntryType1or3).
Thisattributewillinsurethatthedataforthatcolumnwillnotbenull.
Foraccesstoexistingtables,alloftheoptionsareavailable,however,caremustbetakenifnewdataiswrittentoatablewhentheORA_DONTWRITENULLSparameterisspecified.
Thiswillcreateasituationwhereonlythenewrecordswithnullkeysappearinthepropersortedorder.
ProcessingexistingSQLServerTablesToaccessexistingSQLServertablesyoumustbuildaFormatandLinkwiththeappropriatetableinformation.
Besuretousetheexactspellingfordataelementnamesandthetablename.
26Copyright2013ThoroughbredSoftwareInternational,Inc.
SQLServersupportsmanydatatypes.
NotalloftheSQLdatatypeshaveaonetooneThoroughbredequivalent.
ManyofthedatatypescanbederivedusingBasiccode.
ForexampleSQLServersupports4differentintegers(bigint,int,smallint,andtinyint).
Eachofthesedatatypesarebinarybyteswapped.
UsingDECandSWPyoushouldbeabletodeterminetheirvalues.
RefertoyourMicrosoftdocumentationforspecificinformationontheSQLServersupporteddatatypes.
BeawareofthefollowingSQLServerconstructs:Variablelengthfieldswheredatagreaterthan255characters.
NumericfieldsexceedingThoroughbred'smaximumof14significantdigits.
Columnnamesexceeding20charactersinlength.
Youmaybeabletoaccessatablethatcontainstheabovecharacteristicsforreadingandupdating.
Bydefiningaformatthatcontainsapartiallistofthefieldsdefinedinthetable,datacanbereadandupdatedwithoutaffectingthefieldsnotincludedintheformat.
TextFieldsforSQLServerDataDictionary-IVtreatsservertablesinternallyasMSORTforI/Oprocessing.
ThereforetextfielddataassociatedwithserversmustbestoredinaseparatefileasisthecurrentconventionwithMSORTtypefiles.
Thisfile,referencedasthetextfileintheLinkheader,mustbestoredontheserverwiththemaintabletomaintaindataintegrity,toprovideaccesstoallclients,andforbackingup.
IftheFormatdefinesoneormoretextfieldsthenaseparatetableforthetextdataisautomaticallygeneratedwhenthetablefortherecorddataiscreated.
TherecorddataisstoredinthetablereferencedbytheTableNamefieldandthetextfielddataisstoredinatablereferencedbytheTextFilefield.
Ifpresent,adot(.
)inthefilenameisconvertedtoanunderscore(_).
ThedefinitionofthetexttableisderivedfromtherecorddataandanumberofpresetfieldsthatareusedinternallybyDictionary-IV.
ThisdoesnotappearasaDictionary-IVFormatbutexistsasanSQLServertable.
AlogicalFormatcalledIDSV1hasbeencreatedtoaidinthehandlingofthisinformation.
ItisusedbythesystemandisnotassociatedwithanyspecificLink.
TheSQLServertablelayoutisasfollows:KEY_FIELDvarchar(len=lengthofprimarykey)TEXT_IDvarchar(len=1)CREATE_DATEdatetimeCHANGE_DATEdatetimeTEXT_WIDTHdecimal(3,0)TEXT_FIELDtextKEY_FIELDContainsthekeytotherecord.
Thisissimilartothecurrentkeystructureoftextfielddata,exceptthereisnoneedforasequencecountersincetheentiretextfieldisstoredinonerecord.
Thisispossiblebecausethetextdatatypehasamaximumstoragecapacityof2gigabytes.
TEXT_IDContainsthetextfieldidentifier.
(SeeFormatdefinition).
27Copyright2013ThoroughbredSoftwareInternational,Inc.
CREATE_DATESpecifiesthecreationdate.
CHANGE_DATESpecifiesthedateofthelastchange.
TEXT_WIDTHUsedbyDictionary-IVforcompatibilitytolocalMSORTprocessing.
TEXT_FIELDBecausethistextdatahasbeenmadeavailableasanSQLtable,thedataiscomprisedofpurelyprintablecharacters(exceptlineterminating$0A$).
Thereforeanycolororattributeinformationisstrippedpriortostorageinthetable.
GraphiccharactersareconvertedtoastandardASCIIcharacterrepresentation.
ChangesmadefromanSQLServerapplicationareavailabletotheThoroughbredapplication.
Allfilelevelaccess(create,erase,andclear)performedontherecordtableisalsoexecutedonthetexttable.
HoweverallrecordlevelaccessrequiresadistinctOPENofthetexttable.
ThisisconsistentwiththecurrentMSORTtextfieldoperation.
DuringI/Oprocessinga$FF$isimpliedinthefirstbyteofthekey.
Thisbytedoesnotappearinthedatabutisrequiredforprogrammingconsistency.
Thebyteisrequiredforallkeyaccessandisreturnedinallkeyfunctions(KEY,FKY,LKY,andPKY).
TheTextfielddataisautomaticallymaintainedwhenaccessedviaDictionary-IV.
NochangesarerequiredtoCONNECTs,Scripts,Reports,orQueriesthatcurrentlyuseTextfields.
28Copyright2013ThoroughbredSoftwareInternational,Inc.
PERFORMANCETUNINGThefollowingdescribeshowtomaximizethethroughputusingsortsandprocesscache.
SortsTomaximizethethroughputforanumberoftheBasicI/Ooperationsthatoperateonservertables,youmustcarefullyplanthedesignofthetable.
TheclientprocesscreatesgenericSQLServertables.
ItmakesnoassumptionaboutthedatatobecontainedintherecordotherthanwhatitdeterminesfromtheDictionary-IVFormat.
ThisissufficienttodescribethetabletoSQLServer,butundersomeconditions,sorting,forexamplethismaynotbeenough.
SQLServercreatesresultsetsthatitgeneratesfromstatementsthatspecifyorderingwhetherornotanindexexistsforthecolumnslistedintheORDERBYclause.
Withoutanindexthisoperationisverytimeconsumingforlargetables.
WithintheBasicenvironmentthisismultipliedsinceeachaccesstothetablemayrequireanin-linesort.
Basicsavestheresultsetfromaparticularqueryandusesitifthesubsequentquerymatchestheonethatgeneratedtheresult.
Ifitdoesnotmatch,theresultsetmustberegeneratedthenexttimethequeryisexecuted.
ThesolutionistocreateanindexforthecolumnstosortanddefineatleastonecolumnasNOTNULL(amandatoryfieldinDictionary-IV).
ThismustbedoneortheindexisignoredforORDERBYprocessing.
ThecreateprocesstranslatesmandatoryfieldsandthefirstcolumnoftheprimarykeyintocolumnswiththeNOTNULLconstraintifPRMCREATWDBATTRisconfigured.
Ifthesecolumnsarepartofindicesyouneednofurtherchanges.
Degradationduetoindicesbeingignoredbecomesevidentin:BasicusingLKY(),PKY(),andI/OwithsortsDictionary-IVFileaccesswithsorts(multi-recordmaintenance,viewmaintenance,sortorderchange)andbackwardreadsinfiles(multi-recordmaintenanceuparrowandpageup)Script-IVREADPREVIOUS,LASTTOFIRST,sorts,etc.
LogonCacheAllaccesstotheSQLServerdatabaseoccursthroughonelogintotheserverusingmultiplecursors.
WhenaLOCK,EXTRACT,orWRITEisperformedinBasicanewloginisexecutedtopreservekeypointerstotheothertablesopenedontheoriginallogin(LOCKs,EXTRACTs,andWRITEwillperformcommitsandwillclearallrecordlocks).
OncetheLOCK,EXTRACT,orWRITEiscomplete,thisloginisterminated.
ThelogoncachecountparameterintheIPLINPUTfileisusedtospecifythenumberofloginprocessestoretain.
29Copyright2013ThoroughbredSoftwareInternational,Inc.
Forexample,ifthelogoncachecountissetto5,apoolofupto5SQLloginprocesseswillremainopen.
ThenextLOCK,EXTRACT,orWRITEexecutedbyBasicwillattachitselftooneofthese5processesbypassingtheoverheadtologinandestablishanewprocess.
Byconfiguringthisvalueproperlyabalancedmaximumcanbemaintainedbetweenexcessiveloginprocessingandthenumberofactivelogins.
Amaximumof20loginscanbecached.
RegistrySettingsThefirstBasicclientconnectionwillcreateregistrysettings,iftheydonotalreadyexist.
Thesesettingscanbeusedtooptimizeperformance.
Toviewtheregistrysettings,runregeditfromtheWindowsstartmenu.
TheThoroughbredentriescanbefoundin:\HEY_LOCAL_MACHINE\SOFTWARE\ThoroughbredSoftwareInternational\TSDataServerforSQLServer.
Theregistrysettingscanbeusedasfollows:CLIENT-SYSTEM-INFONotused.
COMMITCOUNTDatabasecommitsaredoneforallactions(add,drop,insert,update,etc.
).
Forbatchprocessing,youmaywanttoadjustthisvaluesocommitsaredonelessoften.
Setthisvaluetothenumberoftransactionsbetweendatabasecommits.
Forexample,ifthisvalueis1000,adatabasecommitwillbedoneafterevery1000transactions.
DATA-SOURCEBasicwillusethisDataSourceforallSQLconnections.
Thisallowsuserstoconnecttoacommondatabase.
ThedefaultsystemDataSourceisLocalServer.
SeeSERVER.
MAPforadditionalcapabilities.
DEBUG-FLAGSThisentryisusedtoenableordisableadditionalinformationthatcanbewrittenintothedebuglogfile.
Theflagvaluesare:FlagNumberDescription1DisableloggingoftheprocessIDnumber2Enableloggingofatimestamp4Enableloggingofparametervalues.
8EnableloggingoffetchedvaluesDEBUG-LEVELIfthisvalueisnon-zero,allSQLstatementswillbelogged.
Thelogfile(tsmssql.
log)canbefoundintheinstalleddirectoryontheServer.
LDA-CACHEThisentrycanbesettochangethenumberofLogonDataAreastocache.
Dependingactivity(read,extract,sortaccess,etc.
),asmanyas6LDAsmaybeassociatedwithanopenBasicchannel.
SeetheOPEN-TABLE-CACHEcommentsbelowregardingresourceusage.
LOGGINGThissetsthelogginggrouptosomethingotherthanthedefault-logginggroup.
30Copyright2013ThoroughbredSoftwareInternational,Inc.
NO-OPENLOCKIfthisentryisnon-zero,noBasiclocksaredoneduringOPEN.
TheBasicOPENwillverifythetableexists.
ThereisnoconceptofOPENingatableinSQL.
ThedefaultlockhelpsBasicreturnanError=0whenausertriestoERASEatableopenedbyanotheruser.
OPEN-TABLE-CACHEAnumericvalueindicatinghowmanyopenBasicchannelstocache.
Thiswillenhanceperformanceintheareaoftableopens.
But,thissettingshouldbeusedwithcaution.
Ifthisvalueissettoohigh,thetsmssqlprocesswillbecomeaburdenonsystemresources.
PathThisentrycontainstheinstallpathandisusedforupdates.
READUNCOMMITTEDIfthisentryisnon-zero,"WITH(NOLOCK)"willbeaddedtogeneratedSQLstatementswhenREADing.
Thiscontrolisenabledbydefault.
UNIQUE-SORTSAnynon-zerovaluewillindicateallsecondarysortsareunique.
Thiswillspeedupthefetchprocess.
Onlysetthisvalue,ifyou'reabsolutelysureyoursortdefinitionswillgenerateuniquekeys.
SeePRMUNIQUE-KEYSforadditionaltuning.
31Copyright2013ThoroughbredSoftwareInternational,Inc.
USINGTHEDEBUGGINGFACILITYSQLOutputAdebuggingfacilityisavailablefromthetsmssqlprocess.
ThedebuggerisactivatedbyeithersettingtheregistryentryDEBUG-LEVELorstartingtheManagerservicewitha–dnparameter.
nCurrentlytheonlydebuglevelis1.
ThissendsthegeneratedSQLstatementstothelogfile(tsmssql.
log).
TheoutputinthelogfilecontainsaseparatelineforeachgeneratedSQLstatement.
Whiledebuggerisactive,outputforallclientsconnectedtothesameSQLServerGroupissenttoonetsmssql.
logfile.
Ifthetsmssql.
logfileiscopied,outputwillcontinuetobeplacedintherenamedfileuntiltheBasicsessionisterminated.
AllsubsequentBasicsessionswouldthenuseanewdebuglog.
32Copyright2013ThoroughbredSoftwareInternational,Inc.
GENERATEDSQLCODEThissectionshowstheresultingSQLsyntaxforThoroughbredfunctions(Italictypeface).
Theyarepresentedinalphabeticalorder.
SQLSyntaxforThoroughbredFunctionsCLOSE(channel)COMMITThisisperformedviaadirectOCIcall,notasaliteralSQLstatement.
ERASEDROPTABLEtable-nameIftextfieldsareconfigured:DROPTABLEtexttable-nameEXTRACT[RECORD](channel)SELECTcolumnsFROMtable-nameWHEREkey-conditions>=current-keyORDERBYsort-informationGetkeyfromfirstrecordorcurrentSELECTSELECTcolumnsFROMtable-nameWHEREkey-conditions=current-keyORDERBYsort-informationFORUPDATENOWAITEXTRACT[RECORD](channel,KEY=)SELECTcolumnsFROMtable-nameWHEREkey-conditions=current-keyORDERBYsort-informationFORUPDATENOWAITFKY()SELECTcolumnsFROMtable-nameWHEREkey-conditionsORDERBYsort-informationGetthefirstrecord.
Extractthekeyinformationfromtherecord.
INITFILEDELETEFROMtable-nameIftextfieldsareconfigured:DELETEFROMtext-table-name33Copyright2013ThoroughbredSoftwareInternational,Inc.
KEY()IfarecordisnotextractedSELECTcolumnsFROMtable-nameWHEREkey>keyoflastrecordreadORDERBYsort-informationOtherwise:Extractthekeyinformationfromtherecord.
LKY()SELECTcolumnsFROMtable-nameORDERBYreverse-sortGetthefirstrecord.
Extractthekeyinformationfromtherecord.
LOCK(channel)SELECT1FROMtable-nameWITH(HOLDLOCK,TABLOCKX)WHERE1=2OPEN(channel,OPT="LINK|CREATE")CREATETABLEtable-nameIftextfieldsareconfigured:CREATETABLEtext-table-nameForsort0:CREATEUNIQUEINDEXI_table-name_0Forsortn:CREATEINDEXI_table-name_nOPEN(channel,OPT="LINK")SELECT1FROMtable-nameWITH(HOLDLOCK)WHERE1=2OPEN(channel,OPT="LINK|TEXT")SELECT1FROMtable-nameWITH(HOLDLOCK)WHERE1=2PKY()IfarecordisnotextractedSELECT/*+INDEX_DESC(table-name)*/columnsFROMtable-nameWHEREkey-conditionsORDERBYreverse-sortOtherwise:Extractthekeyinformationfromtherecord.
34Copyright2013ThoroughbredSoftwareInternational,Inc.
PREAD[RECORD](channel)IfaSELECTisinmemorygetthenextrecordElseSELECT/*+INDEX_DESC(table-name)*/columnsFROMtable-nameWHEREkey-conditionsORDERBYreverse-sortREAD[RECORD](channel,KEY=)FIND[RECORD](channel,KEY=)IfaSELECTisinmemorygetthenextrecordElseSELECTcolumnsFROMtable-nameWHEREkey-conditions>=currentkeyORDERBYsort-informationREMOVE(channel,KEY=)SELECTcolumnsFROMtable-nameWHEREkey-conditionsFORUPDATENOWAITDELETEFROMtable-nameWHEREkey-conditionsRENAMEAtablerenamerequiresanSQLstoredprocedure.
UNLOCK(channel)NoSQLequivalent.
DatawillbecommittedandBasic'sinternallockonthechannelwillbereleased.
WRITE[RECORD](channel,KEY=)GetkeyvaluefromtherecorddataSELECTcolumnsFROMtable-nameWHEREkey-conditions=current-keyFORUPDATENOWAITIftherecordexists:UPDATEtable-nameSETcolumn=value,.
.
.
Else:INSERTINTOtable-namecolumn.
.
.
VALUES(value.
.
.
)fiCOMMITThisisperformedviaadirectSQLcall,notasaliteralSQLstatement.
35Copyright2013ThoroughbredSoftwareInternational,Inc.
MultipleSelectStatementsThefollowingBasiccommandsmayproducemultipleselectstatementswhensequentiallyaccessingtableswithmultiplekeyfieldsdefinedintheFormat:READ[RECORD]FIND[RECORD]PREAD[RECORD]EXTRACT[RECORD]KEY()PKY()FKY()UnsupportedBasicDirectivesThereisnoequivalentSQLcodeforthefollowingBasicdirectives:ADDADDSORTFILEREMSORTTheseitemsarenotsupportedwhenappliedtoservertables.
36Copyright2013ThoroughbredSoftwareInternational,Inc.
TECHNICALREFERENCEThefollowingprovidesinformationspecifictoScript-IV,Dictionary-IV,ThoroughbredBasic,andSQLServerviews.
Script-IV/Dictionary-IVThefollowinginformationisspecifictoScript-IVandDictionary-IVDATA-FILEIS,SORT-FILEISThisfeaturewillbesupportedbuttheobjectreferencedbythiscommandmustbeaLinkratherthanalocaldatafile.
TheLinkcanthenreferenceeitheraservertableoralocalfile.
ThefiletypesetinthisLinkmustbe"M".
AccessSubsetofColumnsBydefiningaformatthatcontainsasubsetofthecolumnsthataredefinedinatable,accesstotablesthatcontainunsupporteddatatypes(BIGINT,IMAGE,NTEXT,etc.
)ispossible.
AllofthepossibleI/Odirectivesaresupportedwithnoeffectontheunreferencedfields.
BasicSoftIncludesWhenanOPENisexecutedonaserverLink,theFormatreferencedintheLinkheaderissoftincluded.
ThismeansthattheFormatdataareaisdefinedinmemory.
However,theFormatnamewillnotappearintheFMTNLlistandwillnotinterferewithanyhardincludesthattakeplace.
ThesoftincludeisexecutedinternallyasaFORMATINCLUDE#format-name,OPT="NONE"sothatanydatadefinedinaFormatdataareaatthetimeofanOPENofaserverLinkthatreferencesthisFormatispreserved.
IfhoweveraREADoraWRITEisexecutedusinganIOLIST,theFormatdataareawillbemodifiedsincetheI/OoperationusestheFormatdataareatoparsethedataintothepropervariablevalues.
37Copyright2013ThoroughbredSoftwareInternational,Inc.
EnhancedAccesstoTablesWherepossiblesequentialaccesstotablesmayprovidefasteraccesstimesduringREADoperations.
ThisispossiblebecauseaparticularI/Ostatementwillgeneratearesultsetontheserver.
SubsequentREADoperationswillsimplymovethroughtheresultsetandreturnthedatatoBasic.
TheareawherethisismostobviousiswhenatableissortedbyBasicinawaythatnoindexexistsinSQLServer.
TheinitialREADwillforcethesorttotakeplace,butallREADSthatfollowwillgetthedatafromtheresultsetwithouttheneedtosortagain.
IfanyotherI/OstatementhadoccurredbetweenREADsanotherresultsetwouldhavebeencreatedforcingare-sortonthenextREADstatements.
ThefollowingisNOTconsideredasequentialREAD:K$=KEY(CH);READ(CH,KEY=K$)Thiscodegeneratestwoseparateresultsets.
EachREADthatspecifiesaspecifickeygeneratesanewresultset.
AsequentialREADlookslike:READ(CH,ERC=2)TablesSQLServerViewsWherepossibletheuseofanSQLServerviewasthetableinLinkdefinitionshouldbeavoided.
ViewswillnotsupportalloftheSQLstatementsthataregeneratedbyBasicstatements.
ThemostcommonofthesewillbetheSELECTFORUPDATEwhichistheEXTRACTdirectiveinBasic.
Thereforeitispossiblethatservererrors(ERR=150)willoccuraccessingViewswhichwillinterruptnormalprogramfunctioning.
AlsofortheViewdatatobereturnedinasortedorder,thesortmustbeexplicitlydefinedinthedefinitionoftheviewinSQLServer.
Merelydefiningkeyfieldswillnotproducesortedresults.
AsortmustbedefinedinthelinkheaderandthenusedinallI/Ostatements.
38Copyright2013ThoroughbredSoftwareInternational,Inc.
ERRORMESSAGESThefollowingdescribesclienterrormessages.
Client/ServerBasicErrorMessages150Explanation:Serversystemerror.
FormoreinformationrefertoTCB(20)togettheservererrornumber.
AlsoseeVirtualErrorTablebelow.
171Explanation:UndefinedLink.
VirtualErrorTableExplanation:ErrorsaccessingSQLServertableswillgeneratetexterrorsinaVirtualErrorTable.
TheVirtualErrorTableisdefinedastheLink:OOLSQLER.
BeforeusingtheVirtualErrorTable,youwillneedtoupdatethisLinktoreflectyourServerID.
AfteranyerrortryingtoaccessSQLServerdata,youcanchecktheVirtualErrorTableformoredetailedSQLServererrormessages.
Oftenthistablewillcontainmorethanoneerrormessage,soyouwillneedtoreadrecordsuntilyoureachendoffile.
TheVirtualErrorTableisaccessedlikeanSQLServertablebutitisstoredinThoroughbredmemory.
Itdoesn'tactuallymakeanSQLServercall.
ThefollowingissamplecodeonaccessingtheVirtualErrorTable:CH=UNT;FORMATINCLUDE#OOFSQLEROPEN(CH,OPT="LINK")"OOLSQLER";CLEARERC;WHILEERC2;READ(CH,ERC=2)#OOFSQLER;IFERC=0PRINT#OOFSQLERFI;WENDThoroughbredBasicStartupMessagesNomatchforserveridinSERVER.
MAPExplanation:Thetwo-characterserveriddefinedintheIPLINPUTfilecannotbelocatedintheSERVER.
MAPfile.
Action:Verifycontentsofbothfiles.
39Copyright2013ThoroughbredSoftwareInternational,Inc.
CouldnotresolveNetworkaddressforThoroughbredforeignfileserverExplanation:SystemdoesnotunderstandtheNetworkaddressconfiguredintheSERVER.
MAPfile.
Action:VerifythattheaddressorhostnameintheSERVER.
MAPfileisavalidvalueforthesystem.
OnsystemsthatuseDNS,besurethesystemisconfigured.
InvalidThoroughbredforeignfileservertypeExplanation:ThesystemfoundaninvalidvalueintheservertypeparameterintheIPLINPUTserverdeviceline.
Action:VerifythattheserverdevicelineisdefinedproperlywithaServertypeof4.
CannotmixSQLandWindowsAuthenticationforSQLServerExplanation:TheIPLINPUTfileindicatesWindowsAuthenticationbutalsoincludesanSQLServerLogin/PasswordAuthentication.
Action:EithersettheAuthenticationTypeto1(SQLServer)orremovetheSQLServerLogin/Passwordvalue.
CouldnotconnecttoThoroughbredforeignfileserverExplanation:ThoroughbredBasiccouldnotestablishcommunicationtothetsmssqlmgrmanager.
Action:VerifythattheThoroughbredManagerserviceisstarted.
40Copyright2013ThoroughbredSoftwareInternational,Inc.
TROUBLESHOOTINGThefollowingprovidestroubleshootinginformationspecifictoSQLServer.
LinkErrorError0occursopeningtheLinkandnolocksseemtobeactiveinBasicorfromanSQLapplication.
SincetheLinkheaderisreadinternallybyBasicwhenopeningtheLink,thisrecordneedstobeaccessibleatopentime.
IfitisextractedthroughDictionary-IV,forexample,theerror0occurs.
SettingthePRMREADONLYparameterintheIPLINPUTfilewillclearthissituation.
ServerTableErrorErrorsopeningservertables.
VerifythattheDEVlinefortheserverwherethetableresidesispresentintheappropriateIPLINPUTfile.
VerifytheuserhasSQLServercredentialsfortheDatabaseandTable.
VerifytheLinkhasaTablename.
IftheassociatedFormathastextfieldsdefined,verifythattheLinkalsohasaTextFileName.
UnexpectedSortingSequenceorErrorsAccessingSQLTablesIfaLinkheaderhasbeenconfiguredtoaccessaservertabledefinedontheserverasaview,unexpectedresultsrangingfromincorrectlysorteddatatotsmssqlprocessfailuresmayresult.
Itissuggestedthataccesstotheserverbelimitedtoobjectsdefinedastables.
SQLdoesnotsupportcertainoperationsonviewssuchasSELECT.
.
.
FORUPDATE.
Besurethattheprimarykeyindexispresent.
CheckthecollationsequencefortheSQLServerDatabase.
ThedefaultcollationsequencemaynotbeASCII.
HangingonaWRITEUndercertaincircumstancesaWRITEmayhangiftherowislockedinSQLServerusingashare(S)orsharerowexclusive(SRX)typelock.
ThisoccursbecausetheINSERT/UPDATEpartoftheSQLcodeforaWRITEstatementdoesnotsupportNOWAITandwillwaituntilthelockisremovedbytheSQLServerprocess.
TheshareandsharerowexclusivetypelocksarenotgeneratedbyanyBasiccode,andthereforethissituationcannotoccurbetweenBasictasks.
41Copyright2013ThoroughbredSoftwareInternational,Inc.
SomeAccessUsingSortsisVerySlowSortsthatcontainreferencestosubstringsoffieldsmaynotperformaswellassortsonfullfields.
ThisisbecauseSQLServerhasnofacilitytodefineindexesonportionsoffields.
FormoreinformationseethePerformanceTuningsectionofthismanual.