CiviCRM Community Forums (archive)

Hi,

I've configured the SMS API in CiviCRM, but I keep getting a "DB Error: syntax error", as posted in http://forum.civicrm.org/index.php/topic,25231.0.html.

I enabled debugging and got this:

Database foutcode: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND civicrm_phone.phone IS NOT NULL AND ' at line 23, 1064
Aanvullende gegevens:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => REPLACE INTO       I_11 (phone_id, contact_id)

                    SELECT DISTINCT     civicrm_phone.id as phone_id,
                                        civicrm_contact.id as contact_id
                    FROM                civicrm_phone
                    INNER JOIN          civicrm_contact
                            ON          civicrm_phone.contact_id = civicrm_contact.id
                    INNER JOIN          civicrm_group_contact
                            ON          civicrm_contact.id = civicrm_group_contact.contact_id
                    INNER JOIN          civicrm_mailing_group
                            ON          civicrm_group_contact.group_id = civicrm_mailing_group.entity_id
                                AND     civicrm_mailing_group.entity_table = 'civicrm_group'
                    LEFT JOIN           X_11
                            ON          civicrm_contact.id = X_11.contact_id
                    WHERE
                                       (civicrm_mailing_group.group_type = 'Include')
                        AND             civicrm_mailing_group.search_id IS NULL
                        AND             civicrm_group_contact.status = 'Added'
                        AND             civicrm_contact.do_not_sms = 0
                        AND             civicrm_contact.is_opt_out = 0
                        AND             civicrm_contact.is_deceased = 0
                        AND             civicrm_phone.phone_type_id =
                        AND             civicrm_phone.phone IS NOT NULL
                        AND             civicrm_phone.phone != ''
                        AND             civicrm_mailing_group.mailing_id = 11
                        AND             X_11.contact_id IS null [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND             civicrm_phone.phone IS NOT NULL
                        AND     ' at line 23]
    [type] => DB_Error
    [user_info] => REPLACE INTO       I_11 (phone_id, contact_id)

                    SELECT DISTINCT     civicrm_phone.id as phone_id,
                                        civicrm_contact.id as contact_id
                    FROM                civicrm_phone
                    INNER JOIN          civicrm_contact
                            ON          civicrm_phone.contact_id = civicrm_contact.id
                    INNER JOIN          civicrm_group_contact
                            ON          civicrm_contact.id = civicrm_group_contact.contact_id
                    INNER JOIN          civicrm_mailing_group
                            ON          civicrm_group_contact.group_id = civicrm_mailing_group.entity_id
                                AND     civicrm_mailing_group.entity_table = 'civicrm_group'
                    LEFT JOIN           X_11
                            ON          civicrm_contact.id = X_11.contact_id
                    WHERE
                                       (civicrm_mailing_group.group_type = 'Include')
                        AND             civicrm_mailing_group.search_id IS NULL
                        AND             civicrm_group_contact.status = 'Added'
                        AND             civicrm_contact.do_not_sms = 0
                        AND             civicrm_contact.is_opt_out = 0
                        AND             civicrm_contact.is_deceased = 0
                        AND             civicrm_phone.phone_type_id =
                        AND             civicrm_phone.phone IS NOT NULL
                        AND             civicrm_phone.phone != ''
                        AND             civicrm_mailing_group.mailing_id = 11
                        AND             X_11.contact_id IS null [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND             civicrm_phone.phone IS NOT NULL
                        AND     ' at line 23]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="REPLACE INTO       I_11 (phone_id, contact_id)

                    SELECT DISTINCT     civicrm_phone.id as phone_id,
                                        civicrm_contact.id as contact_id
                    FROM                civicrm_phone
                    INNER JOIN          civicrm_contact
                            ON          civicrm_phone.contact_id = civicrm_contact.id
                    INNER JOIN          civicrm_group_contact
                            ON          civicrm_contact.id = civicrm_group_contact.contact_id
                    INNER JOIN          civicrm_mailing_group
                            ON          civicrm_group_contact.group_id = civicrm_mailing_group.entity_id
                                AND     civicrm_mailing_group.entity_table = 'civicrm_group'
                    LEFT JOIN           X_11
                            ON          civicrm_contact.id = X_11.contact_id
                    WHERE
                                       (civicrm_mailing_group.group_type = 'Include')
                        AND             civicrm_mailing_group.search_id IS NULL
                        AND             civicrm_group_contact.status = 'Added'
                        AND             civicrm_contact.do_not_sms = 0
                        AND             civicrm_contact.is_opt_out = 0
                        AND             civicrm_contact.is_deceased = 0
                        AND             civicrm_phone.phone_type_id =
                        AND             civicrm_phone.phone IS NOT NULL
                        AND             civicrm_phone.phone != ''
                        AND             civicrm_mailing_group.mailing_id = 11
                        AND             X_11.contact_id IS null [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND             civicrm_phone.phone IS NOT NULL
                        AND     ' at line 23]"]
)

What can be causing this error?

Gregory

I just had the same problem last night and debugged it exactly the same way. It may be something we need to look at in future releases - having an option in CiviSMS to select which location_type it uses for SMS messages.

You either have to rename your 'SMS' phone to Mobile, or change Mobile to whatever you 'SMS' phone is called in CRM/Mailing/BAO/Mailing.php.

Probably better to change your phone name to Mobile - but we should look at this in future releases.

Brian

That would work too. I use 'Cell' as my label, but unless you went into the DB and changed it manually, it stays as Mobile.

The only issue that could arise is if someone was to delete the Mobile Phone Type initially because they weren't using it, and later add a Cell phone type and put in Cell as the value (or something else). If there were to be a place to select which phone to use for SMS, I would suggest putting it in the Phone Type Options menu. Simply another checkbox saying 'Use for SMS'.

We could also make Mobile a reserved phone type since we probably want to prevent people from 'breaking' the system without knowing that they are doing so.