Zoho CRM ODBC & Powershell

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
Dogeron
Posts: 3
Joined: Wed 27 Sep 2017 16:25

Zoho CRM ODBC & Powershell

Post by Dogeron » Mon 16 Oct 2017 10:09

Hi,
I've clearly got something wrong with the following code but I can't see what - and a straight select * works fine.
>> Running (Test.ps1) Script...
>> Platform: V5 64Bit (Elevated)
select * from Products where "PRODUCT NAME" = "COUPFL150"
ERROR: Exception calling "Fill" with "1" argument(s): "ERROR [HY000] [Devart][ODBC][Zoho CRM]Unknown column 'COUPFL150'"
Test.ps1 (93, 2): ERROR: At Line: 93 char: 2
ERROR: + (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | o ...
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : OdbcException
ERROR:

*** PowerShell Script finished. ***
>> Execution time: 00:00:06
>> Script Ended
Can someone point me in the right direction.

Thanks.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Zoho CRM ODBC & Powershell

Post by MaximG » Wed 18 Oct 2017 13:05

Try changing the query text as follows :

Code: Select all

select * from Products where "PRODUCT NAME" = 'COUPFL150'

Dogeron
Posts: 3
Joined: Wed 27 Sep 2017 16:25

Re: Zoho CRM ODBC & Powershell

Post by Dogeron » Wed 18 Oct 2017 14:04

Hi Maxim,
Modified and re-run, output below.
select * from Products where "PRODUCT NAME" = 'COUPFL150'

ERROR: Exception calling "Fill" with "1" argument(s): "ERROR [HY000] [Devart][ODBC][Zoho CRM]Select Columns and Condition are mandatory"
Test.ps1 (93, 2): ERROR: At Line: 93 char: 2
ERROR: + (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | o ...
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : OdbcException
ERROR:
Which is quite odd.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Zoho CRM ODBC & Powershell

Post by MaximG » Thu 19 Oct 2017 08:00

To investigate the issue, please provide the content of the used Test.ps1 script via the e-support form ( https://www.devart.com the "Support"\"Request Support" menu)

Dogeron
Posts: 3
Joined: Wed 27 Sep 2017 16:25

Re: Zoho CRM ODBC & Powershell

Post by Dogeron » Tue 31 Oct 2017 15:19

Hi Everyone,

With help from Maxim at Devart the following Select script works fine using the where clause.

Code: Select all

#Function to get ZOHO CRM Data via ODBC
function Get-Zoho-Data
{
	param ([string]$query = $(throw 'query is required.'))
	$ConnectionString = "DSN=ZohoCRM;"
	$conn = New-Object System.Data.Odbc.OdbcConnection
	$conn.ConnectionString = $ConnectionString
	$conn.open()
	$cmd = New-object System.Data.Odbc.OdbcCommand($query, $conn)
	$ds = New-Object system.Data.DataSet
	(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
	$conn.close()
	$ds.Tables[0]
}



#####################
# Create ZOHO SQL Statement #
#####################
#$CRMsql = "select * from Products" # <----This works 
#$CRMsql = "select * from Products where `"PRODUCT NAME`"  =  'COUPFL150' "   #<-----this fails

$CRMsql = "select * from Products where ""Product Name"" = 'COUPFL150'"  # <------code provided by Maxim at DevArt

$CRMsql

$ZohoData = Get-Zoho-Data -query $CRMsql

$ZohoData
Note how the column name in the where statement is quoted.

Following this success, the following update script works as well - again notice the column quoting and the different Function to write data.

Code: Select all

#Function to set ZOHO CRM via ODBC

function Set-Zoho-Data
{
	param ([string]$query = $(throw 'query is required.'))
	$ConnectionString = "DSN=ZohoCRM;"	
	$conn = New-Object System.Data.Odbc.OdbcConnection
	$conn.ConnectionString = $ConnectionString
	$cmd = new-object System.Data.Odbc.OdbcCommand($query, $conn)
	$conn.open()
	$cmd.ExecuteNonQuery()
	$conn.close()
}

###################################################
# Create SQL to Update List Price for Products on Zoho CRM #
###################################################

$updates = @{
	"CURTAIN"   = 15.39;
	"FILTER-1"    = 35;
	"FILTER-2"   = 500;
	"FILTER-3"   = 416.667;
	"FILTER-4"   = 600;
	"FILTER-5"   = 533.333;
	"FILTER-6"   = 599;
	"FILTER-7"   = 660
}

#Loop each row of the hash table
foreach ($row in $updates.GetEnumerator())
{
        #build SQL
	$Zoho_SQL = "update Products set `"Unit Price`" = " + $row.Value + " where `"Product CODE`" =  '" + $row.Name + "'"
	$Zoho_SQL
	
	#Execute SQL Statement
	$ZohoData = Set-Zoho-Data -query $Zoho_SQL
	$ZohoData
}
This should be enough to get anyone going with the DevArt Zoho CRM driver and PowerShell - enjoy.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Zoho CRM ODBC & Powershell

Post by MaximG » Wed 01 Nov 2017 15:25

The documentation about using Batch operations when working with ODBC driver is available by the link : https://docs.microsoft.com/en-us/sql/od ... statements

mojoj168
Posts: 1
Joined: Wed 27 Dec 2017 13:17

Re: Zoho CRM ODBC & Powershell

Post by mojoj168 » Wed 27 Dec 2017 13:23

The documentation about using Batch operations when working with ODBC driver is available by the link : https://docs.microsoft.com/en-us/sql/od ... statements

it is also used by official partners that've used this reference code you may look at some more reference here
http://www.techloyce.com/zoho-partner-consultant/

Post Reply