MySQL Integration causes an Exception

[PromanUser]

hi
i have integrated my CRM over MySQL. all is working good beside the call reporting:
here the according xml scenario

<Scenario Id="ReportCall" Type="SQLDatabase" EntityId="" EntityOrder="">
<Query SkipIf="[ReportCallEnabled]!=True||[ReportCallSQLStatement]==&quot;&quot;||[EntityId]==&quot;&quot;" DatabaseType="MySQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];Uid=[Username];Pwd=[Password];charset=utf8mb4;Allow User Variables=true;" StatementPasses="2" Statement="[ReportCallSQLStatement]" />
<Variables />
<Outputs AllowEmpty="true" />
</Scenario>

in the Log 3cxSystemService i have this exception

2025/04/07 13:26:07.254|0011|Debg| [Npgsql.Connection] Closed connection to 127.0.0.1:5432/database_single
2025/04/07 13:26:07.255|0067|Erro| [Integration.Crm.Engine.CrmProcessor] CRM: Exception during call reporting
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.
at TcxLexicalParser.LexEvaluator.Or(IValueManager valueManager, Element arg1, Element arg2)
at TcxLexicalParser.LexEvaluator.ProcessInt(FunctionElement functionElement, IValueManager valueManager)
at TcxLexicalParser.LexEvaluator.Process(Element element, IValueManager valueManager, Boolean isFunctionArgument)
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)

what can be the problem?
i'm using currenlty for testing a dummy sql

INSERT INTO calls (id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, duration_hours, duration_minutes, date_start, date_end, parent_type, parent_id, status, direction, reminder_time, email_reminder_time, email_reminder_sent) VALUES (UUID(), '3CX Journaling Test', NOW(), NOW(), '1', '1', 'Test call log entry from 3CX template.', 0, '1', 0, 1, NOW(), NOW(), NULL, NULL, 'Held', 'Inbound', -1, -1, 0);
my Version of 3cx is 20.0 release 6

3cx version 20.0 release 6

 

[Evolute IT]

You're database is expecting a boolean somewhere and you're sending a string. The query is prepared so it has to be valid. Try it in your SQL database directly to get an actual error.

 

[PromanUser]

i'm using a dummy dump sql statement

INSERT INTO calls (id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, duration_hours, duration_minutes, date_start, date_end, parent_type, parent_id, status, direction, reminder_time, email_reminder_time, email_reminder_sent) VALUES (UUID(), '3CX Journaling Test', NOW(), NOW(), '1', '1', 'Test call log entry from 3CX template.', 0, '1', 0, 1, NOW(), NOW(), NULL, NULL, 'Held', 'Inbound', -1, -1, 0);

in the statement there is not conversion ... nothing.. i can execute it in my sql console without any problem.

 

[edossantos_sipcaller]

Your SkipIf attribute is the problem here:
SkipIf="[ReportCallEnabled]!=True||[ReportCallSQLStatement]==""||[EntityId]=="""

You can't use the OR (||) operator with 3 arguments. Only 2 arguments are allowed. You need to rephrase that with nested IIF function calls.

 

[PromanUser]

thank you for your fast answer, i changed the scenario to:

<Scenario Id="ReportCall" Type="SQLDatabase" EntityId="" EntityOrder="">
<Query SkipIf="[ReportCallEnabled]!=True" DatabaseType="MySQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];Uid=[Username];Pwd=[Password];charset=utf8mb4;Allow User Variables=true;" StatementPasses="2" Statement="[ReportCallSQLStatement]" />
<Variables />
<Outputs AllowEmpty="true" />
</Scenario>

but i stil get the error

2025/04/07 15:59:42.047|0114|Debg| [Npgsql.Connection] Closed connection to 127.0.0.1:5432/database_single
2025/04/07 15:59:42.048|0109|Erro| [Integration.Crm.Engine.CrmProcessor] CRM: Exception during call reporting
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.
at TcxLexicalParser.LexEvaluator.Or(IValueManager valueManager, Element arg1, Element arg2)
at TcxLexicalParser.LexEvaluator.ProcessInt(FunctionElement functionElement, IValueManager valueManager)
at TcxLexicalParser.LexEvaluator.Process(Element element, IValueManager valueManager, Boolean isFunctionArgument)
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)
2025/04/07 15:59:42.079|0109|Trac| [Npgsql.Connection] Socket connected to 127.0.0.1:5432

what can it still be?
thank you for any advice

 

[edossantos_sipcaller]

The error you get now is something else. Are you trying to connect to the 3CX database? That's not allowed, you shouldn't do it.

 

[PromanUser]

no this connection error i have on all calls to the ReportCall (but not to the other scenarios

the parameters for the connection are always the same

<Parameters>
<Parameter Name="Server" Type="String" Parent="General Configuration" Editor="String" Title="Server:" Validation="" Default="192.168.190.xx" ListValues="" RequestUrl="" RequestUrlParameters="" ResponseScenario="" />
<Parameter Name="Port" Type="String" Parent="General Configuration" Editor="String" Title="Port:" Validation="" Default="3306" ListValues="" RequestUrl="" RequestUrlParameters="" ResponseScenario="" />
<Parameter Name="Database" Type="String" Parent="General Configuration" Editor="String" Title="Database:" Validation="" Default="dbuser" ListValues="" RequestUrl="" RequestUrlParameters="" ResponseScenario="" />
<Parameter Name="Username" Type="String" Parent="General Configuration" Editor="String" Title="Username:" Validation="" Default="cdpw" ListValues="" RequestUrl="" RequestUrlParameters="" ResponseScenario="" />
<Parameter Name="Password" Type="Password" Parent="General Configuration" Editor="String" Title="Password:" Validation="" Default="dbpw" ListValues="" RequestUrl="" RequestUrlParameters="" ResponseScenario="" />

i was more thinking that 3cx was trying to connect locally to it's postgress db..

 

[edossantos_sipcaller]

Now that I see the error in more detail, the log line related to the 3CX database is just something from 3CX wrote to the log before the error, but not related to it. So the error is still this:
Unable to cast object of type 'System.String' to type 'System.Boolean'.

And happens in the context of the execution of an OR function, which is weird as I don't see that anywhere.... Maybe your template was not updated? If you just replaced the file in the server, try restarting the 3CX System Service service after hours, so it can reload the new template.

 

[PromanUser]

thank you
i restarted the system .. no change
i changed the config to remove the Skipif

<Scenario Id="ReportCall" Type="SQLDatabase" EntityId="" EntityOrder="">
<Query SkipIf="" DatabaseType="MySQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];Uid=[Username];Pwd=[Password];charset=utf8mb4;Allow User Variables=true;" StatementPasses="2" Statement="[ReportCallSQLStatement]" />
<Variables />
<Outputs AllowEmpty="true" />
</Scenario>

the change is visible if i view the template in the 3cx management interface.

but i still have the same exception

2025/04/08 09:39:44.239|0012|Erro| [Integration.Crm.Engine.CrmProcessor] CRM: Exception during call reporting
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.
at TcxLexicalParser.LexEvaluator.Or(IValueManager valueManager, Element arg1, Element arg2)
at TcxLexicalParser.LexEvaluator.ProcessInt(FunctionElement functionElement, IValueManager valueManager)
at TcxLexicalParser.LexEvaluator.Process(Element element, IValueManager valueManager, Boolean isFunctionArgument)
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, CancellationToke

can this be an error in 3cx with mysql and the scenario ReportCall?
the other scenarios are working fine wiht the connection string ..

 

[edossantos_sipcaller]

What have you configured as [ReportCallSQLStatement]?

 

[PromanUser]

i the variabe i had this (a dummy sql statement) (inserted in the Management Console CRM Field for Call Journaling SQL Satement:

INSERT INTO calls (id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, duration_hours, duration_minutes, date_start, date_end, parent_type, parent_id, status, direction, reminder_time, email_reminder_time, email_reminder_sent) VALUES (UUID(), '3CX Journaling Test', NOW(), NOW(), '1', '1', 'Test call log entry from 3CX template.', 0, '1', 0, 1, NOW(), NOW(), NULL, NULL, 'Held', 'Inbound', -1, -1, 0);

i changed it now this way so see if it's working better.

<Scenario Id="ReportCall" Type="SQLDatabase" EntityId="" EntityOrder="">
<Query
SkipIf="[ReportCallEnabled]!=True"
DatabaseType="MySQL"
ConnectionString="Server=[Server];Port=[Port];Database=[Database];Uid=[Username];Pwd=[Password];charset=utf8mb4;Allow User Variables=true;"
StatementPasses="2"
Statement="INSERT INTO calls (
id, name, date_entered, date_modified, modified_user_id, created_by,
description, deleted, assigned_user_id, duration_hours, duration_minutes,
date_start, date_end, parent_type, parent_id, status, direction,
reminder_time, email_reminder_time, email_reminder_sent
) VALUES (
UUID(), '3CX Journaling Test', NOW(), NOW(), '1', '1',
'Test call log entry from 3CX template.', 0, '1', 0, 1,
NOW(), NOW(), NULL, NULL, 'Held', 'Inbound', -1, -1, 0
);"
/>
<Variables />
<Outputs AllowEmpty="true" />
</Scenario>

but i still get the same exception

 

[edossantos_sipcaller]

The error you get happens while evaluating an expression. There are 3 expressions evaluated during a database scenario execution:
1) The SkipIf attribute.
2) The ConnectionString attribute
3) The Statement attribute

Try checking what you're specifying for any of those fields.