3cx journaling MySQL

[Martin N.]

Hello guys,

we are still running version 18 SP 9, and obvously want to upgrade to 20. We are kinda very dependent on 3CX and all integrations, features etc. so because of that I ve set testing envioronment (free one) to see how especially journaling will be working, as we do have other important services dependent on it.

I ve set exactly the same setup for journaling (using default MySql template) but from uknown reason its still not feeding data into the database, once I am trying to test, it returns nothing back (logs etc)

Running MySql 5.7

Here is my the lookup by number sql statement:

SELECT 1 contactid, 'Ignore' firstname, 'Ignore' lastname, '' as email,'[Number]' as phonebusiness, '[Number]' as phonemobile, '[Number]' as faxbusiness

Call journaling statement:

INSERT INTO 3cx_journaling (CallType, Number, Agent, Duration, CallStartTimeLocal, CallEndTimeLocal, CallStartTimeUTC, CallEndTimeUTC) VALUES ('[CallType]','[Number]','[Agent]','[Duration]','[[CallStartTimeLocal].ToString("yyyy-MM-dd HH:mm:ss")]','[[CallEndTimeLocal].ToString("yyyy-MM-dd HH:mm:ss")]','[[CallStartTimeUTC].ToString("yyyy-MM-ddTHH:mm:ssZ")]','[[CallEndTimeUTC].ToString("yyyy-MM-ddTHH:mm:ssZ")]')

As I stated before this setup works correctly on ver 18, however not working on testing ver 20.

Maybe anyone know how to adjust select and insert query?

 

[Evolute IT]

See here: https://www.3cx.com/docs/sql-database-pbx-integration/

Queries changed and now use parameters for advanced security.

 

[Martin N.]

Does it mean I would need to rename columns as following on my database and run statements as stated in the link?

 

[Evolute IT]

Does it mean I would need to rename columns as following on my database and run statements as stated in the link?

View attachment 42808

No just replace the [variable] with @Variable in your queries.

 

[Martin N.]

So like this for example?

INSERT INTO 3cx_journaling (CallType, Number, Agent, Duration, CallStartTimeLocal, CallEndTimeLocal, CallStartTimeUTC, CallEndTimeUTC) VALUES ('@CallType','@Number','@Agent','@Duration','[@CallStartTimeLocal.ToString("yyyy-MM-dd HH:mm:ss")]','[@CallEndTimeLocal.ToString("yyyy-MM-dd HH:mm:ss")]','[@CallStartTimeUTC.ToString("yyyy-MM-ddTHH:mm:ssZ")]','[@CallEndTimeUTC.ToString("yyyy-MM-ddTHH:mm:ssZ")]')

 

[Evolute IT]

So like this for example?

INSERT INTO 3cx_journaling (CallType, Number, Agent, Duration, CallStartTimeLocal, CallEndTimeLocal, CallStartTimeUTC, CallEndTimeUTC) VALUES ('@CallType','@Number','@Agent','@Duration','[@CallStartTimeLocal.ToString("yyyy-MM-dd HH:mm:ss")]','[@CallEndTimeLocal.ToString("yyyy-MM-dd HH:mm:ss")]','[@CallStartTimeUTC.ToString("yyyy-MM-ddTHH:mm:ssZ")]','[@CallEndTimeUTC.ToString("yyyy-MM-ddTHH:mm:ssZ")]')

Just read the page. It explains how to use the @Variables for each database engine, as well as give example for the date formatting.

 

[edossantos_sipcaller]

For date times, this will not work:
[@CallStartTimeLocal.ToString("yyyy-MM-dd HH:mm:ss")]

You will need to use the conversion functions of your database. 3CX will send a timestamp parameter.