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.