Author Topic: Bug in CRM_Core_DAO::executeQuery? (Read 1039 times)

lee.gooding · March 23, 2015, 11:08:27 am

See my second post for thoughts as to what I think is causing the issue...

I'm having a really strange issue with CRM_Core_DAO::executeQuery. I'm dynamically generating a SELECT query and running it using executeQuery. If I run the query that is generated directly on the database from the console it will return all the results properly. HOWEVER, here is where the oddness is, the results returned when the query is run through executeQuery do NOT return values for the results. This is the EXACT same query that returns all the results when run through the console.

For example, here are the results from the console:

Code: [Select]

'2', 'Goodring', 'Stephen', 'Stephen Lee Goodring', '226', 'Technology: Graphics,SGL: Wednesday', 'Regular', 'A friend'
'4589', 'Goodring', 'Tiffany', 'Tiffany Goodring', '226', 'Clear River Kids', 'Regular', NULL

The results from the last column come back empty when i run executeQuery.

Code: [Select]

'2', 'Goodring', 'Stephen', 'Stephen Lee Goodring', '226', 'Technology: Graphics,SGL: Wednesday', 'Regular', ''
'4589', 'Goodring', 'Tiffany', 'Tiffany Goodring', '226', 'Clear River Kids', 'Regular', ''

Here's the other thing...the code works fine on my other developers system. However, it does not work on my 2 systems. Could it be a permissions issue? I'm about to lose my mind with this!

Here is the query that I am running:

Code: [Select]

SELECT `civicrm_contact`.`id` AS `id`, `civicrm_contact`.`last_name` AS `last_name`, 
`civicrm_contact`.`first_name` AS `first_name`, 
`civicrm_contact`.`display_name` AS `display_name`, 
civicrm_group_contact.group_id as group_id, 
GROUP_CONCAT(DISTINCT `civicrm_membership_type`.`name` SEPARATOR ',') AS `membership_type`, 
civicrm_value_small_group_68.small_group_member_role_222 AS role ,
civicrm_value_first_time_visitor_2.how_did_they_hear_about_us__11 as 'How did they hear about us?' 
FROM `civicrm_contact` 
INNER JOIN civicrm_group_contact on civicrm_contact.id = civicrm_group_contact.contact_id AND civicrm_group_contact.status = 'Added' 
LEFT JOIN `civicrm_membership` ON `civicrm_contact`.`id` = `civicrm_membership`.`contact_id` AND civicrm_membership.status_id = 2 
LEFT JOIN `civicrm_membership_type` ON `civicrm_membership`.`membership_type_id` = `civicrm_membership_type`.`id` 
LEFT JOIN civicrm_value_small_group_68 ON civicrm_contact.id = civicrm_value_small_group_68.entity_id 
LEFT JOIN civicrm_value_first_time_visitor_2 ON civicrm_contact.id = civicrm_value_first_time_visitor_2.entity_id 
WHERE (`civicrm_contact`.`first_name` IS NOT NULL) 
AND (`civicrm_contact`.`last_name` IS NOT NULL) 
AND (`civicrm_contact`.`is_deleted` = 0) 
AND `civicrm_contact`.`contact_type` = 'Individual' 
AND civicrm_group_contact.group_id = 226 
GROUP BY civicrm_contact.id

lee.gooding · March 23, 2015, 11:47:13 am

I think I just figured out the issue.

For the line:

Code: [Select]

civicrm_value_first_time_visitor_2.how_did_they_hear_about_us__11 as 'How did they hear about us?'
When I set AS 'How did they hear about us?' it will NOT return the results. If I remove that it will return them properly.

With that said, it looks like DAO executeQuery does not work when there are spaces in the column name.

Is this normal behavior? Seems broken to me if the query works when run directly in mysql console.

totten · March 23, 2015, 02:43:50 pm

1. I thought that field aliases would use backquotes instead of single-quotes... but it looks like MySQL accepts either (when running manually). Maybe column-aliases aren't exactly data (single-quotes) or identifiers (backquotes), so perhaps it's good that MySQL is forgiving.

2. On development installs (git-based), Civi sets "SET SESSION sql_mode = STRICT_TRANS_TABLES". This can sometimes produce stricter behavior in Civi than you would see when running SQL manually.

3. When running this script, I see that the DAO munges the column name to use underscores instead of spaces:

Code: [Select]

<?php
// FILE: space-example.php
// usage: drush scr space-example.php
civicrm_initialize();
$d = CRM_Core_DAO::executeQuery("select 'hello' as `sdf dfs`");
while ($d->fetch()) {
  print_r($d);
}

Code: [Select]

CRM_Core_DAO Object
(
    [_DB_DataObject_version] => 1.8.12
    [__table] =>
    [N] => 1
    [_database_dsn] =>
    [_database_dsn_md5] => d35619e94c12a76de55bca89ec59bfe2
    [_database] => d46civi_hc3u1
    [_query] =>
    [_DB_resultid] => 7
    [_resultFields] =>
    [_link_loaded] =>
    [_join] =>
    [_lastError] =>
    [sdf_dfs] => hello
)

I'm fairly sure that Civi per se isn't doing the munging, but it may be in DB_DataObject or one of mysql drivers.

4. Generally, I think we avoid using spaces and punctuation in field/column names. It makes translation harder, and when you pass the symbol through multiple layers (e.g. MySQL and DAO and forms and HTML and Javascript), it's fairly common for one layer or another to stumble on complex field names. Sticking to [a-zA-Z0-9_]+ is generally safest.

lee.gooding · March 24, 2015, 08:14:11 am

Totten,

Thanks for talking through this. It's helpful to have a better understanding of "why"

. I ended up going in and converting all my spaces to underscores to deal with this. Seems like best practice is to NOT use spaces in column names.

I'm actually suspicious that it may be the mysql driver since my developer's system runs it just fine (and he's running the same CiviCRM codebase).

Quote from: totten on March 23, 2015, 02:43:50 pm

1. I thought that field aliases would use backquotes instead of single-quotes... but it looks like MySQL accepts either (when running manually). Maybe column-aliases aren't exactly data (single-quotes) or identifiers (backquotes), so perhaps it's good that MySQL is forgiving.

2. On development installs (git-based), Civi sets "SET SESSION sql_mode = STRICT_TRANS_TABLES". This can sometimes produce stricter behavior in Civi than you would see when running SQL manually.

3. When running this script, I see that the DAO munges the column name to use underscores instead of spaces:

Code: [Select]
<?php // FILE: space-example.php // usage: drush scr space-example.php civicrm_initialize(); $d = CRM_Core_DAO::executeQuery("select 'hello' as `sdf dfs`"); while ($d->fetch()) { print_r($d); }
Code: [Select]
CRM_Core_DAO Object ( [_DB_DataObject_version] => 1.8.12 [__table] => [N] => 1 [_database_dsn] => [_database_dsn_md5] => d35619e94c12a76de55bca89ec59bfe2 [_database] => d46civi_hc3u1 [_query] => [_DB_resultid] => 7 [_resultFields] => [_link_loaded] => [_join] => [_lastError] => [sdf_dfs] => hello )
I'm fairly sure that Civi per se isn't doing the munging, but it may be in DB_DataObject or one of mysql drivers.

4. Generally, I think we avoid using spaces and punctuation in field/column names. It makes translation harder, and when you pass the symbol through multiple layers (e.g. MySQL and DAO and forms and HTML and Javascript), it's fairly common for one layer or another to stumble on complex field names. Sticking to [a-zA-Z0-9_]+ is generally safest.

CiviCRM Community Forums (archive)

News:

Author Topic: Bug in CRM_Core_DAO::executeQuery? (Read 1039 times)

lee.gooding

Bug in CRM_Core_DAO::executeQuery?

lee.gooding

Re: Oddity in CRM_Core_DAO::executeQuery?

totten

Re: Bug in CRM_Core_DAO::executeQuery?

lee.gooding

Re: Bug in CRM_Core_DAO::executeQuery?