Companion CD includes SQL Anywhere Studio 9 Developer Edition (for Windows), all the code from the examples and full BNF from the book in HTML format!
SQL Anywhere Studio 9 Developer’s Guide describes in detail how to administer and program SQL Anywhere, the leading relational database for mobile, distributed, and embedded applications. This book focuses on the various tasks performed during the life cycle of a typical database, such as creating tables and other schema objects, and inserting, selecting, updating, and deleting data. Additional topics include packaging SQL code into procedures, triggers, and events; distributing data across multiple databases using MobiLink; protecting the database from disaster; and tuning for performance.
Learn how to create and normalize tables with various data types and constraints; insert and manage data; retrieve data with the SELECT statement; synchronize data with MobiLink; protect your data through the use of logging, backups, and validation; optimize your database using the SQL Anywhere performance tuning tools: request-level logging, Index Consultant, Execution Profiler, and Graphical Plan.
| Preface | ix | |
| Acknowledgments | xiii | |
| About the Author | xiv | |
| Chapter 1 | Creating | 1 |
| 1.1 | Introduction | 1 |
| 1.2 | Codd's 12 Rules for Relational Databases | 2 |
| 1.3 | Five Types of Tables | 4 |
| 1.4 | Global Permanent Table | 6 |
| 1.5 | Data Types | 7 |
| 1.5.1 | A String Is a String: Binary, Character, Long | 8 |
| 1.5.2 | Dates and Times | 10 |
| 1.5.3 | Exact Decimals | 10 |
| 1.5.4 | Floating-Point Numbers | 11 |
| 1.5.5 | Binary Integers | 12 |
| 1.5.6 | Literals | 13 |
| 1.6 | Column Properties | 14 |
| 1.7 | Computed Columns | 14 |
| 1.8 | Default Values | 16 |
| 1.8.1 | Default Autoincrement | 17 |
| 1.8.2 | Default Global Autoincrement | 18 |
| 1.8.3 | Literal Defaults | 19 |
| 1.8.4 | Special Update Defaults | 20 |
| 1.8.5 | Expressions as Defaults | 21 |
| 1.9 | Null Property | 21 |
| 1.10 | Column Constraints | 21 |
| 1.10.1 | Not Null Constraint | 22 |
| 1.10.2 | Column Check Constraint | 22 |
| 1.10.3 | Primary Key Column Constraint | 23 |
| 1.10.4 | Foreign Key Column Constraint | 24 |
| 1.10.5 | Unique Column Constraint | 25 |
| 1.11 | User-Defined Data Types | 25 |
| 1.12 | Free Space | 27 |
| 1.13 | Table Constraints | 27 |
| 1.13.1 | Table Check Constraint | 28 |
| 1.13.2 | Primary Key Table Constraint | 28 |
| 1.13.3 | Foreign Key Table Constraint | 29 |
| 1.13.4 | Unique Table Constraint | 30 |
| 1.14 | Remote Data Access | 31 |
| 1.14.1 | Create Server | 32 |
| 1.14.2 | Create Externlogin | 32 |
| 1.14.3 | Create Remote and Proxy Tables | 33 |
| 1.14.4 | Create Existing Table | 33 |
| 1.15 | Temporary Tables | 35 |
| 1.15.1 | Global Temporary Tables | 36 |
| 1.15.2 | Local Temporary Tables | 37 |
| 1.15.2.1 | Create Table #table_name | 37 |
| 1.15.2.2 | Declare Local Temporary Table | 38 |
| 1.15.2.3 | Select Into #table_name | 40 |
| 1.16 | Normalized Design | 40 |
| 1.16.1 | First Normal Form | 41 |
| 1.16.2 | Second Normal Form | 42 |
| 1.16.3 | Third Normal Form | 43 |
| 1.16.4 | Boyce-Codd Normal Form | 44 |
| 1.16.5 | Fourth Normal Form | 45 |
| 1.16.6 | Fifth Normal Form | 46 |
| 1.17 | Chapter Summary | 47 |
| Chapter 2 | Inserting | 49 |
| 2.1 | Introduction | 49 |
| 2.2 | Insert | 49 |
| 2.2.1 | Insert All Values | 50 |
| 2.2.2 | Insert Named Values | 52 |
| 2.2.3 | Insert Select All Columns | 53 |
| 2.2.4 | Insert Select Column List | 54 |
| 2.2.5 | Insert Select With Auto Name | 55 |
| 2.3 | Load Table | 56 |
| 2.4 | Isql Input | 64 |
| 2.5 | Chapter Summary | 68 |
| Chapter 3 | Selecting | 69 |
| 3.1 | Introduction | 69 |
| 3.2 | Logical Execution of a Select | 70 |
| 3.3 | From Clause | 80 |
| 3.4 | Join | 82 |
| 3.4.1 | Cross Join | 82 |
| 3.4.2 | Inner Join | 83 |
| 3.4.3 | Left Outer Join | 84 |
| 3.4.4 | Right Outer Join | 85 |
| 3.4.5 | Full Outer Join | 86 |
| 3.5 | Derived Tables | 87 |
| 3.6 | Multi-Table Joins | 90 |
| 3.7 | Select From Procedure Call | 96 |
| 3.8 | Lateral Procedure Call | 99 |
| 3.9 | Select List | 101 |
| 3.10 | Expressions and Operators | 102 |
| 3.10.1 | If and Case Expressions | 105 |
| 3.11 | Top 15 Scalar Built-in Functions | 107 |
| 3.12 | Boolean Expressions and the Where Clause | 113 |
| 3.12.1 | Comparison Predicates | 116 |
| 3.12.2 | Exists Predicates | 117 |
| 3.12.3 | In Predicates | 118 |
| 3.12.4 | Between Predicates | 119 |
| 3.12.5 | Null Test Predicates | 120 |
| 3.12.6 | Like Predicates | 120 |
| 3.12.7 | Trigger Predicates | 122 |
| 3.13 | Group by Clause | 123 |
| 3.14 | Aggregate Function Calls | 125 |
| 3.15 | Group by Rollup Clause | 131 |
| 3.16 | Having Clause | 134 |
| 3.17 | Order by Clause | 135 |
| 3.18 | Select Distinct | 137 |
| 3.19 | First and Top | 137 |
| 3.20 | Number(*) | 139 |
| 3.21 | Into Clause | 140 |
| 3.22 | Union, Except, and Intersect | 141 |
| 3.23 | Create View | 145 |
| 3.24 | With Clause | 148 |
| 3.24.1 | Recursive Union | 149 |
| 3.25 | Unload Table and Unload Select | 155 |
| 3.26 | Isql Output | 160 |
| 3.27 | Chapter Summary | 165 |
| Chapter 4 | Updating | 167 |
| 4.1 | Introduction | 167 |
| 4.2 | Single-Row Update | 167 |
| 4.3 | Multi-Row Update | 169 |
| 4.4 | Logical Execution of a Set Update | 169 |
| 4.4.1 | Set Update | 174 |
| 4.5 | Update Where Current of Cursor | 176 |
| 4.6 | Chapter Summary | 179 |
| Chapter 5 | Deleting | 181 |
| 5.1 | Introduction | 181 |
| 5.2 | Single-Row Delete | 181 |
| 5.3 | Multi-Row Delete | 182 |
| 5.4 | Logical Execution of a Set Delete | 183 |
| 5.4.1 | Set Delete | 185 |
| 5.5 | Delete Where Current of Cursor | 188 |
| 5.6 | Truncate Table | 190 |
| 5.7 | Chapter Summary | 194 |
| Chapter 6 | Fetching | 195 |
| 6.1 | Introduction | 195 |
| 6.2 | Cursor Fetch Loop | 195 |
| 6.2.1 | Declare Cursor for Select | 199 |
| 6.2.2 | Declare Cursor Using Select | 203 |
| 6.2.3 | Declare Cursor for Call | 204 |
| 6.2.4 | Open and Close Cursor | 206 |
| 6.2.5 | Fetch Cursor | 206 |
| 6.3 | Cursor For Loop | 207 |
| 6.4 | Chapter Summary | 210 |
| Chapter 7 | Synchronizing | 211 |
| 7.1 | Introduction | 211 |
| 7.2 | How MobiLink Works | 212 |
| 7.3 | The MobiLink ASA Client | 216 |
| 7.4 | MobiLink Client Setup | 217 |
| 7.4.1 | Create Publication | 217 |
| 7.4.2 | Create Synchronization User | 219 |
| 7.4.3 | Create Synchronization Subscription | 220 |
| 7.4.4 | The Dbmlsync Command Line | 221 |
| 7.4.5 | Syssync and Publication Defaults | 226 |
| 7.5 | The MobiLink Server | 228 |
| 7.6 | MobiLink Server Setup | 229 |
| 7.6.1 | Connection-Level Scripts | 229 |
| 7.6.2 | Session-Level Scripts | 230 |
| 7.6.3 | Table-Level Scripts | 232 |
| 7.6.4 | Row-Level Scripts | 233 |
| 7.6.4.1 | Handling Uploaded Inserts | 233 |
| 7.6.4.2 | Handling Uploaded Updates | 234 |
| 7.6.4.3 | Handling Uploaded Deletes | 235 |
| 7.6.4.4 | Handling Upload Conflicts | 236 |
| 7.6.4.5 | Handling Upload Errors | 240 |
| 7.6.4.6 | Downloading Inserts and Updates | 243 |
| 7.6.4.7 | Downloading Deletes | 246 |
| 7.6.4.8 | Handling Download Errors | 249 |
| 7.7 | The MobiLink System Tables | 254 |
| 7.8 | MobiLink Monitor | 258 |
| 7.9 | Chapter Summary | 260 |
| Chapter 8 | Packaging | 261 |
| 8.1 | Introduction | 261 |
| 8.2 | Begin Block | 261 |
| 8.3 | Exception Handler | 264 |
| 8.4 | Assignment Statement | 267 |
| 8.5 | IF Statement | 268 |
| 8.6 | Case Statement | 269 |
| 8.7 | While Loop | 270 |
| 8.8 | Execute Immediate | 271 |
| 8.9 | Create Procedure, Call, and Return | 274 |
| 8.10 | Create Function | 280 |
| 8.11 | Create Trigger | 284 |
| 8.12 | Create Event | 293 |
| 8.13 | Trigger Event | 301 |
| 8.14 | Create Variable | 304 |
| 8.15 | Chapter Summary | 305 |
| Chapter 9 | Protecting | 307 |
| 9.1 | Introduction | 307 |
| 9.2 | Options | 308 |
| 9.3 | Transactions | 313 |
| 9.4 | Savepoints and Substransactions | 320 |
| 9.5 | Error Handling | 324 |
| 9.5.1 | Signal and Resignal | 324 |
| 9.5.2 | Raiserror and Create Message | 327 |
| 9.5.3 | Rollback Trigger | 333 |
| 9.6 | Locks | 336 |
| 9.7 | Blocks and Isolation Levels | 339 |
| 9.7.1 | Isolation Level 0 | 340 |
| 9.7.2 | Isolation Level 1 | 343 |
| 9.7.3 | Isolation Level 2 | 346 |
| 9.7.4 | Isolation Level 3 | 349 |
| 9.8 | Deadlock | 355 |
| 9.9 | Mutexes | 359 |
| 9.10 | Grant Connect | 361 |
| 9.10.1 | Table Privileges | 364 |
| 9.10.2 | View Privileges | 367 |
| 9.10.3 | Execution Privileges | 369 |
| 9.10.4 | Special Privileges | 370 |
| 9.10.5 | Grant Group | 372 |
| 9.11 | Logging and Recovery | 375 |
| 9.12 | Backup | 379 |
| 9.12.1 | Full Offline Image Backup | 379 |
| 9.12.2 | Full Online Image Backup | 381 |
| 9.12.3 | Differential Online Log Backup | 384 |
| 9.12.4 | Incremental Online Log Backup | 385 |
| 9.12.5 | Live Log Backup | 386 |
| 9.13 | Restore | 387 |
| 9.14 | Validation | 393 |
| 9.15 | Chapter Summary | 396 |
| Chapter 10 | Tuning | 399 |
| 10.1 | Introduction | 399 |
| 10.2 | Request-Level Logging | 400 |
| 10.3 | Index Consultant | 408 |
| 10.4 | Execution Profiler | 413 |
| 10.5 | Graphical Plan | 416 |
| 10.6 | Fragmentation and Reorganization | 421 |
| 10.6.1 | File Fragmentation | 421 |
| 10.6.2 | Table Fragmentation | 423 |
| 10.6.3 | Table Reorganization | 428 |
| 10.6.4 | Index Fragmentation | 429 |
| 10.6.5 | Index Reorganization | 432 |
| 10.6.6 | Database Reorganization with Unload/Reload | 433 |
| 10.7 | Create Index | 437 |
| 10.8 | Database Performance Counters | 443 |
| 10.9 | Tips and Techniques | 446 |
| 10.10 | Chapter Summary | 453 |
| Index | 455 |
Anonymous
Posted Thu Jan 06 00:00:00 EST 2005
This book is simply a must-to-have for db-developers; it presents a complete and compact reference for experienced developers as well as it offers a comprehensive immersion in SQL Anywhere to beginners and less experienced ones. If I had to use only one word to express my opinion about this book I would just say: RELIEF. Relief of not getting drowned again in hundred of pages of irrelevant topics, misleading information, decorative pictures and tools descriptions. I highly recommend this book to application programmers, as myself, accustomed to flirting with different db-engines but still seeking a solid understanding relational db paradigms.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Overview
Companion CD includes SQL Anywhere Studio 9 Developer Edition (for Windows), all the code from the examples and full BNF from the book in HTML format!
SQL Anywhere Studio 9 Developer?s Guide describes in detail how to administer and program SQL Anywhere, the leading relational database for mobile, distributed, and embedded applications. This book focuses on the various tasks performed during the life cycle of a typical database, such as creating tables and other schema objects, ...