Zoho CRM ODBC & Powershell

Zoho CRM ODBC & Powershell

Postby 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.
Dogeron
 
Posts: 3
Joined: Wed 27 Sep 2017 16:25

Re: Zoho CRM ODBC & Powershell

Postby MaximG » Wed 18 Oct 2017 13:05

Try changing the query text as follows :
Code: Select all
select * from Products where "PRODUCT NAME" = 'COUPFL150'
MaximG
Devart Team
 
Posts: 740
Joined: Mon 06 Jul 2015 11:34

Re: Zoho CRM ODBC & Powershell

Postby 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.
Dogeron
 
Posts: 3
Joined: Wed 27 Sep 2017 16:25

Re: Zoho CRM ODBC & Powershell

Postby 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)
MaximG
Devart Team
 
Posts: 740
Joined: Mon 06 Jul 2015 11:34

Re: Zoho CRM ODBC & Powershell

Postby 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.
Dogeron
 
Posts: 3
Joined: Wed 27 Sep 2017 16:25

Re: Zoho CRM ODBC & Powershell

Postby 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/odbc/reference/develop-app/batches-of-sql-statements
MaximG
Devart Team
 
Posts: 740
Joined: Mon 06 Jul 2015 11:34


Return to ODBC Drivers