Introducing SQL Anywhere Studio - djack.com.pl

401 Pages·2004·2.2 MB·English

Introducing SQL Anywhere®
Studio
Partnumber: DC38120-01-0902-01
Lastmodified: October2004
Copyright©1989–2004Sybase,Inc.Portionscopyright©2001–2004iAnywhereSolutions,Inc.Allrightsreserved.
Nopartofthispublicationmaybereproduced,transmitted,ortranslatedinanyformorbyanymeans,electronic,mechanical,manual,optical,or
otherwise,withoutthepriorwrittenpermissionofiAnywhereSolutions,Inc.iAnywhereSolutions,Inc.isasubsidiaryofSybase,Inc.
Sybase,SYBASE(logo),AccelaTrade,ADAWorkbench,AdaptableWindowingEnvironment,AdaptiveComponentArchitecture,AdaptiveServer,
AdaptiveServerAnywhere,AdaptiveServerEnterprise,AdaptiveServerEnterpriseMonitor,AdaptiveServerEnterpriseReplication,Adaptive
ServerEverywhere,AdaptiveServerIQ,AdaptiveWarehouse,AnswerBase,AnywhereStudio,ApplicationManager,AppModeler,
APTWorkbench,APT-Build,APT-Edit,APT-Execute,APT-Library,APT-Translator,ASEP,AvantGo,AvantGoApplicationAlerts,AvantGo
MobileDelivery,AvantGoMobileDocumentViewer,AvantGoMobileInspection,AvantGoMobileMarketingChannel,AvantGoMobilePharma,
AvantGoMobileSales,AvantGoPylon,AvantGoPylonApplicationServer,AvantGoPylonConduit,AvantGoPylonPIMServer,AvantGo
PylonPro,BackupServer,BayCam,Bit-Wise,BizTracker,CertifiedPowerBuilderDeveloper,CertifiedSYBASEProfessional,CertifiedSYBASE
ProfessionalLogo,ClearConnect,ClientServices,Client-Library,CodeBank,ColumnDesign,ComponentPack,ConnectionManager,Convoy/DM,
Copernicus,CSP,DataPipeline,DataWorkbench,DataArchitect,DatabaseAnalyzer,DataExpress,DataServer,DataWindow,DB-Library,
dbQueue,DevelopersWorkbench,DirectConnectAnywhere,DirectConnect,DistributionDirector,DynamicMobilityModel,Dynamo,e-ADK,
E-Anywhere,e-BizIntegrator,ECGateway,ECMAP,ECRTP,eFulfillmentAccelerator,ElectronicCaseManagement,EmbeddedSQL,EMS,
EnterpriseApplicationStudio,EnterpriseClient/Server,EnterpriseConnect,EnterpriseDataStudio,EnterpriseManager,EnterprisePortal(logo),
EnterpriseSQLServerManager,EnterpriseWorkArchitecture,EnterpriseWorkDesigner,EnterpriseWorkModeler,eProcurementAccelerator,
eremote,EverythingWorksBetterWhenEverythingWorksTogether,EWA,E-Whatever,FinancialFusion,FinancialFusion(anddesign),Financial
FusionServer,FormulaOne,FusionPowerede-Finance,FusionPoweredFinancialDestinations,FusionPoweredSTP,GatewayManager,
GeoPoint,GlobalFIX,iAnywhere,iAnywhereSolutions,ImpactNow,IndustryWarehouseStudio,InfoMaker,InformationAnywhere,Information
Everywhere,InformationConnect,InstaHelp,IntelligentSelf-Care,InternetBuilder,iremote,iScript,JaguarCTS,jConnectforJDBC,
KnowledgeBase,LogicalMemoryManager,MailAnywhereStudio,MainframeConnect,MaintenanceExpress,ManageAnywhereStudio,MAP,
M-BusinessChannel,M-BusinessNetwork,M-BusinessServer,MDIAccessServer,MDIDatabaseGateway,media.splash,MessageAnywhere
Server,MetaWorks,MethodSet,MLQuery,MobiCATS,MyAvantGo,MyAvantGoMediaChannel,MyAvantGoMobileMarketing,MySupport,
Net-Gateway,Net-Library,NewEraofNetworks,NextGenerationLearning,NextGenerationLearningStudio,ODEVICE,OASiS,OASiSlogo,
ObjectConnect,ObjectCycle,OmniConnect,OmniSQLAccessModule,OmniSQLToolkit,OpenBiz,OpenBusinessInterchange,OpenClient,
OpenClient/Server,OpenClient/ServerInterfaces,OpenClientConnect,OpenGateway,OpenServer,OpenServerConnect,OpenSolutions,
Optima++,OrchestrationStudio,PartnershipsthatWork,PB-Gen,PCAPTExecute,PCDB-Net,PCNetLibrary,PhysicalArchitect,Pocket
PowerBuilder,PocketBuilder,PowerThroughKnowledge,power.stop,Power++,PowerAMC,PowerBuilder,PowerBuilderFoundationClass
Library,PowerDesigner,PowerDimensions,PowerDynamo,PoweringtheNewEconomy,PowerJ,PowerScript,PowerSite,PowerSocket,
Powersoft,PowersoftPortfolio,PowersoftProfessional,PowerStage,PowerStudio,PowerTips,PowerWareDesktop,PowerWareEnterprise,
ProcessAnalyst,QAnywhere,Rapport,RelationalBeans,RepConnector,ReplicationAgent,ReplicationDriver,ReplicationServer,Replication
ServerManager,ReplicationToolkit,ReportWorkbench,Report-Execute,ResourceManager,RW-DisplayLib,RW-Library,S.W.I.F.T.Message
FormatLibraries,SAFE,SAFE/PRO,SDF,SecureSQLServer,SecureSQLToolset,SecurityGuardian,SKILS,smart.partners,smart.parts,
smart.script,SQLAdvantage,SQLAnywhere,SQLAnywhereStudio,SQLCodeChecker,SQLDebug,SQLEdit,SQLEdit/TPU,
SQLEverywhere,SQLModeler,SQLRemote,SQLServer,SQLServerManager,SQLServerSNMPSubAgent,SQLServer/CFT,
SQLServer/DBM,SQLSMART,SQLStation,SQLToolset,SQLJ,StageIIIEngineering,Startup.Com,STEP,SupportNow,SybaseCentral,
SybaseClient/ServerInterfaces,SybaseDevelopmentFramework,SybaseFinancialServer,SybaseGateways,SybaseLearningConnection,
SybaseMPP,SybaseSQLDesktop,SybaseSQLLifecycle,SybaseSQLWorkgroup,SybaseSynergyProgram,SybaseUserWorkbench,Sybase
VirtualServerArchitecture,SybaseWare,SyberFinancial,SyberAssist,SybMD,SyBooks,System10,System11,SystemXI(logo),SystemTools,
TabularDataStream,TheEnterpriseClient/ServerCompany,TheExtensibleSoftwarePlatform,TheFutureIsWideOpen,TheLearning
Connection,TheModelForClient/ServerSolutions,TheOnlineInformationCenter,ThePowerofOne,TotalFix,TradeForce,Transact-SQL,
TranslationToolkit,TurningImaginationIntoReality,UltraLite,UltraLite.NET,UNIBOM,Unilib,Uninull,Unisep,Unistring,URKRuntimeKit
forUniCode,Versacore,Viewer,VisualWriter,VQL,WarehouseControlCenter,WarehouseStudio,WarehouseWORKS,WarehouseArchitect,
Watcom,WatcomSQL,WatcomSQLServer,WebDeploymentKit,Web.PB,Web.SQL,WebSights,WebViewer,WorkGroupSQLServer,
XA-Library,XA-Server,andXPServeraretrademarksofSybase,Inc.oritssubsidiaries.
Allothertrademarksarepropertyoftheirrespectiveowners.
ii
Contents
AboutThisManual ix
SQLAnywhereStudiodocumentation . . . . . . . . . . . . . x
Accessinginformationonline . . . . . . . . . . . . . . . . . . xiii
Documentationconventions . . . . . . . . . . . . . . . . . . . xvi
TheAdaptiveServerAnywheresampledatabase . . . . . . . xviii
TheCustDBsampledatabase . . . . . . . . . . . . . . . . . xix
Findingoutmoreandprovidingfeedback . . . . . . . . . . . xx
I SQL Anywhere Studio Overview 1
1 IntroducingSQLAnywhereStudio 3
WelcometoSQLAnywhereStudio . . . . . . . . . . . . . . . 4
InstallingSQLAnywhereStudio. . . . . . . . . . . . . . . . . 6
2 IntroducingAdaptiveServerAnywhereandUltraLite 9
IntroductiontoAdaptiveServerAnywhere . . . . . . . . . . . 10
Intendeduses . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Hallmarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Thedatabaseserver . . . . . . . . . . . . . . . . . . . . . . . 14
Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
IntroductiontoUltraLite . . . . . . . . . . . . . . . . . . . . . 16
ComparingAdaptiveServerAnywhereandUltraLite . . . . . 18
3 IntroducingReplicationTechnologies 21
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Consolidatedandremotedatabases . . . . . . . . . . . . . . 23
Propagationmethods . . . . . . . . . . . . . . . . . . . . . . 26
Sybasereplicationtechnologies . . . . . . . . . . . . . . . . 29
II Platform Support 35
4 SQLAnywhereStudioforWindowsCE 37
Gettingstarted . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Usingthesampleapplications . . . . . . . . . . . . . . . . . 45
ConfiguringWindowsCEdatabases . . . . . . . . . . . . . . 53
UsingthedatabaseserveronWindowsCE . . . . . . . . . . 62
Usingtheadministrationutilities. . . . . . . . . . . . . . . . . 69
iii
SQLAnywhereStudiofeaturesnotsupportedonWindowsCE 80
5 SQLAnywhereStudioSupportedPlatforms 95
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
WindowsandNetWareoperatingsystems . . . . . . . . . . . 98
UNIX,Linux,andMacintoshoperatingsystems . . . . . . . . 103
MobiLinksynchronizationconsolidateddatabases . . . . . . 107
iAnywhereSolutionsODBCdriverssupportedplatforms . . . 108
UltraLitetargetplatforms. . . . . . . . . . . . . . . . . . . . . 109
Operatingsystemversions . . . . . . . . . . . . . . . . . . . 111
III Working with Databases 115
6 TheArchitectureofDatabaseApplications 117
Relationaldatabaseconcepts . . . . . . . . . . . . . . . . . . 118
Thepiecesofadatabasesystem . . . . . . . . . . . . . . . . 123
Howthepiecesfittogether . . . . . . . . . . . . . . . . . . . 125
Multi-tiercomputingarchitecture . . . . . . . . . . . . . . . . 128
Usingmultipledatabases . . . . . . . . . . . . . . . . . . . . 129
Applicationprogramminginterfaces . . . . . . . . . . . . . . 131
InsideAdaptiveServerAnywhere . . . . . . . . . . . . . . . . 136
7 SelectingDatafromDatabaseTables 139
SQLanddatabasecomputing . . . . . . . . . . . . . . . . . . 140
SELECTstatement . . . . . . . . . . . . . . . . . . . . . . . . 142
Selectingacompletetable. . . . . . . . . . . . . . . . . . . . 143
Selectingcolumnsfromatable . . . . . . . . . . . . . . . . . 145
Orderingqueryresults . . . . . . . . . . . . . . . . . . . . . . 148
Selectingrowsfromatable . . . . . . . . . . . . . . . . . . . 151
8 SelectingDatafromMultipleTables 157
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Joiningtablesusingthecrossproduct . . . . . . . . . . . . . 159
UsingtheONphrasetorestrictajoin . . . . . . . . . . . . . 160
Joiningtablesusingkeyjoins . . . . . . . . . . . . . . . . . . 162
Joiningtablesusingnaturaljoins . . . . . . . . . . . . . . . . 164
Joiningtablesusingouterjoins . . . . . . . . . . . . . . . . . 166
9 SelectingDataUsingSubqueries 167
Introducingsubqueries. . . . . . . . . . . . . . . . . . . . . . 168
Single-rowandmultiple-rowsubqueries . . . . . . . . . . . . 170
Usingsubqueriesinsteadofjoins . . . . . . . . . . . . . . . . 172
iv
10 SelectingAggregateData 175
Summarizingdata . . . . . . . . . . . . . . . . . . . . . . . . 176
Afirstlookataggregatefunctions. . . . . . . . . . . . . . . . 177
Applyingaggregatefunctionstogroupeddata . . . . . . . . . 178
Restrictinggroups . . . . . . . . . . . . . . . . . . . . . . . . 180
11 UpdatingtheDatabase 183
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Addingrowstoatable . . . . . . . . . . . . . . . . . . . . . . 185
Modifyingrowsinatable . . . . . . . . . . . . . . . . . . . . 186
Deletingrows . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Groupingchangesintotransactions . . . . . . . . . . . . . . 188
Integritychecking . . . . . . . . . . . . . . . . . . . . . . . . . 191
IV SQL Anywhere Studio Tutorials 195
12 TheSampleDatabase 197
Aboutthesampledatabase . . . . . . . . . . . . . . . . . . . 198
Lesson1: Makeacopyofthesampledatabase . . . . . . . . 200
Lesson2: StarttheAdaptiveServerAnywheredatabaseserver201
Lesson3: Displayingthedatabaseserverwindow . . . . . . 203
Lesson4: Shutdownthedatabaseserver . . . . . . . . . . . 205
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
13 MakingtheConnection 207
Aboutconnections . . . . . . . . . . . . . . . . . . . . . . . . 208
AboutODBCdatasources . . . . . . . . . . . . . . . . . . . 209
Lesson1: CreateanODBCdatasource . . . . . . . . . . . . 210
Lesson2: ConnectusingtheSampleODBCdatasource . . 213
Lesson3: Othermeansofconnecting . . . . . . . . . . . . . 214
Lesson4: Disconnectingfromthesampledatabase . . . . . 215
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
14 UsingInteractiveSQL 217
AboutInteractiveSQL . . . . . . . . . . . . . . . . . . . . . . 218
Lesson1: StartInteractiveSQL . . . . . . . . . . . . . . . . . 219
Lesson2: TheInteractiveSQLinterface . . . . . . . . . . . . 220
Lesson3: DisplayingdatausingInteractiveSQL . . . . . . . 226
Lesson4: WorkingwithSQLstatements . . . . . . . . . . . . 232
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
15 ManagingDatabaseswithSybaseCentral 241
AboutSybaseCentral . . . . . . . . . . . . . . . . . . . . . . 242
v
Lesson1: StartSybaseCentralandconnect . . . . . . . . . 243
Lesson2: TheSybaseCentralinterface . . . . . . . . . . . . 244
Lesson3: Viewingthesampledatabase . . . . . . . . . . . . 246
Lesson4: Createandedittables . . . . . . . . . . . . . . . . 251
Lesson5: Manageusersandgroups . . . . . . . . . . . . . . 253
Lesson6: Viewandeditstoredprocedures . . . . . . . . . . 255
Lesson7: Backupyourdatabase . . . . . . . . . . . . . . . 258
Restorethesampledatabase . . . . . . . . . . . . . . . . . . 259
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
16 DesigningandBuildingaDatabase 261
Aboutdesigninganewdatabase . . . . . . . . . . . . . . . . 262
Designingandbuildingasimpledatabase . . . . . . . . . . . 263
Lesson1: Createadatabasefile . . . . . . . . . . . . . . . . 264
Lesson2: Connecttoyourdatabase . . . . . . . . . . . . . . 266
Lesson3: Designandcreateatable . . . . . . . . . . . . . . 267
Lesson4: Identifyandcreateprimarykeys . . . . . . . . . . 269
Lesson5: Designcolumnproperties . . . . . . . . . . . . . . 271
Lesson6: Designandcreaterelationshipsbetweentables . . 274
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
17 SynchronizingDatabaseswithMobiLink 279
AboutMobiLink . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Lesson1: Createyourdatabases . . . . . . . . . . . . . . . . 282
Lesson2: Preparethedatabasesforsynchronization . . . . 286
Lesson3: StarttheMobiLinksynchronizationserver . . . . . 289
Lesson4: RuntheMobiLinksynchronizationclientutility . . . 290
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
18 ReplicatingDatawithSQLRemote 293
AboutSQLRemote . . . . . . . . . . . . . . . . . . . . . . . 294
Lesson1: GettingStarted . . . . . . . . . . . . . . . . . . . . 295
Lesson2: Setuptheconsolidateddatabase . . . . . . . . . 296
Lesson3: Setuptheremotedatabase . . . . . . . . . . . . . 300
Lesson4: Replicatedata . . . . . . . . . . . . . . . . . . . . 302
Lesson5: Restorethedatabaseanddatabasesettings . . . 305
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
19 DesigningDatabaseswithPowerDesigner 309
AboutPowerDesigner . . . . . . . . . . . . . . . . . . . . . . 310
Lesson1: GettingStarted . . . . . . . . . . . . . . . . . . . . 312
Lesson2: Addacolumn . . . . . . . . . . . . . . . . . . . . . 317
Lesson3: Checkyourwork . . . . . . . . . . . . . . . . . . . 320
vi
Lesson4: Savechangesandgeneratedatabase . . . . . . . 321
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
20 CreatingReportswithInfoMaker 327
AboutInfoMaker . . . . . . . . . . . . . . . . . . . . . . . . . 328
Lesson1: GettingStarted . . . . . . . . . . . . . . . . . . . . 329
Lesson2: Createabasicreport. . . . . . . . . . . . . . . . . 330
Lesson3: Enhanceyourreport . . . . . . . . . . . . . . . . . 333
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
21 MicrosoftVisualBasicQuickStart 339
Tutorial: DevelopingaVisualBasicapplication . . . . . . . . 340
V Appendix 343
22 Glossary 345
Index 363
vii
viii
About This Manual
Subject ThisbookintroducesSQLAnywhereStudio,thecompleterelational
data-managementandsynchronizationsystemformobile,embedded,and
workgroupcomputing.
SQLAnywhereStudioisasetofsoftwarecomponentsforworkingwith
relationaldata. ItincludestheAdaptiveServerAnywhereandUltraLite
relationaldatabase-managementsystems,aswellasdatasynchronization
andreplicationtechnology. Italsoincludesapplicationsfordesigningand
deployingdatabases,andforcreatingcustomreportsanddataentryforms.
Audience Thisbookisforallapplicationdevelopersanddatabaseadministratorsusing
SQLAnywhereStudio.
Beforeyoubegin Thisbookassumesanelementaryfamiliaritywithrelationaldatabasesand
SQL.
ix
SQL Anywhere Studio documentation
ThisbookispartoftheSQLAnywheredocumentationset. Thissection
describesthebooksinthedocumentationsetandhowyoucanusethem.
TheSQLAnywhere TheSQLAnywhereStudiodocumentationisavailableinavarietyofforms:
Studiodocumentation inanonlineformthatcombinesallbooksinonelargehelpfile;asseparate
PDFfilesforeachbook;andasprintedbooksthatyoucanpurchase. The
documentationconsistsofthefollowingbooks:
¤ IntroducingSQLAnywhereStudio Thisbookprovidesanoverviewof
theSQLAnywhereStudiodatabasemanagementandsynchronization
technologies. Itincludestutorialstointroduceyoutoeachofthepieces
thatmakeupSQLAnywhereStudio.
¤ What’sNewinSQLAnywhereStudio Thisbookisforusersof
previousversionsofthesoftware. Itlistsnewfeaturesinthisand
previousreleasesoftheproductanddescribesupgradeprocedures.
¤ AdaptiveServerAnywhereDatabaseAdministrationGuide This
bookcoversmaterialrelatedtorunning,managing,andconfiguring
databasesanddatabaseservers.
¤ AdaptiveServerAnywhereSQLUser’sGuide Thisbookdescribes
howtodesignandcreatedatabases;howtoimport,export,andmodify
data;howtoretrievedata;andhowtobuildstoredproceduresand
triggers.
¤ AdaptiveServerAnywhereSQLReferenceManual Thisbook
providesacompletereferencefortheSQLlanguageusedbyAdaptive
ServerAnywhere. ItalsodescribestheAdaptiveServerAnywhere
systemtablesandprocedures.
¤ AdaptiveServerAnywhereProgrammingGuide Thisbookdescribes
howtobuildanddeploydatabaseapplicationsusingtheC,C++,andJava
programminglanguages. UsersoftoolssuchasVisualBasicand
PowerBuildercanusetheprogramminginterfacesprovidedbythose
tools. ItalsodescribestheAdaptiveServerAnywhereADO.NETdata
provider.
¤ AdaptiveServerAnywhereSNMPExtensionAgentUser’sGuide
ThisbookdescribeshowtoconfiguretheAdaptiveServerAnywhere
SNMPExtensionAgentforusewithSNMPmanagementapplicationsto
manageAdaptiveServerAnywheredatabases.
¤ AdaptiveServerAnywhereErrorMessages Thisbookprovidesa
completelistingofAdaptiveServerAnywhereerrormessagestogether
withdiagnosticinformation.
x