You are viewing limited content. For full access, please sign in.

Question

Question

How can I export Laserfiche images to a SQL Server database table directly?

asked on October 30, 2014

We used to export Laserfiche files as images to a hard drive, then in our web application, we load the images using upload functions. These images are stored in a SQL Server database table.

 

Now we want to directly export a Laserfiche file to the SQL Server database table without save to a hard drive. Is there any solution?

 

Thanks!

Hu

1 0

Replies

replied on October 30, 2014

Hu,

 

To test it I created a SQL table with three fields; DocID, DocName, and Image.  DocID is an int, DocName is nvarchar(255), and Image is a SQL image type.

 

The code snippet below is for a workflow SDK script activity.  It exports a document from LF as a tif image and stores it directly in a SQL table defined above. 

(NOTE: You have to add a script reference to Laserfiche.DocumentServices to expose the export functions)

 

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports Laserfiche.RepositoryAccess
Imports Laserfiche.DocumentServices
Imports System.IO

Namespace WorkflowActivity.Scripting.SDKScript
    '''<summary>
    '''Provides one or more methods that can be run when the workflow scripting activity is performed.
    '''</summary>
    Public Class Script1
        Inherits RAScriptClass91
        '''<summary>
        '''This method is run when the activity is performed.
        '''</summary>
        Protected Overrides Sub Execute()
            'Retrieve a reference to the bound entry...
            Dim docInfo as DocumentInfo = DirectCast(Me.BoundEntryInfo, DocumentInfo)
            'Initialize an instance of DocumentExporter...
            Dim exporter As New DocumentExporter()
            'Initialize the byte array, memory stream, and reader...
            Dim image() As Byte
            Dim stream As MemoryStream = New MemoryStream()
            Dim reader As BinaryReader = New BinaryReader(stream)

            'Set the exported image format to tif...
            exporter.PageFormat = DocumentPageFormat.Tiff

            'Export the image to the memory stream...
            exporter.ExportPages(docInfo, docInfo.AllPages, stream)

            'Read the image stream into the byte array...
            image = reader.ReadBytes(stream.Length)

            'Connect to the SQL database...
            Dim connectionString As String = "Data Source=SAMANTHA-PC\SQLEXPRESS; Initial Catalog=LfTest; Integrated Security=True"

            Using connection As SqlConnection = New SqlConnection(connectionString)

                'Build the INSERT statement...
                Dim command As SqlCommand = New SqlCommand("INSERT INTO ExportedImages (DocID, DocName, Image) Values(@DocID, @DocName, @Image)", connection)

                'Set the parameters...
                command.Parameters.Add("@DocID",  SqlDbType.Int).Value = docInfo.Id
                command.Parameters.Add("@DocName", SqlDbType.NVarChar, 255).Value = docInfo.Name
                command.Parameters.Add("@Image", SqlDbType.Image, image.Length).Value = image

                'Open the connection, execute the command, then close the connection...
                connection.Open()
                command.ExecuteNonQuery()
                connection.Close

            End Using

            'Cleanup...
            reader.Close()
            stream.Close()

            image = Nothing
            stream.Dispose
            reader.Dispose
            docInfo.Dispose
            exporter = Nothing

        End Sub
    End Class
End Namespace

 

2 0
replied on October 30, 2014

Hu,

With the SDK (or a workflow script activity) you should be able to export a document from LF as an image to a byte array via a memory stream and then directly write that byte array to a SQL BLOB field.

Are you looking for some code snippets as well?

0 0
replied on May 29, 2016

Hi Cliff,

My requirement is exactly the same. Trying to insert picture into sql. 

So I tried the exact code which you mentioned but I'm  getting an error as given below.

Error 1 )   'BoundEntryInfo' is not a member of 'WorkflowActivity.Scripting.Script.Script1'.    \Script\Script 1.vb  

 

Can you please help me to resolve this ?

 

Thanks in advance

-Rajah

  

 



 

0 0
replied on May 29, 2016

Sounds like you're using a script activity instead of the SDK script one.

1 0
replied on May 29, 2016

Hi Miruna,

Thanks a lot. I didnt notice that. Now its working.

Thanks 

Rajah  

1 0
replied on May 29, 2016

Hi Miruna,

Now i have another error when i execute the workflow <SDK script error>. It says login failed for user.

But  when i execute the script from the script editor it is inserting the record in the database. 

SDK Script error.png
0 0
replied on May 29, 2016

The Windows account that the Worfklow service is running under is likely different than the account that you are running the Designer under.  If your SQL connection string is set to use 'Integrated Security=True' then SQL is trying to use the Windows account that the process is running under.  In this case the account that you are running the Designer under has the appropriate privileges and the account that the Workflow service is running under does not.

You can either add the Windows account that the Workflow service is running under as a trusted account in SQL or you can 'hard code' a UserId and password into the SQL connection string.

Here is a link to a resource for SQL connection strings for some examples...

https://www.connectionstrings.com/sql-server/

0 0
replied on May 29, 2016

Hi Cliff,

As you said, I have changed the Designer's account which has permission to access the server. 

Thanks a lot cliff. It  worked ..

0 0
replied on May 30, 2016

Hi Cliff,

Further testing, I am able to insert the image in sql successfully. refer image <record in sql.png>

But the file size is very small for a tiff file. It shows 94 bytes but the actual file size is 112KB refer  image  <image size.png>. 

Is it the same way it came when you tried ?

The sql database that we are writing is a staging database for a third party application to read. We dont have access to that application. To validate that we are writing the image correctly could you please guide me on how  to view that image from sql ? or  suggest a tool if any to view ?

image size.png
record in sql.png
image size.png (68.84 KB)
0 0
replied on May 30, 2016

The difference in file size would worry me as well.  I would do a quick Google search for exporting an image file from a SQL table to get some ideas.  (It doesn't look like SQL has a native way of viewing image data but it looks like there are SQL scripts that you can run to export the image data back to a local share and then use image viewing software to open the exported file.)

0 0
replied on May 30, 2016

Hi Cliff,

Thank you .. That will be very helpful for me. 

0 0
replied on June 1, 2016

Hi Cliff,

I have some question. Please help me if you know the answers

My question is

  1. I want to know in which format the script is writing the image ?  In the script it is mentioned tiff but we have different formats in tiff itself (ex: tiff B&W, tiff groupiv, tiff color etc) and  
  2. Which base of string its writing ?  In the production side they are treating this data as a  blob represented as a base 64 string.  

 

In my case The file which I am writing through script is very small (around 100 bytes ) which is bit suspicious for a tiff image. Do you have any suggestion regarding this ?

0 0
replied on June 1, 2016

Rajah,

The script as outlined in the thread above is exporting the image into a byte array as a TIFF image with 'CCIT Group IV compression for bitonal images and LZW for other bit depths' (Per the script editor intellisense help text). 

Using the script editor intellisense on the exporter objects it looks like the class also supports TiffJpeg for 'JPEG compression for non bitonal images and CCIT Group IV compression for bitonal images', and TiffFlate 'TIFF with Flate (zlib/RFC1951) encoding'

 

On the SQL side the image is being written to a SQL IMAGE data type which is a binary data type just like the BLOB data type.  It would also appear that MS is deprecating the IMAGE data type in SQL 2016 and is recommending using VARBINARY(MAX) data type instead.  https://msdn.microsoft.com/en-us/library/ms143729.aspx

 

I would tend to agree that there is a problem if the exported byte array for a TIFF is only 100 bytes.  I will work up a test on my demo system and see if I can duplicate the problem.

 

 

0 0
replied on June 2, 2016

Hi Cliff,

I tried changing the datatype in sql as VARBINARY(MAX) and also tried with different file formats in the script but still i am  getting the file size around 500 bytes. 

Is it the same way it worked with your sample script ?

 

Thank you 

-Rajah 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.