4 Replies Latest reply on Sep 18, 2012 12:17 PM by LSNOVER

    Simple ESS table, schema doesn't pull

    nrgsoft

      I have many MySQL tables in-use with ESS however I have one table which will not pull all columns. Below are the columns that come thru along with the table schema. The randomness of the order that's coming thru really confuses me. DB is MySQL 5.0.90 on Windows with the recommended 3.5.14 driver.

       

      Any insight would be most appreciated.

       

      auction_multi_item

      creation_timestamp

      discount

      for_auction

      handling_amount

      id

      insurance_amount

      mc_fee

      mc_gross

      mc_handling

      mc_shipping

      nrg_ship_actual

      nrg_ship_published

      raw_log_id

      shipping

      shipping_discount

      tax

      test_ipn

       

       

      CREATE TABLE IF NOT EXISTS `paypal_orders` (

      `id` int(10) unsigned NOT NULL auto_increment,

      `raw_log_id` int(10) default NULL,

      `receiver_email` varchar(127) collate utf8_bin default NULL,

      `payment_status` varchar(25) collate utf8_bin default NULL,

      `pending_reason` varchar(25) collate utf8_bin default NULL,

      `payment_date` varchar(100) collate utf8_bin default NULL,

      `option_name1` varchar(64) collate utf8_bin default NULL,

      `option_selection1` varchar(200) collate utf8_bin default NULL,

      `option_name2` varchar(64) collate utf8_bin default NULL,

      `option_selection2` varchar(200) collate utf8_bin default NULL,

      `option_name3` varchar(64) collate utf8_bin default NULL,

      `option_selection3` varchar(200) collate utf8_bin default NULL,

      `option_name4` varchar(64) collate utf8_bin default NULL,

      `option_selection4` varchar(200) collate utf8_bin default NULL,

      `option_name5` varchar(64) collate utf8_bin default NULL,

      `option_selection5` varchar(200) collate utf8_bin default NULL,

      `option_name6` varchar(64) collate utf8_bin default NULL,

      `option_selection6` varchar(200) collate utf8_bin default NULL,

      `option_name7` varchar(64) collate utf8_bin default NULL,

      `option_selection7` varchar(200) collate utf8_bin default NULL,

      `option_name8` varchar(64) collate utf8_bin default NULL,

      `option_selection8` varchar(200) collate utf8_bin default NULL,

      `option_name9` varchar(64) collate utf8_bin default NULL,

      `option_selection9` varchar(200) collate utf8_bin default NULL,

      `memo` varchar(255) collate utf8_bin default NULL,

      `shipping_method` varchar(100) collate utf8_bin default NULL,

      `btn_id` varchar(50) collate utf8_bin default NULL,

      `mc_gross` double default NULL,

      `mc_fee` double default NULL,

      `mc_shipping` double default NULL,

      `mc_handling` double default NULL,

      `shipping_discount` double default NULL,

      `insurance_amount` double default NULL,

      `handling_amount` double default NULL,

      `discount` double default NULL,

      `shipping` double default NULL,

      `tax` double default NULL,

      `mc_currency` varchar(10) collate utf8_bin default NULL,

      `txn_id` varchar(25) collate utf8_bin default NULL,

      `txn_type` varchar(25) collate utf8_bin default NULL,

      `first_name` varchar(75) collate utf8_bin default NULL,

      `last_name` varchar(75) collate utf8_bin default NULL,

      `address_street` varchar(200) collate utf8_bin default NULL,

      `address_city` varchar(50) collate utf8_bin default NULL,

      `address_state` varchar(40) collate utf8_bin default NULL,

      `address_zip` varchar(20) collate utf8_bin default NULL,

      `address_country` varchar(64) collate utf8_bin default NULL,

      `address_status` varchar(25) collate utf8_bin default NULL,

      `payer_email` varchar(127) collate utf8_bin default NULL,

      `payer_status` varchar(25) collate utf8_bin default NULL,

      `payment_type` varchar(25) collate utf8_bin default NULL,

      `notify_version` varchar(50) collate utf8_bin default NULL,

      `verify_sign` varchar(255) collate utf8_bin default NULL,

      `address_name` varchar(130) collate utf8_bin default NULL,

      `transaction_subject` varchar(150) collate utf8_bin default NULL,

      `protection_eligibility` varchar(50) collate utf8_bin default NULL,

      `ipn_status` varchar(25) collate utf8_bin default NULL,

      `subscr_id` varchar(25) collate utf8_bin default NULL,

      `custom` varchar(255) collate utf8_bin default NULL,

      `reason_code` varchar(25) collate utf8_bin default NULL,

      `contact_phone` varchar(25) collate utf8_bin default NULL,

      `item_name` varchar(127) collate utf8_bin default NULL,

      `item_number` varchar(127) collate utf8_bin default NULL,

      `invoice` varchar(127) collate utf8_bin default NULL,

      `for_auction` tinyint(10) default NULL,

      `auction_buyer_id` varchar(75) collate utf8_bin default NULL,

      `auction_closing_date` varchar(100) collate utf8_bin default NULL,

      `auction_multi_item` double default NULL,

      `creation_timestamp` timestamp NULL default CURRENT_TIMESTAMP,

      `address_country_code` varchar(2) collate utf8_bin default NULL,

      `payer_business_name` varchar(150) collate utf8_bin default NULL,

      `receiver_id` varchar(15) collate utf8_bin default NULL,

      `test_ipn` tinyint(1) default '0',

      `nrg_status` varchar(100) collate utf8_bin default 'New',

      `nrg_tracknum` varchar(100) collate utf8_bin default NULL,

      `nrg_ship_published` double default '0',

      `nrg_ship_actual` double default '0',

      `nrg_service` varchar(100) collate utf8_bin default NULL,

      `nrg_shipdatetime` datetime default NULL,

      `nrg_billingweight` double default '0',

      `nrg_serviceoptioncharges` double default '0',

      PRIMARY KEY (`id`),

      KEY `receiver_id` (`receiver_id`),

      KEY `receiver_email` (`receiver_email`),

      KEY `payment_status` (`payment_status`),

      KEY `txn_id` (`txn_id`),

      KEY `nrg_status` (`nrg_status`),

      KEY `raw_log_id` (`raw_log_id`)

      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=115221 ;

        • 1. Re: Simple ESS table, schema doesn't pull
          TimDietrich

          After a quick glance, it looks like the columns that have been setup with collation set to "utf8_bin" are the ones that aren't coming through.

           

          Do you really need that collation? Maybe you could try "utf8_general_ci" instead?

           

          -- Tim

          • 2. Re: Simple ESS table, schema doesn't pull
            nrgsoft

            That fixed it. Always nice to have another set of eyes.

             

            For future reference, here's the SQL to change the default collation on a table:

             

            ALTER TABLE <table name> COLLATE utf8_general_ci;

             

            This snippet will change the collation for existing columns. If the text is not utf8, it will convert it:

             

            ALTER TABLE <table name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

            • 3. Re: Simple ESS table, schema doesn't pull
              beverly

              The collation AND sometimes I've seen the "auto-enter" fields not accessible to ESS. I guess the theory is that because the External does that work, we don't need to see it. But HEY! they should be "read-only" if that's the case...

               

              Beverly

              • 4. Re: Simple ESS table, schema doesn't pull
                LSNOVER

                Also, be careful of the overall length of your table (field names and table name).  Once you reach a certain point, the buffer will not be large enough to parse the SQL string and return data from Filemaker to SQL.  This is relatively small in FM11 (about 16K characters).  FM12 has increased the size of the buffer by a good factor, but there is still a limit.