CODEWITHSUNDEEP
c#.netoledbvisual-foxprodbase
Adan Sandoval
Adan Sandoval

Reputation: 466

Memo field from DBF file only returns a few characters using VFP OLE DB Provider for .NET

I am developing a Winforms app that reads info from a .DBF database. I use the VFP OLE DB Provider for regular queries and it works just fine.

But I have come to a point where I need to read an image stored in a Memo field. If I do a regular SELECT query to get the field I only get a string with the value of

ÿØÿà

Which I believe it's part of the metadata for a JPE image but obviously I am missing some info.

What I need to do is extract the info from the database and display it in a PictureBox in a form.

This is the code I use to read info from the DB:

public DataTable SendQuery(string query)
{
    try
    {
        Conn = new OleDbConnection
        {
            ConnectionString = "Provider=vfpoledb;Data Source=C:\Data;Extended Properties=dBASE IV;Collating Sequence=machine;"
        };
        Conn.Open();

        OleDbDataAdapter adapter = new OleDbDataAdapter(query, Conn);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        Conn.Close();
        return ds.Tables[0];
    }
    catch (OleDbException e)
    {
        MessageBox.Show(e.Message + "\nWith error" + e.ErrorCode, "Error de base de datos");
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message, "Error general");
    }
    finally
    {
        Conn.Close(); //Just to be sure
    }
    return null;
}

As I mentioned earlier, this works fine when reading texts and numbers, (even a memo field in which I store large texts) but it's just not working with this particular image in a Memo field.

Just as a note, I am sure that nor the database or the fields are corrupt.

Upvotes: 0

Views: 1165

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

By default a memo field is treated as a string with OleDb. In C# however, unlike the documentation, a string is a ASCIIZ string. It wouldn't read past seeing a \x00 character. You can cast the field to a blob and thus read as a binary value. Here is an example:

VFP code for creating sample data:

CREATE TABLE c:\temp\imageData FREE (id i, ext c(3), filedata m)
INSERT INTO c:\temp\imageData (id, ext, filedata) VALUES (1,'jpg',FILETOSTR('C:\Program Files (x86)\Windows Kits\10\bin\10.0.17763.0\arm64\AccChecker\AccChecker_HelpFiles\image001.jpg'))
INSERT INTO c:\temp\imageData (id, ext, filedata) VALUES (2,'jpg',FILETOSTR('C:\Program Files (x86)\Windows Kits\10\bin\10.0.17763.0\arm64\AccChecker\AccChecker_HelpFiles\image002.jpg'))
INSERT INTO c:\temp\imageData (id, ext, filedata) VALUES (3,'jpg',FILETOSTR('C:\Program Files (x86)\Windows Kits\10\bin\10.0.17763.0\arm64\AccChecker\AccChecker_HelpFiles\image003.jpg'))

Code to read from VFP:

void Main()
{
    var table = SendQuery("select id, ext, cast(filedata as blob) as filedata from imageData");

    foreach (DataRow row in table.Rows)
    {
        var bytes = (byte[])row["filedata"];
        var id = (int)row["id"];
        var ext = (string)row["ext"];

        File.WriteAllBytes(Path.Combine(@"c:\temp", $"test_image{id}.{ext.Trim()}"),bytes);
    }
}

public DataTable SendQuery(string query)
{
    string cnStr = @"Provider=vfpoledb;Data Source=C:\Temp;";
    try
    {
        DataTable tbl = new DataTable();
        new OleDbDataAdapter(query, cnStr).Fill(tbl);
        return tbl;
    }
    catch (OleDbException e)
    {
        MessageBox.Show(e.Message + "\nWith error" + e.ErrorCode, "Error de base de datos");
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message, "Error general");
    }
    return null;
}

PS: You could have used Linq and thus you wouldn't have such a problem (Tom Brothers has a driver for VFP - Linq To VFP).

Upvotes: 1

Related Questions