Connecting Software Connect Bridge – User Manual
Contents
1. Connect Bridge User manual Connecting Software Description Location SharepointSt setString 2 exchangeRs getString Body sharepointSt setString 3 exchangeRs getString Location sharepointSt setTimestamp 4 exchangeRs getTimestamp StartDate StartTime sharepointSt setTimestamp 5 exchangeRs getTimestamp EndDate EndTime System out printiIn Inserting exchangeRs getString Subject STEP 5 INSERT the data into the target server sharepointSt execute exchangeRs close exchangeSt close sharepointSt close STEP 6 Close Connections exchangeConn close sharepointConn close 2015 06 18 Page 44 56 Connecting fey ca C Tr ware Connect Bridge User manual Connecting Software 6 CB SQL Reference This section provides a reference to the Connect Bridge version of the SQL Syntax It closely resembles the ANSI 92 standard Please check the registered users section of the developer s corner on our website www connecting software com for the most up to date information 6 1 General SQL Syntax Schema defines a mandatory part defines an optional part defines one option to be selected expression three dots define a potential repetition of the expression as many times as needed All keywords and references are not case sensitive You can write them in upper case or lower case Use the Connection browser in the CB Query Analyzer to iden2. ASC ascending sort order A gt Z DESC descending sort order Z gt A Example Select all Contacts show only the columns Surname amp Given Name First name and sort the list by surname from A to Z SELECT Surname Givenname FROM Contact ORDER BY Surname ASC Same example but with alias please note that in case of aliases the resulting column headers will show the alias name SELECT Surname AS s Givenname AS g FROM Contact ORDER BY s ASC LIMIT designates the maximum number of rows to be returned OFFSET designates the number of rows after the first that should be skipped before the selection begins Example Returns the Surname GivenName Email amp Phone Nunmber of the conacts number 3 4 amp 5 OFFSET 2 means start with number 3 and LIMIT 3 means take three incuding the one you started with SELECT Surname GivenName Emaill TelephoneNumber FROM Contact LIMIT 3 OFFSET value_reference Any literal value used in quotes has to fit to the data type of the column the value belongs to For more information take a look on supported data types See the connection browser in the CB Query Analyzer for a respective list and chapter 6 2 8 SUPPORTED DATA TYPES for details For example the column BirthDate in Contacts requires a MgDateTime data type trying to assign a simple number won t be enough Simple Example get the subject and body from all tasks SELECT Subject Body FROMTask Full Example
3. operator_reference column_reference operator_referencevalue_reference a operator_reference refers to one of the following standard operators lt smaller than gt bigger than lt smaller or equal to gt bigger or equal to equal to and or like Comparison operators lt gt lt gt are used to execute a comparison based on the data type of the column designated in the column_reference operand Example Select all tasks that have a due date of Aug 31st orlater SELECT FROMtaskWHEREDueDate gt 2012 08 31 00 00 00 000 2015 06 18 Page 50 56 Connecting fey Software Connect Bridge User manual Connecting Software The like operator can be applied on string operands only It evaluates to true or false if the value of the column_reference contains a substring provided as the second operand in the condition Example Selects all contacts that have a surname that contains the part Do and shows the Columns Surname and Givenname This would include a John Doe but also a James Done or a Charlene Randolph SELECT Surname Givenname FROM Contact WHERE Surname LIKE Do Logical operators and or are used to construct more complex conditions andrequired both conditions to be true in order to qualify orrequires one or the other Example Selects all contacts that have a surname that contains the part Do and shows the Columns Surname and Givenname and
4. 8087 UID demouser PWD password ACC accountS 2015 06 18 Page 42 56 Connecting fey Software Connect Bridge User manual Connecting Software harePoint Connection exchangeConn DriverManager getConnection exchangeConnectionString Connection sharepointConn DriverManager getConnection sharepointConnectionString Statement exchangeSt exchangeConn createStatement System out printin Connecting to Exchange STEP 2 Provide an appropriate object like a Data Table in C ora ResultSet in JAVA STEP 3 Fill the object with data from the source server ResultSet exchangeRs exchangeSt executeQuery SELECT FROM Appointment create a new JDBC statement for inserting PreparedStatement SharepointSt sharepointConn prepareStatement INSERT INTO Calendar Title Description Location StartTime EndTime VALUES 2 STEP 4 Manipulate the data and or apply a workflow rule in this case check if the appointment is private if not gt insert it into sharepoint while exchangeRs next Boolean isPrivate exchangeRs getBoolean IsPrivate if isPrivate null amp amp isPrivate System out printIn Skipping exchangeRs getString Subject continue Title fill its parameters with values for the sharepoint account sharepointSt setString 1 exchangeRs getString Subject 2015 06 18 Page 43 56 Connecting fey SOTWia re
5. amp 4b contains following windows see also Figure 25 Query Analyzer Expanded View The SQL statement editing window 4a where you write and execute SQL statements Parameter editing window extended view 4c where you can define parameters used in SQL Stored Procedures Click on the blue button in the SQL editing window to open Results 4c shows the result of the executed statement s Messages expanded view 4d shows information concerning the last executed SQL statement s 2015 06 18 Page 31 56 Connecting Sottware Connect Bridge User manual Connecting Software File View Options Help E New query amp Connection browser g Query history Connection browser Ax y ery 4 CB CRM 9 ath E bed Dp execute F5 BP cecrm O wizardaccesspevilege 1 EXEC SP UPDATE ENTITY ai wizardpage 2 InEntityName HE wordlow FP wordiowdependancy a wordiowleg J E Stored procedures SP_ADD_STRING_ATTRIBUTE JE SP_CREATE_ENTITY E SP_DEFAULT_SCHEMA H E SP_DELETE_ENTITY E SP_REFRESH_MGW H E SP_UPDATE_ENTITY 9 0 SP_UPDATE_SCHEMA 4B CB EXCHANGE CB SHAREPOINT i H E SP_DELETE_ATTRIBUTE iror Wo 52013 11 1 1 21s DRIVER Meda Gateway ODBC Dever UID Demolser 1ACCeCR M20 EXEC SP_UPDATE_ENTIT iin Gritty Name 5 9 2073 11 0 5 018 is ENSC DRIVER Media Gateway ODBC Driver UlD Demo User 1 A0C CRM20 select irom contact G 5 9 2013 105 0 7328 DRIV
6. 2015 06 18 Page 46 56 Connecting fey software Connect Bridge User manual Connecting Software Skip 20 messages and select 10 messages from the Inbox folder where the Subject is equal to Test and order the rows by the name of the sender from A to Z SELECTDisplayName From To Subject Body DateReceived FROMMessage JOINInbox ONMessage ID Inbox FKItemID WHERESubject Test ORDERBY From ASC LIMIT10 OFFSET20 6 2 2 INSERT Inserts data into a target server It represents the C in CRUD INSERT INTO table_reference lt column_reference column_reference values value_reference value_reference Select Scope_identity Scope_Identity returns the ID generated by the INSERT statement but only when used in conjuction with the INSERT statement Example Create a new contact and get the generated ID of this insertedrecord INSERT INTO Contact GivenName Surname City Country Department Emaill VALUES John Smith NewYork USA unknown John Smith domain com SELECT Scope_identity 6 2 3 UPDATE Updates existing data on a target server with new information It represents the U in CRUD UPDATE table_reference SET column_reference value_reference column_reference value_reference WHERE condition_reference Example Change the start time of appointment with name Appointment1 to start 30 minutes before current start time UPD
7. Description Location StartTime EndTime VALUES sharepointConn fill its parameters with values for the sharepoint account insertToSharepointCmd Parameters AddWithValue Title appointment Subject insertToSharepointCmd Parameters AddWithValue Description appointment Body insertToSharepointCmd Parameters AddWithValue Location appointment Location insertToSharepointCmd Parameters AddWithValue StartTime appointment StartDate insertToSharepointCmd Parameters AddWithValue EndTime appointment EndDate Console WriteLine Inserting 0 appointment Subject STEP 5 INSERT the Data into the target server int affectedRowCount insertToSharepointCmd ExecuteNonQuery t Console WriteLine Closing connections STEP 6 Close Connections exchangeConn Close sharepointConn Close Console WriteLine Press any key to exit Console ReadKey bs lt summary gt Creates an odbc connection to CB server lt summary gt lt param name host gt CB server host name lt param gt lt param name port gt port the CB server service is listening to lt param gt lt param name accountName gt CB Account name has configured credentials to log into the remote systems lt param gt lt param name userName gt CB User name lt param gt lt param name password gt CB password lt param gt lt r
8. MS SharePoint via the CB Connector These can be actual User Accounts like John Doe or Service Accounts like ServiceCB The actual user Accounts are usually chosen in case of a client server scenario e g a proprietary portal or ERP system accesses MS Exchange Emails for each user via the Connect Bridge Service Accounts are usually chosen for server server integrations e g synchronizing appointments between Dynamics CRM and MS SharePoint In either case the Account needs to exist in the domain first then registered with the appropriate rights in the target server After that the account can be created and configured in the CB Admin Console for details please check the CB Installation amp Configuration Guide Accounts can be managed in the Accounts section of the CB Admin Tool Open the Admin Tool see Figure 10 Account Administration navigate on the left pane 1 down to Manage Accounts then double click on Accounts The middle pane Grid 2 will show you all the existing accounts Description of columns displayed in the grid 2 4 6 ID CB internal ID of the record 2 4 7 AccountName represents the name of the account eres 7 ys AP rs Tip Use the prefix ACCOUNT_ or ACC_ in order to facilitate recognition of the Account Name inside a connection string By default CB creates one account per installed Connector 2015 06 18 Page 15 56 Connecting fy Connect Bridge
9. Shows and activates the Event viewer COC wegis SSieesLine s Da sjollave certigo Tor Ciesla Ojo aL ois Help About Shows the About window The Icon Bar 2 contains icons for common tasks Icon Description i a Opens the Query window for the selected N J New query connection Opens and activates the Connection browser Connection browser liq Event viewer Opens and activates the Event viewer Table 2 Icon bar icons The Connection Browser 3 on the left showing connections to both the CB server shown as Administration as well as connections via the CB server to target servers like MS SharePoint and MS Exchange The default view of the QA only shows one connection the one to the CB Server called Administration Later other connections can be added GE The green Plus button allows to create a new connection alternatively click on the Menu File New Connection That will open the New Connection dialog 2015 06 18 Page 27 56 Connecting Software Connect Bridge User manual Connecting Software A Edit connection Connection name DEV_SVC_SP_CLOUD Connection string DRIVER Media Gateway ODBC i lai ERVICEMGW_SP_CLOUD IMPL CORBA PORT 8087 HOSTSocalhost PWD 1234567 E9 E Shared connection oniy one ODBC connection will be shared for this instance and it needs to be opened manually Figure 20 New Edit Connection Dialog Give the new connection a meaningful name add the appropriate co
10. User manual Connecting Software kd connect Bridge Administration ools Vermon Lalesi Adminstration vonta Connected Io L2 ME Ta h An TN bP S 3 5 TE rele gy L Rg oe beeen E EA LA oT To al ile ea ar Ae a Bed Lotte IEI EIN Wald ae end ee oe ee Administration Help a a a e i TV iT JETA 1 plog Ea LOUR T A Fj ES N P RIN Miine an EA pa ch E ACCOUNT_OUTLOOK Se E eee ACCOUNT_EXCHANGE_2010 SBE SSIS A Sie EE EE or FE a Pin Figure 10 Account Administration The following actions can be performed on with an account a Create a new account Click on New button on the right pane 3 A dialog window appears see Figure 11 Account Management 1 Account Name The name of the account 2 Connector Select the name of the Connector you want to use with the account One account can only work with one Connector please remember that when naming the accounts 3 Connector Properties Fill the values of required properties for corresponding Connector this is connector specific see the respective CB Connector Reference and check the CB Installation and Configuration Guide for the most common connectors 4 When you have finished the configuration click the Save button in order to apply these settings 2015 06 18 Page 16 56 Connecting fey f Connect Bridge User manual Connecting Software la C Bride pe 4 a aa 4 4 PERT 143012239132 rE oi he Se ee 1M tore Conn ct d To 127 0 0
11. a persistent effect on schemata and data or which may control transactions program flow connections sessions or diagnostics It also include the semicolon statement terminator Though not required on every platform it is defined as a standard part of the SQL grammar Stored Procedure It is a subroutine available to applications that access a relational database system Stored procedures can consolidate and centralize logic that was originally implemented in applications 2015 06 18 Page 55 56 Connecting fey Sota re Connect Bridge User manual Connecting Software 7 2 Abbreviations Abbreviation Meaning Application Programming Interface Connect Bridge Common Object Request Broker Architecture Microsoft Dynamics Customer Relationship Management DLL Dynamic Link Library EULA End User License Agreement FTP File Transfer Protocol GUI Graphical User Interface IP Internet Protocol JDBC Java Database Connectivity JRE Java Runtime Environment Microsoft SharePoint a TCP Transmission Control Protocol XML Extensible Markup Language 2015 06 18 Page 56 56
12. a license When you receive a Connector please unzip it to the Plugins directory of your server installation usually C Program Files Connecting Software Connect Bridge CbServerService Plugins see below 2015 06 18 Page 12 56 Connecting fy Software Connect Bridge User manual Connecting Software Extraction path and options Advanced Destination path will be created if does not exist C Program Files Connecting Software Connect Bridge MgServerService Plugins lt a i El p Program Files Extract and replace files ff i Hede Common Files D Extract and update files i Connecting Software EFJ El J Connect Bridge i Eg Documentation Overwrite mode i 0 Drivers Ask before overwrite 2 2 a MaServerService va i BA BackupLog C Overwrite without p t opo o o LOD Overwrite without promp baa isl Database Skip existing files i Ba a hedi libs 6 Fresh existing files only O Rename automatically E MgWebService oo oF b Plugins Pee Ega CBActiveDirectoryConnector _ Extract archives to subfolders i be 2 CBSalesForceConnector Keep broken files i be 3 MGAdministrationPlugin MGCrmPlugin Display files in Explorer Display MGEXPlugin i Be a 2 oo MGEXPlugin 2010 Figure 8 Unzip Example Once unzipped open the Admin Tool double click on Connectors 1 then New 2 give it the same name as the file you have received without
13. connection for example Appointment in SharePoint which is called Calendar there First check if you have selected the correct server in the dropdown menu then check for spelling errors That usually solves the problem Finally type any SQL statements 1 you wish to execute and press F5 or the Execute SQL Commands button You can also auto generate SQL statements by right clicking on an entity in the connection browser for example Contact and then choosing one of the options in the context menu displayed see Figure 29 Auto generate SQL Please check out our SQL Reference in Chapter 6 CB SQL Reference for a detailed description of possible SQL commands 2015 06 18 Page 36 56 Connecting SOT a re Connect Bridge User manual Connecting Software jal Connect Bridge Query Analyzer xs File View Options Help E New query Connection browser 3 Query history Connection browser ax 7 wiQue sqlQuery_5 CB CRM S OZ OJR JS G be A execute F5 cacrm S Connections a 1 UPDATE account H E Administration 2 SET accountcategorycode lt accountcategorycode MgString gt oe l CB CRM 3 accountclassificationcode lt accountclassificationcode MgString gt B 9 Tables 4 accountid lt accountid MgString gt i o 5 accountnumber lt accountnumber MgString gt i b ga Col Generate Select accountratingcode lt accountr
14. of the parameters window 3 _ l Connect Bridge Query Analyzer File View Options Help E New query amp Connection browser 3 Query history Connection browser ax sqlQuery_6 CB CRM x OFO 8as l GF ted Bpexecute Fs ccm Connections 1 EXEC SP UPDATE ENTITY Administration 2 InEntityName 5 1 CB CRM i E Tables P fa Stored procedures jE SP_LADD_STRING_ATTRIBUTE H E SP_CREATE_ENTITY a 2 SP_DEFAULT_SCHEMA i H E SP_DELETE_ATTRIBUTE H E SP_DELETE_ENTITY E 1 Ooo B A SP_REFRESH_MGW 7 5 2 2 Ge EH Renate S E SP_UPDATE Generate Exec ja H CB SHAREPOIN H CB NAV Figure 30 Auto generate Stored Procedures 2015 06 18 Page 37 56 Connecting fey Software Connect Bridge User manual Connecting Software In the sample shown in Figure 30 Auto generate Stored Procedures the stored procedure SP_UPDATE_ENTITY which update the schema of a table has been right clicked then Generate parameterized Exec was chosen and the stored procedure as shown in the Query Editor Window was auto generated After filling in Value as the value for the parameter InEntityName in the Parameter Window on the right and pressing Execute F5 the result will be shown below in the Results window 4 2 3 View Results Errors Results and Errors Messages can be seen in their respective tabs
15. or MGW If you should meet the term Media Gateway while using the Connect Bridge please kindly email us a screenshot Thanks for understanding Connecting Software documentation team 1 1 Document Structure The CB Administration Tool Chapter 2 provides an in depth look into the functionality of the administration tool provided by Connecting Software It covers managing of Accounts Groups Users Licenses Logs etc The CB Service Controller Chapter 3 contains a brief introduction to the service controller A practical little tool designed to handle common administrative issues without having to resort to the Admin Tool or Windows Administration The CB Query Analyzer Chapter 4 covers the Query Analyzer Connecting Software s main tool to query amp test the functionality of the CB Server by means of the CB SQL Language A Brief Workflow Development Tutorial Can be found in Chapter 5 Designed to give a short introduction into the principles of developing integration solutions with the Connecting Software Connect Bridge The CB SQL Reference In Chapter 6 a reference of the CB variation of the SQL Language used to control the CB Server is provided You will find that it closely matches ANSI SQL 2015 06 18 Page 4 56 Connecting fey sottware Connect Bridge User manual Connecting Software 1 2 Operating Principles The Connect Bridge is a powerful yet simple to use solution to connect standard serv
16. the Administrator User and click Edit The dialog screen displayed allows you to change the password Then click Save Once the connection to CB Server is established main window of CB Server Administration Tool is shown see Figure 5 CB Admin Tool Main Window lt 1 Connect E 1098 Adminstration oa Administration Help Figure 5 CB Admin Tool Main Window 2015 06 18 Page 8 56 Connecting fey ta C Tr ware Connect Bridge User manual Connecting Software 2 2 Description of the User Interface Components Navigation Panel 1 refers to the panel on the left side of the main window It contains 2 selection trees Serversand Manage Grid Editor 2 the grid table in the middle shows the names the column Key and values the column Values of the properties selected in the Navigation Pane Items in Navigation Panel Servers The list of actions you can execute against a connected CB Server By the time of this documentation release the client is only able to connect to one CB Server at a time gt Logs Allows the management of the logging settings of the CB Server gt Restart Server Restarts the CB Server the tool is connected to gt Update Manager For internal purposes only Manage the list of entities you can manage via CB Server Administration Tool gt Connectors Allows you to manage install modify uninstall CB Connectors which are installed on the CB Server gt G
17. the CB Administration Tool by clicking the Windows Start button then go to AII Programs choose the folder Connect Bridge then choose Connect Bridge Server Administration Tool After startup the following dialog appears see Figure 3 Admin Tool Login Screen You need to provide the host name or IP address where your CB Server is installed Server the Username its Password and the Port for communication via CB ODBC Driver the default is 8087 Connect Toc Administrator Password Fort 2087 The default password for Administrator is 1234 Figure 3 Admin Tool Login Screen Hint in this case the CB Server was installed on the local machine therefore the Server entry would be localhost or its IP address 127 0 0 1 The Administrator user is created automatically during CB installation in the system The initial password is shown in the note bellow the password box Upon first login you will be prompted to change the password see Figure 4 Change Password Dialog Change Password First Logon Administrator Old Password LEELEE New Powe Confirm Password Figure 4 Change Password Dialog 2015 06 18 Page 7 56 Connecting fey i Connect Bridge User manual Connecting Software It is highly recommended to change the password of the Administrator user after the first login In order to do that go to Manage then select Users in the left pane then click on
18. the First Nameequals John This would include John Doe but not James Done or Charlene Randolph SELECTSurname GivenName FROMContact WHERESurmame LIKE Do and GivenName John Example Selects the subject of all appointments that start on or later thanAug ist 2012 or end on Aug 31st or sooner SELECTSubject FROMAppointment WHEREStartDate gt 2012 08 01 00 00 01 000 orEndDate lt 2012 08 31 00 00 01 000 6 2 8 FUNCTIONS Standard SQL functions that allow manipulation of data functions_reference function_name parameters_reference function_name parameters_reference 2015 06 18 Page 51 56 Connecting fey t C Tr ware Connect Bridge User manual Connecting Software Function_namerefers an item of the following list of supported functions Upper Converts all characters in a string to upper case Lower Converts all characters in a string to lower case Substring column_reference start Returns a substring of the original index endindex string starting with character number startindex and ending with character number endindex Returns the length of the string Row_number Retrieves a sequential numbering of rows Count Counts all records in a table which fulfill a criteria Scope_identity See insert statement Function parameters are handled in an equivalent manner to stored procedures parameters see Stored Procedures Examples Returns a
19. 1 Administration Figure 11 Account Management b Edit an exisiting account The only difference to a new account is that you have to select the account in the middle pane and click Edit Apart from that the same steps apply as described in a Create a new account c Delete an exisiting account In order to deltete an account select the account in the middle pane and click Delete Warning This will terminate access to the server connected via that particular account for all groups that use this account and subsequently to all users assigned to the respective group 2015 06 18 Page 17 56 Connecting fey sottware Connect Bridge User manual Connecting Software 2 6 Managing Groups CB Groups are organizational units that not only help organize users into functional groups but also provide the link to Accounts That means a Group has permissions for one or more Accounts and Users belong to one or more Groups Groups can be managed in the Groups section of the CB Admin Tool Open the Admin Tool see Figure 12 Group Administration navigate on the left pane 1 down to Manage and Groups then double click on Groups The middle pane Grid 2 will show you all existing Groups Description of columns displayed in the grid 2 6 1 ID CB internal ID of the record 2 6 2 GroupName Represents the name of the group Tip Use the prefix group or GRP_ in order to facilitate differentiation between
20. 27 Query Workflow B DB Execute F5 B DEMO_EX CLOUD Open Script File Execute SQL Command s Open Parameters Window Save To Script File Choose Connection Figure 28 Query Editor Icon Bar One Tab 2 will open per New query command You can alternate between different connections by switching tabs In the Query Editor Icon Bar see Figure 28 Query Editor Icon Bar or 3 in Figure 27 Query Workflow choose one or more of the following options 2015 06 18 Page 35 56 Connecting fey SOTWia re Connect Bridge User manual Connecting Software Icon Description Open Script File Opens a script file that contains one or more SQL Save To Script File Saves the SQL statements currently displayed in the Query Editor Window 4 to a new script file Execute SQL Command s Executes the SQL commands currently displayed in the Query Editor Window 4 Alternatively press F5 to Choose Connection Change the connection the SQL statements currently displayed in the Query Editor Window 4 are executed against Open Parameters Window Opens the Parameters Window see 4c of Figure 25 Query Analyzer Expanded View only needed in case of parameters of stored procedures WARNING It is a common error to choose the wrong connection when executing an SQL statement So when you are receiving an Error 42000 for example you tried to execute a statement against an entity that doesn t exist in the context of the chosen
21. ATE Appointment SET StartDate 2012 01 07 12 30 00 000 EndDate 2012 01 07 16 30 00 000 WHERE Organizer user AND DisplayName Appointment1 AND StartDate 2012 01 07 13 00 00 000 2015 06 18 Page 47 56 Connecting fey t C Tr ware Connect Bridge User manual Connecting Software 6 2 4 DELETE Deletes existing data on a target server It represents the D in CRUD a gt WARNING Unless certain precautions have been set in the accounts setup by the CB Administrator DELETE FROM Entity without a WHERE clause will DELETE ALL DATA in that entity e g DELETE FROM Contact will delete all contacts It is therefore highly recommended to ALWAYS USE A WHERE CLAUSE WHEN DELETING DELETE FROM table_reference WHERE condition_reference Example Remove tasks with the name Taski created on the shown date and time DELETE FROM Task WHERE DisplayName Task1 AND CreationDate 201 2 07 05 08 30 00 000 6 2 5 STORED PROCEDURES Stored procedures are preconfigured sets of commonly used or useful functionalities provided by Connecting Software Please check out the respective Connector Reference Or our registered users section of the developer s corner on our webpage www connecting software com for a detailed description of all available stored procedures EXEC stored_procedure_name parameter_reference parameter_reference stored_procedure_name The name of the stored procedure
22. Cmd new OdbcCommand SELECT FROM Appointment exchangeConn OdbcDataAdapter selectExAppointmentsAdapter new OdbcDataAdapter selectExAppointmentsCmd STEP 2 Provide an appropriate object like a Data Table in C or a ResultSet in JAVA DataTable exAppointmentsDataTable new DataTable Console WriteLine Connecting to Exchange exchangeConn Open STEP 3 Fill the object with data from the source server Console WriteLine Fetching Exchange appointments selectExAppointmentsAdapter Fill exAppointmentsDataTable Console WriteLine Found 0 appointments exAppointmentsDataTable null O exAppointmentsDataTable Rows Count open connection to exchange and process exchange appointments inserting them into sharepoint Console WriteLine Connecting to Sharepoint sharepointConn Open STEP 4 Manipulate the data and or apply a workflow rule in this case check if the appointment is private if not gt insert it into sharepoint foreach DataRow appointment in exAppointmentsDataTable Rows if tappointment IsNull IsPrivate amp amp bool appointment IsPrivate 2015 06 18 Page 40 56 Connecting a software Connect Bridge User manual Connecting Software Console WriteLine Skipping 0 appointment Subject continue bs create a new odbc command for inserting OdbcCommand insertToSharepointCmd new OdbcCommand INSERT INTO Calendar Title
23. DYNAMICS_NAV_2005 Figure 13 Group Management b Edit agroup You can change which accounts the group has access to by selecting the Group in the Middle Pane of the Group Administration Window see Figure 12 Group Administration 2 then click Edit The remaining steps are identical to the ones described in a create a new group c Delete a group You can delete a group by selecting the Group in the Middle Pane of the Group Administration Window see Figure 12 Group Administration 2 then clicking Delete Warning This will cancel the permissions of any user that is a member of that particular group for the accounts assigned to the group 2015 06 18 Page 20 56 Connecting fey ta C Tr ware Connect Bridge User manual Connecting Software 2 7 Managing Users CB Users are either domain users that access Connect Bridge functionality via their host application in client server mode e g a portal or an industry specific ERP solution or they are Service Users that represent the access rights of a particular application or developer e g a program that handles the synchronization workflow of contacts between CRM and NAV Quite often users might ask why there is a separation of Users and Accounts as these follow a similar logic The reason is simple the User represents the person or service that consumes CB services while the account represents the access to a target server via a CB Con
24. ER Media Gateway ODBC Driver UID Demolser ACC CRM20 Figure 25 Query Analyzer Expanded View The Event viewer window 5 shows the history of the executed statements with their properties 4 2 Working with the CB Query Analyzer In order to work with the Query Analyzer the following steps need to be executed 1 s Create one or more new connections 2 Connect to one or more server via the created connections 3 s Execute a CB SQL statement or Stored Procedure 4 View Results Errors in the appropriate Tab in the lower part 4b or 4d of the Query Editor To create a new connection to a Connector server create on New Connection either by the green plus icon or by right clicking any connection and clicking New Connection in the context menu A dialog screen appears that requires entry of a name 9 of the connection string and the connection string itself 2015 06 18 Page 32 56 Connecting Software Connect Bridge User manual Connecting Software Connecting Software recommends to use names that include Name of User Name of Account Name of Connector location of the server e g use DEV_SRV_SP_MYDOMAIN for a development user that accesses a ServiceCB Account of a SharePoint in the local domain called MYDOMAIN or use ADM_JD_EX_CLOUD for an administrative user that accesses exchange in the cloud via the John Doe account if John has previously allowed that A Edit connection Connection name DEMO
25. LSRV_SP_LOCAL Connection string DRIVER Media Gateway ODBC Driver UID DemoUser1 ACC ACC_SERVICEMGW IMPL CORBA PORT 8087 HOST 123 456 789 000 PWD 1234 Shared connection only one ODBC connection will be shared for this instance and it needs to be opened manually Test connection Save Cancel TT Figure 26 New Connection Dialog The Connection string consists of the following parts 1 DRIVER Media Gateway ODBC Driver This is a fixed part do not modify 2 UID username put a valid user name e g DemoUser01 in the sample shown in Figure 26 New Connection Dialog instead of username A user name is valid if it can be seen in the administration tool under the section Users 3 ACC nameofaccount a valid account name e g ACC_SERVICEMGW in the Sample shown in Figure 26 New Connection Dialog instead of nameofaccount An account name is valid if it can be seen in the administration tool under the section Accounts 4 IMPL CORBA PORT 8087 is fixed please do not modify unless you are using a port other then 8087 5 HOST paddressORservername Please put the name of the server you are connecting to e g SharePoint1 or the IP Address of the target server e g 123 456 789 000in the sample shown in Figure 26 New Connection Dialog instead of jpaddressORservername 6 PWD password put the appropriate password for the User instead of password e g 1234in the sample shown in Figure 26 New Conn
26. Refreshes the existing connection Table 3 Connection browser icons Expanding the Tables section will show the entire set of entities available to the user expanding those will show details regarding Columns i e Properties As depicted in Figure 24 Connection browser Tables amp Columns Announcements Attachments and Calendar are Entities from the SharePoint AllDayEvent CategoryValue etc are properties of the calendar entity table The property type can be seen in brackets Boolean for AllDayEventand String for CategoryValue 2015 06 18 Page 30 56 Connecting fey Software Connect Bridge User manual Connecting Software Connection browser Qo a Administration 5 CB CRM fi Tables a account aa Columns Had accountcategornrcode Ma String 2 accountclassificationcode MaString HE accountid Ma 5tring accountnumber Mg String accountratingcode MgString 2 Ha address 1_addressid Mg String address 1_addresstypecode MagString HE address 1_ city Mg String 2 Had address 1_ country MgString 2 Ha address 1_county MgString address 1_fax Mg String HB address 1_freighttemscode Ma Sting fi address 1_latitude MgDouble Ha address 1_line1 MgString HE address 1_line Mg String fi address 1_line3 MgString 2 Had address 1_longitude Ma Double address 1_name MaString Figure 24 Connection browser Tables amp Columns The Query Editor 4a
27. Users and Groups By default CB creates a standard group called group_administrators 2015 06 18 Page 18 56 Connecting fey Connect Bridge User manual Connecting Software Gs C r t Eride Administration Help Olo S Figure 12 Group Administration The following actions can be performed on with a Group a Create a new group Click on New button on the right pane 3 A dialog window appears see Figure 13 Group Management 1 Group Name The name of the group for naming see the tip above 2 Select the accounts you wish to give this group access to by clicking on them in the Account Detail Window 3 Click on the Arrow Right Button to add them to the list If you wish to add all accounts there is no need to select them all Just use the Double Arrow Right Button Arrow Left Button removes a single account Double Arrow Leftremoves all Double Arrow Right Button Arrow Right Button Arrow Left Button Double Arrow Left Button Table 1 Group Selection Arrows 2015 06 18 Page 19 56 Connecting fey Software Connect Bridge User manual Connecting Software 4 When you have finished the configuration click the Save button in order to apply these settings El Grou pAdministration Group Group Name group _ administrator ACCOUNT EXCHANGE ACCOUNT_ADMINISTRATION ACCOUNT OUTLOOK ACCOUNT EXCHANGE 2010 ACCOUNT_DYNAMICS_CRM_2011 ACCOUNT SHAREPOINT _2010 ACCOUNT_
28. any extensions e g MGWNAVPlugin 3 Click the ellipsis button 4 browse to the Plugins Directory and select the appropriate dll file e g MGWNAVPlugin dll 5 Then click Save 6 see Figure 9 Connector Installation 2015 06 18 Page 13 56 Connecting fey I Connect Bridge User manual Connecting Software E Connect Bridge Administration Tools Version 4 2012 3912 Administration Monitor Connected To 127 001 Administration Help Figure 9 Connector Installation Now you need to request a license for the newly installed Connector Please make sure that you describe in the message body which Connector exactly you want the license for 3 Uninstall a Connector In order to uninstall a Connector select the Connector in the middle pane grid then click Delete Delete does not remove the Connector from the computer it merely makes it unavailable to the CB If you want to delete it completely delete it from the Plugins installation folder usually C Program Files Connecting Software Connect Bridge CbServerService Plugins Should you accidentally delete a Connector you can add it again Same procedure as installation but you need to reconfigure the accounts as they will have lost the reference 2015 06 18 Page 14 56 Connecting fey Software Connect Bridge User manual Connecting Software 2 5 Managing Accounts CB Accounts are used to access a target server like MS Exchange or
29. are com In order to implement our scenario and handle any other workflow the developer needs to follow these steps 1 Create one ODBC JDBC Web Services connection to each target server Provide an appropriate object like a DataTable in C or a ResultSet in JAVA Fill the object with data from the source server Manipulate the data and or apply a workflow rule INSERT or UPDATE the Data in the target server o WS o N Close connections For simplicity s sake we will hard code connection data in the samples given below In real life Connecting Software however recommends to use app config or properties files and properly encrypt any sensitive information 5 1 1 C sample using System using System Collections Generic using System Data using System Ling using System Text using System Data Odbc namespace AppointmentWorkflowTutorialBasic class Program static void Main string args 2015 06 18 Page 39 56 Connecting fey SOTWia re Connect Bridge User manual Connecting Software STEP 1 Create one ODBC JDBC WebServices connection to each target server OdbcConnection exchangeConn CreateMGWConnection 123 456 789 000 8087 accountExchange demouser password OdbcConnection sharepointConn CreateMGWConnection 123 456 789 000 8087 accountSharepoint demouser password prepare a select command and a data adapter OdbcCommand selectExAppointments
30. atingcode MgString gt a TEN ae E addressl_addressid lt address1_addressid MgString gt feuds EG addressi addresstypecode lt addressi addresstypecode MgString gt a aeii address1 city lt addressi city MgString gt s activi G address1 country lt address1 _country MgString gt SREE ot enerate Delete as i w acti addressi_county lt address1_county MgString gt i a annotat Generate parameterized Insert addressi_fax lt addressi_fax MgString gt HO amuan Generate parameterized Update EER E ana See a appli w B f appointment i 53 asyncoperation E Results ez Messages amp attachment i 4 attributemap J a audit F c bulkdeletefailure 6 0 bulkdeleteoperation B bulkoperation _ 8 9 bulkoperationlog i h FA heinace nt TT gt Figure 29 Auto generate SQL This is particularly useful when INSERT ing or UPDATE ing more elaborate entities The difference between the parameterized versions and the ones without is that the parameterized ones will also auto generate parameters to be used in the parameters window In case of stored procedures 2 auto generate also works by generating eithera parameterized or a non parameterized version of the chosen stored procedure Simply right click on the appropriate one and choose see Figure 30 Auto generate Stored Procedures In case of the parameterized simply add the appropriate parameters inthe Values Column
31. capitalized list of surnames SELECTUPPER Surname FROMContact Returns the characters of the surname starting with character 2 and endign with character 4 e g from Smith it would return mit SELECTSubstring Surname 2 4 FROMContact Returns the length of each surname SELECTLen Surname FROMContact A common use is the combiantion of these 2 select all thecharacters starting with the third to the last in case of Smith it would return ith SELECTSubstring Surname 3 Len Surname ASName FROMContact Returns a sequential number of the row for use in developementlogic SELECTRow_number ASNumber Surname FROMContact Count all folders SELECT COUNT FROM Folder 2015 06 18 Page 52 56 Connecting fey Software Connect Bridge User manual Connecting Software 6 2 9 DATA TYPES The following table provides a list of data types supported by the CB Data Type Description NumericTypes Ss Signed byte Int16 Int32 Int64 Single Double Decimal String any string Boolean trueor false Char Single Character e g a DateTime Data and Time in the format yyyy mm dd hh mm ss 000 e g 2012 08 01 13 15 00 000 Byte array Array of bytes Table 5 Supported Data Types 2015 06 18 Page 53 56 Connecting fey ca C Tr ware Connect Bridge User manual Connecting Software 7 Glossary and Abbreviations 7 1 Glossary Client Server It is
32. distributed application which partitions tasks or workloads between the providers of a resource or service called servers and service requesters called clients Communication port Any software that uses the Internet exchanges data between your computer and other computers connected to the Internet Depending on the type of information exchanged between the software of your PC and the other computers of the Internet specific communication channels also called communication ports are used Communication ports are associated to the numbers e g for SMTP is the port 25 Connection String It is a string that specifies information about a data source and the means of connecting to it It iS passed in code to an underlying driver or provider in order to initiate the connection Daemon In multitasking computer operating systems a daemon is a computer program that runs as a background process rather than being under the direct control of an interactive user In Windows OS daemons are called services Data Source In our meaning it is a database which holds data localhost It is the standard hostname given to the address of the loopback network interface CB Administration Tool It provides functionalities to manage and configure Connecting Software Connect Bridge CB Server Service Controller It controls CB Server Service It allows user to start and stop CB Server Service The tool is installed on the server machine CB Drive
33. e mydomain username in the field User Login 2015 06 18 Page 22 56 Connecting a software Connect Bridge User manual Connecting Software l Connect Bndge Administration Tools Version 1 4 2012 3912 Administration Monitor Connected To 127 0 0 1 fo els Administration Help Gg Servers UserLogin UserName JA ai MgServer SYSTEM System User a ioo Update Manager Be Connectors E User Administration F Groups CA i i User x en Hem Hams P Access Method ac mee administrator 4 User and Password dis Ghent User Login Windows Authentication 8 Data access administrator Password Retype Password SECS CECPKECREKCECCCPREC PCE SPACE CE FCCPT HPS Group Details group administrator i fi Figure 15 Users Management b Edit an existing User By selecting an existing user in the middle pane grid 2 of the Users Administration Window see Figure 14 Users Administration and then clicking Edit changes in the assignment of the user to a particular group or password changes can be done To change a password simply type a new one in the field Password and type it again for confirmation purposes in the Retype Password field then click Save To change the assignments use the arrow key as described in Table 1 Group Selection Arrows c Delete an existing User In order to delete an existing user select the user in the middle pane grid 2 of the Users Administrat
34. ection Dialog 2015 06 18 Page 33 56 Connecting Fey SO TT Connect Bridge User manual Connecting Software Then choose a connection type The Query Analyzer can be operated in 2 modes Mode Description Default Mode Each SQL statement executed will open a new connection to the CB Server The data is retrieved and the connection closed automatically This means saga execution than shared mode but less impact Ee cense PO FONOCGE PCOMMeNOe Shared Connection Each SQL statement opens a connection and executes the statement but doesn t close the connection until the user does it manually This mode has a higher performance than the Default Mode but has a negative impact on the licenses used Recommended Table 4 Connection Modes Click the checkbox underneath the connection string text box in order to enable Shared Connection otherwise Default Mode is automatically chosen see Figure 26 New Connection Dialog Click on the Test Connectionbutton to test the connection An error message will pop up if the connection failed Please check all parts of the connection string also inthe Admin Tool as well as any relevant firewall settings Click on Saveto save the connection string For more details regarding the connection strings please check the respective Connector Reference 4 2 1 Connect To connect to a data sources from QA go to the Connection Browser window expand the corresponding connection or
35. en the Admin Tool navigate on the left pane down to Manage and Connectors then double click Connectors see Figure 7 Connector Administration The Middle Pane Grid will display a list of installed connectors Description of columns displayed in the grid ID CB internal ID PluginEnabled if checked the connector is enabled if unchecked it will be disabled PluginName Name of the CB Connector important for use in the Accounts section PluginPath Designates the installation path of the Connector 2015 06 18 Page 11 56 Connecting fey Connect Bridge User manual Connecting Software Administration Help Sa SS age a Edit f NGOKPugn NPlugns MGO weaned sone O rm Pugns MGCmPugnWGOmPugn d dl O we a aaah a a Ss Figure 7 Connector Administration The following actions can be performed on with a connector 1 Enable Disable a CB Connector Select a connector in Grid and click on Edit button on the right panel in the main window You cannot change any settings of property for the installed connector These settings are part of the connector library configuration and are pre configured settings from the installation The only available option is to enable disable the Connector by ticking the checkbox next to the name 2 Install a new CB Connector The standard CB installation comes with a set of connectors Should you have purchased additional connectors you need to add them first and then request
36. ers like Microsoft Exchange SharePoint or Dynamics CRM either with each other or with third party products Connect Bridge Concept Dynamics CRM Dynamic NAV gt a web f p CRM Connector ERP Connector Connect Bridge Server ir Sharepoint Exchange SharePoint Connector Connector Exchange Server a SEN REST Web Service Connect Bridge Driver Service SEa web dav 0D 0 0 07 WebService Custom For example l Program AX SAP Sage Portals CMS C Java etc DMS BI PBX CRM ERP Workflow Data Transformation amp Integration ey Additional Connectors under www connecting software com Connecting Figure 1 Server to Server The principle is simple As depicted in Figure 1 Server to Server the CB Server connects to standard servers with a proprietary Connector that uses standard interfaces like WebServices REST etc to access entities of the destination server The data integration is maintained this way A custom program then accesses the CB Server either via CB ODBC JDBC or Webservices driver and uses CB SQL to control the integration SELECT FROM Appointments or INSERT INTO Contacts can then be used instead of having to learn the intricacies of the individual interface In the second scenario as depicted in Figure 2 CB Client Server Scenario software vendors of specialised LOB or Portal solutions can use the CB to access functionality of Standa
37. esea eaonencecinens 47 a A DELCE TE sees tenance cgene soar A eicaocamesemectocnonaieanessaeasatnonenoacieeas 48 O22 oS STORED PROCEDURES wristrss ce save wheaetogacenets irae onana anana 48 A VO INS esc v ae tessa at sacar oe ate ome aes gas fete see mee pee ord E 49 6227 CONDITIONS pea ceeesme cee enaeeea rss ceee ene scmaeeasiaassacenet ince cnaseaaumemen ones coanesee 50 6 2 9 UNG TIONS wen saansacneneacarennrcsionecenoannsaeeaenseaneayagagnaaeennsschasssaaungconvens 51 CA IDATA TIPES eae rere ec stn eee tee eae ent ibeses niente epee copa tear cen eenne 53 2015 06 18 Page 2 56 Connecting fey software Connect Bridge User manual Connecting Software 7 Glossary and FD Ole Vl AU OMS tasosccaseenan chan ainintiiadau nA RARA 54 Tid GIOSSAY apsanrtesscnes se E E A A E onan 54 Fil PDO OV AU ONS sprees cytotec noes O enema A EAA enone niin eines eaneeen Guan aint 56 2015 06 18 Page 3 56 Connecting fey software Connect Bridge User manual Connecting Software 1 Overview This guide is designed to give a more in depth view of the Connect Bridge components and how to use them then the Installation amp Configuration Guide The aspects below will be covered by this manual WARNING Former name of the company and product has been modified from CNS Connect Media Gateway to Connecting Software amp Connect Bridge respectively This is why several file names include the abbreviation MG
38. eturns gt lt returns gt 2015 06 18 Page 41 56 Connecting a software Connect Bridge User manual Connecting Software static OdbcConnection CreateMGWConnection string host int port string accountName string userName string password OdbcConnection conn OdbcConnectionStringBuilder connStrBuilder new OdbcConnectionStringBuilder connStrBuilder Driver Media Gateway ODBC Driver connStrBuilder Add ACC accountName connStrBuilder Add UID userName connStrBuilder Add PWD password connStrBuilder Add PORT port ToString connStrBuilder Add HOST host connStrBuilder Add IMPL CORBA conn new OdbcConnection connStrBuilder ToString conn ConnectionTimeout 240 return conn 5 1 2 Java sample import java sql Connection import java sql DriverManager import java sql PreparedStatement import java sql ResultSet import java sql SQLException import java sql Statement public class Program public static void main String args throws SQLException ClassNotFoundException Class forName com cnsconnect mgw jdbc MgDriver STEP 1 Create one ODBC JDBC WebServices connection to each target server String exchangeConnectionString jdbc MgDriver IMPL CORBA ENC UTF 8 HOST 123 456 789 000 PORT 8087 UID demouser PWD password ACC accountE xchange String sharepointConnectionString jdbc MgDriver IMPL CORBA ENC UTF 8 HOST 123 456 789 000 PORT
39. fe Connecting software Connecting Software Connect Bridge User Manual Summary This guide is designed to enable the reader to effectively work with the Connecting Software Connect Bridge CB It will introduce the various tools like the Connect Bridge Query Analyzer the CB Administration Tool the CB SQL Language Reference and many others Document History Version Date Author Changes 1 0 2013 04 12 PAN Document creation 1 1 2015 06 18 MBE Design adaption All rights reserved No part of the document may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose without the written permission of Connecting Software s r o amp Co KG Company or product names mentioned in this document may be trademarks or registered trademarks of their respective companies Connecting fey ca C Tr ware Connect Bridge User manual Connecting Software Table of Contents ED NCW E E PE E E EEE EEE ET 4 tL MOO CUNT MUCUNG o aaa rrr aienea INDE E ENEON ower 4 1 2 Operating Principles ccccceeececeeereeeeereunesscuenssucceeugueeesseceesseseresenes 5 Z Cb AOM Ga Ol TOO errn atc iescnnewer sees yernecntaniedoetseeeesaanesdeseeeonee ecuanen 6 2 1 Connect To CB Serv r scasns2soscxcenacancananer ence caencanenneacicnr canner baeacseareteade 7 2 2 Description of the User Interface Components ccccceee eee eee eee e ee eees 9 23 Managing OOS sicoavieassenteutve gereis
40. file The file is generated by Connecting Software based on your request After validation you can activate the license in your software once you have received it from Connecting Software via Email Show Log By clicking on this button a list with your activity logs will be shown server side logs only 2015 06 18 Page 25 56 Connecting fey Software Connect Bridge User manual Connecting Software 4 The CB Query Analyzer The Connect Bridge Query Analyzer QA is a client tool designed to access the CB Server It can be used to run CB SQL statements against all target servers connected to the CB server via CB Connectors The main purpose of the tool is to test out CB SQL statements before embedding them into proprietary workflow and data transformation code It can be also used to train CB SQL and to get to know the entity model of the connected servers Technically QA operates by parsing the SQL statement or Stored Procedure passes it on to the CB Server via ODBC connection waits for the response and displays the result ee Tip Please make sure you run the CB Query Analyzer with Administrator rights on your local machine In order to do that right click on the Query Analyzer icon click on Properties go to the Tab Shortcuts click on Advanced and then click the checkbox Run as administrator 4 1 Layout of the CB Query Analyzer Developers familiar with common database administration tools will find the layo
41. group_administrators 2015 06 18 Page 21 56 Connecting ey Connect Bridge User manual Connecting Software Er Servers Gi 127 001 Figure 14 Users Administration The following actions can be performed on with a User a Create a new user 1 Open the Admin Tool and double click on Users 2 Click on New button on the right pane 3 A dialog window appears see Figure 15 Users Management 3 Provide a meaningful name one that is used within the CB the user login and password and retype the password 4 Select User and Password if you do not want to use a Domain User or Windows Authentication if you want to use a Domain User and Windows Authentication 5 Select the groups you wish to give this user access to by clicking on them in the Account Detail Window then click on the Arrow Right Button to add them to the list If you wish to add all users there is no need to select them all Just use the Double Arrow Right Button Arrow Left Button removes a single user Double Arrow Left removes all for a description of the arrows please see Table 1 Group Selection Arrows 6 Click on Save In case of User Authentication these can be identical In case of Windows Authentication however it might be practical to use a shorter User Name to avoid having to use the fully qualified domain name every time If Windows Authentication is greyed out you haven t used a properly qualified domain name lik
42. ion Window see Figure 14 Users Administration and then click Delete Warning Once a user has been deleted all connection strings that use the particular user will no longer work Please make sure you contact the people responsible for integrations applications and make sure that they are aware of the deletion before you actually delete 2015 06 18 Page 23 56 Connecting fy SOTWa re Connect Bridge User manual Connecting Software 3 The CB Service Controller A neat little tool that should help you to quickly resolve common administration issues like starting and stopping and checking the status of the server service requesting and activating a license or looking at log files It can be found in the Connect Bridge Folder of the Start Menu Bieter eee a Ba SOL Server Management Studio GHP Microsoft Visual Studio 2010 _ takahashi Zp Paint Documents E Getting Started ED Windows Media Center Calculator Pictures Music Games fey CB Administration Tool Computer La Sticky Notes RQ Snipping Tool Eee Devices and Printers Control Panel riceContro Default Programs Help and Support Figure 16 Connect Bridge Folder in Windows Start Menu Once started it will appear in the System Tray on the lower right hand side where either a double click which will show the dialog depicted in Figure 18 CB Service Controller Window or a right click on the Connecting Software Icon
43. ionship between certain columns in these tables The Join operations are supported only if there is an explicit constraint defined between the tables The left table stands before JOIN the right table behind it Returns all rows from the left table even if there are no matches in the right table Returns all rows from the right table even if there are no matches in the left table Returns rows if there is at least one match in both tables If there are rows in the left table that do not have matches in the right table those rows will NOT be listed and vice versa 2015 06 18 Page 49 56 Connecting fey ta C Tr ware Connect Bridge User manual Connecting Software Examples Select all tasks with their task priority SELECT Subject Body StartDate DueDate FkTaskPriority AS Priority FROM Task LEFT JOIN TaskPriority ONTask FKTaskPriority TaskPriority ID Select all appointments even if no importance is assigned tothem SELECT DisplayName Body FKAppointmentImportance FROM AppointmentImportance RIGHT JOIN Appointment ON Appointment ID AppointmentImportance ID CRM sample Selects last name amp first name of all contacts thatare attached to an account SELECT lastname firstname FROM contact AS c JOIN account AS aON c parentcustomerid a accountid 6 2 7 CONDITIONS Refers to conditional values used in a WHERE clause condition_reference column_reference operator_reference value_reference
44. n of log events W Event Viewer W File Message Types W Log Eror Messages W Log Info Messages Log Wam Messages E Log Debug Messages Figure 6 Log Dialogs On the tab Generalthe group Destination of log events allows to select wherethe logged events should be saved to Options include either the Windows Event Viewer or a physical file that can be found in the MgServerService folder of the CB installation usually C Program Files Connecting Software Connect Bridge CbServerService logOutputfile txt The group Message Types allows to specify what kind of messages should be logged Log Error Messages CB errors will be logged Log Info Messages All information messages will be logged Log Warn Messages All warning messages will be logged Log Debug Messages Full debugging info from the CB Server will be logged This choice produces the most detailed output but also represents the biggest demand on the computer resources The Users tab allows you to decide for which users these logs should be stored All potential users are shown in the list Available By selecting one or more and clicking on the Arrow Right Button the respective user is chosen and therefore included in the logging During the installation of the CB user Administrator is created and automatically moved to the list box Selected Thus his actions on the server will always be logged 2015 06 18 Page 10 56 Connecting fey ta C Tr ware Co
45. nector While there are cases where users and accounts will be identical e g when a user accesses his exchange mailbox via the Connect Bridge from his ERP system there are many cases where solution providers want to differentiate between the user that accesses the CB and the account that accesses the target server e g when a teamsite owner in SP starts a synchronization workflow with CRM where it has no access rights In order to allow for maximum flexibility in the implementation of such integration solutions Connecting Software has decided to differentiate between users and accounts a a lt Tip If you need help to implement a security and role model that complies with your company s compliance rules please contact Connecting Software or a certified integration partner for consulting support Users can be managed in the Users section of the CB Admin Tool Open the Admin Tool see Figure 14 Users Administration navigate on the left pane 1 down to Manage and Users then double click on Users The middle pane Grid 2 will show you all existing Groups Description of columns displayed in the grid 2 6 3 ID CB internal ID of the record 2 6 4 UserLogin The login name of the user usually the domain name 2 6 5 UserName The name that is used in the connection string can be identical with UserLogin By default CB creates a standard user called administrator this user is automatically assigned to the default
46. nnect Bridge User manual Connecting Software When finished click on the OK button You are asked to restart the Connecting Software Connect Bridge Service in order to apply the new settings From that point on the logging behaves according to the new configuration O Warning Extensive logging can create significant amounts of log files It can also be a substantial drain on the CB server resources thus slowing it down Connecting Software therefore recommends to use minimal logging during normal operations and extended logging only when trying to find errors 2 4 Managing Connectors As described in the introduction the CB Server uses CB Connectors to connect to third party products like MS Exchange or MS SharePoint This enables users of the CB to access the full functionality of the respective server without having to cope with the intricacies of each individual interface A significant amount of CB Connectors is already available including but not limited to 2 4 1 MS Exchange 2 4 2 MS SharePoint 2 4 3 MS Dynamics CRM 2 4 4 MS Dynamics NAV 2 4 5 etc Connecting Software and partners are constantly working to expand the number of available connectors Please check our webpage www connecting software com or contact a Connecting Software representative or partner to get an updated version of the CB Connector list Connectors can be installed managed and uninstalled in the Connector section of the CB Admin Tool Op
47. nnection string optionally click on Test connectionand click Save By clicking on the Button next to the name of the connection the tree is expanded a connection established and the user can take a more in depth look at Tables i e Entities and Stored Procedures available Paw E connect Bridge Query Analyzer File View Options Help B New query 8 Connection browser Query history Connection browser a xX OPO BAS e Connections oe Administration CB CRM al CB EXCHANGE CB SHAREPOINT a CB NAV Figure 21 Connection Browser Tree Expanded 2015 06 18 Page 28 56 Connecting Software Connect Bridge User manual Connecting Software If the CB was able to establish a connection a green tick mark is displayed next to the name of the connection on top of the database icon see CB CRM in Figure 21 Connection Browser Tree Expanded If the connection fails a red Do not enter sign is displayed see CB EXCHANGE in Figure 21 Connection Browser Tree Expanded In that case right click on the connection and click on Show Error Connecting Software recommends to use the following syntax in order to name a connection USR_ACC_SRV_DOMAIN E g DEV_SVC_SP_CLOUD would be a developer user using the standard CB service Account connecting to a SharePoint server in the CLOUD Office365 In case you cannot find a solution for the error yourself please check the support page of ou
48. of the lower section of the Query Editor Window see Figure 19 Query Analyzer Standard View 4b and Figure 25 Query Analyzer Expanded View 4d It might also be useful to check the Event Viewer Window see Figure 25 Query Analyzer Expanded View 5 by clicking on the Event viewer button see Table 2 Icon bar icons For a full reference of available stored procedures please check the respective Connector reference orthe registered users section of our developer s corner on www connecting software com The parameterized versions of the SQL statements are particularly useful when the statements are only tested in the QA and meant to be inserted into custom codelater 2015 06 18 Page 38 56 Connecting fey Software Connect Bridge User manual Connecting Software 5 Workflow Development Tutorial The following sample gives a quick tutorial on how to access the CB Server from your own code Samples are given in C and JAVA but any programming language that can use ODBC JDBC or Web Services can be used 5 1 Scenario In the given scenario we will demonstrate a simple workflow by showing how to insert an appointment from MS Exchange the source server into MS SharePoint the destination server based on a condition Flag Private false In this example we are using ODBC for C and JDBC for JAVA For Web Services please refer to the registered users section of developer corner on our webpage www connecting softw
49. r One of following three drivers CB ODBC Driver CB JDBC Driver CB Web Service Driver 2015 06 18 Page 54 56 Connecting fey ia C Tt ware Connect Bridge User manual Connecting Software CB JDBC Driver JAR file created by Connecting Software Connect Bridge developers in the java programming language It communicates with the CB Server via TCP IP protocols CORBA mechanism is used for communication between CB JDBC Driver and CB Server Service processes CB ODBC Driver DLL library created by Connecting Software Connect Bridge developers in C C It communicates with the CB Server via the TCP IP protocols CORBA is used as a communication mechanism CB Connector The connector which provides the access to one or more of external data sources CB Server Server component of CB CB Server Service It is a daemon running in the Windows system as a service where Connecting Software Connect Bridge is installed It listens for SQL statements from client application processes the statement and provides a result usually requested data CB Web Service Driver CB Web Service Driver is installed on the server machine and it runs under the Internet Information Server IIS It provides API interface accessible via Proxy Web Service to the Client Application Query Analyzer It is a client tool designed to access data provided by Connecting Software Connect Bridge SQL statement It is a SQL language element which may have
50. r website www connecting software com or contact your support representative Connect Bridge Query Analyzer io File View Options Help E New query 88 Connection browser Query history Connection browser ax O7e Bas 8 Connections 9 Administration CB CRM 2 1 CB EXCHANGE CB SHAREPOIN Show error H CB NAV New connection Edit connection Delete connection amp ONO Connect Disconnect Refresh amp Figure 22 Connection Error 2015 06 18 Page 29 56 Connecting fey SOTWia re Connect Bridge User manual Connecting Software The following commands are available in the Connection browser the same commands are also available through the context menu via right click on the connection see Figure 22 Connection Error see Figure 23 Icons Connection browser Connection browser Oo Q Create a new connection Refresh Edit selected connection Disconnect Delete selected connection Connect Figure 23 Icons Connection browser Icon Description Create a new connection Opens the dialog window to add a connection string to create new connection to a server Edit selected connection Opens the dialog window of the selected connections and allows modification of the connection string Delete selected Deletes the selected connection Connect Connects to the Connector server designated in the connection strina Disconnect Disconnects the exiting connection Refresh
51. rd Servers quickly safely and effectively A typical example would be to access Exchange accounts from within the Portal or obtain CRM contacts from within an industry specific LOB 2015 06 18 Page 5 56 Coonecung te Connect Bridge User manual Connecting Software Your Software Client Application Figure 2 CB Client Server Scenario For more details on how to connect to the CB Server with custom applications please check the Workflow Tutorial in this manual and visit the Developer s Corner on our webpage www connecting software com 2 CB Administration Tool The CB Server Administration Tool provides functionalities to manage and configure the Connect Bridge The tool provides a friendly Graphical User Interface GUI capable of managing and configuring the entire CB Server installed on a local PC or on a remote server It uses simple query statements and specific System Stored Procedures to provide all management features in the Connect Bridge It is a client tool which communicates via CB ODBC Driver with the internal CB Administration Connector of the CB Server The CB Administration Connector is the CB module responsible to communicate with the internal database system therefore providing all mechanisms to interact with it including security performance and scalability 2015 06 18 Page 6 56 Connecting fey sottware Connect Bridge User manual Connecting Software 2 1 Connect To CB Server Start
52. roups Allows you to manage groups of users and their access to accounts gt Accounts Allows you to manage CB Accounts that access servers via connectors gt Users Allows you to manage Users gt Licenses Allows you to manage Licenses gt Data Providers Allows you to manage Data Providers local or remote Description of the Menu Items Administration gt Connect To Allows you to establish a new connection to a CB Server local or remote gt FTP Manager Offers the possibility to transfer files via ftp to the CB Server from a client machine connected to the CB Server Help gt About Connect Bridge Administration Tool Provides information about the Connect Bridge Administration Tool and CB Connectors gt Request new license Allows you to request a new Connecting Software Connect Bridge license from Connecting Software gt Activate license Allows you to activate the Connect Bridge license provided by Connecting Software 2015 06 18 Page 9 56 Connecting fey SOTWia re Connect Bridge User manual Connecting Software 2 3 Managing Logs You can choose the output location of the log files from the Navigation Panel expand Servers then expand a server you are connected to e g localhost and click on Logs Figure 6 Log Dialogs depicts the two screens available within the Log Dialog General Eii Select the users that will attempt to log Selected administrator Destinatio
53. select connection name and click on icon Connectin the Connection Browser toolbar above the list of Connections After the connection has been established you will see a green checkmark on the left next to the name of the connection Should the connection fail a red amp white Do not enter icon will appear In that case please right click on the connection and click on Show Errors The first time you connect to a new server the connection will take a bit longer to be established as the entire schema is downloaded for the first time so please be patient 2015 06 18 Page 34 56 Connecting fey software Connect Bridge User manual Connecting Software 4 2 2 Execute a CB SQL statement or Stored Procedure Once a connection has been established click on the connection in the Connection browser and then click on the button New query 1 in Figure 27 Query Workflow Alternatively simply click on the New query button and choose the appropriate connection from the Connection Window see Figure 28 Query Editor Icon Bar E connect Bridge Query Analyzer g koea Fie View i Help 1 ction browser g Query history AX _ sqiQuery_1 CB CRM x a B E Eecute tfs B cecrm k i select from Contact H E Administration mi c e CB CRM Lg 541362824017 e37ida34 8250 4 WELE 30eade7c07a1 NUL d648fe11 3afc4 address _addressk address address address 1_city Figure
54. supported by the database You can get a list of all supported stored procedures by execution of following SQL statement EXEC SPSYSODBCPROCEDURES To get a list of parameters for some stored procedures use following statement EXEC SPSYSODBCPROCEDURECOLUMNS stored_procedure_name 2015 06 18 Page 48 56 Connecting fey a C Tr ware Connect Bridge User manual Connecting Software parameter_reference parameter_reference parameter_reference Refers to the comma Separated list of stored procedure parameters referenced by value Use single quotes to denote a parameter value Make sure that the data type matches the expected data type e g string int etc Example Forward a sent mail message with an existing ID to a user with the mail address userTo domain com and prefix the subject of the mail withthe string FWD EXEC SP_FORWARD_MESSAGE lt CHANGE THIS TO EXISTING Message ID gt body FWD userTo domain com 6 2 6 JOINS Joins are meant to combine information from several tables based on conditions They are needed whenever lookup tables are used or when trying to filter information based on conditions on other tables e g finding all contacts in CRM that are a member of a certain account join_reference INNER LEFT RIGHT JOIN table_reference ONcondition_reference The JOINKeyword is used in an SQL statement to query data from two or more tables based on a relat
55. tify the correct names of Tables and Columns for your statements Note All SQL Examples are written for the MS Exchange Connector unless otherwise designated 6 2 Supported Data Manipulation Statements 6 2 1 SELECT Reads the data from the connected server It represents the R in CRUD SELECT column_reference function_reference FROM table_reference ioin reference WHERE condition reference ORDER BY column_reference ASC DESC LIMIT number OFFSET number column_reference column_reference AS alias P column_referenc AS alias Refers to the name of a column or its alias i e shortcut Use commas to separate columns A column must be part of the table chosen in table_reference If the column name conflicts with an SQL keyword use brackets e g from An Alias is a string that can be sued instead of the column name and as an abbreviation function_reference see chapter 6 2 7 FUNCTIONS table_reference refers to the name of the table chosen CRUD is a general term denoting the most common commands in data manipulation Create Read Update Delete 2015 06 18 Page 45 56 Connecting fey ta C Tt ware Connect Bridge User manual Connecting Software join_reference see chapter 6 2 5 JOINS condition_reference see chapter 6 2 6 CONDITIONS ORDER BY defines the sort order of the returned list according to the given priority rule for ordering first column has the highest priority
56. tote nirean er Aran nan NE sauce 10 24 Managing ConNnNeclorS oasearere enen anna REE 11 2 3 Managing ACCOUNTS cneenecenatorcnutenctcces eee hin in aeae ei ra nii 15 AG Managing ASVOUDS eier SEO 18 2 7 Managing WISCES sundress onscadaceemceeeeoees iat iier a a E ENE EE 21 3 The CB Service Controller s ssssssssssusnnnnnnnnnnnnnnnnnnnnnnnnnnnnnunnnnnunnnnnnnnnnnnnnnnnnnnnnnn 24 A IPO CE OUST ANAIYZOT seren riniri ninne Geneesasatene sain aeraewennanapinasaaene rE a 26 4 1 Layout of the CB Query AnalyZer sssssssnsssrsnnnrrsnnrrnrnrrrrnrnrrnrrerrenernne 26 4 2 Working with the CB Query Analyzer cccccccc cece eee e teen eeeeeeeeeaaaaeeaaaas 32 A2 Gog aie nee ne re ee ne re ener ene er eee nn er ee 34 4 2 2 Execute a CB SQL statement or Stored Procedure ccecceeeeeeeeeeees 35 BZ VIEW Res lts ErrOlS ceeuewaun nse iasemenaces axatiaatacna neue wendeasmsesnieeesean ee eanined 38 5 Workflow Development Tutorial sssnsssssnnrnnnnnrnnnnrnnrnnrrnnnnrrnnrrnnnnrrnnennrnnennrrnnnnnne 39 F O NANO aa E A 39 i ee Sea OF OE E i EA AR T ahi 39 LAING O ar AI A season 42 CCB SOE Reema O erre EE EEEE E eerie 45 6 1 General SQL Syntax Schema sssssssssssnnrsnrnrnenerrnennrrnrnnrrnnrnrrnnrrnnrnrrne 45 6 2 Supported Data Manipulation Statements sssessessersrrerrerrnrnrrerrererrne 45 Ca De ELEC e E a a eee 45 622 INSER To E eee see ee tae sees cree 47 7 UPDATE arses sapere oon cone onense tress I E RR E E d
57. ut of the QA quite familiar Connecting Software has strived to make working with QA as easy and straightforward as possible The following items are shown in the default view EB Connect Bridge Query Analyzes Co a File View Options Help E New query Connection browser i Query history 2 orroaz BF A E Eecute Fs B cecrm Connections 5 i select from contact Administration 3 RESICE CRM 3 B E Tables e account pE accountleads p o activiymimeatachmen p activitypaty p e activitypointer m a asyncoperation i ti accounirolecode address _addressx address _addresst address 1_city tL 54 13h 82 4c 17 4 S aude ul NUL e37da34 8250 4 weit h 0 bulkdeletefaiure wou LAL Meade 7c O7a 1 gy Atlanta H E bulkdeleteoperation VEL NULI deife 1 3afe A T H E bulkoperation VELI MULI Fie30d1 82834 _ AUL Figure 19 Query Analyzer Standard View 2015 06 18 Page 26 56 Connecting fey software Connect Bridge User manual Connecting Software The Menu Bar 1 a standard menu bar contains the following items Level 1 Level 2 Level 3 Description File New COLTS Ty ine generate a new ule against a connection Connection To create a new connection to a Server Open Script File Opens a text file that Contains pre generated Sole Baleemenire Close Closes the application View Connection Shows and activates the browser Connection browser Event viewer
58. will give you access to the provided functionality see Figure 17 CB Service Controller Icon If you cannot find the Service Controller you did not install the server components of the CB Please install or connect remotely to the machine where they are installed In case the Complete installation option was chosen the service should run automatically 2015 06 18 Page 24 56 Connecting fey software Connect Bridge User manual Connecting Software Start Service Stop Service Request License Activate License Exit En Customize Figure 17 CB Service Controller Icon Connect Bridge Service Controller x Start Stop Request License Co n n ect LA B rl d ge n License Show Log Figure 18 CB Service Controller Window The features provided by the CB Service Controller are gt Startbutton to start the CB Server Service gt Stopbutton to stop the CB Server Service gt Request License By clicking on this button you can send a license request to Connecting Software You need to define the location where the license file has to be stored in your computer In case you do not have a default mail application configured you will receive a message indicating that you will have to send the request manually via your preferred mail client to the Connecting Software support team Activate License By pressing this button you will be asked to provide the location of the license
Download Pdf Manuals
Related Search