ODBC Driver for Zoho Projects

Build 22.0.8389

Executing Queries

This section describes how to execute queries using MySQL remoting from any tool that can connect to MySQL.

When executing queries, you use a two-part name format, as shown below,

[<Schema Name (normally but not always the service name)>].[<Table Name>] 
where
  • Schema Name is the name of the system schema or user-defined schema.
  • Table Name is the name of the table you are copying data from.

SELECT Statements

To create new records:
SELECT * FROM [ZohoProjects].[Portals] WHERE Id = '123456' 
Note that if the data source supports case-sensitive identifiers, it is not possible to query tables of the same name that only vary by upper or lower case-sensitive (ex: Account and ACCOUNT in the same schema). This is because MySQL treats identifiers as case-insensitive, so multiple tables of the same name in a single schema are fundamentally incompatible with the MySQL protocols.

INSERT, UPDATE, and DELETE Statements

To insert, update, or delete a single row of data (that is, not a batch of records), you need to use the two-part name. For example:
INSERT INTO [ZohoProjects].[Portals] (Id) VALUES ('123456'); 

Bulk Operations

To perform bulk operations, you can use syntax similar to the following:

You can run a bulk INSERT using a SELECT query in place of a VALUES list.

INSERT INTO [ZohoProjects].[Portals] ([Id]) 
SELECT [Id] FROM [Local_Portals] 
You can run a bulk UPATE using a JOIN with a local table.
UPDATE [ZohoProjects].[Portals]  
INNER JOIN [Local_Portals] ON [Local_Portals].[Id] = [ZohoProjects].[Portals].[Id] 
SET [ZohoProjects].[Portals].[Id].[Local_Id] 
You can run a bulk DELETE using the IN filter with sub-SELECT query.
DELETE FROM [ZohoProjects].[Portals WHERE [Id] IN (SELECT [Id] FROM Local_Portals) 

Execute Stored Procedures

Use the following to run the stored procedure:
Call CreateJob('Insert')

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 22.0.8389