Recently active topics

51 Topics

Hesjona HEmployee

published in Editions

“Unable to translate bytes [...] at index 0 from specified code page to Unicode.” error in .NET Core applications

In .NET Core applications, when handling data with a character set that is not bundled with .NET Standard, you may come across the following error message: “Unable to translate bytes [..] at index x from specified code page to Unicode”. To resolve this issue, you will need to install the System.Text.Encoding.CodePages dependency Install-Package System.Text.Encoding.CodePages , and add the following line of code to your application at the start of your code: System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); In the following example, I have used the CData ADO.NET Provider for Amazon Marketplace to create a connection to our Japanese Amazon Marketplace account in Entity Framework Core 6.0 (please consult the “Getting Started with EFCore” section of the driver’s documentation: https://cdn.cdata.com/help/ONJ/ado/pg_efCoreOverview.htm ). The API’s charset is charset=Windows-31J: This character encoding isn’t supported by default in .NET Core, hence

ReiMEmployee

published in Data Sources

Connecting to Netsuite Sandbox accounts using our CData drivers

The 'AccountID' connection property serves as the distinguishing factor between NetSuite production accounts and sandbox accounts.  “AccountID”, indicates the company account your username is associated with on NetSuite.  Note: You can find your NetSuite account ID at the beginning of the NetSuite URL. For example, if the URL is https://1234567.app.netsuite.com/, your account ID is 1234567.  Sandbox account IDs are always the same as production account IDs, with ‘_SB1’, ‘_SB2’, ‘_SB3’, and so forth, added to the end:  Production Account Id  345678  Sandbox Account Id (1)  345678_SB1  Sandbox Account Id (2)  345678_SB2  Sandbox Account Id (3)  345678_SB3    When connecting to NetSuite sandbox account, make sure to configure the appropriate account ID "AccountID={productionAccountId}_SB1".  For additional details on how to establish a connection to NetSuite, please consult our driver's documentation using the follow

RajeshApprentice

asked in CData Sync

Error adding task to job

I’m attempting to add a task to an existing job and get an error:“The job <job-name> contains an unlicensed connection: Only 10 tasks are allowed in the Job” I also tried creating a job from this existing job and got an error:The connection [Salesforce_abc] is unlicensed: Unlicensed connections cannot be shared across multiple jobs. We have an Enterprise license, and my job is using SalesForce as a source and Azure SQL as a destination. I tried adding a task to a different job that uses Salesforce as source and Azure SQL as destination, and it worked.

quangdutran809Collaborator

asked in CData Drivers & Connectors

AirTable JDBC driver could not detect integer, long (biginteger)

I am using AirTable JDBC driver and getting metadata for tables, but notice that all numeric column in AirTable will be detected as FLOATFor example, these two numbers are integer and long but if I get column type from metadata, all of them are FLOAT with size 8: DatabaseMetaData databaseMetaData = conn.getMetaData(); Is this intentional?

Ismete MEmployee

published in Editions

DBAmp: “MISSING_ARGUMENT:(External ID field name) not specified:--" error when performing Upsert operation with SF_TableLoader using an External ID

This error is thrown by Salesforce itself when attempting to Upsert an input table that does not always contain a value for the External ID field(in this case External_ID__c as shown in the example below). When performing Upsert with SF_TableLoader, it is necessary for the External ID field to contain a valid value. In this case, it should not be empty. If any of the records contains an empty value, the BULK API cannot decide whether that specific record needs to be updated or inserted. The external ID needs to be specified, as the error itself states. API reference on how the upsert operation works: (https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/bulk_api_2_0_upsert.htm?_ga=2.136492534.793560252.1693256725-1249321776.1673529617) Example query executed that reproduces the issue: EXEC SF_TableLoader 'Upsert:BULKAPI, Parallel, Batchsize(500)', 'SALESFORCE','Account_Upsert','External_ID__c' Account_Upsert input table example: Account_Upsert_Result table:

rocketmaniacApprentice

asked in CData Drivers & Connectors

UpdateCommand.Parameters have wrong value in ADO.NET Provider for Presto

Following the example code for Update, we have this against one of our Hive transactional datastores: PrestoDataAdapter dataAdapter = new PrestoDataAdapter("SELECT _id, name FROM hive.transactional_schema.[TableName]", connection); dataAdapter.UpdateCommand = new PrestoCommand( "UPDATE hive.transactional_schema.[TableName] SET name = @name " + "WHERE _id = @_id", connection); dataAdapter.UpdateCommand.Parameters.Add(new PrestoParameter("@name", "name", DbType.String)); dataAdapter.UpdateCommand.Parameters.Add(new PrestoParameter("@_id", "_id", DbType.String)); dataAdapter.UpdateCommand.Parameters[1].SourceVersion = DataRowVersion.Original; DataTable table = new DataTable(); dataAdapter.Fill(table); DataRow firstrow = table.Rows[0]; firstrow["name"] = firstrow["name"] + "-Updated"; dataAdapter.Update(table);firstrow shows the updated name property, but the query that gets sent to Trino is:UPDATE "hive"."transactional_schema".[TableName] SET "name" = 'name' WHERE "_id" = '_id'so I gu

quangdutran809Collaborator

asked in CData Drivers & Connectors

SQL Analysis Services JDBC Driver detects all column as VARCHAR

Hi Cdata folks,Cdata driver latest version v23Weird thing is that SQL Analysis Services JDBC Driver detects all column as VARCHAR no matter what type of the model column: This is the model in Visual Studio: Before importing, the data type is also detected correctly in Visual Studio: But in meta data, type for all columns is VARCHARThis is how I get the metadata:Connection conn = DriverManager.getConnection("jdbc:ssas:AuthScheme=BASIC;User=user;Password=pwd;URL=http://HOST/OLAP/msmdpump.dll;");Statement stat = conn.createStatement();DatabaseMetaData databaseMetaData = conn.getMetaData(); try(ResultSet columns = databaseMetaData.getColumns("TabularProject3","Model","Table","integer")){ while(columns.next()) { System.out.println(columns.getString("COLUMN_NAME")); System.out.println(columns.getString("DATA_TYPE")); System.out.println(columns.getString("COLUMN_SIZE")); System.out.println(columns.getString("TYPE_

Charlie SEmployee

posted in CData Arc

Using OAuth 2.0 in the Email Send and Receive Connectors

OAuth 2.0 Overview In today’s standards, OAuth 2.0 is becoming increasingly popular as a means to authorize users who are attempting to access a resource, be it a mail server, API endpoint or database. A common example of this shift is the deprecation of basic authentication in Exchange Online (Microsoft’s mail server for Outlook), which can be read about in more detail in Microsoft’s article, here.OAuth 2.0 is an authorization protocol and not an authentication protocol; a means of granting users access to a set of resources - in the case of what is discussed here, that resource would be a mail server.At a very high level, OAuth 2.0 can be summarized in the following steps: The connecting client acquires both a client ID and a client secret from the authorization server. The client requests to be authorized by the authorization server by providing their client ID and client secret. Additionally, any necessary scopes and a redirect URI are also provided. If the supplied credentials

ReiMEmployee

published in Editions

“BatchSize” in SSIS

In general, it's important to keep in mind that when using our CData drivers alongside various tools, one must consider the configurations and properties of these tools, as they can potentially override drivers' configurations.  The "BatchSize" connection property in our drivers refers to the number of records committed as a batch to the server during INSERT/UPDATE/DELETE operations (for data sources that support batches).  When configuring this property, it's important to consider the value of the “BatchSize” within the SSIS component itself. You can find this property in the 'Advanced Editor' window of the destination component. Follow these steps to access and modify it:  In the SSIS Data Flow task, right-click on the “CData {datasource} Destination” component;  Click on the option labeled “Show Advanced Editor...”;  In the “Advanced Editor” window click on the “Component Properties” tab;  In the “Custom Properties” section, you will find the “BatchSize” property listed; 

OP_TesterApprentice

asked in CData Drivers & Connectors

Python connector for google drive errors out when selecting from one of the system tables

It is failing with an error that is not very descriptive when a query like SELECT * FROM sys_connection_hierarchy is executed in PythonThe same query works fine in JDBC. Can you please advise?

Andrew HEmployee

published in Editions

Common issues with PBI Report Server, PBI Report Builder, Microsoft Report Builder

## Common issues with PBI Report Server, PBI Report Builder, Microsoft Report Builder. Notice: The veracity of this article is subject to change based on Microsoft’s implementation of their tools (Last Updated: September 26, 2023). ## PBI Report Server Error Message- “Cannot create a connection to data source ‘CData’.” Possible Fixes: ~1. Bitness Mismatch~ Bitness of PBI Report Server: https://learn.microsoft.com/en-us/power-bi/report-server/system-requirements#:~:text=Installation%20of%20Power%20BI%20Report%20Server%20is%20supported%20on%20x64%20processors%20only Bitness of Microsoft Report Builder: This error often occurs due to a bit-ness issue with the DSN you are using. For instance, if Microsoft Report Builder was used to publish or create the PBI file, since it only accepts 32-bit drivers, that data source will fail to work once the file is published or imported into PBI Report Server. If you want to publish or import a PBI file to PBI Report Server, the easiest way to get a

russell-jerseypostInfluencer

asked in CData Arc

MySQL Connector: Custom Query and Message Headers

Hi I have a complex MySQL query with several JOINs need to only fetch new data from the tables. I have read https://cdn.cdata.com/help/AZH/mft/Database-AC.html#only-process-new-or-changed-records but not sure how to apply this to a Custom Query, Can custom queries us data from the message header? If so I may be able to use the method James suggested at

MattMApprentice

asked in CData Arc

CData Arc - Mapping CSV with parent-child rows to flat CSV format

Hi all, I’m trying to create a mapping to translate a CSV format with parent-child rows into a flat CSV format. My source file is structured like this:Row Type Order number Customer name Total Cost Header 12345 John Smith $15.00 Row Type Item code Item name Cost Line 40123 5mm cable $5.00 Line 40124 10mm cable $10.00 Row Type Order number Customer name Total Cost Header 12346 Jane Lane $10.00 Row Type Item code Item name Cost Line 40124 10mm cable $10.00 Note that the actual file does not have headings, so looks like this:Header 12345 John Smith $15.00 Line 40123 5mm cable $5.00 Line 40124 10mm cable $10.00 Header 12346 Jane Lane $10.00 Line 40124 10mm cable $10.00 I am wanting to convert this to a flat CSV structure that combines data from the parent header and child line rows. So the above would be translated to:Order number Customer name Item code Item name Cost 12345 John

Matt SEmployee

posted in CData Arc

Lookups in CData Arc

What are Lookups?Lookups describe a situation where you need to find a target piece of data, like an Account ID, but you only have a related public piece of data, like an email domain. You can reference some data store, like a database or SaaS platform, to “look up” the Account ID based on the email domain. If you’ve ever “looked up” the definition of a word in a dictionary, then you already understand the concept -- the spelling of the word is your starting public data (equivalent to the email domain), the definition of the word is the target data (equivalent to the Account ID), and the dictionary itself is the data store.Why are Lookups useful?Lookups solve critical problems that may arise when integrating external data (like EDI documents) into internal systems (like CRM or ERP platforms). When you integrate data into an ERP system, you may need to reference internal identifiers or private values like an Account ID, a warehouse ID, or a contract number. Inbound EDI documents will co

TechWizard2023Apprentice

asked in CData User Group

Capabilities of CData excel adapter with BIgQuery

Our users are using Oracle smartview for financial reporting and are looking for an excel based tool to have similar capabilities in BigQuery. I wanted to know if CData plugin for excel can do the below:Slice and dice data based on BigQuery data models Consume hierarchies maintained in bigquery. For example a legal entity hierarchy that would be maintained centrally in BQ and consumed in financial reports Schedule reports Do cell based formulas. So for example, I want to create a forumla that goes into a cube and returns net earnings for the year input by the user in one of the cells.

JohnCommunity Manager

posted in Getting Started

Exciting News: CData Recognized in Forrester Wave™ for Cloud Data Pipelines Q4 2023!

Hello CData Community, We're thrilled to share some exciting news with you all! CData Software has been featured in the Forrester Wave™ report for Cloud Data Pipelines, Q4 2023. This recognition highlights our commitment to providing a robust and reliable cloud data pipeline solution, especially for hybrid cloud environments. Our strengths in data sources and access, along with our focus on AI/ML, data intelligence, and performance, have been acknowledged in this prestigious report. We’re proud of our journey so far and excited about the road ahead, as we continue to evolve and enhance our capabilities. But this isn't just our achievement — it's yours too! Your support, feedback, and engagement have been instrumental in our growth and success. We would love to hear your thoughts and experiences with CData Sync. How has it impacted your data integration and analytics? Let's celebrate this milestone together and continue our journey towards innovative and efficient data management soluti

quangdutran809Collaborator

asked in CData Drivers & Connectors

Is "CREATE TABLE" supported with Cdata JDBC Drivers for NoSQL database?

I have looked at the document: https://cdn.cdata.com/help/EEJ/jdbc/pg_overview.htm for Elasticsearch but create table statements could not be found here.So it means Cdata does not support SQL-like create table statement? Is there any way I could create index (Elasticsearch) or collections (MongoDB) with JDBC driver?

asked in CData Drivers & Connectors

Cannot update Azure Table with Excel Add-In (trial version)

Hi, I’m trying to update records in an Azure Table with the trial version of the CData Excel Add-In for Azure Table Storage (v23.0.8716). I’ve tried using both an Access Key and a Shared Access Token.I can load the data in Excel without issue but cannot update. I keep getting the following error message:“The url must be a valid http or https url”Has anyone else seen this? Is this a limitation of the trial version?Thanks

GijsbertApprentice

asked in CData User Group

Select statement error on where clause

Below the query is would like to run.It is supposed to select rows from the contactcollectiontable and exclude records based statusecode and on multiple values in the FunctionCode column.$where = "StatusCode = '2' and FunctionCode<>'Z14' and FunctionCode<>'Z95' and FunctionCode<>'Z96' and FunctionCode<>'Z97');" $A = Select-SAPHybrisC4C -Connection $conn -Table "ContactCollection" -Columns $Columns -Where $WhereHowever the code above is not working and throws this error:Select-SAPHybrisC4C : [500] Could not execute the specified command: [ERROR] Error in filter System Query, Operation failed::Expression can not converted into ABAP select options. At line:2 char:6+ $A = Select-SAPHybrisC4C -Connection $conn -Table "ContactCollection" ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (CData.SAPHybris...HybrisC4CSelect:SAPHybrisC4CSelect) [Select-SAPHybrisC4C], avx + FullyQualifiedErrorId :

SatyendraApprentice

asked in CData Sync

Issue with CDATA Sync Job Using Incremental Replication Despite Removal

Hello CDATA Community,I hope this message finds you well. I am currently facing an issue with the CDATA Sync tool and would appreciate any insights or assistance from the community.Problem Description: I have configured a job with a single task, where the source is Salesforce, and the destination is an SQL database. Initially, I ran the task without incremental replication, and it successfully synchronized all records for a specific Salesforce object. Later, I implemented incremental replication and verified that it worked as expected. However, when I decided to remove incremental replication and attempted to sync the data again, the job seems to persist in using incremental replication, and not all data is being synchronized. Steps Performed:Created a job with Salesforce as the source and SQL database as the destination. Ran the task without incremental replication, successfully syncing all records. Implemented incremental replication and verified its functionality. Removed incrementa

rocketmaniacApprentice

asked in CData Drivers & Connectors

'Unknown datatype: JSON.' in ADO.NET Provider for Presto

I’m getting the following exception when trying to run the following code. Does the driver support the Trino JSON type? I don’t see json referenced in GetResultTypeData method source.string sql = "SELECT _id, CAST(extra_columns AS JSON) as extra_columns FROM hive.default.measures";PrestoCommand cmd = new PrestoCommand(sql, connection);PrestoDataReader rdr = cmd.ExecuteReader(); Message: Test method Juniper.Test.Trino.TrinoClientTests.PrestoAdoRead threw exception: System.Data.CData.Presto.PrestoException: [500] Could not execute the specified command: Unknown datatype: JSON. ---> oro230j.TC: Unknown datatype: JSON. Stack Trace: ZN.GetResultDataType(FormulaEvaluationContext )RqC.K(SqlFormulaColumn )RqC.mI(SqlTable , SqlColumn )Rod.C(SqlCollection`1 )Rod.Nd(LLB`2 )Roh.Nd(LLB`2 )KJ.U(SQLQuery , Int32 , RRq )KJ.d(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , LeW )RUJ.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )RUG.ExecuteQuery(IDataStat

rocketmaniacApprentice

asked in CData Drivers & Connectors

How to add/enable header X-Trino-User for ADO.NET Provider for Presto

I’m getting the following exception when trying to run the following code.Is there a way to enable or set the HTTP Header X-Trino-User?PrestoConnection connection = new PrestoConnection($"Server={_configuration["TrinoServerIP"]};Port={_configuration["TrinoServerPort"]};");connection.Open();string sql = "SELECT * FROM hive.default.customer";PrestoCommand cmd = new PrestoCommand(sql, connection);PrestoDataReader rdr = cmd.ExecuteReader();while (rdr.Read()){ Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["Id"], rdr["Name"]));} PrestoAdo Source: TrinoClientTests.cs line 91 Duration: 1.4 sec Message: Test method Juniper.Test.Trino.TrinoClientTests.PrestoAdo threw exception: System.Data.CData.Presto.PrestoException: [500] Could not execute the specified command: HTTP protocol error. 401 Basic authentication or X-Trino-User must be sent. ---> oro230j.TC: HTTP protocol error. 401 Basic authentication or X-Trino-User must be sent. Stack Trace: LdJ.j(Lax[] , Int32 , Int3

asked in CData Arc

Reading the MDN

We have a lot of manufacturers that we send X12 850 purchase orders to. We don’t always receive a 997 and I am wondering if its possible to read and parse the MDN and extract the response confirming delivery.My goal is to confirm AS2 delivery and add an Email send connector to the flow and notify our purchasing department that the PO was received. Thanks

MalcolmApprentice

asked in CData Drivers & Connectors

How to connect one Power BI Premium tabular model to another as a datasource?

Hi all,We are migrating from on-prem tabular models to PowerBI Premium models. Most datasources work flawlessly (including the CDATA Snowflake connector). However, we have been unable to use another tabular model as a data source when using PowerBI Premium models…We have the same problem when trying to connect to our Power BI Premium models from our data catalog (Ataccama). OLD On-prem Visual Studio source:Friendly connection name: [model name]Server or File Name: [server] \ [Folder]Database name: [Database name]OLD Ataccama /CDATA JDBC connector:jdbc:cdata:ssas:User=[user];catalog="[catalog]";URL=http://[server]/OLAP/msmdpump.dllNew Connections that work:Excel Connection (Power BI Premium Data Source):Provider=MSOLAP.8;Integrated Security=[];Persist Security Info=[];Initial Catalog=sobe_wowvirtualserver-[GUID];Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://ana

DeCalApprentice

asked in CData User Group

WorkDay StarSchema

Does anyone have a Data Model (Star Schema ideally) visual they can share for new users to WorkDay intending to use PowerBI with the adapter?

1
2
3

Page 1 / 3

CData Software is a leading provider of data access and connectivity solutions. Our standards-based connectors streamline data access and insulate customers from the complexities of integrating with on-premise or cloud databases, SaaS, APIs, NoSQL, and Big Data.

Connect With Us

Get Started

Data Connectors

ETL/ ELT Solutions

Cloud & API Connectivity

OEM & Custom Drivers

Connect With Us

Get Started

Data Visualization

Company

News & Insights