CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion (Moderator: Donald Lobo) »
  • Issue with CiviMigrate example
Pages: [1]

Author Topic: Issue with CiviMigrate example  (Read 1083 times)

jakecivi

  • I post frequently
  • ***
  • Posts: 140
  • Karma: 0
Issue with CiviMigrate example
July 20, 2012, 05:52:21 pm
Anyone (especially Eileen) know why, upon enabling civimigrate and visiting the Migrate UI or running 'drush ms', I get these errors?  Drupal 6, Civi 3.4, happened with recommended and -dev versions of DBTNG and Migrate.  Thanks!

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server       [warning]
version for the right syntax to use near ':column' at line 1
query: SHOW columns from civimigrate_example_people_names WHERE field = :column
database.mysqli.inc:134
Invalid argument supplied for foreach() civimigrate.class.inc:103                                  [warning]

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 'DEFAULT NULL,
`destid1` INT unsigned NULL DEFAULT NULL COMMENT 'ID of API desti' at line 2' in /Applications/MAMP/htdocs/civitest/sites/all/modules/dbtng/database/database.inc:2045
Stack trace:
#0 /Applications/MAMP/htdocs/civitest/sites/all/modules/dbtng/database/database.inc(2045): PDOStatement->execute(Array)
#1 /Applications/MAMP/htdocs/civitest/sites/all/modules/dbtng/database/database.inc(597): DatabaseStatementBase->execute(Array, Array)
#2 /Applications/MAMP/htdocs/civitest/sites/all/modules/dbtng/database/schema.inc(633): DatabaseConnection->query('CREATE TABLE {m...')
#3 /Applications/MAMP/htdocs/civitest/sites/all/modules/migrate/plugins/sources/sqlmap.inc(144): DatabaseSchema->createTable('migrate_map_peo...', Array)
#4 /Applications/MAMP/htdocs/civitest/sites/a in /Applications/MAMP/htdocs/civitest/sites/all/modules/dbtng/database/database.inc on line 2045
Drush command terminated abnormally due to an unrecoverable error.                                 [error]

jakecivi

  • I post frequently
  • ***
  • Posts: 140
  • Karma: 0
Re: Issue with CiviMigrate example
July 23, 2012, 02:18:11 pm
Okay, so at least part of the issue was an error in the civimigrate.install file.

It read
Code: [Select]
drupal_install_schema('civimigrate_mappings'); where it should have read
Code: [Select]
  drupal_install_schema('civimigrate');

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Issue with CiviMigrate example
July 23, 2012, 04:40:22 pm
Hi,

I've just made that change
git@github.com:eileenmcnaughton/civimigrate.git
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Issue with CiviMigrate example
July 23, 2012, 05:01:01 pm
Hi,

In answer to your question on using CiviMigrate with a csv file - I don't think I've really got it right yet - but here's what I have got working. (the mappings themselves are in the civimigrate_mappings table.

Code: [Select]
<?php

class Individual extends Civimigration_drupal6 {
  protected $base_table = '';
  protected $base_table_id = '';
  protected $autoquery = 1;
  protected $entity = 'contact'; // this is the default
  protected $debug = 1;
  protected $matchexternalidentifier = 1;
  protected $description = 'Contact data - main import';
  protected $keyField = 'person_ref';

  public function __construct($child = 0) {
    parent::__construct();

    // Create a map object for tracking the relationships between source rows
    $this->map = new MigrateSQLMap($this->machineName,
        array(
          $this->keyField => array(
            'type' => 'varchar',
            'length' => 100,
            'not null' => TRUE,
           )
        ),
        MigrateDestinationTerm::getKeySchema()
      );

      $columns = $this->getcsvMappings();
      // Instantiate the class using the path to the CSV file and the columns.
      $this->source = new MigrateSourceCSV('sites/mysite/files/civicrm/afsupload.csv',
        $columns,
        array('header_rows' => 1)
      );

  }
  function getcsvMappings(){
    return array(
       array('person_ref', 'external identifier'),
       array('ENGLISH_FIRSTNAME', 'ENGLISH_FIRSTNAME'),
       array('ENGLISH_LASTNAME', 'ENGLISH_LASTNAME'),
       array('ENGLISH_NICKNAME', 'ENGLISH_NICKNAME'),
       array('DATE_OF_BIRTH', 'birth date'),
       array('MAILING ADDRESS', 'MAILING ADDRESS'),
       array('ENGLISH_ZIP', 'ENGLISH_ZIP'),
       array('PREFERRED_EMAIL', 'PREFERRED_EMAIL'),
       array('preferred_email_two', 'preferred_email_two'),
       array('SEX', 'gender'),
       array('TITLE', 'prefix'),
       array('work_email','work_email'),
       array('WORK_FAX','WORK_FAX'),
       array('WORK_TELEPHONE','WORK_TELEPHONE'),
       array('INSTANT_MESSAGE_ADDRESS','INSTANT_MESSAGE_ADDRESS'),
       array('COUNTRY','COUNTRY'),
       array('ENGLISH_ADDRESS1','ENGLISH_ADDRESS1'),
       array('ENGLISH_ADDRESS2','ENGLISH_ADDRESS2'),
       array('ENGLISH_CITY', 'ENGLISH_CITY'),
       array('PERSON_ID','PERSON_ID'),
       array('NATIVE_TITLE','NATIVE_TITLE'),
       array('NATIVE_FIRSTNAME','NATIVE_FIRSTNAME'),
       array('NATIVE_LASTNAME','NATIVE_LASTNAME'),
       array('NATIVE_NICKNAME','NATIVE_NICKNAME'),
       array('NATIVE_ADDRESS1','NATIVE_ADDRESS1'),
       array('NATIVE_ADDRESS2','NATIVE_ADDRESS2'),
       array('NATIVE_CITY','NATIVE_CITY'),
       array('NATIVE_ZIP','NATIVE_ZIP'),
       array('MAIN_ORG_ID','MAIN_ORG_ID'),
       array('ORG_LINK_FROM_DATE','ORG_LINK_FROM_DATE'),
       array('ORG_LINK_TO_DATE','ORG_LINK_TO_DATE'),
       array('STATUS','STATUS'),
       array('PERSON_ROLE','PERSON_ROLE'),
       array('affiliation_id','affiliation_id'),
       array('AFFILIATION_TYPE','AFFILIATION_TYPE'),
       array('CONNECTION_TYPE','CONNECTION_TYPE'),
       array('GL_AFFILIATION','GL_AFFILIATION'),
       array('organization_ref','organization_ref'),
       array('ORG_TYPE','ORG_TYPE'),
       array('ORGANIZATION_ID_FOR_PL19,ORGANIZATION_ID_FOR_PL19'),
       array('MAIN_ORG_NAME','MAIN_ORG_NAME'),
       array('MAIN_ORG_NATIVE_NAME','MAIN_ORG_NATIVE_NAME'),
       array('MAIN_ORG_CHAPTER','MAIN_ORG_CHAPTER'),
       array('MAIN_ORG_CHAPTER_ID','MAIN_ORG_CHAPTER_ID'),
       array('MAIN_ORG_CONTACT_NAME','MAIN_ORG_CONTACT_NAME'),
       array('MAIN_ORG_COUNTRY','MAIN_ORG_COUNTRY'),
       array('main_org_english_address1','main_org_english_address1'),
       array('MAIN_ORG_ENGLISH_ADDRESS2','MAIN_ORG_ENGLISH_ADDRESS2'),
       array('MAIN_ORG_ENGLISH_CITY','MAIN_ORG_ENGLISH_CITY'),
       array('MAIN_ORG_ENGLISH_ZIP','MAIN_ORG_ENGLISH_ZIP'),
       array('MAIN_ORG_ENGLISH_STATE','MAIN_ORG_ENGLISH_STATE'),
       array('MAIN_ORG_NATIVE_ADDRESS1','MAIN_ORG_NATIVE_ADDRESS1'),
       array('MAIN_ORG_NATIVE_ADDRESS2','MAIN_ORG_NATIVE_ADDRESS2'),
       array('MAIN_ORG_NATIVE_CITY','MAIN_ORG_NATIVE_CITY'),
       array('MAIN_ORG_NATIVE_STATE','MAIN_ORG_NATIVE_STATE'),
       array('MAIN_ORG_NATIVE_ZIP','MAIN_ORG_NATIVE_ZIP'),
       array('MAIN_ORG_EMAIL','MAIN_ORG_EMAIL'),
       array('MAIN_ORG_FAXNUM','MAIN_ORG_FAXNUM'),
       array('NATIVE_ORG_SUB_TYPE','NATIVE_ORG_SUB_TYPE'),
       array('MAIN_ORG_WEB_SITE','MAIN_ORG_WEB_SITE'),
       array('ORG_STATUS','ORG_STATUS'),
       array('person_organization_id','person_organization_id'),
    );
  }
}
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

jakecivi

  • I post frequently
  • ***
  • Posts: 140
  • Karma: 0
Re: Issue with CiviMigrate example
July 23, 2012, 05:18:17 pm
The uninstall line still says civimigrate_mappings

Is your getcsvMappings() necessary if you're using a header row?

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Issue with CiviMigrate example
July 23, 2012, 06:14:13 pm
I've just updated the uninstall.

In theory - no the getCSV mappings isn't necessary - I think I was just having a few probs.
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

jakecivi

  • I post frequently
  • ***
  • Posts: 140
  • Karma: 0
Re: Issue with CiviMigrate example
July 24, 2012, 07:41:41 am
I got it working with an empty array for $columns to import some Households from a CSV.

The only thing is, do I need to specifically assign redundant fields, like the "Household Name" field to both sort_name and display_name?  Is there a prepare function I can call on the CiviCRM Contact that will automatically populate all that?  I'm also wondering about addresses - it looks like the DB has split out fields for address parsing, which it seems Civi must do by itself (suffix, predirectional, street name, street type, postdirectional, street unit) - do I need to explicitly parse those myself and assign them values in the Migration class, or is there a prepare function I can call for those too?

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Issue with CiviMigrate example
July 24, 2012, 03:01:24 pm
CiviMigrate is a layer over the API which is a layer over the BAO. Some fields are sensibly prefilled - display_name & sort_name should be amongst those.

I updated the example for address so you can see the street_parsing param on the api

http://svn.civicrm.org/civicrm/branches/v4.2/api/v3/examples/Address/AddressParse.php
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

jakecivi

  • I post frequently
  • ***
  • Posts: 140
  • Karma: 0
Re: Issue with CiviMigrate example
July 24, 2012, 03:14:29 pm
I asked about display_name versus sort_name because, when I assigned only a Household's display_name, I ended up with  contact search results that looked like a bunch of blank lines.  Then, when I clicked view on one of them, I would see the contact's name as the page header but not within the contact summary.

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Issue with CiviMigrate example
July 24, 2012, 03:23:17 pm
try setting household_name only
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion (Moderator: Donald Lobo) »
  • Issue with CiviMigrate example

This forum was archived on 2017-11-26.