How can I extract a file stored as a BLOB in a FoxPro table?

Question

I have some Foxpro tables, one of which includes a Blob field. I know the data type stored within the Blob (a MapPoint file), but I don't how to extract it, because I don't have FoxPro (not can I easily obtain it).

Is there a way to take the .DBF and .FPT files and extract the MapPoint files stored within?

DaveB · Answer 1 · 2011-11-26T06:29:39.573

You can use C# and ADO.NET to extract the data into files. Here is some sample code:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace SaveFoxProMemoFieldAsFile
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\data\;Collating Sequence=MACHINE;Null=Yes";

            string sqlSelect = "SELECT filedata FROM filelist";
            int fileNumber = 1;
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using(OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sqlSelect;
                    command.CommandType = CommandType.Text;

                    try
                    {
                        connection.Open();
                        using(OleDbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if(reader.HasRows)
                            {
                                while(reader.Read())
                                {
                                    byte[] binaryData = (byte[])reader["filedata"];

                                    FileStream fs = new FileStream(string.Format(@"C:\data\file_{0}.pdf", fileNumber++), FileMode.OpenOrCreate, FileAccess.Write);
                                    fs.Write(binaryData, 0, binaryData.Length);
                                    fs.Close();
                                }
                            }
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            Console.WriteLine("Program execution complete");
            Console.WriteLine("Press any key to end");
            Console.ReadKey();
        }
    }
}

Visit Microsoft OLE DB Provider for Visual FoxPro 9.0 if you need the FoxPro driver.

score 0 · Answer 2 · edited Sep 11 '17 at 20:09

I needed to pull some PDF files that were being stored in what I believe is a memo field in FoxPro. The schema listed the field as LONGVARCHAR.

I was able to use the source code provided, but needed to change the SQL or I would get a casting error.

If I pulled the PDF file out as a string, it would get truncated whenever it ran into a NULL \0 value in the BLOB.

Here is the SQL I used:

string sqlSelect = "SELECT cast(inc_rawdata as Blob) as inc_rawdata, inc_filename  FROM F2_522_SYS_MAP_FILES";

score 0 · Answer 3 · edited May 23 '17 at 11:55

This previous link shows how to connect to VFP from C#. That answer actually shows connection with OleDbProvider which is similar to answer by DaveB, however, VFP is NOT SQLServer connection and uses VFP OleDbProvider which is available at Microsoft's website for VFP

Used in conjunction with each other (connecting to VFP OleDB) and running query. The connection string only needs to point to the physical path where the data resides, and can query from any .dbf in that folder. You do not have to explicitly connect to an actual "database" as VFP implies a path IS the location of the database.

Anyhow, the other question, joined with Dave should be able to get your data available and write it to a stream. The only other caveat is that the file stream that you are writing too, make sure its a binary file. Otherwise, anytime you write a byte that is an {enter} key, might be forced written with a trailing {line feed}. I found that one out the hard way a long time ago writing binary image files when building 2d bar codes and it getting messed up.

How can I extract a file stored as a BLOB in a FoxPro table?

3 Answers3