Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Friday, March 29, 2024 
 
How to Save & Read Images in a SalesLogix Database  
Description:  Using a connection via the SalesLogix OLE DB provider you are able to save files or images in a SalesLogix database. The provider ensures that these saved files or images will synchonize to remote users once saved in the database. This article will look at writing image files to an account table to store a logo image for each account as well as read the image from the database to display on the account detail screen.

Category:  Architect How To Articles
Author:  Ryan Farley
Submitted:  5/18/2005
   
Stats: 
Article has been read 23402 times

Rating: - 5.0 out of 5 by 7 users
 

fiogf49gjkf0d
How to Save & Read Images in a SalesLogix Database

Using a connection via the SalesLogix OLE DB provider you are able to save files or images in a SalesLogix database. The provider ensures that these saved files or images will synchonize to remote users once saved in the database. This article will look at writing image files to an account table to store a logo image for each account as well as read the image from the database to display on the account detail screen.


Enter the BLOB

Files can be saved into special fields in a database called BLOB fields. Saving a file in a BLOB field (Binary Large OBject) means that you are writing the raw bytes that make up the file into the field as binary. This is nothing at all specific to SalesLogix, most native database systems have this capability although some might refer to these fields as something other than BLOB such as OLE Object fields, or RAW fields, etc. The great part about using BLOB fields with SalesLogix is that since the SalesLogix OLE DB Provider supports writing data to BLOB fields, the data stored in the BLOB will synchronize to remote users just as any other data would. The fact that a file is stored in the field makes no difference.


Creating BLOB Fields in SalesLogix

Before we can get started, we need to create a BLOB field on some table in our SalesLogix database. This is done the same way that any field is created in SalesLogix. Fire up the Admistrator and open the DB Manager. Create a new table or select an existing one and then add a new field. Choose a field type of "Blob". That's it, you're ready to write some data to it. Note that in the sample code in this article I created a 1:1 account table named "C_ACCOUNTIMAGE" and added a BLOB field named "Logo".


Writing a File to a BLOB

We are now ready to save a file into our blob field. If you've worked with BLOB fields using ADO before than you already know everything you need. The only thing to keep in mind is to ensure you're using a connection via the SalesLogix provider so it will synchronize. In the sample code we'll be working with in this article we'll be using VBScript inside a standard SalesLogix plugin, nothing external, so we don't really need to worry about that.

To write a file into a BLOB field we need to follow these steps:
  1. Open an updateable Recordset for the table you are saving the file to.
  2. If needed, add a new record in the Recordset (or just update the current existing record).
  3. Load the file into an ADO Stream object.
  4. Read the stream into a field in the Recordset.
  5. Call Update on the Recordset to save the data to the database.
It really is a simple process. If you're worried about using a Stream or you haven't used one before then don't sweat it. The code is easy. Let's take a look. This function will take a filename and load the file into the BLOB field we created earlier.

Sub SaveImage(ByVal FileName)
Dim rs
Dim stream

    'open a recordset
    Set rs = CreateObject("ADODB.Recordset")
    With rs
         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .LockType = adLockOptimistic
         .Open "select * from c_accountimage where accountid = '" & CurrentID & "'"

         'check to see if a row exists for the CurrentID
         If .RecordCount = 0 Then
             'add a new row for the CurrentID
             .AddNew
             .Fields("accountid").Value = CurrentID
             .Fields("createuser").Value = Application.BasicFunctions.CurrentUserID
             .Fields("createdate").Value = Now
         End If
         .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
         .Fields("modifydate").Value = Now
    End With

    'open a stream to load the file
    Set stream = CreateObject("ADODB.Stream")
    With stream
         .Type = adTypeBinary
         .Mode = adModeReadWrite
         .Open
         .LoadFromFile(FileName)

         rs.Fields("logo").Value = .Read
         rs.Update

         .Close
    End With
    Set stream = Nothing

    rs.Close
    Set rs = Nothing
End Sub    

Like I said, the code is fairly simple and easily understood. All we need now is a way to allow the user to select an image file, then pass the file name off to this function and our image has been saved to the database.

See How to Use Common Dialogs in SalesLogix for more information on prompting the user to select a file. For this sample, we will use the option of using a LookupEdit control to keep things simple.


Reading the File From the BLOB

Reading the file from the BLOB is even easier than it was to save it there. For this example code we will extract the BLOB contents to a file again. The steps are similar to the steps to save the file into the BLOB:
  1. Open a Recordset to the table & record where the file is saved.
  2. Create an ADO Stream object and write into the stream the Value of the BLOB field in the Recordset.
  3. Save the stream to a file.
About as simple as it gets. The following function takes a string parameter indicating the file name where the BLOB contents will be saved:

Function LoadImage(ByVal FileName)
Dim rs
Dim stream

    LoadImage = False

    Set rs = CreateObject("ADODB.Recordset")
    With rs
        'open recordset to the appropriate record        
        Set .ActiveConnection = Application.GetNewConnection
        .CursorLocation = adUseClient
        .Open "select * from c_accountimage where accountid = '" & CurrentID & "'"
    End With

    If rs.RecordCount > 0 Then
        Set stream = CreateObject("ADODB.Stream")
        With stream
            .Type = adTypeBinary
            .Mode = adModeReadWrite
            .Open

            'write field value to stream
            .Write rs.Fields("logo").Value
            'save stream to a file
            .SaveToFile FileName, adSaveCreateOverWrite
            .Close
        End With
        Set stream = Nothing
        LoadImage = True
    End If

    rs.Close
    Set rs = Nothing
End Function    

We now have the BLOB contents written back out to a file. All we need to do now is make it useful by doing something with the file. As mentioned before, we want to allow the user to save a logo image file for each account. We want to allow the user to select and save the image file which we've already done. We now want to display the logo on the account detail screen. What we are going to do is on the Change event of the account detail, write the saved logo to a file (same file every time, we'll just overwrite it each time the user moves records) and then display the file in am image control on the account detail. With the code above we now have the file extracted, we just need to load it into an image control. To do this we need to use the built in VBScript function LoadPicture. The LoadPicture function takes a file name as a parameter and returns an OLE Picture object. So once you have the file extracted it is nothing more than loading the picture into the image control on the account detail screen:

Image1.Picture = LoadPicture(myImageFileName)     

You can run that code, after extracting the file from the BLOB of course, each time the user moves from account to account (using the Change event of the account detail screen). This article contains a sample bundle (see How to Save & Read Images in a SalesLogix Database Sample Bundle or Related Articles section below) which puts all of this to use and results in the following:




Wrapping it up

While this article focused on saving image files to the database, you can really go a lot further than that. You could save sound files, documents, installs, or any type of file to the database using the same code used in this article. However, keep in mind that all of this does need to synchronize. So be cautious to not bloat the database and sync traffic unless it is needed for the solution.

Until next time, happy coding.
-Ryan

 

About the Author

  Ryan Farley
(SalesLogix Business Partner)
Customer FX Corporation

fiogf49gjkf0d

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix since 2001 and believes in sharing with the community. He loves C#, Javascript, Python, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

View Ryan's SalesLogix Mobile Seveloper Series
View Ryan's SalesLogix SData Developer Series
View Ryan's Git for the SalesLogix Developer series



View online profile for Ryan Farley
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Related Articles 
 - How to Save & Read Images in a SalesLogix Database Sample Bundle - Submitted by: Ryan Farley
 - How to Use Common Dialogs in SalesLogix - Submitted by: Ryan Farley

 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Kris Halsrud
 

Re: How to Save & Read Images in a SalesLogix Database
Posted: 5/19/2005 9:32:35 AM
fiogf49gjkf0d
Ryan, another great article. One thing that should be touched on is how to determine the best location to create the file from the BLOB field. Because of user security and different client set ups you may not be sure a directory exists, or if it does that the user has the required rights to that directory.

SalesLogix has two built in functions for determining local paths related to SalesLogix

Application.BasicFunctions.GetAttachmentPath finds the local attachment path for the current user. This is the folder located under the Documents and Settings folder which all users should have rights to
Application.BasicFunctions.GetApplicationPath finds the directory that SalesLogix is installed to (Typically C:\Program Files\SalesLogix) This directory may not accessible to all users depending on their rights

With these two options and knowing the attachment path is better due to security concerns, this should be the choice to use here. So by writing something like this:

sFileName = Application.BasicFunctions.GetAttachmentPath & "\" & "myBLOBFile.tmp"

We can go one step further in this code by introducing the File System Object. This is a visual basic object that exposes a computers file system to scripting
Find more about the FSO at Microsoft's web site: http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vaobjfilesystemobject.htm

To begin with you need to create an instance of the object

Dim FSO
set fso = CreateObject("Scripting.FileSystemObject")

Now one of the methods in the FSO is GetTempName, which basically generates a random temporary filename. The syntax for this is as simple as:

sFileName = fso.GetTempName

Using this we know don't even need to hard code a file name into our script. We can do something like the following to build a function. Notice my function differs from Ryan's a bit in that my function is passed a SQL statement. This allows my function to be used to extract out multiple files in different locations within the database.


Function BlobToFile(ByVal sSQL, ByVal sFieldName)
'Reads a bob DB file to temporary file for viewing purposes
Dim oCon
Dim oRS
Dim oStream
Dim fso
Dim sFileName

set fso = CreateObject("Scripting.FileSystemObject")
sFileName = Application.BasicFunctions.GetAttachmentPath & "\" & fso.GetTempName

' Create the File from a Blob
Set oCon = Application.GetNewConnection
If ChkError ("Error getting connection information:") > 0 Then
Exit Function
End If
Set oRS = CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient
oRS.Open sSQL, oCon
Set oStream = CreateObject("ADODB.Stream")
With oStream
.Type = adTypeBinary
.Mode = adModeReadWrite
.Open
.Write oRS.Fields(0).Value
.SaveToFile sFileName, adSaveCreateOverWrite
.Close
End With
Set oStream = Nothing
oRS.Close
Set oRS = Nothing
Set oCon = Nothing
BlobToFile = sFileName
End Function

Now to use this function we would use the following command

Sub ShowImageFromDB
Dim sFileName
sFileName = BlobToFile ("select logo from c_accountimage where accountid = '" & currentid & "'")
ImgIntro.Picture = loadpicture(sFilename)
End Sub

Now one thing we would also want to add to the code would be a command to delete the temporary file created within our function and used by the loadpicture command. To do this we can use the File System Object again and another method to delete a file, DeleteFile. Adding the following to our code accomplishes this:

Sub ShowImageFromDB
Dim fso
Dim sFileName
set fso = CreateObject("Scripting.FileSystemObject")
sFileName = BlobToFile ("select logo from c_accountimage where accountid = '" & currentid & "'")
ImgIntro.Picture = loadpicture(sFilename)
fso.deletefile sFilename
set fso = nothing
End Sub


Again, not a lot different than what Ryan initially documented but the importance of thinking about using a known valid and accessible directory makes the code a little more universal.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Save & Read Images in a SalesLogix Database
Posted: 5/19/2005 10:32:01 AM
fiogf49gjkf0d
Kris,

Thanks for the comments. SalesLogix actually did make this a little easier - as far as providing a location that could be written to without the user needing any elevated rights. There is now a function under Application.BasicFunctions called "GetPersonalDataPath". This function returns a path that the user is guarnteed to be able to write to. The path is the "SalesLogix" directory under the users "My Documents" folder.

You could safely use this (Application.BasicFunctions.GetPersonalDataPath & "\MyFile.bmp") to write out the file and would not have to worry about permissions or related issues.

-Ryan
 
Brad Corbin
 

Much easier in v7.0
Posted: 10/24/2006 10:24:04 AM
fiogf49gjkf0d
This article is still valid for 6.2, but its now about 1000x easier in SalesLogix v7.0:

1. Add a blob field to the appropriate table
2. Add an image control to the appropriate form
3. Bind the "BinaryStream" property to the blob field
4. Add a button (or a click event, or whatever) that calls one line of code:
image1.OpenPictureFromFile "",True,False

Done.

(arguments of OpenPictureFromFile are FileName, ShowDialog, SizeLimit)

If you want, you can provide the user the ability to clear the image:
image1.Picture = Nothing

Done!
 
susanr
 

Re: How to Save & Read Images in a SalesLogix Database
Posted: 3/29/2007 7:55:58 AM
fiogf49gjkf0d
Brad, Thanks for the tip on OpenPictureFromFile in v7, it's exactly what I was looking for. I've been looking for more documentation on the image object in v7 and haven't found much out there. Do you know where I could find more details on the image functions? Specifically, I'm interested in how to test if an image object exists in the DB and how to limit the size, height & width of the picture.


 
Adam Conde
 

Re: How to Save & Read Images in a SalesLogix Database
Posted: 8/23/2007 5:11:10 PM
fiogf49gjkf0d
Would you happen to know how to read from a SalesLogix created blob field using C#?
What I'm trying to do is extract a Crystal Report from the PLUGIN table using C#. When I do this, it retrieves the file but it is corrupt. When I compare the retrieved file from the actual .rpt file the data is there, it's just all out for order.

Thanks!
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Save & Read Images in a SalesLogix Database
Posted: 8/23/2007 6:31:16 PM
fiogf49gjkf0d
@Adam,

Conversation continued here: http://www.slxdeveloper.com/forum.aspx?forumid=3003&postid=10224

-Ryan
 
Alex G



Re: How to Save & Read Images in a SalesLogix Database
Posted: 10/26/2007 11:55:24 AM
fiogf49gjkf0d
Ryan,

Is it possible to make the same customization for WEB 7.2 form? I mean, using the standard AA things?

Thanks!
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Save & Read Images in a SalesLogix Database
Posted: 10/29/2007 11:40:13 AM
fiogf49gjkf0d
Alexander,

"Is it possible to make the same customization for WEB 7.2 form? I mean, using the standard AA things"

No. It *is* possible to build something like this for the 7.2 web client, but not in AA. You'd have to build this as a custom ASCX SmartPart and add it into the client.

-Ryan
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 3/29/2024 8:28:55 AM