ODBC PowerPack 1.8.1 released

Introduction

This release brings tons of new features and bug fixes. We have added Spport for Salesforce Driver and All API Drivers. It fixes important issues with connectors for JDBC Bridge Driver, Salesforce Driver and Data Gateway. To see full release notes click here

New Features/Improvements

  • NEW: All Amazon, Azure, Secure FTP Drivers- Allow ConvErrorHandlingMode option to continue on type conversion / truncation errors
  • NEW: All API Drivers – Add support for CASE Statement in WHERE / GROUP BY Clause
  • NEW: All Drivers – Add Advanced Option TempStorageMode (Memory=0, Disk=1) and TempStorageDirectory to handle OutOfMemory Exception for large dataset result
  • NEW: All Drivers – Allow to change max rows setting more than 20000 rows in the Preview Tab
  • NEW: API Driver – Add support for Multi Key Lookup in SELECT statement (e.g. select * from OrderDetails where OrderId=100 and ItemId=22)
  • NEW: API Driver – Add Support for true | false keywords in INSERT / UPDATE statements
  • NEW: Data Gateway – Prompt to restart like datasources tab when user edit Users and dont restart
  • NEW: Data Gateway – Salesforce – List Tables in SSMS for Linked Server
  • NEW: Excel Driver – Choose Tab automatically with matching string in any Tab – New Property SearchStringForTabSelect
  • NEW: Excel Driver – Provide a way to read all Tabs with matching regex
  • NEW: General – New connection level property ConnectionStringFile property to load long connection string from file – Specifically addressing the 1024+ character limit with ConnectionString for Ado.net / OdbcConnection classes in programming (e.g. C#)
  • NEW: JDBC Bridge Driver – Add Option to force output on or off ( Prefix #Output-On or #Output-Off)
  • NEW: JDBC Bridge Driver – Provide options to enable retry certain errors (e.g. RetryCount, RetryDelay,RetryDelayMultiplier,RetryMessageRegex)
  • NEW: Salesforce Driver – Add support for AssignmentRuleId, UseDefaultAssignmentRule options in UPDATE, INSERT, UPSERT
  • NEW: Salesforce Driver – Add Support for Odbc SQLPrepareW (Fetch Meta without invoking Data Fetch)
  • NEW: Salesforce Driver – Increase default BatchSize from 200 to 2000 rows per batch to increase the read speed
  • NEW: Salesforce Driver – Provide a connection UI option – IncludeDeleted (Includes deleted rows in SELECT query)
  • NEW: Salesforce Driver – Update examples to indicate how to use FieldsToSetNullIfInputNull, AssignmentRuleId, UseDefaultAssignmentRule, AllOrNone, AllowFieldTruncation, AllowSaveOnDuplicates options

Bug fixes

  • FIX: All API Drivers – EXEC WebRequest might fail sometimes with SSL/TLS Error
  • FIX: All API Drivers – Failure in EXEC WebRequest does not show full error sometimes – It shows just one line error – Target Invocation error instead
  • FIX: All API Drivers – Long Text (i.e. DT_NTEXT) is detected as DT_WSTR(50) when you choose XML Connector Format to view metadata
  • FIX: All API Drivers – When you use ConvErrorHandlingMode option in WITH clause it says not supported
  • FIX: All Drivers – ConvErrorHandlingMode option not working correctly
  • FIX: API Driver – Create Virtual table with re-ordered required columns only and on preview it is loading data based on column index instead of the particular column data
  • FIX: API Driver – Created Custom Virtual Table is not listed in the drop down
  • FIX: API Driver – Only last statement executed when multiple SQL executed in a batch separated by semicolon (e.g. your-sql-stmt; your-sql-stmt ; your-sql-stmt)
  • FIX: API Driver – Qualified Key (tablename.keyname) in WHERE is not parsed as Primary Key (e.g. SELECT * FROM MyTable WHERE MyTable.Id=123)
  • FIX: API Driver – Quote around for column names with special characters (e.g. – , $, . )
  • FIX: API Driver – When WHERE clause not supplied DELETE / UPDATE doesnt do anything
  • FIX: API Driver – You may get OutOfMemory Exception for large dataset (Cache Options in WITH clause not working either)
  • FIX: Data Gateway – Empty Query might hang
  • FIX: Data Gateway – Enforce Database name in connection setting / throw better error when DB name is not set – Currently you see misleading error: Object reference not set
  • FIX: Data Gateway – In certain situation you will see CAL needed error (even previous connection failed) – For example connection from Machine-1 caused SSL/TLS secure channel… and later connect fromMachine-2
  • FIX: Data Gateway – Some registry key entries not cleaned up (under HKEY_CURRENT_USER\.DEFAULT\SOFTWARE\Classes\CLSID) if Service account has no write access to HKEY_CLASSES_ROOT
  • FIX: Data Gateway – SqlNative Client DSN UI doesnt show any entry for Database dropdown
  • FIX: JDBC Bridge Driver – After caller app is crashed java.exe is not released from memory (orphaned)
  • FIX: JDBC Bridge Driver – Catalog name listed many times in Power BI / Excel or some other Apps or Sometimes tables not listed
  • FIX: JDBC Bridge Driver – Files sitting in same folder as driver jar not detected (e.g. Infor *.ionapi file not found error)
  • FIX: JDBC Bridge Driver – Hide unwanted objects from preview tab (e.g. SYSTEM VIEWS, INDEX)
  • FIX: JDBC Bridge Driver – High CPU usage issue
  • FIX: JDBC Bridge Driver – Stored Proc doesnt return output
  • FIX: Salesforce Driver – Column [__id] wasn’t found – Error occurs if system columns used in the WITH Clause Output attribute for INSERT / UPDATE / DELETE / UPSERT
  • FIX: Salesforce Driver – ConcurrencyMode option in INSERT / UPDATE / UPSERT …. WITH clause throws error – Invalid cast from ‘System.String’ to ‘ZappySys.Crm.Salesforce.ConcurrencyMode’.
  • FIX: Salesforce Driver – EnableBulkMode=1 causes duplicate data for INSERT / UPSERT Operation when called via OPENQUERY in SQL Linked Server but if you use EXEC .. AT works fine
  • FIX: Salesforce Driver – Inner Exception not displayed in some cases (e.g. Proxy Issue)
  • FIX: Salesforce Driver – UPDATE / INSERT Static Value doesnt output column if you use Output=[columns list]
  • FIX: Salesforce Driver – UPDATE Not showing columns in the output if its not matching exact case as defined in Salesforce
  • FIX: Salesforce Driver – UPDATE with OUTPUT=[column list] doesnt work (it skips columns you like to output)
  • FIX: Salesforce Driver – When Where clause in DELETE / UPDATE returns no record then you might get error – Index was out of range
  • FIX: Salesforce Driver – When you use SOURCE Clause with Output=’Column1, Column2, …’ or Output=’*’ you always get lower case names in the output
  • FIX: Salesforce Driver – White space before DELETE / UPDATE causes error – Index and length must refer to a location within the string
  • FIX: Salesforce Driver – You may get OutOfMemory exception when many input rows supplied by SOURCE clause in UPDATE/DELETE/INSERT/UPSERT statement plus Output option supplied in WITH
  • FIX: Salesforce, API Driver – CRUD Operations with SOURCE clause may not fire operation if issued via ExecuteNonQuery Method
Posted in Announcements.