Transcription

Progress DataDirect forODBC DriversReferenceDecember 2020

Copyright 2021 Progress Software Corporation and/or one of its subsidiaries or affiliates. Allrights reserved. These materials and all Progress software products are copyrighted and all rights are reserved by ProgressSoftware Corporation. The information in these materials is subject to change without notice, and ProgressSoftware Corporation assumes no responsibility for any errors that may appear therein. The references inthese materials to specific platforms supported are subject to change.Chef, Chef (and design), Chef Infra, Code Can (and design), Compliance at Velocity, Corticon, DataDirect (anddesign), DataDirect Cloud, DataDirect Connect, DataDirect Connect64, DataDirect XML Converters, DataDirectXQuery, DataRPM, Defrag This, Deliver More Than Expected, DevReach (and design), Icenium, Inspec,Ipswitch, iMacros, Kendo UI, Kinvey, MessageWay, MOVEit, NativeChat, NativeScript, OpenEdge, Poweredby Chef, Powered by Progress, Progress, Progress Software Developers Network, SequeLink, Sitefinity (andDesign), Sitefinity, Sitefinity (and design), SpeedScript, Stylus Studio, Stylized Design (Arrow/3D Box logo),Styleized Design (C Chef logo), Stylized Design of Samurai, TeamPulse, Telerik, Telerik (and design), TestStudio, WebSpeed, WhatsConfigured, WhatsConnected, WhatsUp, and WS FTP are registered trademarksof Progress Software Corporation or one of its affiliates or subsidiaries in the U.S. and/or other countries.Analytics360, AppServer, BusinessEdge, Chef Automate, Chef Compliance, Chef Desktop, Chef Habitat, ChefWorkStation, Corticon.js, Corticon Rules, Data Access, DataDirect Autonomous REST Connector, DataDirectSpy, DevCraft, Fiddler, Fiddler Everywhere, FiddlerCap, FiddlerCore, FiddlerScript, Hybrid Data Pipeline, iMail,JustAssembly, JustDecompile, JustMock, KendoReact, NativeScript Sidekick, OpenAccess, PASOE, Pro2,ProDataSet, Progress Results, Progress Software, ProVision, PSE Pro, Push Jobs, SafeSpaceVR, SitefinityCloud, Sitefinity CMS, Sitefinity Digital Experience Cloud, Sitefinity Feather, Sitefinity Insight, Sitefinity Thunder,SmartBrowser, SmartComponent, SmartDataBrowser, SmartDataObjects, SmartDataView, SmartDialog,SmartFolder, SmartFrame, SmartObjects, SmartPanel, SmartQuery, SmartViewer, SmartWindow, Supermarket,SupportLink, Unite UX, and WebClient are trademarks or service marks of Progress Software Corporationand/or its subsidiaries or affiliates in the U.S. and other countries. Java is a registered trademark of Oracleand/or its affiliates. Any other marks contained herein may be trademarks of their respective owners.Updated: 2021/01/08Progress DataDirect for ODBC Drivers: Reference: Version November 20203

Copyright4Progress DataDirect for ODBC Drivers: Reference: Version November 2020

ContentsTable of ContentsWelcome to the Progress DataDirect for ODBC Drivers Reference .9What is ODBC?.9How does it work?.10Why do application developers need ODBC?.11Troubleshooting.13Diagnostic tools.13ODBC trace.13Test loading tool.17ODBC Test.17iODBC Demo and iODBC Test.18Logging for Java components.18The demoodbc Application.21The example application.22Enabling debug record mode.22Other tools.23Error messages.23Troubleshooting issues.25Setup/connection issues.25Interoperability issues.26Performance issues.28Failover.29Connection failover.30Extended connection failover.31Select connection failover.32Guidelines for primary and alternate servers.33Using client load balancing .34Using connection retry.34Summary of failover-related options.35A connection string example.36An odbc.ini file example.36Client information.39How databases store client information.40Storing client information.40Progress DataDirect for ODBC Drivers: Reference: Version November 20205

ContentsCode page values.43IANAAppCodePage values .43IBM to IANA code page values.48Teradata code page values.50ODBC API and scalar functions.51API functions.51Scalar functions.53String functions.55Numeric functions.57Date and time functions.58System functions.60Internationalization, localization, and Unicode.61Internationalization and nt.63Unicode character encoding.63Background.63Unicode support in databases.64Unicode support in ODBC.64Unicode and non-Unicode ODBC drivers.65Function calls.65Data.68Default Unicode mapping.69Driver Manager and Unicode encoding on UNIX/Linux.70References.71Character encoding in the odbc.ini and odbcinst.ini files.71Designing ODBC applications for performance optimization.73Using catalog functions.74Caching information to minimize the use of catalog functions.74Avoiding search patterns.75Using a dummy query to determine table characteristics.75Retrieving data.76Retrieving long data.76Reducing the size of data retrieved.76Using bound columns.77Using SQLExtendedFetch instead of SQLFetch.776Progress DataDirect for ODBC Drivers: Reference: Version November 2020

ContentsChoosing the right data type.78Selecting ODBC functions.78Using SQLPrepare/SQLExecute and SQLExecDirect.78Using arrays of parameters.79Using the cursor library.80Managing connections and updates.80Managing connections.80Managing commits in transactions.81Choosing the right transaction model.81Using positioned updates and deletes.81Using SQLSpecialColumns.81Using indexes.83Introduction.83Improving row selection performance.84Indexing multiple fields.84Deciding which indexes to create.85Improving join performance.86Locking and isolation levels.87Locking.87Isolation levels.88Locking modes and levels.89SSL encryption cipher suites.91DataDirect Bulk Load.97DataDirect Bulk Load functions.97Utility functions.98GetBulkDiagRec and GetBulkDiagRecW.98Export, validate, and load functions.100ExportTableToFile and ExportTableToFileW.100ValidateTableFromFile and ValidateTableFromFileW.103LoadTableFromFile and LoadTableFromFileW.105Using the TableName parameter with the Salesforce driver.108SetBulkOperation (Salesforce driver only).110GetBulkOperation (Salesforce driver only) .111DataDirect Bulk Load statement attributes.113SQL BULK EXPORT PARAMS.113SQL BULK EXPORT.113Progress DataDirect for ODBC Drivers: Reference: Version November 20207

ContentsDataDirect connection pooling.115Creating a connection pool.116Adding connections to a pool.116Removing connections from a pool.116Handling dead connections in a pool.117Connection pool statistics.118Summary of pooling-related options.118Threading.119WorkAround options.1218Progress DataDirect for ODBC Drivers: Reference: Version November 2020

1Welcome to the Progress DataDirect forODBC Drivers ReferenceThis guide provides general information for Progress DataDirect for ODBC drivers, including troubleshootingtips, descriptions of advanced features, and optimizing ODBC applications. The content of this guide appliesto all ODBC drivers unless otherwise noted.The reference acts as a compliment to the driver user's guides, which provide detailed instructions on configuringand using drivers. For complete driver documentation sets, visit the Progress Documentation .html.Note: This reference refers the reader to Web pages using URLs for more information about specific topics,including Web URLs not maintained by Progress DataDirect. Because it is the nature of Web content to changefrequently, Progress DataDirect can guarantee only that the URLs in this reference were correct at the time ofpublishing.For details, see the following topics: What is ODBC?What is ODBC?The Open Database Connectivity (ODBC) interface by Microsoft allows applications to access data in databasemanagement systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximuminteroperability, which means a single application can access different DBMS. Application end users can thenadd ODBC database drivers to link the application to their choice of DBMS.Progress DataDirect for ODBC Drivers: Reference: Version November 20209

Chapter 1: Welcome to the Progress DataDirect for ODBC Drivers ReferenceThe ODBC interface defines: A library of ODBC function calls of two types: Extended functions that support additional functionality, including scrollable cursors Core functions that are based on the X/Open and SQL Access Group Call Level Interface specification SQL syntax based on the X/Open and SQL Access Group SQL CAE specification (1992) A standard set of error codes A standard way to connect and logon to a DBMS A standard representation for data typesThe ODBC solution for accessing data led to ODBC database drivers, which are dynamic-link libraries onWindows and shared objects on UNIX and Linux. These drivers allow an application to gain access to one ormore data sources. ODBC provides a standard interface to allow application developers and vendors of databasedrivers to exchange data between applications and data sources.How does it work?The ODBC architecture has four components: An application, which processes and calls ODBC functions to submit SQL statements and retrieve results A Driver Manager, which loads drivers for the application A driver, which processes ODBC function calls, submits SQL requests to a specific data source, and returnsresults to the application A data source, which consists of the data to access and its associated operating system, DBMS, and networkplatform (if any) used to access the DBMSThe following figure shows the relationship among the four components:10Progress DataDirect for ODBC Drivers: Reference: Version November 2020

What is ODBC?Why do application developers need ODBC?Using ODBC, you, as an application developer can develop, compile, and ship an application without targetinga specific DBMS. In this scenario, you do not need to use embedded SQL; therefore, you do not need torecompile the application for each new environment.Progress DataDirect for ODBC Drivers: Reference: Version November 202011

Chapter 1: Welcome to the Progress DataDirect for ODBC Drivers Reference12Progress DataDirect for ODBC Drivers: Reference: Version November 2020

2TroubleshootingThis part guides you through troubleshooting Progress DataDirect for ODBC drivers. It provides you withsolutions to common problems and documents error messages that you may receive.For details, see the following topics: Diagnostic tools Error messages Troubleshooting issuesDiagnostic toolsThis chapter discusses the diagnostic tools you use when configuring and troubleshooting your ODBCenvironment.ODBC traceODBC tracing allows you to trace calls to ODBC drivers and create a log of the traces.Creating a trace LogCreating a trace log is particularly useful when you are troubleshooting an issue.To create a trace log:Progress DataDirect for ODBC Drivers: Reference: Version November 202013

Chapter 2: Troubleshooting1. Enable tracing (see "Enabling tracing" for more information).2. Start the ODBC application and reproduce the issue.3. Stop the application and turn off tracing.4. Open the log file in a text editor and review the output to help you debug the problem.For a complete explanation of tracing, refer to the following Progress DataDirect Knowledgebase s/Article/3049See alsoEnabling tracing on page 14Enabling tracingProgress DataDirect provides a tracing library that is enhanced to operate more efficiently, especially inproduction environments, where log files can rapidly grow in size. The DataDirect tracing library allows you tocontrol the size and number of log files.On Windows, you can enable tracing through the Tracing tab of the ODBC Data Source Administrator.On UNIX and Linux, you can enable tracing by directly modifying the [ODBC] section in the system information(odbc.ini) file.On macOS, you can also enable tracing through the Tracing tab of the iODBC Data Source Administrator.Windows ODBC AdministratorOn Windows, open the ODBC Data Source Administrator and select the Tracing tab. To specify the path andname of the trace log file, type the path and name in the Log File Path field or click Browse to select a log file.If no location is specified, the trace log resides in the working directory of the application you are using.Click Select DLL in the Custom Trace DLL pane to select the DataDirect enhanced tracing library,xxtrcyy.dll, where xx represents either iv (32-bit version) or dd (64-bit version), and yy represents thedriver level number, for example, ivtrc28.dll. The library is installed in the \Windows\System32 directory.After making changes on the Tracing tab, click Apply for them to take effect.Enable tracing by clicking Start Tracing Now. Tracing continues until you disable it by clicking Stop TracingNow. Be sure to turn off tracing when you are finished reproducing the issue because tracing decreases theperformance of your ODBC application.When tracing is enabled, information is written to the following trace log files: Trace log file (trace filename.log) in the specified directory. Trace information log file (trace filenameINFO.log). This file is created in the same directory as thetrace log file and logs the following SQLGetInfo information: SQL DBMS NAME SQL DBMS VER SQL DRIVER NAME SQL DRIVER VER SQL DEFAULT TXN ISOLATION14Progress DataDirect for ODBC Drivers: Reference: Version November 2020

Diagnostic toolsThe DataDirect enhanced tracing library allows you to control the size and number of log files. The file sizelimit of the log file (in KB) is specified by the Windows Registry key ODBCTraceMaxFileSize. Once the sizelimit is reached, a new log file is created and logging continues in the new file until it reaches its file size limit,after which another log file is created, and so on.The maximum number of files that can be created is specified by the Registry key ODBCTraceMaxNumFiles.Once the maximum number of log files is created, tracing reopens the first file in the sequence, deletes thecontent, and continues logging in that file until the file size limit is reached, after which it repeats the processwith the next file in the sequence. Subsequent files are named by appending sequential numbers, starting at1 and incrementing by 1, to the end of the original file name, for example, SQL1.LOG, SQL2.LOG, and so on.The default values of ODBCTraceMaxFileSize and ODBCTraceMaxNumFiles are 102400 KB and 10,respectively. To change these values, add or modify the keys in the following Windows Registry section:[HKEY CURRENT USER\SOFTWARE\ODBC\ODBC.INI\ODBC]Warning: Do not edit the Registry unless you are an experienced user. Consult your system administrator ifyou have not edited the Registry before.Edit each key using your values and close the Registry.System information (odbc.ini) fileThe [ODBC] section of the system information file includes several keywords that control tracing:Trace [0 1]TraceFile trace filenameTraceDll ODBCHOME/lib/xxtrcyy.zzODBCTraceMaxFileSize file sizeODBCTraceMaxNumFiles file numberTraceOptions 0where:Trace [0 1]Allows you to enable tracing by setting the value of Trace to 1. Disable tracing by setting the valueto 0 (the default). Tracing continues until you disable it. Be sure to turn off tracing when you arefinished reproducing the issue because tracing decreases the performance of your ODBC application.TraceFile trace filenameSpecifies the path and name of the trace log file. If no path is specified, the trace log resides in theworking directory of the application you are using.TraceDll ODBCHOME/lib/xxtrcyy.zzSpecifies the library to use for tracing. The driver installation includes a DataDirect enhanced libraryto perform tracing, xxtrcyy.zz, where xx represents either iv (32-bit version) or dd (64-bit version),yy represents the driver level number, and zz represents either so or sl. For example, ivtrc28.sois the 32-bit version of the library. To use a custom shared library instead, enter the path and nameof the library as the value for the TraceDll keyword.The DataDirect enhanced tracing library allows you to control the size and number of log files withthe ODBCTraceMaxFileSize and ODBCTraceMaxNumFiles keywords.Progress DataDirect for ODBC Drivers: Reference: Version November 202015

Chapter 2: TroubleshootingODBCTraceMaxFileSize file sizeThe ODBCTraceMaxFileSize keyword specifies the file size limit (in KB) of the log file. Once this filesize limit is reached, a new log file is created and logging continues in the new file until it reachesthe file size limit, after which another log file is created, and so on. The default is 102400.ODBCTraceMaxNumFiles file numberThe ODBCTraceMaxNumFiles keyword specifies the maximum number of log files that can becreated. The default is 10. Once the maximum number of log files is created, tracing reopens thefirst file in the sequence, deletes the content, and continues logging in that file until the file size limitis reached, after which it repeats the process with the next file in the sequence. Subsequent filesare named by appending sequential numbers, starting at 1 and incrementing by 1, to the end of theoriginal file name, for example, odbctrace1.out, odbctrace2.out, and so on.TraceOptions [0 1 2 3]The ODBCTraceOptions keyword specifies whether to print the current timestamp, parent processID, process ID, and thread ID for all ODBC functions to the output file. The default is 0. If set to 0, the driver uses standard ODBC tracing. If set to 1, the log file includes a timestamp on ENTRY and EXIT of each ODBC function. If set to 2, the log file prints a header on every line. By default, the header includes the parentprocess ID and process ID. If set to 3, both TraceOptions 1 and TraceOptions 2 are enabled. The header includes atimestamp as well as a parent process ID and process ID.ExampleIn the following example of trace settings, tracing has been enabled, the name of the log file isodbctrace.out, the library for tracing is ivtrc28.so, the maximum size of the log file is 51200KB, and the maximum number of log files is 8. Timestamp and other information is included inodbctrace.out.Trace 1TraceFile ODBCH

Jan 8, 2021