CRM Intergraion: INSERT statement now working

[rsp#24]

Hello,

We have CRM integration in the 3CX. Our system got updated with v18 9A patch automatically and insert statement (which use for Call journaling) stopped working. But, Lookup By Number SQL Statement is working fine.

https://www.3cx.com/blog/releases/v18-update9a/

Also, we tried with updated query suggested in the below post but it is not working.

https://www.3cx.com/blog/news/sql-database-integration-update/

Please help to resolve this problem!

Thank you in advance!

 

[edossantos_sipcaller]

You will need to check the 3cxSystemService.log in verbose mode to understand what's the error in the SQL statement you're using.

 

[rsp#24]

You will need to check the 3cxSystemService.log in verbose mode to understand what's the error in the SQL statement you're using.

Thank you! So, we have the below error in the log file.

Exception during call reporting: MySqlConnector.MySqlException (0x80004005): Data too long for column 'CallTime' at row 1
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 966
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 43
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 137
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 468
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 345
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 337
   at Integration.Crm.Engine.SqlDatabaseClient.Execute(String statement, SqlQueryParameter[] parameters, CancellationToken token)
   at Integration.Crm.Engine.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, SqlQueryParameter[] queryParameters, CancellationToken token)
   at Integration.Crm.Engine.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, SqlQueryParameter[] sqlQueryParameter, CancellationToken token)
   at Integration.Crm.Engine.ScenarioProcessorBase.Execute(ProtocolScenario scenario, SqlQueryParameter[] sqlQueryParameter, CancellationToken token)
   at Integration.Crm.Engine.CrmProcessor.ExecuteScenarioByName(String scenarioName, IEnumerable`1 parameters, Boolean useNoAuthProvider, CancellationToken token)
   at Integration.Crm.Engine.CrmProcessor.ExecuteScenarioWithRetries(String scenarioId, Dictionary`2 parameters, CancellationToken token)
   at Integration.Crm.Engine.CrmProcessor.ReportCall(CallEndedEvent callEndedEvent, CancellationToken token)

 

[rsp#24]

Anyone know how to format datetime in the INSERT statement?

For example, we have data like below and datatype is VARCHAR,

5/23/2024 2:21:16 PM

It is storing datetime like this

2024-05-23 02:21:16.965790

It is not given on this webpage

https://www.3cx.com/blog/news/sql-database-integration-update/

 

[rsp#24]

I solved that issue. I used below SQL query.

DATE_FORMAT(@CallTime, '%c/%e/%Y %l:%i:%s %p')

 

[edossantos_sipcaller]

There are many ways to convert a timestamp to a string in MySQL. You should check the MySQL documentation to find what's best for your case. For example, an option could be:
DATE_FORMAT(@CallStartTimeUTC,'%Y-%m-%d %k:%i:%S')

More details on formatting tokens here:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

 

[rsp#24]

Thank you so much