Where are the Attachment file stored

Discussions related to custom development with Select.
enendza
Posts: 75
Joined: Wed Oct 16, 2019 12:22 pm

Where are the Attachment file stored

Post by enendza »

Morning -

Couple of questions about the attachments associated with an order.

Where are the attachments stored? I see this \\{filename}\attachments\SignedDocuments\{filename}, how to I access this from file explorer to view the files outside of SoftPro>

Is the URI recorded in the database associated with the order?

Thanks in advance for the help

Emma
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Where are the Attachment file stored

Post by BobRichards »

The attached files are stored in the blob database as serialized objects. The only way to get an attachment is to open the order and open it using Select API commands. Without opening the order and getting attachment information, you cannot even determine where to look in the blob database for an attachment.

No Windows File Explorer plugin exists for Select attachments.
Bob Richards, Senior Software Developer, SoftPro
enendza
Posts: 75
Joined: Wed Oct 16, 2019 12:22 pm

Re: Where are the Attachment file stored

Post by enendza »

Thanks so much for the reply.

I have this challenge:

We have a background process that uploads attachments to SoftPro using the API
Most of the time is works fine
However, if the file is open by another user we can't access the order and get the error "File cannot be opened" because it is locked by another user.
Therefore, we cannot upload the file and the background process ends
Any advice on how to handle that?

We were wondering if we could just persist it directly into the DB without using the API?

Thanks in advance

Emma
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Where are the Attachment file stored

Post by BobRichards »

You cannot add the attachment to the database directly. It has to be done through the order.

We have other users that have the same problem with how to modify an order if the order is already open. It frequently boils down to writing information to a queue (such as a custom DB table) and periodically waking up a handler to unload items in the queue that are not open. You can use the Select API to create a timer (IScheduledTrigger) to wake up a handler every 60 seconds to service the queue.
Bob Richards, Senior Software Developer, SoftPro
enendza
Posts: 75
Joined: Wed Oct 16, 2019 12:22 pm

Re: Where are the Attachment file stored

Post by enendza »

thanks so much for the response.
enendza
Posts: 75
Joined: Wed Oct 16, 2019 12:22 pm

Re: Where are the Attachment file stored

Post by enendza »

Follow up question -

is there a way that I can find out who has the record / order locked?

This would allow the background process to send an email message to let the user know that the automated process is trying to add a file please exit the order

thanks in advance

Emma
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Where are the Attachment file stored

Post by BobRichards »

It is possible by going to the SQL tables but it is not guaranteed. Be aware that this may not work if the user exited Select without closing properly or for other edge case.

Code: Select all

DECLARE @session uniqueidentifier = (
		SELECT l.[SessionID]
		FROM [pf].[Order] o
		JOIN [core].[Lock] l ON o.[Guid] = l.[ObjectIdentifier]
		WHERE Number = 'XAT19000011'
		)

DECLARE @securityID uniqueidentifier = (
		SELECT [SecurityIdentityID]
		FROM [core].[Session]
		WHERE [ID] = @session
		)

DECLARE @email varchar(max) = (
		SELECT [EmailAddress]
		FROM [core].[SecurityUser]
		WHERE [ID] = @securityID
		)

select @email
Bob Richards, Senior Software Developer, SoftPro
toddsou
Posts: 75
Joined: Wed Jul 25, 2012 9:39 am

Re: Where are the Attachment file stored

Post by toddsou »

Hi-

We are looking to discover the attachment names (don't need the blobs/content). You had mentioned that this metadata is serialized within the saved Order.
The attached files are stored in the blob database as serialized objects. The only way to get an attachment is to open the order and open it using Select API commands. Without opening the order and getting attachment information, you cannot even determine where to look in the blob database for an attachment.
Can you point us to the right location for getting this serialized metadata information? We're not opposed to parsing it to get at what we need.

Thank you.
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Where are the Attachment file stored

Post by BobRichards »

The serialized data format is not public and can change at any time. This allows use to make improvements, changes and/or additions to enhance our product. Changes should be invisible to third-party developers so their code does not need to change as we move from version to version.

Sorry
Bob Richards, Senior Software Developer, SoftPro
toddsou
Posts: 75
Joined: Wed Jul 25, 2012 9:39 am

Re: Where are the Attachment file stored

Post by toddsou »

Understood on your stance, and I agree that there are consequences to this type of coupling and dependency.

The solution being worked on is for some automated testing to take place, and so we're looking to give those testing scripts visibility to the SPS Order (via database entries, e.g.). They do not have access to the SDK.

To your concern, we are prepared to make corresponding changes to our solution if/when we upgrade to a new SPS version and those inner workings have been improved/changed. That is a known risk with a known mitigation plan. I can provide plenty of details about the formal process we go through to vet a new release, including the changes we have to make anyway to our packages as a result of new/changed behavior in the SDK/service. So any changes needed by the automation test scripts would simply be included in that migration effort.

And so, we would appreciate some insight and guidance wrt identifying the named Attachments for a given Order w/o the SDK.

Thank you.
Post Reply