Parker Software Forum : Script for inserting binary file data into a db

Script for inserting binary file data into a db : I am having a problem when i...

Mon, 23 Apr 2012 15:43:55 +0000

Author: jjaroska
Subject: 7069
Posted: 23 Apr 2012 at 3:43pm

I am having a problem when i read the file inserted into the database. i get the error the magic number is wrong. it seems that this is a common asp.net error message when the whole file is not present. i can write and read other files when i use the file component from devexpress but not when i use the script from this thread. can someone help?

Edited by jjaroska - 23 Apr 2012 at 3:44pm

Script for inserting binary file data into a db : We had a problem with the script...

Mon, 27 Jun 2011 13:36:49 +0000

Author: andyj1970
Subject: 7069
Posted: 27 Jun 2011 at 1:36pm

We had a problem with the script running againt Oracle 11g database to load attachments as above. The first attachment loads fine but subsequent ones were not being loaded. The error message shown in the service log was:

2011-06-22:12:20:07 ## file closed successfully ##

2011-06-22:12:20:07 Error in script: ROW-00014: Cannot update row as the data in the database has changedzfPu

2011-06-22:12:20:07 New record added to database

I checked some info online and the script appeared to be fine. I'm not a VB expert but one suggestion was to change the connection from adOpenDynamic to adOpenKeyset as below.

cec_contacts.Open "cec_contacts",DBConnection,adOpenKeyset,adLockOptimistic

Once we did this the script now processes multiple attachments successfully.

Script for inserting binary file data into a db : Thanks James,This is my script,...

Wed, 15 Jun 2011 13:45:52 +0000

Author: ash0602
Subject: 7069
Posted: 15 Jun 2011 at 1:45pm

Thanks James,

This is my script, as mentioned earlier, %msg_uid% is not getting updated in the parent table (but getting updated in the child table). I was doing field extraction, and now even after adding in the script below, the cec_contacts.cnt_msg_uid (my parent table's column for %msg_uid%) is not getting updated. Without which I'll not be able to reference attachments from the parent table. Need help on this:

Sub Main()

' this script saves the saved attachment filenames in the Attachment table

' we have added a reference to the Microsoft ActiveX Data Objects (ADO).

' it assumes you already have a table called 'cec_contact_attachments' in the database.

If MSG_AttachmentCount=0 Then Exit Sub ' if no attachments do nothing

Dim DBConnection As New ADODB.Connection

' child table

Dim cec_contact_attachments As New ADODB.Recordset

' parent table

Dim cec_contacts As New ADODB.Recordset

Dim myStream As ADODB.Stream

Dim A As Integer

On Error GoTo erl

DBConnection.ConnectionString="Provider=OraOLEDB.Oracle.1;Password=custemails;Persist Security Info=True;User ID=custemails;Data Source=apexdev;" ' use the connection string specified on the Database tab

DBConnection.Open

' open the cec_contacts table

cec_contacts.Open "cec_contacts",DBConnection,adOpenDynamic,adLockOptimistic

' update relevant columns

cec_contacts.Fields("cnt_msg_uid")=MSG_UID

cec_contacts.Fields("cnt_file_count")=MSG_AttachmentCount

cec_contacts.Update

cec_contacts.Close

' open the cec_contact_attachments table

cec_contact_attachments.Open "cec_contact_attachments",DBConnection,adOpenDynamic,adLockOptimistic

For A=1 To MSG_AttachmentCount

If len(msg_attachment(A)) > 0

Then

AddToLog("Processing: " & MSG_Attachment(A))

cec_contact_attachments.AddNew

'cec_contact_attachments.Fields("cat_cnt_id")=CNT_ID

cec_contact_attachments.Fields("cat_cnt_msg_uid")=MSG_UID

cec_contact_attachments.Fields("cat_file_sequence")=A

cec_contact_attachments.Fields("cat_file_name")=MSG_Attachment(A)

Set myStream = New ADODB.Stream

AddToLog("File is type: " & MSG_AttachmentType(A))

myStream.Type = adTypeBinary

AddToLog("Opening temp file: " & MSG_AttachmentTempLocation(A))

myStream.Open

myStream.LoadFromFile(MSG_AttachmentTempLocation(A))

cec_contact_attachments.Fields("cat_file_content")=myStream.Read

cec_contact_attachments.Fields("cat_file_size")=myStream.Size

myStream.Close

cec_contact_attachments.Fields("cat_file_mime_type")=MSG_AttachmentType(A)

cec_contact_attachments.Update

End If

Next A

cec_contact_attachments.Close

DBConnection.Close

Call AddToLog("cec_contact_attachments table updated successfully")

Exit Sub

erl:

Call AddToLog("Error in script: " & Err.Description)

End Sub

Script for inserting binary file data into a db : The script would be the same,...

Wed, 15 Jun 2011 13:38:33 +0000

Author: James Horton
Subject: 7069
Posted: 15 Jun 2011 at 1:38pm

The script would be the same, just make sure that the Update Database section of the trigger is referencing your database via the Connection String or builder.

Script for inserting binary file data into a db : Hi, Can someone help with similar...

Wed, 15 Jun 2011 10:43:58 +0000

Author: ash0602
Subject: 7069
Posted: 15 Jun 2011 at 10:43am

Hi,

Can someone help with similar script for Oracle, please?

And where should I put this script into?

Thanks,

Ashish

Edited by ash0602 - 15 Jun 2011 at 10:45am

Script for inserting binary file data into a db : That normally means you haven't...

Mon, 10 May 2010 09:36:23 +0000

Author: Daniel
Subject: 7069
Posted: 10 May 2010 at 9:36am

That normally means you haven't added the reference to ADO with the references button at the top of the window; have you done this?

Script for inserting binary file data into a db : Dim DBConnection As New ADODB.Connection...

Thu, 29 Apr 2010 19:45:40 +0000

Author: iareanet
Subject: 7069
Posted: 29 Apr 2010 at 7:45pm

Dim DBConnection As New ADODB.Connection
Dim Attachments As New ADODB.Recordset
Dim myStream As ADODB.Stream
are giving me an error

expecting a specific object type

Script for inserting binary file data into a db : Hi Jen (sorry for the delayed...

Thu, 18 Feb 2010 20:50:44 +0000

Author: Daniel
Subject: 7069
Posted: 18 Feb 2010 at 8:50pm

Hi Jen (sorry for the delayed response, I didn't spot this)

As far as I am aware there aren't any issues with file size - I've definitely done 1MB files before. It could be a setting in the database options that controls this, or a time out? What error code does it give?

Script for inserting binary file data into a db : i'm using a variation on...

Thu, 14 Jan 2010 19:28:27 +0000

Author: jenik
Subject: 7069
Posted: 14 Jan 2010 at 7:28pm

i'm using a variation on this script to insert an attachment into CRM. but it seems to fail when the attachment is over 200 KB. is there a setting either in Email2DB or on the server that i can do to increase the file size limit? i've added a buffer of 500KB to my code, but it doesn't seem to make a difference:

myStream.Read(512000)

thanks,
jen

Script for inserting binary file data into a db : For SQL Server you could alsouse: Attachments.Open...

Tue, 19 May 2009 13:50:02 +0000

Author: Stephen
Subject: 7069
Posted: 19 May 2009 at 1:50pm

For SQL Server you could also use:

Attachments.Open "Attachments",DBConnection,adOpenDynamic,adLockOptimistic,adCmdTableDirect

The 'adCmdTableDirect' opens the specified table directly.

Steve

Script for inserting binary file data into a db : For MS SQL 2005 the following...

Sun, 17 May 2009 16:45:59 +0000

Author: matrixIII
Subject: 7069
Posted: 17 May 2009 at 4:45pm

For MS SQL 2005 the following line as giving an error - something about stored procedure not found.

Attachments.Open "Attachments",DBConnection,adOpenDynamic,adLockOptimistic

I changed it to the following and it started working

Attachments.Open "Select * from Attachments where 1=2",DBConnection,adOpenDynamic,adLockOptimistic

Thanks.. this article was a great help!

Edited by matrixIII - 17 May 2009 at 4:46pm

Script for inserting binary file data into a db : Hi Eds, Thanks for that update!...

Wed, 19 Nov 2008 11:50:42 +0000

Author: Daniel
Subject: 7069
Posted: 19 Nov 2008 at 11:50am

Hi Eds,

Thanks for that update!

Script for inserting binary file data into a db : When writing to MySQL 5.1 you...

Sun, 16 Nov 2008 18:26:57 +0000

Author: Eds
Subject: 7069
Posted: 16 Nov 2008 at 6:26pm

When writing to MySQL 5.1 you will need the following or else the binary field will be null.

DBConnection.CursorLocation = adUseClient
DBConnection.Open

Script for inserting binary file data into a db : Thefollowing script modifies the...

Mon, 04 Aug 2008 14:37:38 +0000

Author: Daniel
Subject: 7069
Posted: 04 Aug 2008 at 2:37pm

The following script modifies the "Sample" script for "Backup To MS Access & Save Attachments" and saves the data directly to the table.

This is the layout of the attachments table:

Sub Main()   ' this script saves the saved attachment filenames in the Attachment table
    ' we have added a reference to the Microsoft ActiveX Data Objects (ADO).
    ' it assumes you already have a table called 'Attachments' in the database.
    If MSG_AttachmentCount=0 Then Exit Sub ' if no attachments do nothing
    Dim DBConnection As New ADODB.Connection
    Dim Attachments As New ADODB.Recordset
    Dim myStream As ADODB.Stream
    Dim A As Integer
    On Error GoTo erl
    DBConnection.ConnectionString=SQL_Connection ' use the connection string specified on the Database tab
    DBConnection.Open
    ' open the Attachments table
    Attachments.Open "Attachments",DBConnection,adOpenDynamic,adLockOptimistic
    For A=1 To MSG_AttachmentCount
        If len(msg_attachment(A)) > 0
          AddToLog("Processing: " & MSG_Attachment(A))
          Attachments.AddNew
          Attachments.Fields("UID")=MSG_UID
          Attachments.Fields("AttachmentNumber")=A
          Attachments.Fields("AttachmentName")=MSG_Attachment(A)
          Set myStream = New ADODB.Stream
          AddToLog("File is type: " & MSG_AttachmentType(A))
          myStream.Type = adTypeBinary
          AddToLog("Opening temp file: " & MSG_AttachmentTempLocation(A))
          myStream.Open
          myStream.LoadFromFile(MSG_AttachmentTempLocation(A))
          Attachments.Fields("AttachmentData")=myStream.Read
          Attachments.Fields("AttachmentSize")=myStream.Size
          myStream.Close
          Attachments.Fields("AttachmentType")=MSG_AttachmentType(A)
          Attachments.Update
      End If
    Next A
    Attachments.Close
    DBConnection.Close
    Call AddToLog("Attachment table updated successfully")
    Exit Sub
erl:
    Call AddToLog("Error in script: " & Err.Description)
End Sub

Edited by Daniel - 28 Jan 2009 at 11:00am