SQLite data definitions (req'd fields?)

Question asked by Greg Corwin - 8/15/2016 at 10:52 AM

Answered

We are looking to automate the updating of mailing list subscribers by inserting them in the SQLite db using a powershell script. Inserting them isn't a big deal, but what I am confused by are the some fields in the Subscribers and MailingListSubscribers tables. There are CreatedOn and EditedOn and BounceAddress in Subscribers, and SubscribedOn in MailingListSubscribers. The date fields are a timestamp of some sort, but they are not Epoch Time.

My question: are these fields required and if so what is the format I should be using?

TIA - Greg

5 Replies

Reply to Thread

Matt Petty Replied

8/15/2016 at 1:37 PM

Employee Post Marked As Answer

They appear to be using DateTime format.
Snippet of code that lead me to this.

            toSave.EditedOn = DateTime.UtcNow;
            SQLiteHelper.ExecuteNonQuery(ConnectionString, query, toSave.EmailAddress, toSave.BounceCount, toSave.BounceAddress, toSave.EditedOn.Ticks,
                                         toSave.CreatedOn.Ticks, toSave.ID);

EditedOn = new DateTime(Convert.ToInt64(reader["EditedOn"])),

Part of the creation statement.

[CreatedOn] NUMERIC  NOT NULL,
[EditedOn] NUMERIC  NOT NULL

Hopefully that helps a bit. I would provide atleast a time, even if it is a min value. Since a null may cause issues, if it will even let you do it.

Matt Petty

Senior Software Developer

SmarterTools Inc.

www.smartertools.com

Greg Corwin Replied

8/17/2016 at 10:24 AM

thanks, what about the BounceAddress? what file did you fine this in? I can't seem to find a file in the MRS folder that has this code

-GWC

Matt Petty Replied

8/18/2016 at 12:12 PM

Employee Post

BounceAddress is a GUID we generate.

Matt Petty

Senior Software Developer

SmarterTools Inc.

www.smartertools.com

Greg Corwin Replied

8/18/2016 at 12:22 PM

so theoretically we can just create a 32 digit GUID?

-GWC

Matt Petty Replied

8/19/2016 at 10:17 AM

Employee Post

Yup, not an email address just a straight up GUID. The system automatically adds the email address domain and such when sending.

Matt Petty

Senior Software Developer

SmarterTools Inc.

www.smartertools.com

Back to Community Threads

Reply to Thread

Enter the verification text