11/21/2024 3:50:37 PM
slxdeveloper.com
Now Live!
|
|
| |
| 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 23859 times
|
Rating:
 - 5.0 out of 5 by 7 users |
|
|
|
fiogf49gjkf0d
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.
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.
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".
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:
- Open an updateable Recordset for the table you are saving the file to.
- If needed, add a new record in the Recordset (or just update the current existing record).
- Load the file into an ADO Stream object.
- Read the stream into a field in the Recordset.
- 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 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:
- Open a Recordset to the table & record where the file is saved.
- Create an ADO Stream object and write into the stream the Value of the BLOB field in the Recordset.
- 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:
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
|
|
|
|
|
|
Rate This Article
|
you must log-in to rate articles. [login here]
|
|
|
|
Please log in to rate article. |
|
|
|
Comments & Discussion
|
you must log-in to add comments. [login here]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| 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. | |
|
| 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 | |
|
| 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! | |
|
| 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.
| |
|
| 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! | |
|
| |
| 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! | |
|
| 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...
|
|
|
|
|
|