Toggle sidebar Toggle sidebar

Solved MySQL insert into contacts table

Status
Not open for further replies.
greychain

greychain

Gold Partner
Advanced Certified
Joined
Jul 13, 2018
Messages
779
Reaction score
122
I'm trying to INSERT INTO `3cxcdr`.`contacts` (`number`) VALUES ([Number]); but the [Number] part isn't resolving the number.

Lookup works with select *from contacts where 'number' = [Number];

What syntax do I need?
 
Last edited:
Template with default values removed. This did not work.

XML: 
<?xml version="1.0"?>
<Crm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Country="US" Name="Database MySQL" Version="25">
  <Number Prefix="AsIs" MaxLength="[MaxLength]" />
  <Connection MaxConcurrentRequests="2" />
  <Parameters>
    <Parameter Name="Server" Type="String" Parent="General Configuration" Editor="String" Title="Server:" Default="" />
    <Parameter Name="Port" Type="String" Parent="General Configuration" Editor="String" Title="Port:" Default="" />
    <Parameter Name="Database" Type="String" Parent="General Configuration" Editor="Sql" Title="Database:" Default="" />
    <Parameter Name="Username" Type="String" Parent="General Configuration" Editor="String" Title="Username:" Default="" />
    <Parameter Name="Password" Type="String" Parent="General Configuration" Editor="String" Title="Password:" Default="" />
    <Parameter Name="LookupSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Lookup SQL Statement:" Default="SELECT contactid, firstname, lastname, companyname, email, phonemobile, phonemobile2, phonehome , phonehome2, phonebusiness, phonebusiness2, phoneother, faxbusiness , faxhome ,pager,  photourl FROM contacts WHERE phonemobile LIKE '%[Number]%' or phonebusiness like '%[Number]%' or faxbusiness LIKE '%[Number]%'" />
    <Parameter Name="ContactUrlPrefix" Type="String" Parent="General Configuration" Editor="String" Title="Contact URL Prefix:" Default="https://mycrmurl/contacts/" />
    <Parameter Name="ContactUrlSuffix" Type="String" Parent="General Configuration" Editor="String" Title="Contact URL Suffix:" Default="/show" />
    <Parameter Name="ReportCallEnabled" Type="Boolean" Editor="String" Title="Enable Call Journaling" Default="True" />
    <Parameter Name="ReportCallSQLStatement" Type="String" Parent="ReportCallEnabled" Editor="Sql" Title="Call Journaling SQL Statement:" Default="INSERT INTO `3cxcdr`.`cdr` (`callid`, `duration`, `time-start`, `time-answered`, `time-end`, `reason-terminated`, `from-no`, `to-no`, `from-dn`, `to-dn`, `dial-no`, `reason-changed`, `final-number`, `final-dn`, `bill-code`, `bill-rate`, `bill-cost`, `bill-name`, `chain`, `from-type`, `to-type`, `final-type`, `from-dispname`, `to-dispname`, `final-dispname`) VALUES ('[Number]', '[Duration]', '[CallStartTimeLocal]', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); INSERT INTO calls (contactnumber, agentextension, description, calldatetime, callduration) VALUES ('[Number]', '[Agent]', '3CX PhoneSystem Call', '[DateTime]', '[Duration]')" ListValues="" />
    <Parameter Name="CreateContactEnabled" Type="Boolean" Editor="String" Title="Enable Contact Creation" Default="True" />
    <Parameter Name="CreateContactSQLStatement" Type="String" Parent="CreateContactEnabled" Editor="Sql" Title="Contact Creation SQL Statement:" Default="INSERT INTO contacts (firstname, lastname, phonebusiness) VALUES ('New', '3CX Contact',  '[Number]' );SELECT contactid, firstname, lastname, companyname, email, phonemobile, phonemobile2, phonehome , phonehome2, phonebusiness, phonebusiness2, phoneother, faxbusiness , faxhome ,pager,  photourl FROM contacts WHERE phonemobile LIKE '%[Number]%' or phonebusiness like '%[Number]%' or faxbusiness LIKE '%[Number]%'" />
  </Parameters>
  <Authentication Type="Basic" />
  <Scenarios>
    <Scenario Type="SQLDatabase">
      <Query SkipIf="" DatabaseType="MySQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[LookupSQLStatement]" />
      <Rules>
        <Rule Type="Any" Ethalon="">contactid</Rule>
      </Rules>
      <Variables>
        <Variable Name="ContactID">contactid<Filter /></Variable>
        <Variable Name="FirstName">firstname<Filter /></Variable>
        <Variable Name="LastName">lastname<Filter /></Variable>
        <Variable Name="CompanyName">companyname<Filter /></Variable>
        <Variable Name="Email">email<Filter /></Variable>
        <Variable Name="PhoneMobile">phonemobile<Filter /></Variable>
        <Variable Name="PhoneMobile2">phonemobile2<Filter /></Variable>
        <Variable Name="PhoneHome">phonehome<Filter /></Variable>
        <Variable Name="PhoneHome2">phonehome2<Filter /></Variable>
        <Variable Name="PhoneBusiness">phonebusiness<Filter /></Variable>
        <Variable Name="PhoneBusiness2">phonebusiness2<Filter /></Variable>
        <Variable Name="PhoneOther">phoneother<Filter /></Variable>
        <Variable Name="FaxBusiness">faxbusiness<Filter /></Variable>
        <Variable Name="FaxHome">faxhome<Filter /></Variable>
        <Variable Name="Pager">pager<Filter /></Variable>
        <Variable Name="PhotoUrl">photourl<Filter /></Variable>
      </Variables>
      <Outputs Next="" AllowEmpty="false">
        <Output Type="ContactUrl" Passes="0" Value="[ContactUrlPrefix][ContactID][ContactUrlSuffix]" />
        <Output Type="FirstName" Passes="0" Value="[FirstName]" />
        <Output Type="LastName" Passes="0" Value="[LastName]" />
        <Output Type="CompanyName" Passes="0" Value="[CompanyName]" />
        <Output Type="Email" Passes="0" Value="[Email]" />
        <Output Type="PhoneMobile" Passes="0" Value="[PhoneMobile]" />
        <Output Type="PhoneMobile2" Passes="0" Value="[PhoneMobile2]" />
        <Output Type="PhoneHome" Passes="0" Value="[PhoneHome]" />
        <Output Type="PhoneHome2" Passes="0" Value="[PhoneHome2]" />
        <Output Type="PhoneBusiness" Passes="0" Value="[PhoneBusiness]" />
        <Output Type="PhoneBusiness2" Passes="0" Value="[PhoneBusiness2]" />
        <Output Type="PhoneOther" Passes="0" Value="[PhoneOther]" />
        <Output Type="FaxBusiness" Passes="0" Value="[FaxBusiness]" />
        <Output Type="FaxHome" Passes="0" Value="[FaxHome]" />
        <Output Type="Pager" Passes="0" Value="[Pager]" />
        <Output Type="PhotoUrl" Passes="0" Value="[PhotoUrl]" />
      </Outputs>
    </Scenario>
    <Scenario Id="ReportCall" Type="SQLDatabase">
      <Query SkipIf="" DatabaseType="MySQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password];Persist Security Info=True;Convert Zero Datetime=True" StatementPasses="1" Statement="[ReportCallSQLStatement]" />
      <Variables />
      <Outputs Next="" AllowEmpty="false" />
    </Scenario>
    <Scenario Id="CreateContactRecord" Type="SQLDatabase">
      <Query SkipIf="[CreateContactEnabled]!=True" DatabaseType="MySQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="1" Statement="[CreateContactSQLStatement]" />
      <Rules>
        <Rule Type="Any" Ethalon="">contactid</Rule>
      </Rules>
      <Variables>
        <Variable Name="ContactID">contactid<Filter /></Variable>
        <Variable Name="FirstName">firstname<Filter /></Variable>
        <Variable Name="LastName">lastname<Filter /></Variable>
        <Variable Name="CompanyName">companyname<Filter /></Variable>
        <Variable Name="Email">email<Filter /></Variable>
        <Variable Name="PhoneMobile">phonemobile<Filter /></Variable>
        <Variable Name="PhoneMobile2">phonemobile2<Filter /></Variable>
        <Variable Name="PhoneHome">phonehome<Filter /></Variable>
        <Variable Name="PhoneHome2">phonehome2<Filter /></Variable>
        <Variable Name="PhoneBusiness">phonebusiness<Filter /></Variable>
        <Variable Name="PhoneBusiness2">phonebusiness2<Filter /></Variable>
        <Variable Name="PhoneOther">phoneother<Filter /></Variable>
        <Variable Name="FaxBusiness">faxbusiness<Filter /></Variable>
        <Variable Name="FaxHome">faxhome<Filter /></Variable>
        <Variable Name="Pager">pager<Filter /></Variable>
        <Variable Name="PhotoUrl">photourl<Filter /></Variable>
      </Variables>
      <Outputs AllowEmpty="false">
        <Output Type="ContactUrl" Passes="0" Value="[ContactUrlPrefix][ContactID][ContactUrlSuffix]" />
        <Output Type="FirstName" Passes="0" Value="[FirstName]" />
        <Output Type="LastName" Passes="0" Value="[LastName]" />
        <Output Type="CompanyName" Passes="0" Value="[CompanyName]" />
        <Output Type="Email" Passes="0" Value="[Email]" />
        <Output Type="PhoneMobile" Passes="0" Value="[PhoneMobile]" />
        <Output Type="PhoneMobile2" Passes="0" Value="[PhoneMobile2]" />
        <Output Type="PhoneHome" Passes="0" Value="[PhoneHome]" />
        <Output Type="PhoneHome2" Passes="0" Value="[PhoneHome2]" />
        <Output Type="PhoneBusiness" Passes="0" Value="[PhoneBusiness]" />
        <Output Type="PhoneBusiness2" Passes="0" Value="[PhoneBusiness2]" />
        <Output Type="PhoneOther" Passes="0" Value="[PhoneOther]" />
        <Output Type="FaxBusiness" Passes="0" Value="[FaxBusiness]" />
        <Output Type="FaxHome" Passes="0" Value="[FaxHome]" />
        <Output Type="Pager" Passes="0" Value="[Pager]" />
        <Output Type="PhotoUrl" Passes="0" Value="[PhotoUrl]" />
      </Outputs>
    </Scenario>
  </Scenarios>
</Crm>
 
After this I went the next step and deleted the template generated by the CRM template generator, used the default MySQL template, cut and pasted all the commands from the CRM template generator and now it works.
 
  • Like
Reactions: edossantos
Status
Not open for further replies.

Latest Posts

Forum statistics

Threads
112,323
Messages
591,894
Members
165,472
Latest member
arneg
3CX Logo Footer Copyright © Sitemap Privacy
Top Bottom