Source language: Translate to:

Storing Very much data into a Memo Field...

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Storing Very much data into a Memo Field...

Postby David de Argentina » Fri Jul 26, 2013 1:58 pm

Hi all,

I'm doing a program that needs put a lot of data into a memo fields of an MSAccess Database.
Data is stored into a NeoBook Variable

I tried 3 ways:

a)
Code: Select all
dbpAddRecord / SetVar "[db.table.MemoField]" "A lot of data..." / dbpSaveEdits "db" Table"


b)
Code: Select all
dbpExecSQL "db" "Insert into table (MemoField) values ('a lot of data...');" ""


c)
Code: Select all
dbpClearParameters "db" "ClearVariables=Yes"
dbpAddParameter "db" "@MemoField" "Memo" "Input" "0" "a lot of data..."
dbpExecProc "db" "MyInsertFunction" "table"


The 3 ways works... not very fine, are terriblely sloooooow....

Does any know another way to insert a big amount of data into a memo field ?

Thanks in advance,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Storing Very much data into a Memo Field...

Postby Gaev » Fri Jul 26, 2013 3:19 pm

Daid de Argentina:
put a lot of data into a memo fields of an MSAccess Database.
are terriblely sloooooow.
Can you define ...

1) a lot of data ... what is the average/maximum number of bytes ?
2) terribly slow ... how many seconds ?

... and is this database stored on a local drive or across the internet ?
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Storing Very much data into a Memo Field...

Postby David de Argentina » Fri Jul 26, 2013 3:57 pm

Hi Gaev,

I'm doing an Outlook Express Database (DBX) exporter and MDB importer.
Time & Data are random, depending on each email data.
I tested some emais with 3-4 MB attachments and take a couple of minutes... each one !
I'm testing just now link the save imported mails procedure within the Load mails VBScript Function.
It should be more efficient, because it don't need send data to NeoBook neither import data from Neo Variables.
Perhaps vbScript should be the better solution...
I guess next week i could send you a program for testing purposes.
Grettings from Buenos Aires,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Storing Very much data into a Memo Field...

Postby Neosoft Support » Sat Jul 27, 2013 10:27 am

What about using a picture field instead? Despite the name, a picture field is actually a blob field and can be used to store any type of file. It might be faster since only the file's name is transferred between NeoBook and the plug-in. However, your data would have to be stored in a file.

If you're copying data from one table to another, you might be able to use SQL:

http://www.w3schools.com/sql/sql_select_into.asp

or between two databases:

http://stackoverflow.com/questions/940930/copy-data-from-a-table-in-one-database-to-another-separate-database
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Storing Very much data into a Memo Field...

Postby David de Argentina » Sat Jul 27, 2013 1:04 pm

Thanks Dave,

Program does the following:

a) Extract any EML file from DBX Outlook Express Database (Inbox.dbx, sent.dbx, etc) and Store the created EML Files to an selected (Drive)Folder
b) Batch import of all EML Files to store them into a selected table of a MDB Access Database
c) User can pick one (or several) EML Files and store them into a selected table of a MDB Access Database
d) User can view the EML File, Extract Attachments, Print, etc.
e) User can view the stored into MDB table, Extract Attachments, Print, etc.

DBX Files are like Frankenstein ones, not ADODB or ODBC compatible databases. No known programs can handle them.
Is for this reason i can't use your suggestions...

This program could help Vista, Seven or 8 Windows users to keep access old emails stored on XP Machine...

Greetings from Buenos Aires,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Storing Very much data into a Memo Field...

Postby Neosoft Support » Sun Jul 28, 2013 10:53 am

Unfortunately, I don't know anything about DBX files. :(
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Storing Very much data into a Memo Field...

Postby dpayer » Mon Jul 29, 2013 9:35 am

David de Argentina wrote:Hi Gaev,

I'm doing an Outlook Express Database (DBX) exporter and MDB importer.
Time & Data are random, depending on each email data.
I tested some emais with 3-4 MB attachments and take a couple of minutes... each one !David de Argentina



David, you are taking on a project I thought about but never jumped into.

I think Microsoft has many low level tools to access/search/sort data that we do not have so their searches are relatively quick. I suggest separating out the attachments from the from the .eml file (they are mime encoded), and storing them in a folder and only reference the attached files in the database by a title that would be a hash of the file or some other way to get around duplicate names.

This way, your searches are limited to smaller fields and the database size does not get uncontrollable. (I have Outlook PST files that are 4-5 gigs - they are similar to the files Outlook Express uses for storage).

Sounds like an interesting and challenging project. An email client does a lot of things.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Storing Very much data into a Memo Field...

Postby Gaev » Mon Jul 29, 2013 10:14 am

David de Argentina:

This suggestion from David P ...
I suggest separating out the attachments from the from the .eml file (they are mime encoded), and storing them in a folder and only reference the attached files in the database by a title that would be a hash of the file or some other way to get around duplicate names.
... is worth considering.

The file can be stored in folders that mirror the mailbox folder structures within Outlook Express plus 'the message record number' in the database ... and the actual file names would be file name of the attachment e.g. ...

c:\OE\NeoBook\Customers\John Doe\1234\abc.jpg
c:\OE\NeoBook\Customers\John Doe\1234\xyz.jpg
c:\OE\NeoBook\Customers\John Doe\4567\abc.jpg
etc.

... where ...

OE is the root folder for the attachments
John Doe is a mailbox folder (in Outlook Express) within Customers within NeoBook
1234 and 4567 are the message records associated with the attachment
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Storing Very much data into a Memo Field...

Postby dpayer » Mon Jul 29, 2013 11:36 am

Gaev wrote:The file can be stored in folders that mirror the mailbox folder structures within Outlook Express plus 'the message record number' in the database ... and the actual file names would be file name of the attachment e.g. ...

c:\OE\NeoBook\Customers\John Doe\1234\abc.jpg
c:\OE\NeoBook\Customers\John Doe\1234\xyz.jpg
c:\OE\NeoBook\Customers\John Doe\4567\abc.jpg
etc.


Just remember that some emails have multiple attachments and over time, you may have the same file name used again and again. So I might suggest creating a GUID for each attachment (see this thread on creating a GUID using a NB function - http://neosoftware.com/community/viewto ... 22&t=19563 ). Rename the file using the GUID. Then store in a folder. Have a table in the DB that associates the GUID with the original file name (and its location).

[Hmmm, may want to have a flat file with the same information in case the database gets whacked] - and create a restore process in case something happens to the db.

Use the GUID in the database to represent the file, then retrieve it as needed.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Storing Very much data into a Memo Field...

Postby David de Argentina » Mon Jul 29, 2013 12:32 pm

Hi Dave (@NeoSoftware)

I'm working with a VBScript Function in order to save emails into a MDB Table.
Most cases, it works fine and fast.
On a few cases, i received an error dialog as this:

Image

(English traslation of the error: "Data provider or other service returned an E_FAIL status")

This kind of dialog appairs on Debug Mode, and allow the options "Cancel" or "Continue"
On Execution Mode, Dialog appairs as MessageBox, with a button "Ok" only, and cancel the process.

Could you consider add the "Continue" button into this messagebox ?
or a clause something like this:
"On Error resume next"

Thanks in advance,
David de Argentina
Last edited by David de Argentina on Mon Jul 29, 2013 12:43 pm, edited 2 times in total.
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Storing Very much data into a Memo Field...

Postby David de Argentina » Mon Jul 29, 2013 12:35 pm

Hi Gaev & DavidP;

Actually program uses a specific folder ( c:\Mails ) in order to store the extracted EML files
The attachments are stored into a C:\Mails\Temp Folder
Program is still under construction, when terminate, will allow user to select where the attachments will be put.

Thanks to both,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Storing Very much data into a Memo Field...

Postby Gaev » Mon Jul 29, 2013 12:59 pm

@David P.:
Just remember that some emails have multiple attachments and over time, you may have the same file name used again and again.
Not to go off on a tanget ... but the proposed naming scheme takes care of that ... e.g. you store the email in mailbox folder named John Doe ... which is a child of mailbox folder Customers ... which in turn is a child of mailbox folder NeoBook ... assume that the UNIQUE record number (in the Database) for this email message is 1234 ... then all attachments for this message would be placed in a (windows) folder c:\OE\NeoBook\Customers\John Doe\1234\ ... if this message had attachment files abc.jpg and xyz.jpg, you end up with ...

c:\OE\NeoBook\Customers\John Doe\1234\abc.jpg
c:\OE\NeoBook\Customers\John Doe\1234\xyz.jpg

... the John Doe mailbox folder may later contain another message with an attachment called abc.jpg ... which may or may not have the same content as the earlier message ... assuming that this new message was stored in the Database with the record number of 4567 ... its attachment would be stored in ...

c:\OE\NeoBook\Customers\John Doe\4567\abc.jpg

... different messages with the same named attachment file should be placed in separate locations ... so I don't see the problem with multiple attachments.

@David de Argentina:
Program is still under construction, when terminate, will allow user to select where the attachments will be put.
... and hopefully to also select the folder for the EML files (c:\mail) ... and the name and folder for the database.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Storing Very much data into a Memo Field...

Postby David de Argentina » Mon Jul 29, 2013 1:54 pm

First Beta of the program:

http://www.neoespecialistas.com.ar/files/EMLHandler.zip

Unzip the Zip on any Folder.

Notes:

* Zip has 2 files:
DBX2EML2MDB.exe is the the mail handler
MyMailViewer.exe is the mail viewer. First time you run, program try to set itself to the default launch program for .EML files. If not work at second try, you could set this program manually

* In theory, you could open any EML file from anywhere. I defined c:\Mails folder to export the Mails within DBX files. You can change this.

* Batch Import EML to MDB: At this time, i compiled the program with the dbpAddRecord / Setvar "[db.Table.field]" "blah" / dbpSaveEdits. This is not the best solution, but i don't get error messages.

* When you open a mail using the MyMailViewer program (or double click on DBX2EML2MDB.exe row) if you see into the attachment combobox something like "NoName.EML", is just a resend email that contains another EML within. You could open it clicking the "See Attachment" button.

Tell me if anything works fine for you.
Greetings from Buenos Aires,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Storing Very much data into a Memo Field...

Postby Neosoft Support » Tue Jul 30, 2013 10:38 am

Hi David,

I'm working with a VBScript Function in order to save emails into a MDB Table.
Most cases, it works fine and fast.
On a few cases, i received an error dialog as this...

This kind of dialog appairs on Debug Mode, and allow the options "Cancel" or "Continue"
On Execution Mode, Dialog appairs as MessageBox, with a button "Ok" only, and cancel the process.


I don't think NeoBook can continue running a script that has crashed outside of debug mode. Have you tried adding an "On Error..." line to your script?
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests

cron