Progress DataDirect ® ® for for Apache JDBC™ Cassandra ™ User's Guide 6.0.0 Release Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved. ThesematerialsandallProgress®softwareproductsarecopyrightedandallrightsarereservedbyProgress SoftwareCorporation.Theinformationinthesematerialsissubjecttochangewithoutnotice,andProgress SoftwareCorporationassumesnoresponsibilityforanyerrorsthatmayappeartherein.Thereferencesinthese materialstospecificplatformssupportedaresubjecttochange. Corticon,DataDirect(anddesign),DataDirectCloud,DataDirectConnect,DataDirectConnect64,DataDirect XMLConverters,DataDirectXQuery,DataRPM,DeliverMoreThanExpected,Icenium,KendoUI,NativeScript, OpenEdge,PoweredbyProgress,Progress,ProgressSoftwareDevelopersNetwork,Rollbase,SequeLink, Sitefinity(andDesign),SpeedScript,StylusStudio,TeamPulse,Telerik,Telerik(andDesign),TestStudio,and WebSpeedareregisteredtrademarksofProgressSoftwareCorporationoroneofitsaffiliatesorsubsidiaries intheU.S.and/orothercountries.Analytics360,AppServer,BusinessEdge,DataDirectSpy,SupportLink, DevCraft,Fiddler,JustAssembly,JustDecompile,JustMock,Kinvey,NativeScriptSidekick,OpenAccess, ProDataSet,ProgressResults,ProgressSoftware,ProVision,PSEPro,Sitefinity,SmartBrowser, SmartComponent,SmartDataBrowser,SmartDataObjects,SmartDataView,SmartDialog,SmartFolder, SmartFrame,SmartObjects,SmartPanel,SmartQuery,SmartViewer,SmartWindow,andWebClientare trademarksorservicemarksofProgressSoftwareCorporationand/oritssubsidiariesoraffiliatesintheU.S. andothercountries.JavaisaregisteredtrademarkofOracleand/oritsaffiliates.Anyothermarkscontained hereinmaybetrademarksoftheirrespectiveowners. PleaserefertothereadmeapplicabletotheparticularProgressproductreleaseforanythird-party acknowledgementsrequiredtobeprovidedinthedocumentationassociatedwiththeProgressproduct. Updated:2018/03/28 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 3 Copyright 4 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 Contents Table of Contents Chapter 1: Welcome to the Progress DataDirect for JDBC for Apache Cassandra Driver......................................................................................11 What's New in this Release?................................................................................................................12 Requirements........................................................................................................................................13 DriverandDataSourceClasses...........................................................................................................13 VersionStringInformation.....................................................................................................................14 Connection Properties..........................................................................................................................14 Complex Type Normalization................................................................................................................15 Collection Types.........................................................................................................................15 TupleandUser-DefinedTypes...................................................................................................18 Nested Complex Types..............................................................................................................21 Data Types............................................................................................................................................22 getTypeInfo()..............................................................................................................................24 Contacting Technical Support...............................................................................................................32 Chapter 2: Getting started...........................................................................35 DriverandDataSourceClasses...........................................................................................................35 Connecting Using the DriverManager...................................................................................................36 Setting the Classpath ................................................................................................................36 Passing the Connection URL.....................................................................................................37 TestingtheConnection...............................................................................................................37 Connecting Using Data Sources...........................................................................................................40 HowDataSourcesAreImplemented.........................................................................................41 Creating Data Sources...............................................................................................................41 CallingaDataSourceinanApplication.....................................................................................41 Chapter 3: Using the Driver.........................................................................43 Connecting from an Application............................................................................................................44 DriverandDataSourceClasses................................................................................................44 Connecting Using the DriverManager........................................................................................44 Connecting Using Data Sources................................................................................................48 Using Connection Properties................................................................................................................50 Required Properties...................................................................................................................50 Mapping Properties....................................................................................................................51 Authentication Properties...........................................................................................................51 Data Encryption Properties........................................................................................................52 Statement Pooling Properties....................................................................................................53 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 5 Contents Additional Properties..................................................................................................................54 Performance Considerations................................................................................................................55 Authentication.......................................................................................................................................56 Configuring User ID/Password Authentication...........................................................................56 ConfiguringtheDriverforKerberosAuthentication....................................................................57 Data Encryption....................................................................................................................................64 Configuring SSL Encryption.......................................................................................................64 ConfiguringSSLServerAuthentication......................................................................................65 Configuring SSL Client Authentication.......................................................................................66 Identifiers...............................................................................................................................................67 IP Addresses.........................................................................................................................................67 ParameterMetadataSupport................................................................................................................68 Insert and Update Statements...................................................................................................68 Select Statements......................................................................................................................68 Isolation Levels.....................................................................................................................................69 Unicode.................................................................................................................................................69 Error Handling.......................................................................................................................................69 Large Object (LOB) Support.................................................................................................................70 Rowset Support....................................................................................................................................70 Executing CQL......................................................................................................................................71 Connection Pool Manager....................................................................................................................71 HowConnectionPoolingWorks.................................................................................................71 Implementing DataDirect Connection Pooling...........................................................................73 Configuring the Connection Pool...............................................................................................76 Connecting Using a Connection Pool........................................................................................77 Closing the Connection Pool......................................................................................................78 CheckingthePoolManagerVersion..........................................................................................79 Enabling Pool Manager Tracing.................................................................................................79 ConnectionPool Manager Interfaces.........................................................................................79 Statement Pool Monitor........................................................................................................................84 UsingDataDirect-SpecificMethodstoAccesstheStatementPoolMonitor..............................85 UsingJMXtoAccesstheStatementPoolMonitor.....................................................................87 ImportingStatementsintoaStatementPool..............................................................................89 Clearing All Statements in a Statement Pool.............................................................................89 Freezing and Unfreezing the Statement Pool............................................................................90 GeneratingaStatementPoolExportFile...................................................................................90 DataDirectStatementPoolMonitorInterfacesandClasses......................................................90 DataDirectTest......................................................................................................................................92 DataDirect Test Tutorial..............................................................................................................93 Tracking JDBC Calls with DataDirect Spy..........................................................................................125 Enabling DataDirect Spy..........................................................................................................125 Chapter 4: Connection Property Descriptions........................................129 AuthenticationMethod.........................................................................................................................132 6 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 Contents CreateMap..........................................................................................................................................133 EncryptionMethod...............................................................................................................................134 FetchSize............................................................................................................................................135 HostNameInCertificate........................................................................................................................136 ImportStatementPool..........................................................................................................................137 InitializationString................................................................................................................................138 InsensitiveResultSetBufferSize...........................................................................................................139 KeyPassword......................................................................................................................................140 KeyspaceName...................................................................................................................................140 KeyStore.............................................................................................................................................141 KeyStorePassword.............................................................................................................................142 LogConfigFile......................................................................................................................................143 LoginConfigName...............................................................................................................................143 LoginTimeout......................................................................................................................................144 MaxPooledStatements........................................................................................................................145 NativeFetchSize..................................................................................................................................146 Password............................................................................................................................................147 PortNumber.........................................................................................................................................148 ReadConsistency................................................................................................................................149 ReadOnly............................................................................................................................................150 RegisterStatementPoolMonitorMBean................................................................................................150 ResultMemorySize..............................................................................................................................151 SchemaMap........................................................................................................................................153 ServerName........................................................................................................................................154 ServicePrincipalName.........................................................................................................................155 SpyAttributes.......................................................................................................................................156 TransactionMode................................................................................................................................157 TrustStore...........................................................................................................................................158 TrustStorePassword............................................................................................................................159 User.....................................................................................................................................................159 ValidateServerCertificate.....................................................................................................................160 WriteConsistency................................................................................................................................161 Chapter 5: Troubleshooting......................................................................163 Troubleshooting your application........................................................................................................163 TurningOnandOffDataDirectSpyLogging............................................................................164 DataDirect Spy Log Example...................................................................................................164 Troubleshooting Connection Pooling..................................................................................................166 Enabling Tracing with the setTracing Method..........................................................................166 Pool Manager Trace File Example...........................................................................................166 Troubleshooting Statement Pooling....................................................................................................170 Generating an Export File with the exportStatement Method..................................................170 Statement Pool Export File Example.......................................................................................170 Troubleshooting Out-of-Memory Errors..............................................................................................171 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 7 Contents Troubleshooting Operation Timeouts..................................................................................................171 UsingJavalogging..............................................................................................................................172 Logging Components...............................................................................................................173 Configuring Logging.................................................................................................................174 Contacting Technical Support.............................................................................................................175 Chapter 6: Supported SQL Functionality.................................................177 Data Definition Language (DDL).........................................................................................................177 Delete..................................................................................................................................................178 Insert...................................................................................................................................................179 Refresh Map (EXT).............................................................................................................................180 Select..................................................................................................................................................180 Select Clause...........................................................................................................................181 Update.................................................................................................................................................191 SQL Expressions................................................................................................................................193 Column Names........................................................................................................................193 Literals......................................................................................................................................193 Operators.................................................................................................................................196 Functions..................................................................................................................................199 Conditions................................................................................................................................199 Subqueries..........................................................................................................................................200 IN Predicate.............................................................................................................................201 EXISTS Predicate....................................................................................................................201 UNIQUE Predicate...................................................................................................................201 Correlated Subqueries.............................................................................................................202 Chapter 7: SQL Escape Sequences for JDBC.........................................205 Date, time, and timestampescape sequences...................................................................................206 Scalar Functions.................................................................................................................................206 Outer Join Escape Sequences...........................................................................................................207 LIKEescapecharactersequenceforwildcards..................................................................................208 Native and Refresh Escape Sequences.............................................................................................208 Chapter 8: JDBC support..........................................................................211 JDBC and JVM Compatibility..............................................................................................................211 Supported Functionality......................................................................................................................211 Array.........................................................................................................................................212 Blob..........................................................................................................................................212 CallableStatement....................................................................................................................213 Clob..........................................................................................................................................225 Connection...............................................................................................................................226 ConnectionEventListener.........................................................................................................231 ConnectionPoolDataSource.....................................................................................................231 8 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 Contents DatabaseMetaData..................................................................................................................232 DataSource..............................................................................................................................240 Driver........................................................................................................................................241 ParameterMetaData.................................................................................................................241 PooledConnection....................................................................................................................242 PreparedStatement..................................................................................................................243 Ref............................................................................................................................................248 ResultSet..................................................................................................................................248 ResultSetMetaData..................................................................................................................259 RowSet.....................................................................................................................................260 SavePoint.................................................................................................................................260 Statement.................................................................................................................................260 StatementEventListener...........................................................................................................264 Struct........................................................................................................................................264 XAConnection..........................................................................................................................265 XADataSource.........................................................................................................................265 XAResource.............................................................................................................................265 Chapter 9: JDBC Extensions.....................................................................267 Using JDBC Wrapper Methods to Access JDBC Extensions.............................................................268 DatabaseMetaData interface..............................................................................................................268 DDBulkLoad Interface.........................................................................................................................269 ExtConnection Interface......................................................................................................................276 ExtDatabaseMetaData Interface.........................................................................................................281 ExtLogControl class............................................................................................................................281 Chapter 10: Designing JDBC Applications for Performance Optimization............................................................................................283 UsingDatabaseMetadataMethods....................................................................................................284 MinimizingtheUseofDatabaseMetadataMethods................................................................284 Avoiding Search Patterns.........................................................................................................285 UsingaDummyQuerytoDetermineTableCharacteristics.....................................................285 Returning Data....................................................................................................................................286 Returning Long Data................................................................................................................286 ReducingtheSizeofReturnedData........................................................................................287 Choosing the Right Data Type.................................................................................................287 RetrievingResultSets..............................................................................................................287 SelectingJDBCObjectsandMethods...............................................................................................288 UsingParameterMarkersasArgumentstoStoredProcedures..............................................288 UsingtheStatementObjectInsteadofthePreparedStatementObject...................................288 Using Batches Instead of Prepared Statements......................................................................289 Choosing the Right Cursor.......................................................................................................290 Using get Methods Effectively..................................................................................................290 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0 9 Contents RetrievingAutoGeneratedKeys..............................................................................................291 Managing Connections and Updates..................................................................................................291 Managing Connections............................................................................................................292 Managing Commits in Transactions.........................................................................................292 Choosing the Right Transaction Model....................................................................................293 Using updateXXX Methods......................................................................................................293 Using getBestRowIdentifier......................................................................................................293 Glossary.......................................................................................................295 Index.............................................................................................................299 10 Progress®DataDirect®forJDBC™forApacheCassandra™:User'sGuide:Version6.0.0