Exception when using database (mysql) CRM integration with contactID that are mistaken for integer types

[bchow]

It seems that somewhere in the 3cx parsing of results returned from our database query in the MySQL Database CRM integration, a string is being parsed as an integer. Is there a way to force the datatype by modifying the MySQL template?

Error is:
Started scenario with empty id
Request text: ConnectionString: Server=[valid connection string]
Statement: SELECT contactid, firstname, lastname, phonemobile, phonehome, phonebusiness, phoneother, faxbusiness from [custom view name] WHERE REPLACE(REPLACE(REPLACE(REPLACE(phonemobile, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%' OR REPLACE(REPLACE(REPLACE(REPLACE(phonehome, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%' OR REPLACE(REPLACE(REPLACE(REPLACE(phonebusiness, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%' OR REPLACE(REPLACE(REPLACE(REPLACE(phoneother, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%' OR REPLACE(REPLACE(REPLACE(REPLACE(faxbusiness, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%'

System.FormatException: One of the identified items was in an invalid format.
at MySqlConnector.Core.TextRow.GetValueCore(ReadOnlySpan`1 data, ColumnDefinitionPayload columnDefinition) in /_/src/MySqlConnector/Core/TextRow.cs:line 112
at MySqlConnector.Core.Row.GetValue(Int32 ordinal) in /_/src/MySqlConnector/Core/Row.cs:line 32
at CrmIntegration.SqlDatabaseClient.Execute(String statement, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token)
at CrmIntegration.CrmProcessor.GetContactInfoFromCrm(String contactNumber, String destinationEmail, Boolean createIfNotFound, CancellationToken token)


And an example of an offending contactID is: 00380000015goyWAAQ

I forced our view to return an actual int for contactid, such as 1237238 and that worked fine, as well as using GUID's also worked .. it only errors when the first part of the contactid string looks like a number.

 

[edossantos]

We're not forcing any type, this looks like a bug in the client library for MySQL server...

We're invoking MySqlConnector.Core.Row.GetValue, which returns an object (we're not assuming any type), and the error is happening inside that method...

Can you try adding a space before the contact ID? For example editing your query to:
SELECT CONCAT(' ',contactid)......

If you're not using the Contact ID for the Contact URL this should not affect you.

 

[bchow]

That did solve it, I do actually use the contact url ... but this pointed me in right troubleshooting direction -- specifically casting to char (even though the underlying datatype is already a char) solved it without prepending a space.

Thanks for the nudge in the right direction!