VFP General Field contents to a SQL table via SPT?

[Dan777]

Does anyone know how to get the contents of a General field in a Foxpro table, into a SQL table using SQL Passthrough?

In this case, I've got an MS graph in the General field of a foxpro cursor. The foxpro cursor has 4 columns, as follows:
iPK - int
cName - c(20)
cdesc - c(100)
oGraph - General

Then I have a SQL table, called tblUserGraphs, with a similiar structure:
iPK - int
cName - c(20)
cdesc - c(100)
oGraph - image

At run time, the fox cursor, called "csrTemp", has the values and the graph that I need to get to SQL. I want to be able to do something like:

lcSql = [INSERT INTO  tblUserGraphs VALUES (] + ;
	TRANSFORM(liNextID) + [,  '] + ;
	csrTemp.cName + [', '] + ;
	csrTemp.cDesc + [', ] + ;
	csrTemp.oGraph )]
lnResult = SQLEXEC(lnConn, lcSql)

Of course the statement returns a -1, and when looking at the SQL Statement passed to SQL via SQL Profiler, it looks like this:

INSERT INTO tblUserGraphs values ( 9, Gen, 'name9', 'desc9')

So obviously that wasn't working. To see if this was even possible, I tried it using a VFP Remote View and TableUpdate, and that did work. In that case, the SQL Statement passed to SQL came over as:

INSERT INTO dbo.visiongraphs (olegraph,cName,cdesc) VALUES (9, 0x151C3200020000000E001000140022003F03ABFD56697375616C20466F7850726F0..., 'name9','desc9')

(Note that for the sake of not making things more convoluted here, I truncated that 2nd Value in that INSERT statement. Where it says "0x151C32...", the actual value SQL received began with those 69 characters, but carried on to be 11,748 characters!!!)

So this told me that VFP does in fact know how to pass graphs in a General field to SQL, and SQL can receive them. But what the hell are those 11,748 characters, and how do I get them. Apparently they represent the graph. Just to make sure it worked, I pulled the data from that SQL table back into a different VFP cursor, and the graph was still in tact and editable. Are those 11,748 characters the hexidecimal representation of the graph?

Any ideas on how to make this work would make myself and my clients very happy!
Thanks,
Dan

 

[baltman]

A general field does not contain just the graph, but also had other information embedded depending on the application assigned to open it on the PC etc.

The characters you see is the binary characters representing the file plus the addedon information.

Unfortunately there is no easy way to get the data out. If you have only one data/file type and it was put into the general field utilizing the same method any time you just need to know how many characters to strip out to end up with just the file data.

You'd be very hard pressed to find anyone that recommends utilizing the General data type as a Binary Memo field does a much better job of allowing the data to be extracted.

To get a better idea of what you're up against check out:

http://support.microsoft.com/kb/161832/

and
Extract Files from General Fields faq184-5314

Brian