Page 1 of 1
Zoho CRM ODBC & Powershell
Posted: Mon 16 Oct 2017 10:09
by Dogeron
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.
Re: Zoho CRM ODBC & Powershell
Posted: Wed 18 Oct 2017 13:05
by MaximG
Try changing the query text as follows :
Code: Select all
select * from Products where "PRODUCT NAME" = 'COUPFL150'
Re: Zoho CRM ODBC & Powershell
Posted: Wed 18 Oct 2017 14:04
by Dogeron
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.
Re: Zoho CRM ODBC & Powershell
Posted: Thu 19 Oct 2017 08:00
by MaximG
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)
Re: Zoho CRM ODBC & Powershell
Posted: Tue 31 Oct 2017 15:19
by Dogeron
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.
Re: Zoho CRM ODBC & Powershell
Posted: Wed 01 Nov 2017 15:25
by MaximG
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
Re: Zoho CRM ODBC & Powershell
Posted: Wed 27 Dec 2017 13:23
by mojoj168
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/