SQL Commands

 

 

If you want to use a specific SQL Command for Insert, Update or Delete SQL statement, enter the SQL in the Insert, Update or Delete edit box.

 

If you click the “Insert parameters” button, GeniusConnect will insert all (used/linked) table columns with ‘@’ sign.
Format Parameter: ‘@<Col. Name>’

 

Example:

Column: tbl_zipcode

Parameter: ‘@tbl_zipcode’

 

Note:
SQL Command for delete supports only primary key field and dynamic parameters.
During delete, GeniusConnect can detect value of the primary key, but not of other Outlook item columns.

Dynamic parameters can be used because they depend on Folder(Folder name..), Outlook (Outlook user..) or PC (NT User..) values and not on individual item values.

 

If you click the “Insert dynamic parameters” button, GeniusConnect will insert all dynamic parameters GeniusConnect will replace the parameters with Outlook values during the execution. See Filter rows for more info about dynamic parameters.

 

 

IMPORTANT:

For INSERT/UPDATE/DELETE, ODBC driver returns number of rows affected by a SQL statement.

GeniusConnect checks this value! Number of rows affected must be 1, otherwise GeniusConnect will display a warning or an error.

 

Your stored procedure/SQL Statement used for Insert/Update/Delete must also return “number of rows affected=1” even if your stored procedure causes other/more/ table modifications.

 

Examples:

ODBC syntax to execute a stored procedure:
{call spMyName (‘@myColumn1’,…)}

 

SQL Server syntax to execute a stored procedure:
EXECUTE spMyName (‘@myColumn1’,…

 

MS SQL Server example files:

SQL script: Example MSSQL Server custom sp.sql

Template: Example MSSQL Server Mail custom sp.ini

For advanced users:
Dynamic parameters defined in Filter rows can be used and will be replaced with runtime values.

 

Attachments specific:

If you are using custom stored procedures to Insert and update Attachments, some older database systems may require blob / image parameter to be the LAST parameter.

Example:

Sp_attachment_update(‘@parentID’, ‘@Attch_nr’,……., ‘@blob_column’)

 

Delete stored procedure for attachments
GeniusConnect will always provide values for parent key(s) to the delete stored procedure. The Attachment Number parameter (mandatory for attachments) can be empty (NULL) when deleting all attachments of an Outlook item.

GeniusConnect will call the custom delete stored procedure in the following situations:

 

  • During store (All/Item)
    Attachments table contains more attachments for an parent record then Outlook item. During this call the attachment number parameter contains always a valid attachment number (0,1,X, never NULL) to be deleted from database table
    Example: User deletes an attachment from an Task
  • Option "Delete Database record when Outlook Item deleted" Is ON, and user deletes an Outlook Item
    During this call the attachment number parameter will be empty (NULL), only the parent key(s) parameter is valid. (GeniusConnect executes attachment delete before parent record delete)

 

Example Delete stored procedure:

CREATE PROCEDURE spDeleteAttachment(@parent_guid uniqueidentifier, @AttachNr int) AS

BEGIN

If @AttachNr is not NULL

BEGIN

delete from Attachments
where parent_guid=@parent_guid and Attachment_nr=@AttachNr

END

Else

BEGIN

delete from Attachments
where parent_guid=@parent_guid

END

 

END