How to work with Image field type on SQL Server (binary data)?

Level Extreme platform

Subscription

Corporate profile

Products & Services

Support

Legal

Français

Articles

Search:

How to work with Image field type on SQL Server (binary data)?

Vlad Grynchyshyn, January 11, 2001

Text field on SQL Serevr is not good to store binary data because it is not reliable and often SQL Server corrupts binary data in text field. varbinary type could be too short. There are certain difficulties to use image field on SQL Server to store and read binary data in VFP. Image field on SQL...

Summary

Text field on SQL Serevr is not good to store binary data because it is not reliable and often SQL Server corrupts binary data in text field. varbinary type could be too short. There are certain difficulties to use image field on SQL Server to store and read binary data in VFP. Image field on SQL Server mapped to general field in VFP by default, causing some difficulties when using SQL Pass Through functions. The workaround idea is to try to use memo field to update data on SQL server. At the end of this article there is an explanation of how to read/write binary data using ADO - there is a problem too.

Description

In case when you can use remote views, you always can map view's field type to another than default type. In our case - map field from general to memo in view. this also described with samples in following MSDN articles: Q145991 and Q245714. The samples are for Oracle, but the same approch is for SQL Server when using image field type to store binary data. In case when you use SQL Pass-Through functions, there are no easy and obvious ways to solve the problem. It is much better to use remote views. For case when there are no other options but SQL Pass-Through, the workaround we found looks complex, but it is reliable as far as Microsoft will keep the internal format of dbf/fpt files the same. The main idea based on the similarity of the storing data in dbf file for general and memo fields. Both field types data stored by the same way in the fpt file and referenced by index from the dbf file field. Thus we can easy change field format from memo to general and vise versa using simple editing of field type byte in the dbf file. VFP after that still can read and interpret content of the field correctly (tested only for reading). This requires, however, one additional data copying to disk, that might work slower with large binary files than expected. The code below shows example of how to write binary data from file to SQL server image type field, and than how to read these data and put them back into the file. NOTE: assure that you use temporary table in samples with 1 field or memo field with binary data, and that field is the *first* field in that table. This is required because position of field type byte in the DBF file. In the sample m.lnHandle is a handle of opened connection to SQL Server.

<b>* WRITE part</b>
lcFileName = "F:\TWAIN20.ZIP" && this file got corrupted when use text field on SQL server
* create temporary buffer table
if file("F:\ttt.dbf")
  erase ("F:\ttt.dbf")
endif
if file("F:\ttt.fpt")
  erase ("F:\ttt.fpt")
endif
select 0
create table "F:\ttt" FREE (mData M)
* take binary data from file into memo
select ttt
append blank
append memo mData from (lcFileName) overwrite
use
* change memo field type to general to match to image type on SQL Server
ll = fopen("F:\ttt.dbf",12)
fseek(ll,43)
fwrite(ll,'G')
fclose(ll)
* write data to SQL Server
use F:\ttt alias __DATA
lcSQL = 'insert into test values (?__DATA.mData)'
=SQLExec(m.lnHandle,m.lcSQL)
use in __DATA


<b>* READ part</b>
* take data from SQL server
m.lcSQL = 'select mData from test'
=SQLExec(m.lnHandle,m.lcSQL,"TT")

*** here we have general field in 'TT' cursor because image type on SQL Server mapped to general
*** field in VFP.
* save data to temporary table
select TT
if file("F:\ttt.dbf")
  erase ("F:\ttt.dbf")
endif
if file("F:\ttt.fpt")
  erase ("F:\ttt.fpt")
endif
copy to "F:\ttt"
use

* change general field type to memo
ll = fopen("F:\ttt.dbf",12)
fseek(ll,43)
fwrite(ll,'M')
fclose(ll)

* save data from memo field into file
use "F:\ttt" alias TT
lcFileName = "F:\qwerty.zip" && test file name with result from SQL Server
if file(lcFileName)
  erase (lcFileName)
endif
* following commands you can replace also by STRtoFile function
local lH
lH = FCREATE(lcFileName)
FWRITE(lH,TT.mData)
FCLOSE(lH)
use in TT
* here you can compare "F:\qwerty.zip" and "F:\TWAIN20.ZIP" - they should match

Finally, some note about the using ADO. In the ADO, image field from SQL Server is returned into the special type of the ADO field object, which have methods GetChunk and AppendChunk. While these methods work ok in VB, in VFP GetChunk does not work as expected because it returns data in unusual variant array type that VFP cannot handle. To read binary data using ADO in VFP, use ADO stream object: assign data from ADO recordset field to stream, then save these data to disk file using stream object methods. To write data using ADO in VFP, you cannot use stream object. AppendChunk method of ADO field object works ok for that. (UPDATED - information provided by Daniel Gramunt in Message #614679) When binary data passed to SQL Server, they're encoded to match teh format required for SELECT statement in T-SQL. The encoding is simple, though in VFP there are no built-in functionality to do such encoding. You can use the Albert Ballinger's great utility FoxCrypto.fll (see http://fox.wikis.com/wc.dll?Wiki~FoxCrypto~VFP). It has a function called HexEncoder which does exactly the same conversion. It will allow to insert binary data into SQL Server with the following syntax:

? SQLEXEC(nSql, "UPDATE MyTable SET ImageField = " + StrToHex(FILETOSTR(MyBinaryFile)) + " WHERE PkField=1")

Where StrToHex function does required converting using HexEncoder function. This works well and the same approach could also be used to send binary data to a stored procedure.

Vlad Grynchyshyn, Soft Serve
Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experience working with many MS development tools, components, utilities and documentation. Most experience is related to Visual FoxPro, MS SQL Server, Visual Studio and Visual Basic. He is active member of Visual FoxPro and SQL Server community, author of several articles for Universal Thread WEB site and other VFP community forums. He was MS MVP 2001-2002. He has a MS MCP certificate in Visual FoxPro area.

More articles from this author

256-color icons in EXE application compiled by VFP

Vlad Grynchyshyn, May 28, 2002

VFP does not support 256-color icons as project's icon (icon displayed for EXE in Windows Explorer). But 16-color icons usually looks ugly and too simplified. Here is an approach that allows organize 256-color icons for VFP EXE application. The approach is based on use of Resource Hacker appli...

Force positioning of text cursor by right mouse click in EditBox and RTF ActiveX controls

Vlad Grynchyshyn, February 8, 2002

There is a situation often when we need some way to position a text cursor in the text of Editbox or RTF ActiveX controlwhen right click on the control. For example, we want to display a shortcut menu on right click. In menu, user select an option that insert some text into the control on place of c...

Grid reconstruction and how to avoid it

Vlad Grynchyshyn, March 16, 2001

Have you ever found a situation when your grid don't want to behave as you directed in design time? Custom controls in columns lost? Code of columns, headers or controls event not running? Read this FAQ then. Grid reconstruction behavior is a complete removing of all grid's controls and columns a...

How to assign code to event in run-time?

Vlad Grynchyshyn, September 21, 2001

In VFP there are no direct way to assign code to the method or event in run-time mode. However, some workaround is possible with use of additional, 'hook' class. The sample below describes the approach. It is possible to make it generic. The approach is very useful to catch, for example, events of t...

How to display Tool Tips for different parts of the Grid?

Vlad Grynchyshyn, February 1, 2002

In VFP6 Grid value of the ToolTipText property of controls is displayed only for the entire grid or for current control when mouse is over it. Becuase there are a lot of parts in the grid, it is often useful to display tool tip for different parts separately. Unfortunately, VFP interprets grid as a ...

How to freeze (lock) a column in the grid without use of split bar

Vlad Grynchyshyn, February 8, 2002

The approach is based on a simple principle. LeftColumn property of the grid represents a column that is currently the leftmost visible column in the grid with current horizontal scrolling. As we scroll the grid horizontally, this property changes. We can assign this number to the ColumnOrder proper...

How to get form caption font properties

Vlad Grynchyshyn, October 6, 2000

Windows form caption properties stored in the user preferences. You can change them using Windows Control Panel\Display\Appearance. To get these parameters programmatically in VFP, you can use following routine as a sample.

How to make a collection property of VFP object

Vlad Grynchyshyn, February 6, 2001

After some investigation, I managed to make a collection-like property of object. The following is a simple sample of how to organize collection-like access (rough, need to improve it to work as real collection). It is just to show an approach.

How to make a grid appear like a list similar to list box

Vlad Grynchyshyn, June 20, 2001

This article describes how to configure grid to appear as a list similar to the list box or the drop-down list of combo box. It is for appearance only. All other things are as usual for any grid. This is useful for case when need to replace list box by a grid to display more records, because list...

How to make SQL Pass-Through cursor updatable

Vlad Grynchyshyn, April 9, 2002

The SQLEXEC() function in VFP allows downloading of data from any data source through ODBC. Unlike remote views, SQL Pass-Through approach does not provide default (automatic) settings in the returned cursor to update data on the server after changes in the cursor at the client side. Anyway, it is p...

How to organize SUM() for character field in SQL Server SELECT query

Vlad Grynchyshyn, September 4, 2001

There is a little problem for query to get something like a sum() for a character field to concatenate character values from all records in the group. Here is described solution. The approach below have an limitation to max 30 records in group for concatenation. Test it if it will work for more r...

How to put grid on the form programmatically

Vlad Grynchyshyn, February 5, 2001

Suggest Grid1 is a name of grid required to put onto the form, grid should contain 10 columns an display data from the MyAlias alias. Grid should contain a combo box in the second colunm with the name Combo1. Following sample routine will allow you to see a generic idea of how to put grid on the for...

How to refresh grid properly after sorting

Vlad Grynchyshyn, January 26, 2001

I have seen many questions about sorting of a grid by clicking on its header and proper refreshing of grid after sorting. There is a reliable way to sort (index) grid cursor and properly refresh the grid without change of the record number AND with very good looking. I made this in my grid class and...

Office XP smart tags: a VFP look

Vlad Grynchyshyn, September 1, 2002

Hussars Very often VFP applications are associated with Microsoft Office programs. Users want to use the formatting abilities of MS Word to create documents, send letters using Outlook and to carry out statistical analysis of data in Excel. "Smart tags" is a new technology used in O...

SPT cursor fetching and progress displaying for SPT cursor downloading

Vlad Grynchyshyn, February 5, 2002

SQL Pass Through commands often can take a lot of time for running and returning the result set to the client. It is often usefult to show a progress bar in case of such lengthy process. Also, FetchAsNeeded option, available for views, could be very useful for SPT cursors as well. However, how we ca...

VFP Grids, Part I

Vlad Grynchyshyn, August 1, 2001

What is the grid and when to use it? Grid control is a set of the VFP objects that allow representing data in a grid-like scrollable list. Grid consist of the grid object itself and a set of the columns. Each column must have a header object and a control that represent a data in the grid col...

VFP Grids, Part II

Vlad Grynchyshyn, September 1, 2001

In this part, I will discuss about grid columns and header tricks on how to make development with grids go faster. I will also discuss about detecting the exact positioning over grid header or cell. As usual, I have included some tips and warnings. Grid column and header tricks - how to make de...

VFP Grids, Part III

Vlad Grynchyshyn, October 1, 2001

In this part, I will discuss about grid sorting by header click, grid sorting marker (indicator) and resizing of grid column by double click to the width of data. As usual, I have included some tips and warnings. Grid sorting by header click Last time, sorting of information by a click on...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, October 1, 2003

This is the monthly column of Vlad Grynchyshyn SQL Server Tips. This column includes four monthly tips in regards to SQL Server which covers various topics either discussed online on the Universal Thread or sent as a contribution by another person.

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, November 1, 2003

This is the monthly column of Vlad Grynchyshyn SQL Server Tips. This column includes four monthly tips in regards to SQL Server which covers various topics either discussed online on the Universal Thread or sent as a contribution by another person.

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, December 1, 2003

This is the monthly column of Vlad Grynchyshyn SQL Server Tips. This column includes four monthly tips in regards to SQL Server which covers various topics either discussed online on the Universal Thread or sent as a contribution by another person.

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, September 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, August 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, July 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, June 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, February 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, January 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, March 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, May 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, April 1, 2003

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...

Vlad Grynchyshyn SQL Server Tips

Vlad Grynchyshyn, December 1, 2002

Vlad Grynchyshyn is senior developer and project manager at the Ukrainian company "Soft Serve Ltd.", that provides custom-developed software solutions in several areas with use of wide range of development tools and languages. Vlad works in the MS Solutions department of the company, and has experie...