Parker Software Ltd Homepage
Forum Home Forum Home > Email2DB Email Parser > Scripting > Scripting Samples
  New Posts New Posts RSS Feed: Updating DB Via Script
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Updating DB Via Script
 Post Reply Post Reply Page  123>
Author
Message
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Stephen View Drop Down
Admin Group
Admin Group
Avatar

Joined: 21 Oct 2005
Location: Stoke on Trent
Posts: 1389 		Post Options Post Options   Quote Stephen Quote  Post ReplyReply Direct Link To This Post Topic: Updating DB Via Script
    Posted: 05 May 2009 at 3:46pm

You can use an Email2DB script to update a database in addition to the regular update database option. This can be useful if you want to conditionally update a database, or you want to perform multiple, custom updates.

The code below shows a simple database update script.
 
First use the Add Reference button in the script editor to add a reference to the Microsoft ActiveX Data Objects 2.x. You will then be able to use the full ADO objects in your scripts.
 


Sub Main()
Dim CO As New ADODB.Connection
Dim RS As New ADODB.Recordset


    On Error GoTo erl
    CO.Open "your connectionstrng"
    RS.Open "SELECT * FROM {yourtablename}",CO,adOpenDynamic,adLockBatchOptimistic,adCmdText
    RS.AddNew
    RS.Fields("fieldname1")=FIELDS_Value("fieldname1")
    RS.Fields("fieldname2")=FIELDS_Value("fieldname2")
    RS.Update
    RS.Close
    CO.Close
    Call AddToLog("Script updated database")
    Exit Sub


erl:
    Call AddToLog("Failed to update database " & Err.Description)
End Sub
 
The FIELDS_Value({fieldname}) array is used to access all your extracted fields. You can use this to assign field values to your table that you want to use.
 
Another method would be to define ADO command objects to execute stored procedures.
 
Steve


Edited by Stephen - 05 May 2009 at 3:47pm
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 875 		Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 02 Sep 2009 at 2:38pm
To set the command timeout to unlimited, do:

CO.CommandTimeout = 0
 
before running your command in the database.
Daniel Tallentire
Support
Parker Software
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 875 		Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 02 Sep 2009 at 2:39pm

To run a command instead of doing an update:

 
Sub Main()
Dim CO As New ADODB.Connection
Dim RS As New ADODB.Command

    On Error GoTo erl
    CO.Open "your connectionstrng"
    CO.CommandTimeout = 0
    RS.Execute"UPDATE {yourtablename} SET VAlues ...",CO,adOpenDynamic,adLockBatchOptimistic,adCmdText
    CO.Close
    Call AddToLog("Script updated database")
    Exit Sub

erl:
    Call AddToLog("Failed to update database " & Err.Description)
End Sub


Edited by Daniel - 02 Sep 2009 at 2:40pm
Daniel Tallentire
Support
Parker Software
Back to Top
wa1sov View Drop Down
New User
New User


Joined: 22 May 2011
Location: USA
Posts: 15 		Post Options Post Options   Quote wa1sov Quote  Post ReplyReply Direct Link To This Post Posted: 22 May 2011 at 2:34am
I recently purchased EMAIL2DB. I am having difficulty locating enough information to perform some conditional custom scripts. Based on teh value of a field in a currently processed trigger, perform a conditional test on one of the fields and based on the number returned (>1) copy all fields of the database to a new record. If the number returned in the test is say 10, I need to repeat this function 10 times. This is all being done with a CSV file. Are there examples of this kind of code available and if not, how can I implement this?
 
Thanks
 
Peter
Back to Top
swhiteman View Drop Down
Expert
Expert


Joined: 13 Mar 2008
Location: NYNY
Posts: 258 		Post Options Post Options   Quote swhiteman Quote  Post ReplyReply Direct Link To This Post Posted: 22 May 2011 at 7:47pm
You should've started a new topic, and I could use more clarity on your setup, but if you're talking about writing multiple identical rows to a CSV based on a numerical variable found in the source e-mail, then the easiest way I can think of is Use Additional Actions > Run Script and do a loop over n ADO SQL executions like the ones described above, using the ODBC Text Driver (which can insert quite ably to CSVs).

Note you aren't using any of the built-in Update Databases methods here, because AFAIK the CSV File Update only inserts one row per trigger, and while the Database Update could well do a loop within, say Microsoft's extended T-SQL dialect, the simplified SQL supported by the ODBC Text Driver won't allow looping (nor, obviously, stored procedures!).  So you do the looping from a little Basic code.
Back to Top
wa1sov View Drop Down
New User
New User


Joined: 22 May 2011
Location: USA
Posts: 15 		Post Options Post Options   Quote wa1sov Quote  Post ReplyReply Direct Link To This Post Posted: 23 May 2011 at 1:31am
Thanks for the reply. I thought I found the topic closest to what I was trying to do. As you can see perhaps nothing is close to what I am trying to do. Sorry if I broke the protocol. As far as the scipting goes, I am far from an expert at VBasic Scripting or any of the scripting languages. I am familar with programming in many other languages such as Basic, C, Fortran, etc. To decribe what I am trying to do again:
 
EMAIL2DB has a trigger setup to populate several fields in a CSV database file from an EMAIL. One of these fields has a value in it that if larger than a given value (>1) needs to fire a loop to copy each field in the database to the next record and to continue writing new records with the redundant informaiton until the value has of this test field has decimated to 1. At that point the loop would terminate. The problem I have is that I do not understand enough of the scripting language to know how to select the next record in the CSV file. I am afraid you lost me with some of the terminology like AFAIK etc. I am by no means an expert at SQL databases and have had very little experience with it. I have set up EMAIL2Db to work with the given email and wrote some scripts to calculate some values to be added to the current record. But iterating through the records with the propper syntax is not clear to me and I have only been able to find very limited examples. I expect I can still use standard For Next looping and standar conditional statements within these scripts. So it is just a matter of getting enough information on how to manipulate the CSV file during the trigger.
 
You made a comment regarding only being able to process one record with the current trigger. That sound like it may pose a problem where I may need to configure all this in external code to EMAIL2DB which would be very dissappointing. Is this going to be the case?
Back to Top
swhiteman View Drop Down
Expert
Expert


Joined: 13 Mar 2008
Location: NYNY
Posts: 258 		Post Options Post Options   Quote swhiteman Quote  Post ReplyReply Direct Link To This Post Posted: 23 May 2011 at 3:02am
I thought I found the topic closest to what I was trying to do.
Well, a forum post has a topic, but it's just an individual post, so it's kind of like responding to a really old e-mail instead of creating a new one. Not so much "protocol" as just the best way to get help for a new issue.
I am familar with programming in many other languages such as Basic
Then you should be fine with E2DB scripting -- it's Sax Basic, an embedded (extension) language that is very VB6/VBA-like and can call local COM objects (such as ADODB as in the examples), making it very powerful.  The fact that it can do things like its own DB lookups, and even call 3rd-party COM objects (I have had it calling an encryption lib) means there can be almost too many ways to skin a cat in Email2DB.  Of course you want to use the built-in functions whenever you can, since it is easier to visualize the rules, but the Sax scripting is there too.

Anyway, to your situation.

Text files like CSVs will are not SQL databases, but Microsoft provides a very basic ODBC (database) driver for text files that you can use to read/write records to/from text files as if they were database tables, and the language you use for such read/writes is a very, very tiny subset of SQL. It's not that you have to be a SQL expert to use the ODBC driver for database tables.  In fact, SQL experts are likely to be more frustrated than SQL novices, since the text driver supports a mere handful of SQL commands (it doesn't even allow modifying existing rows of a CSV using the SQL UPDATE function, but it allows -- as you need here -- appending rows using the SQL INSERT command).  

If you are in any piece of software that requires an "ODBC database" and doesn't even understand the idea of a text file, but all you have is a text file, then the ODBC text driver is a godsend, since it can disguise the text file as a database and allow you to do some pretty cool hacks as a result.

However, if you are really worried about learning a bit of SQL to talk to your CSV, in truth it isn't necessary to talk to the CSV as if it's a database once you're in the Email2DB scripting world -- I was just saying you could easily adapt Stephen/Daniel's scripting suggestions, which are written to expect an ODBC database connection string, to talk to your CSV as a database.  But CSVs are just text files.  All you need to talk to a text file is basic file I/O.  In Email2DB Sax scripting, these are commands like Open, Print, and Close (you should look under Help>Language Help in the script editor for more).

So, for example, if you have assigned your counter variable to the Email2DB variable %counter%, and you have extracted 3 others --  %field1%, %field2%, %field3% -- from the e-mail, here's how to write the 3 fields out counter times in a row to the same CSV file:

Sub Main()
    Open "c:\my.csv" For Append As #1
    For i = 1 To FIELDS_Value("counter")
        Print #1,FIELDS_Value("field1");",";FIELDS_Value("field2");",";FIELDS_Value("field3")
    Next i
    Close #1
End Sub

 You made a comment regarding only being able to process one record with the current trigger. That sound like it may pose a problem where I may need to configure all this in external code to EMAIL2DB which would be very dissappointing

You can only process one incoming e-mail per trigger, and the built-in CSV updater (not the  Sax scripting version I provided above) only writes the variables out once to the CSV.  If you use Sax, you can write out the variables from one e-mail to as many different destinations, as many times, as you want.
Back to Top
wa1sov View Drop Down
New User
New User


Joined: 22 May 2011
Location: USA
Posts: 15 		Post Options Post Options   Quote wa1sov Quote  Post ReplyReply Direct Link To This Post Posted: 24 May 2011 at 3:24am
Smile Ok I'm getting somewhere now. Thanks so much for the tip. I am just having a little trouble with some of the Fields not copying though the syntax seems correct. I placed the new script in the "additional actions" but it seems that it is executing before the current field extraction process. I'm not sure about what is being executed. It may be that opening the file from within the "additional actions" script is not the place to do it. I am going to play some more and get back to you when I understand this a little better. But thanks so much for getting me off "Top Dead Center".
Back to Top
swhiteman View Drop Down
Expert
Expert


Joined: 13 Mar 2008
Location: NYNY
Posts: 258 		Post Options Post Options   Quote swhiteman Quote  Post ReplyReply Direct Link To This Post Posted: 24 May 2011 at 3:49am
I placed the new script in the "additional actions" but it seems that it is executing before the current field extraction process.
Mmm, certainly that should not be the case!  What is the difference between the Fields that copy and those that don't?  Do you see these fields if you also do the built-in CSV Update (do not write to the same CSV as you do in the script, use a different CSV filename in each place).
Back to Top
wa1sov View Drop Down
New User
New User


Joined: 22 May 2011
Location: USA
Posts: 15 		Post Options Post Options   Quote wa1sov Quote  Post ReplyReply Direct Link To This Post Posted: 25 May 2011 at 1:21am
Yea, I inspected the field insert carefully and cannot see why certain fields would not append while other do. I tried copied the first CSV file to a second renamed and let the new script try to append to that and it did not work either though it does iterate the correct number of records. The original CSV file updated all the fields as it always does. The interesting thing is that it appears that the appending is taking place before the field extraction from the processed email. I didn'think that the additional actions script would execute until the intial database was updated and saved. I am not sure what the order of execution is with EMail2db. Anyway, I will try some other things. Thanks for your help and ideas.
 
Smile
Back to Top
 Post Reply Post Reply Page  123>

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.391 seconds.
These are the forums for Parker Software, developers of Live Chat Software: WhosOn and Email Automation Software: Email2DB.