Direct population of emails via MySQL ODBC and SQL Server?

Use this forum for questions regarding adoption and functionality of OpenEMM

Moderator: moderator

tgilbert328
Posts: 20
Joined: Wed Jun 13, 2007 4:20 pm

Direct population of emails via MySQL ODBC and SQL Server?

Post by tgilbert328 »

My company maintains our email lists (with opt-in/opt-out etc) in a different location (both physically and architecture) . We are experimenting with populating the MySQL database tables directly, rather than using the Import feature.

The challenge is that we have many different newsletters and opt-in/opt-out is not a binary issue. A user could unsubscribe to one or two of our lists, but maintain their subscription to others.

So, a coworker and I have gone through the openemm database and we believe we know which tables to update and which to leave alone. Basically, we brought the customer_id field into our database as another key using email as the primary. Then my coworking is building processes in SQL Server using the MySql ODBC driver to INSERT new users and UPDATE existing customers with their new subscriptions.

We know we need to update customer_1_tbl, customer_1_binding, and customer_1_seq. We don't think we need to update anything else.

Has anyone out their done anything similar? If so, can you relate your successes/challenges? If there is interest, I will journal our progress here in this forum so that the next user can benefit.
mattreidy
Posts: 2
Joined: Fri Aug 10, 2007 5:42 pm

RE: Importing Recipients & SQL Server

Post by mattreidy »

I have the same need and am very interested in knowing what you've learned thus-far. Please share what you know. I've had no success importing large (several million rows) recipient lists via the wizard. I'm now testing to see what the limit is... -Matt
asai
Posts: 70
Joined: Fri May 25, 2007 5:48 pm

Post by asai »

I have written a script in PHP which updates the MySQL database, and updates the 3 tables you mentioned. The problem I have is this: while I am successful in getting the info in the database, and the user is successfully added, when I go in to OpenEMM and try view their data, I can't get the data from any of the Custom fields which I defined to display, i.e. city, state, zip.
The email and name information displays, but not the custom info.

Although it resides in the database, it wont display on screen.

Perhaps something is wrong with my MySQL query or something, I'd be willing to share this info with you if you wish.
Hoeney
Posts: 6
Joined: Thu Aug 09, 2007 10:04 pm

Post by Hoeney »

i think you may be forgetting to edit the fields in customer_fields_tbl. there is where you define the custom profile fields
asai
Posts: 70
Joined: Fri May 25, 2007 5:48 pm

Post by asai »

What would that be? What would I edit in customer_fields_tbl?
Hoeney
Posts: 6
Joined: Thu Aug 09, 2007 10:04 pm

Post by Hoeney »

this is from the OpenEMM table doc, it's a good read if you want to edit the db directly. you can view/download it on the openemm.org downloads page:

customer_field_tbl
Column name: Description:
admin_id References admin_tbl.
col_name Name of the column in customer_1_tbl.
company_id References company_tbl.
default_value Value for the given column, if no other value is supplied.
description Description of the column.
mode_edit Set to 1 if the column is editable.
mode_insert Set to 1 if the column is insertable.
shortname A descriptive name for the column. This name is for display in the frontend.

Table description:

This table holds information about the user-created columns in customer_1_tbl. The combination of company_id and col_name is the unique identification of the described column.
asai
Posts: 70
Joined: Fri May 25, 2007 5:48 pm

Post by asai »

Thanks, I'll have a closer look at this.
tgilbert328
Posts: 20
Joined: Wed Jun 13, 2007 4:20 pm

Post by tgilbert328 »

My colleague has successfully built a DTS package in SQL Server 2000 which inserts, updates and deletes (not really deletes, actually we admin opt-out to preserve the statistics) the various tables in the openemm database. He will document the process and the lessons learnt for the community in this forum. Check back in a day or two to see it.

Tim
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

please put docs in wiki

Post by maschoff »

I would prefer if you use the OpenEMM Wiki (http://www.openemm.org/wiki/OpenEMM_Wiki_Home) for this kind of documentation and simply provide a link to the Wiki page in this forum. This will make it easier to find for other users. Thank you!
OpenEMM Maintainer
tgilbert328
Posts: 20
Joined: Wed Jun 13, 2007 4:20 pm

Post by tgilbert328 »

My colleague msafar-ali has documented his steps to automatic population and I added it to the Wiki as requested (albeit later than I had said).

I butchered the formating but the guts of it is there.

http://www.openemm.org/wiki/DatabasePopulation

Tim
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

Thank you!
OpenEMM Maintainer
Post Reply